Create sql server compact file in appdata folder

Question

I am developing a simple piece of software which uses Entity Framework code first and sql server compact 4. At the moment this setup works. Entity framework creates the sql server compact file if it doesn't yet exists. The path to the database is defined from within a connectionstring which is stored inside the app.config file. It is build up like this:

<connectionStrings>
  <add name="DataContext" 
       connectionString="Data source=Database.sdf;"
       providerName="System.Data.SqlServerCe.4.0"/>
</connectionStrings>

However, I want to place the database in a folder within the current user's Application Data folder (the C:\Users\User\AppData\Roaming folder on my win7 machine). I've tried setting the Data source of the connectionstring to something like %APPDATA%\Database.sdf, but this doesn't work, I get an "Illegal characters in path" exception.

I want to stick with the connectionstring method, because I'd like to use a different database for my unit tests than with my actual application. This way it is easy to modify the database by placing an app.config file in the root of the project.

Can someone steer me in the right direction?

Answer 1

Use below:

AppDomain.CurrentDomain.SetData("DataDirectory", Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData));

<connectionStrings>
  <add name="DataContext" 
       connectionString="Data source=|DataDirectory|Database.sdf;"
       providerName="System.Data.SqlServerCe.4.0"/>
</connectionStrings>

Answer 2

    connectionString="Data source=Database.sdf;"

This tells your app to look for Database.sdf in your app’s current working directory; which could be anywhere and may not be writeable. You need to look at a location you specify:

    connectionString="Data source=|DataDirectory|Database.sdf;"

ADO.NET looks for pipe characters in connection strings and expands them to the value of the property of that name in the application’s domain. So what’s the value of the DataDirectory property? It’s supposed to be set by whatever deployed your application:

• .MSI installers set it to the app installation folder. If you allow the user to choose the installation folder, they choose the DataDirectory as well. This is why you should always use |DataDirectory| and never a hard-coded path.
• ClickOnce defines a special data folder in your project.
• Web apps use the App_Data folder.
• The Visual Studio debugger uses the debug folder.

Any Visual Studio files in your project with a “copy to output directory” property will be copied to DataDirectory. In most cases DataDirectory will be a read-only folder. This is fine if your data is read-only, but if you want to write to it you will have to copy your data to a writeable location. Probably the best place is Environment.GetFolderPath( Environment.SpecialFolder.ApplicationData)). There are several ways of doing this:

• If you are creating an empty new data file, just use your API’s standard CREATE DATABASE or new SqlCeConnection() or whatever.
• If you want to start with a pre-populated seed or starter database, include the seed database in your project . In your application startup, check if the database exists in the SpecialFolder.ApplicationData folder and, if not, copy it there.

If you search the web for sample code on creating local database you will run across a lot of bad advice. Do not do the following:

new SqlCeConnection(@"Data source=c:\users\me\myApp\Database.sdf;");  // Do NOT do this!

I hope I don’t have to explain why hard-coding the path to your data is wrong; but be aware that unless you specify a full path in your connection string, the path is relative to your current working directory.

using (var conn = new SqlCeConnection(@"Data source=|DataDirectory|Database.sdf;"))
{
    conn.Open();
    // No No No! This throws an Access Exception for Standard users,
    // and gets deleted when you repair the app!
    var cmd = conn.CreateCommand("INSERT INTO Table (column1, column2) VALUES (@p1, @p2)");
    ...
}

Do not try to modify the data in DataDirectory. Not only is this directory not always modifiable by users, it is owned by the installer not by the user. Repairing or uninstalling the application will delete all the user’s data; users don’t like that. Instead, copy the installed data to a folder writable by the user and make all changes to the copy.

AppDomain.CurrentDomain.SetData("DataDirectory",
    // Wrong, this overwrites where the user installed your app!
    Environment.GetFolderPath(Environment.SpecialFolder.CommonApplicationData));

Do not change the value of DataDirectory in your code, it is set by the installer and if you change it you won’t know where your data was installed. If you are creating an empty database, just open it in your final location. If you are going to make a copy, open the installed database, save it to the user’s location, close the installed database, and open the copy.

I also discourage saving data to Environment.SpecialFolder.CommonApplicationData. This may not be writable by users, and unless there is a very very good reason all users must be allowed to change other users’ data, each user should have their own database.