How do I read the current path of |DataDirectory| from config settings

Question

I'm writing a program that requires the user to select the active database at application startup. I have a Windows Form that will list the databases stored in a sub-folder of ApplicationData specifically for storing the database files. When I create a new database, however, I need to copy the the template database, but can't figure out where it's stored by default.

I've tried:

dpath = ConfigurationManager.AppSettings["DataDirectory"];

I always seem to get a null value in return though. At one point I gave up and figured I could just set the DataDirectory to a folder of my choice, but it seems I'm doing this too late in the execution of my program for it to take effect.

newdpath = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "\\MyAppFolder";

I would appreciate any suggestions on how to either find the location of the database, or set it myself early enough to affect the program operation.

EDIT:

For the second part, I've discovered that I was trying to modify the connection string after a TableAdapter.Fill command had already been performed, thus explaining why it was opening the default database. That mystery has been solved. The first part, however is still a an unknown.

Thank You.

Tester

Answer 1

|DataDirectory| does not come from config settings; you're mixing up three different things:

ConfigurationManager.AppSettings["DataDirectory"]

This comes from config settings; a .config file you have to create and put into your project. This particular setting is the value of the element with key "DataDirectory" in the AppSettings element. This doesn't exist unless you put one in the .config file. Typically this is where you put configuration or startup data that is never changed. You should not put file paths here, as they can be different on the machine users install your database to.

Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)

This is the path to the current user's roaming application data folder defined by the operating system your app was installed on. You cannot change this, it is defined by the OS. You can be sure this folder is writable by the user, will not be deleted if the user uninstalls your application, and will be available if the user roams or logs on from another machine. This is typically where you want to put editable application data.

SqlConnection("Data Source=|DataDirectory|DatabaseFileName.sdf;...")

This is a connection string for an ADO.NET connection. ADO.NET treats vertical bars specially, it looks for an AppDomain data matching the key name between the vertical bars. You can get the same data with:

AppDomain.CurrentDomain.GetData("DataDirectory")

So what writes the value of DataDirectory? It's done by whatever deploys your executable:

• .MSI installers define it as the app's target folder.
• ClickOnce defines a special data folder in your project.
• Web apps use the App_Data folder.
• The Visual Studio debugger uses the debug folder.

Note that .MSI installers can allow the user to change the DataDirectory; this is why you should never hard-code or change DataDirectory, if you do that there is no way to find where your application data was deployed. You typically use the DataDirectory folder for read-only binary data deployed with your executable.

If you need to write to the data deployed with your executable you should first copy it someplace you know the user will be able to write to, such as to Environment.SpecialFolder.ApplicationData, and write to the copy. Not only is DataDirectory not necessarily writable by users, it is part of the deployment and not part of the user data; if you repair or uninstall your executable then DataDirectory gets reinstalled or deleted. Users don't like it when you delete their data, so don't save it to DataDirectory.

Lot

Thank you very much for the clarification. I knew what the ApplicationData directory was, and I now understand that I was confusing the first and 3rd folders from your answer. – Tester

@Dour-High_Arch I tried retreiving 3rd folder as a string, but I keep getting a null. This would be in the context of VisualStudio debugger. I tried the following: string defaultpath = AppDomain.CurrentDomain.GetData("DataDirectory").ToString(); but no luck. Suggestion? – Tester

@PAUL, hmmm... I get DataDirectory null from the debugger as well. Try AppDomain.CurrentDomain.BaseDirectory. Sorry, do not know the difference between DataDirectory and BaseDirectory offhand. – Lot

Someone suggested to me that I use the AppBase folder, but I understand that would only be valid with certain installers. – Tester

Unfortunately, if you allow users to specify the install folder, you have to use a deployment property like DataDirectory or BaseDirectory. – Lot

Answer 2

Put this line before using the ConnectionString, TableAdapter, etc... to create the thing so called DataDirectory.

I have just tested successfully by putting it right before TableAdapter.Fill(DataSet.DataTable);

AppDomain.CurrentDomain.SetData("DataDirectory", @"Full path to your data folder");

I cannot find where and what the DataDirectory is, because it doesn't exist so MSSQL will take the BaseDirectory of AppDomain.CurrentDomain instead.

Ruthenian

Answer 3

The Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) code will return a null value in development every time. The |DataDirectory| is set when the program is installed.

Build the project, then install and test it.

I used that line of code to compact a database in an installed application at runtime.

That code can be set to a variable, as follows...

Dim Beer as string
Beer = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)

This will return the folder path for the INSTALLED |DataDirectory|. Add the database name on with a CStr and another variable...

Dim MyPathA As String = CStr(Beer & "\Workout.mdb")
Dim MyPathB As String = CStr(Beer & "\BackupWorkout.mdb")

Dim JRO As JRO.JetEngine
JRO.CompactDatabase(CStr("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MyPathA), _
CStr("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & MyPathB & ":Jet OLEDB:Engine Type=5"))

On error Errorhandler, 
Errothandler
Kill(MyPathB)

The 1st line is your database, the 2nd line renames it to Backup and compacts it in the same directory. If there is a Backup there, it will trigger the error, which will delete the backup.

After that, say this is button click. Run it all again. Right after the kill line,

Me.Buttonx.PerformClick()

That is how to compact a database in an installed ClickOnce application. Using |DataDirectory| in the code will throw an illegal characters error...

Marlette