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>