Setup projects with SQL Server Compact Edition (CE) databases

This article is no longer relevant. In Visual Studio 2008 SP1, setup projects allow you to select the ‘User’s Application Data Folder’ as a deployment target. This is where any SDF files and other application data and content should go. The ‘Common Application Data Folder’ is no longer accessible for write operations in Windows 7 and should not be used.

The application should use System.Environment.SpecialFolder.ApplicationData to obtain the physical path to this folder. The part about the Data Source string below remains valid, however.

It is no longer an acceptable practice to deploy data files to the same location as an application’s executable. Vista’s more strict security model by default assumes read-only access for the files in the Program Files folder. Unfortunately, even the folks at Microsoft seemed to struggle with this change for a while, because the deployment of project files to the Application Data folders is not implemented very well in Visual Studio (2008). I found quite a few instances in various forums on the Web where people stumbled over this issue, but not a single instance of a good answer. I hope this is one here.

The setup project gives the user the option to install the app “for the current user only” or “for all users”. Consequently, the database file would end up in either the current user’s or the All Users application data folder. The setup would have to write this information somewhere so that the application can later retrieve it, when it comes to accessing the database. How else would it know which application data folder to look in?

To avoid this issue, I just wanted to install the database in the All Users/Application Data folder, regardless of whether the application was installed for one user or for all users. I realize, of course, that two users could not install the application on the same computer without overwriting each other’s data. This is such a remote possibility, though, that I don’t want to consider it.

This turned out to be not so easy, and I figured it would be worth-while posting the solution here (in addition to the my answer on Stackoverflow.com). The first piece of the puzzle I got here:

Form_Load(object sender, EventArgs e)
{
  // Set the db directory to the common app data folder
  AppDomain.CurrentDomain.SetData("DataDirectory",
            System.Environment.GetFolderPath
           (System.Environment.SpecialFolder.CommonApplicationData));
}

The DataDirectory property in the AppDomain will be used to expand a place holder in the connection string. The CommonApplicationData special folder points to the holder that the database was installed to.

Now we need to make sure that the data source contains the DataDirectory placeholder. This piece came from here. In the DataSet designer, find the DataSet’s properties, open the Connection node and edit the ConnectionString property to look as follows:

Data Source=|DataDirectory|\YourDatabase.sdf

Then I followed Lyman Enders Knowles’ instructions (from his own answer to the question on Stackoverflow) for how to add the Common Application Data Folder to the setup project, and I placed the database file in that folder.

I then followed Ove’s suggestion from Stackoverflow, i.e. I checked the “Enable ClickOnce Security Settings” and selected “This is a full trust application” in the Visual Studio project settings.

After that, the application deployed fine on Vista and the database file was accessible for both reads and writes.