Change database path to temp in VS 2015

  • (2 Pages)
  • +
  • 1
  • 2

19 Replies - 2300 Views - Last Post: 22 February 2016 - 04:43 AM Rate Topic: -----

#1 ipman99   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 28
  • Joined: 16-November 15

Change database path to temp in VS 2015

Posted 20 February 2016 - 08:10 AM

hi! I have a database inside my resources, when starting the program automatically it will extract to temp folder. Now I need to connect with it. How can I do that? I tried to change Data Source to temp, but I just don't know how. This is the app.config file in my program. I want to change data source to temp.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="WindowsApplication3.My.MySettings.endbaseConnectionString"
            connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
|DataDirectory
|\endbase.mdb;Jet OLEDB:Database Password=nqq1fdst"
            providerName="System.Data.OleDb" />
    </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
    </startup>
</configuration>


Is This A Good Question/Topic? 0
  • +

Replies To: Change database path to temp in VS 2015

#2 andrewsw   User is offline

  • awks lol ffs
  • member icon

Reputation: 6697
  • View blog
  • Posts: 27,502
  • Joined: 12-December 12

Re: Change database path to temp in VS 2015

Posted 20 February 2016 - 08:41 AM

|DataDirectory| is a substitution string, MSDN reference. You can change what it refers to with
AppDomain.CurrentDomain.SetData("DataDirectory", "C:\somefolder")

If Path.GetTempPath is the correct location then you should use that, it is the one at C:\Users\UserName\AppData\Local\Temp\. "%temp%" is typically the same location, but GetTempPath should probably be used.

Note that temp is not a great choice, unless it is actually temporary.
Was This Post Helpful? 1
  • +
  • -

#3 ipman99   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 28
  • Joined: 16-November 15

Re: Change database path to temp in VS 2015

Posted 20 February 2016 - 09:15 AM

it didn't work. Can you help me, please. Here is what I want to do,
1)I want to hide database inside the exe
2)When opening the program, database will extract to temp
3)Then it will read data from database in temp
4)Database will delete from temp after closing the program.

