Deploying SQL CompactDB on Vista

Recently I have been working on an application that stores user specific data using a local database (SQL Server Compact 3.5). However when I came to deployment it turned out that the application works fine on Windows XP machines, however when being deployed on Windows Vista machines – the program would crash whenever it tried to access the database.

After some time the reason for the crashes was discovered, it turned out to be caused by lacking permissions to the installation directory of application. Considering this fact, the crashes were obviously happening due to the incorrect placement of my local database file.

To deploy the application I had create a Setup Project using Visual Studio 2010. In this project I had placed the local database file (store.sdf) along with the rest of the application files in some subdirectory of the “Application Folder” (C:\Program Files). However files that you needs rights to modify needs to be placed in some subdirectory of the users Application Data folder (C:\Users\Peter\AppData).

If you are creating a Setup Project using Visual Studio, you can do the following to place the store.sdf file in the users Application Data folder:

1. If the folder “User’s Application Data Folder” is not visible when selecting the File System View (in the Setup Project) you need to add it by selecting Add Special Folder -> User’s Application Data Folder.
2. Place the store.sdf in the User’s Application Data Folder, to make it more pretty you can create folder for it using [ProductName].
3. Go to the Registry View in the Setup Project and navigate to HKEY_CURRENT_USER\Software\[Manufacturer].
4. Here you add a folder [ProductName] and navigate into this folder.
5. In here you add a new key (String Value) and name it DatabasePath and give it the value [AppDataFolder][ProductName]\Store.sdf

Now you just need to modify your application to read the instantiate the database a bit differently than what you do now (or what I did at least). Previously I instantiated the database with a call like this:

private StoreEntities storeEntities = new StoreEntities();

This needs to be changed to use the path stored in the registry like in the example below:

RegistryKey rk = Registry.CurrentUser.OpenSubKey(@"Software\[Manufacturer]\[ProductName]");
if(rk != null)
{
  StoreEntities storeEntities = new StoreEntities(GetDatabaseConnection(rk)));
}
else
{
  //The database does not exist, quit application or something!
}

public static EntityConnection GetDatabaseConnection(RegistryKey regKey)
{
  SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder();
  sqlBuilder.DataSource = regKey.GetValue("DatabasePath").ToString();

  EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder();
  entityBuilder.Provider = "System.Data.SqlServerCe.3.5";
  entityBuilder.ProviderConnectionString = sqlBuilder.ToString();
  entityBuilder.Metadata = @"res://*/StoreModel.csdl|res://*/StoreModel.ssdl|res://*/StoreModel.msl";

  return new EntityConnection(entityBuilder.ToString());
}

That is it, you can now access your Local Database file, and because it has been placed in the Application Data folder of the user, you are able to access it without your application crashing.

Hope this helps someone out there, and comments, questions and suggestions are welcome.

UPDATE: See this post for further deployment issues.

Best Regards
/Peter

See larger image SQL Server CE Database Development with the .NET Compact Framework (Paperback) By (author) Rob Tiffany List Price: $49.99 USD New From: $32.99 In Stock Used from: $2.26 In Stock