Using SQL Server Compact 4.0 with Desktop Private Deployment and a Setup project (MSI) (part 2)

I my previous post in this 2 part series, I demonstrated how to use SQL Server Compact 4 with Entity Framework 4 in a desktop application, despite the not excellent tools support.
This time I will show how to implement private deployment, and also how to solve other challenges related to installing via a Windows Installer file (MSI), by adding a Visual Studio Setup project to the solution.
First I will configure the project for Private Deployment, and then add a Setup project.For more information on requirements for Private Deployment, see my blog post SQL Server Compact “Private Deployment” on desktop–an overview.

Enable Private Deployment in a project

This includes copying the required SQL Server Compact 4.0 runtime files, and including these as content in the project, and modifying app.config to refer to the Private managed ADO.NET provider.
Locate the files to be copied in C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private.

Copy all files and folders here to the project folder (on my system C:\projects\Chinook\Chinook.WPF).
Select “Show all files” in Solution Explorer in Visual Studio. Your project should now look similar to this:

Include the amd64 and x86 folder in project (right click), including all content and subfolder as Content, Copy Always. Also include the two managed DLL files in the project root (System.Data.SqlServerCe.dll and System.Data.SqlServerCe.Entity.dll). Make sure to specify “Copy Always”, or the files will not be included in the project output. Verify that all files are included by looking in the debug folder after building the project.
You should now have a project structure like this (same set of files in the x86 folder, of course):

Now modify your app.config to refer to the Private ADO.NET provider, which has assembly version 4.0.0.1, not 4.0.0.0 as the on in the GAC. Using this special assembly version provider will prevent assembly probing from picking up a newer version of the provider in the GAC.

<system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.4.0"/>
      <add name="Microsoft SQL Server Compact Data Provider 4.0" invariant="System.Data.SqlServerCe.4.0" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.1, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
  </system.data>

Notice the version on the type entry is 4.0.0.1.
IMPORTANT: If you reference System.Data.SqlServerCe.dll, make sure to reference the 4.0.0.1 version in your project folder!


Verify that the application still runs, and displays data.

Add and configure a setup project to produce a MSI (Windows Installer) file

(This is a little involved, as I will demonstrate solutions to several deployment issues here).



Start by adding a Visual Studio Installer project to the solution, and call it Chinook.WPF.Setup







Create a setup for a Windows application, and add the Content Files and Primary Output from Chinook.WPF:







For this project, we want to include the Chinook40.sdf file and deploy it with our installer. Other options include creating the sdf file at the first application startup. So we add the Chinook40.sdf file as additional file:







Set the Permanent property on the Chinook40.sdf file to True, to prevent it from being removed during uninstall.







In the file system browser in the Setup project, add the User’s application data folder, this is where we want the sdf file placed,as this is a writable location (Program Files folder is not writable):







Now we can set the folder location of the sdf file to this folder:







So the database file will now be installed in the C:\\Users\\<Username>\\AppData\\Roaming\\ folder. Now we need to modify the connection string. We can do this by manipulating the DataDirectory location, as this is part of the connection string. Add a Startup event handler to App.xaml:

<Application x:Class="Chinook.WPF.App"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             StartupUri="MainWindow.xaml" Startup="Application_Startup">
    <Application.Resources>
         
    </Application.Resources>
</Application>

In this handler, add the following code, notice the comments (!):

private void Application_Startup(object sender, StartupEventArgs e)
        {
            // This is our connection string: Data Source=|DataDirectory|\Chinook40.sdf
            // Set the data directory to the users %AppData% folder
            // So the Chinook40.sdf file must be placed in:  C:\\Users\\<Username>\\AppData\\Roaming\\
            AppDomain.CurrentDomain.SetData("DataDirectory", Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData));
        }

Finally, add a shortcut to the application in the User’s Programs Menu file system folder:







Move the shortcut to the User’s Programs Menu folder, and rename to Chinook.







Now build the Setup project (right click and select Build), and test the installer on a system without SQL Server Compact 4.0 installed. .NET 4.0 Client Profile must be installed, however. The setup will not install if this is not the case.



You can test on your development system, by uninstalling the desktop runtime:







Happy deployment!



You can download the completed solution from here:

https://1drv.ms/u/s!AitHcOtLnuVHgwewiWcudEGRkpEt