(This is a dictionary program (language translator)

How can I do that? in VB?

This post has been edited by andrewsw: 20 February 2016 - 09:17 AM
Reason for edit:: Removed previous quote, just press REPLY

Was This Post Helpful? 0
  • +
  • -

#4 andrewsw   User is offline

  • awks lol ffs
  • member icon

Reputation: 6697
  • View blog
  • Posts: 27,502
  • Joined: 12-December 12

Re: Change database path to temp in VS 2015

Posted 20 February 2016 - 09:28 AM

"it didn't work." I believe what I posted does work, and is the answer to the original, specific, question that you asked.

You are now asking a set of different questions, or, more accurately, stating your requirements; so, of course, my previous post may no longer be relevant to the new questions that you are asking.
Was This Post Helpful? 1
  • +
  • -

#5 IronRazer   User is offline

  • Custom Control Freak
  • member icon

Reputation: 1532
  • View blog
  • Posts: 3,859
  • Joined: 01-February 13

Re: Change database path to temp in VS 2015

Posted 20 February 2016 - 09:29 AM

What didn`t work? Show us what you tried.
Was This Post Helpful? 0
  • +
  • -

#6 IronRazer   User is offline

  • Custom Control Freak
  • member icon

Reputation: 1532
  • View blog
  • Posts: 3,859
  • Joined: 01-February 13

Re: Change database path to temp in VS 2015

Posted 20 February 2016 - 09:50 AM

This seems to save an access DataBase file from the resources to the Temp folder when the form is loading and deletes it when the form is closing.
Public Class Form1
    'create a full path to the Temp Folder with your DataBase filename added to the end of it.
    Private DataBaseFile As String = IO.Path.Combine(IO.Path.GetTempPath, "MyDataBase.mdb")

    Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
        Try
            IO.File.Delete(DataBaseFile) 'when the form is closing delete the file
        Catch ex As Exception
            MessageBox.Show("The DataBase file is still in use and can not be deleted")
        End Try
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'when the form is loading write the bytes of the DataBase file to the temp folder
        IO.File.WriteAllBytes(DataBaseFile, My.Resources.MyDataBase)

        'just to check if the file was successfully created
        If IO.File.Exists(DataBaseFile) Then
            MessageBox.Show("DataBase file was successfully saved to:" & vbNewLine & DataBaseFile)
        End If
    End Sub
End Class


Was This Post Helpful? 0
  • +
  • -

#7 ipman99   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 28
  • Joined: 16-November 15

Re: Change database path to temp in VS 2015

Posted 20 February 2016 - 06:27 PM

Thanks, but I know how to do that, I just want to know how to connect database to the program to fill a table with data

This post has been edited by IronRazer: 20 February 2016 - 06:57 PM
Reason for edit:: Removed large quote. Please use the Reply button

Was This Post Helpful? 0
  • +
  • -

#8 IronRazer   User is offline

  • Custom Control Freak
  • member icon

Reputation: 1532
  • View blog
  • Posts: 3,859
  • Joined: 01-February 13

Re: Change database path to temp in VS 2015

Posted 20 February 2016 - 07:42 PM

Well, i wont say i am all that familiar with using databases but, a quick google search turns up quite a few answers on doing this. I guess it will depend on how you added the database to your project.

It appears that the first link, kleinma suggests that if you used the wizard to add the database then you can look in the application settings for the ConnectionString and change it. However, i am not sure if that will work for what you want to do. He also suggest the same thing as andrewsw has already suggested which should work to change the datadirectory at runtime in your code.

Change path of Data Source

How To Change The Connection String saved in My.Settings in VB 2010

How can i change my app.config file's connection string at runtime?
Was This Post Helpful? 2
  • +
  • -

#9 ipman99   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 28
  • Joined: 16-November 15

Re: Change database path to temp in VS 2015

Posted 20 February 2016 - 08:17 PM

ok, connecting string thing didn't work. :helpsmilie:

I'm not very familiar with databases, but If you ever want to hide the database from users, what will you do? I'm looking for a solution like embedding inside exe.

Was This Post Helpful? 0
  • +
  • -

#10 IronRazer   User is offline

  • Custom Control Freak
  • member icon

Reputation: 1532
  • View blog
  • Posts: 3,859
  • Joined: 01-February 13

Re: Change database path to temp in VS 2015

Posted 21 February 2016 - 04:23 AM

Well, as i said, i am not familiar with using databases either but, what is the purpose of hiding it from the user?

What kind of information does the database have in it?
Was This Post Helpful? 0
  • +
  • -

#11 IronRazer   User is offline

  • Custom Control Freak
  • member icon

Reputation: 1532
  • View blog
  • Posts: 3,859
  • Joined: 01-February 13

Re: Change database path to temp in VS 2015

Posted 21 February 2016 - 05:01 AM

Just because i was curious, i threw this together real quick just to test it out. In a fresh WinForm project i added my PartsDataBase.mdb file to the application`s resources and used the code below. It requires 1 Button and 1 RichTextBox on the form.

When the form is loading, it will write the embedded mdb file to a folder in the ApplicationData folder which is a hidden folder where applications keep files they need to use. You can just leave it there or delete it when your app closes.

Then it sets the ConnectionString of the OleDbConnection to the appropriate provider and datasource path. This seems to work for me but, as i said, it is only an example.

Imports System.Data.OleDb
Imports System.IO

Public Class Form1
    Private myConnection As OleDbConnection = New OleDbConnection
    Private dr As OleDbDataReader
    Private DataFolder As String = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), My.Application.Info.AssemblyName)
    Private DataFile As String = Path.Combine(DataFolder, "PartsDataBase.mdb")

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        If Not Directory.Exists(DataFolder) Then Directory.CreateDirectory(DataFolder)

        If Not File.Exists(DataFile) Then
            File.WriteAllBytes(DataFile, My.Resources.PartsDataBase)
        End If

        myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =" & DataFile
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        myConnection.Open()
        Dim Str As String = "SELECT * FROM Parts"
        Dim cmd As OleDbCommand = New OleDbCommand(Str, myConnection)
        dr = cmd.ExecuteReader
        Dim sb As New System.Text.StringBuilder
        While dr.Read()
            sb.Append(dr("Id").ToString)
            sb.Append(", " & dr("PartNumber").ToString)
            sb.Append(", " & dr("Description").ToString)
            sb.Append(", " & CDbl(dr("Cost")).ToString("C2"))
            sb.AppendLine()
        End While
        RichTextBox1.Text = sb.ToString
        myConnection.Close()
    End Sub
End Class


This post has been edited by IronRazer: 21 February 2016 - 05:29 AM

Was This Post Helpful? 1
  • +
  • -

#12 andrewsw   User is offline

  • awks lol ffs
  • member icon

Reputation: 6697
  • View blog
  • Posts: 27,502
  • Joined: 12-December 12

Re: Change database path to temp in VS 2015

Posted 21 February 2016 - 05:29 AM

The first link in IronRazer's previous post is very useful. I'm going to quote a post from it:

Quote

To use an Access database in a VB.NET project you should use the Project menu or right-click your project in the Solution Explorer to Add Existing Item. You navigate to the Access database and, if prompted, you tell VS to copy the file to the project. You can now forget about the original file located on your desktop or wherever because you now have a source file in your project folder and that's the one you care about when it comes to modifying schema or adding default data.

When you add the database to the project, the IDE will automatically generate a connection string and save it in the config file. That connection string will automatically contain "|DataDirectory|" for the folder path of the data file. The whole point of |DataDirectory| is that it resolves to different locations depending on the type of application and it will work without change from debugging to release. For a Windows application not deployed using ClickOnce, it will resolve to the folder the current EXE was run from. As such, as long as your data file is in the same folder as your EXE, the connection string will work. It doesn't matter what folder that is, as long as both files are in the same place.

Finally, It's not actually that source file in the project folder that you use at run time. This is a point that confuses a lot of people but it's quite logical that it's done that way. When you build your app, the IDE transforms the files in the source folder in whatever way is appropriate for each one and then places the output in the output folder, which will generally be somewhere under the 'bin' folder of your project. For the VB files, that transformation is compiling into an EXE while, for the database file, it's just a straight copy. It's that copy that your app connects to at run time. That is so that any changes you make while debugging will not affect the original source file so, when it comes time to release, a new copy of the nice clean source file is output rather than your having to clean out all the rubbish you put in there while debugging.

By default, a new copy is made every time you build your project. That means that, if you run your project and add some data to the database, then change the code and run again, the data you added will have disappeared. That's because the source file was copied over the output file when the project was built again. If you want to keep your test data between runs then you need to edit the Copy To Output Directory property of the data file and set it to Copy If Newer. That way, a new copy will only be made if you make a change to the source file, e.g. add a new table or add some new default data. If you ever want to start with a new, clean database for your testing you simply get rid of the data file in the output folder and replace it with a new copy of the source folder. The easiest way to do that is to set Copy To Output Directory back to Copy Always for just one run.

So it seems that a way to approach this is to use Copy Always (for Copy To Output Directory), rather than manually copying and removing the database. This way, every time the user runs the application they get a new copy of the original database.

This doesn't address the hiding issue though. It is simply a way to obtain a fresh copy of the database each run. You could add a Database Password (Encrypt with Password) to the database but, wherever you locate it, it will always be accessible to a savvy user, during the time that your application is running.

If it is the design, and any code within the database, that is of concern then you could investigate making an ACCDE.
Was This Post Helpful? 1
  • +
  • -

#13 IronRazer   User is offline

  • Custom Control Freak
  • member icon

Reputation: 1532
  • View blog
  • Posts: 3,859
  • Joined: 01-February 13

Re: Change database path to temp in VS 2015

Posted 21 February 2016 - 05:41 AM

@ andrewsw

I don`t know how but, somehow i guess i missed the part you just pointed out. This whole database thing is new and confusing to me. I have been trying to figure some of it out recently though. I feel like a beginner again. 8)
Was This Post Helpful? 0
  • +
  • -

#14 andrewsw   User is offline

  • awks lol ffs
  • member icon

Reputation: 6697
  • View blog
  • Posts: 27,502
  • Joined: 12-December 12

Re: Change database path to temp in VS 2015

Posted 21 February 2016 - 05:58 AM

I don't find it straight-forward ;), each time a question is asked I seem to be almost starting my investigations from scratch.

Basically, MS Access is a poor choice, both as a distributable resource and, especially, with any attempt to place it behind a server. For a distributable database I would consider SQLite or SQL Server Compact Edition (even though MS isn't encouraging this option). Firebird also appeals to me, although I haven't pursued this interest.

MS, I believe, encourages SQL Server Local Database rather than the (supposedly deprecated) MS SQL Server Compact Edition (CE). Personally, I have a strong suspicion that SQLite et al. would be easier to work with and distribute/configure than SQL Server Local Database (and are probably smaller as well).

I wouldn't call myself an expert though, this is just my assessment ;)
Was This Post Helpful? 0
  • +
  • -

#15 andrewsw   User is offline

  • awks lol ffs
  • member icon

Reputation: 6697
  • View blog
  • Posts: 27,502
  • Joined: 12-December 12

Re: Change database path to temp in VS 2015

Posted 21 February 2016 - 06:05 AM

Get to know Firebird in 2 minutes

Quote

The embedded version is an amazing variation of the server. It is a fully featured Firebird server packed in just a few files. It is very easy to deploy, since there is no need to install the server. It is ideal for CDROM catalogs, demos or standalone desktop applications.

Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2