Using the Microsoft Desktop Stack – Part 1: Setting up SQL Compact 4.0 for Private Deployment

Introduction

Microsoft has revamped its desktop application stack in the past few years, moving from WinForms to WPF, from ADO.NET to the Entity Framework, and from the Jet database engine to SQL Server Compact Edition. This series of articles explains how to use the stack and presents checklists for implementing it.

The series consists of three articles:

• Part 1: Setting up SQL Compact 4.0 for Private Deployment
• Part 2: Using Entity Framework 4.0 with SQL Compact 4.0
• Part 3: Using Entity Framework 4 in an MVVM Application

Parts 1 and 2 contain checklists for setting up SQL Compact and Entity Framework for a desktop application. Part 3 shows how to integrate Entity Framework 4 into a WPF application using the MVVM pattern. The demo app is included with Part 3. The series assumes the developer is working with Visual Studio 2010 (VS 2010), Entity Framework 4.0 (EF 4), and SQL Server Compact Edition 4.0 (SQL Compact 4).

The first step in working with the MS Desktop Stack is typically to create a project and configure it to support SQL Compact 4.0. This first article in the series describes how to set up SQL Compact 4.0 for ‘private deployment’ with a Windows desktop application. You can find additional information on this subject in the SQL Server Compact Development Guide:

• Building Applications for Desktops (SQL Server Compact)
• Private Deployment vs. Central Deployment (SQL Server Compact)

Private deployment refers to installing the SQL Compact 4.0 runtime in your application’s output folder, rather than relying on a global installation on the end user’s machine. It eliminates the need for a separate installation of SQL Compact on the end user’s machine, and it avoids the versioning problems that can arise if a global installation of SQL Compact is upgraded to a later version. The private deployment referred to in this article includes the support files required to use Entity Framework 4.0 with SQL Compact 4.0.

This article also explains how to include SQL Compact database files in a SQL Compact installation. The procedure differs depending on the role of the database file to be included, and it can present unusual and challenging issues.

The checklist in this article assumes that you are developing on a 64-bit machine. In that case, the global SQL Compact 4.0 installation folder on your development machine will contain 32 and 64-bit versions of SQL Compact 4.0. The checklist shows how to install both versions on a target machine; the correct one will be selected automatically at run time.

Step 1: Configure the Project

The first step in setting up a SQL Compact 4.0 private deployment is to create the required folder structure in the target solution.

Step 1a – Create a project library folder: First, if you haven’t already done so, create a project library folder in the root folder of your VS 2010 solution. In the examples below, the library folder is named Library.

Step 1b – Create a SQL Compact subfolder: Next, create a subfolder under the Library folder to hold the SQL Compact 4.0 assemblies. In the examples below, this library folder is named SqlCompact.

Step 1c – Create support assembly subfolders: Finally, create two subfolders under the SqlCompact folder:

• AMD64
• X86

These folders will hold the support assemblies for SQL Compact 4.0. The folders are required to have the names shown above, although the names are not case-sensitive.

Step 2: Copy Assemblies

On a 32-bit machine, the SQL Compact 4.0 assemblies are installed in the Program Files folder. On a 64-bit machine, the assemblies are installed in that folder, and in the Program Files (x86) folder. The sets of files in both of these folders are identical, so whether you are developing on a 32-bit or 64-bit machine, you can get the files to copy from the Program Files folder.

Note that a private deployment of SQL Compact 4.0 requires slightly different versions of the SQL Compact 4.0 assemblies than a global installation. The private deployment versions of the assemblies are found in the following subfolder: C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private. All files (main and supporting assemblies) have to be copied from this subfolder.

MSDN documents the files that must be copied to the target solution at the bottom of this page.

Step 2a – Copy main assemblies: Copy the two main SQL Compact assemblies from the Private folder to the solution SqlCompact folder created in Step 1:

• System.Data.SqlServerCe.dll (the SQL Compact 4.0 main assembly)
• System.Data.SqlServerCe.Entity.dll (the Entity Framework 4.0 data provider)

The SqlCompact folder should now look like this:

Step 2b – Copy the 64-bit C++ runtime assembly: Go to the following folder on the development machine: C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private\amd64.

Copy the following folder from that folder to the AMD64 folder in the target solution:

• Microsoft.VC90.CRT (the Microsoft C++ runtime assembly)

Copy the folder (and its contents) rather than just the contents of the folder. The folder contains the 64-bit Microsoft C++ runtime that is required when running SQL Compact under certain versions of Windows.

Step 2c – Copy 64-bit support assemblies: Copy the following four files from the same folder to the AMD64 folder in the target solution:

• sqlceer40EN.dll (SQL Compact 4.0 Native Error Strings and Resources)
• sqlceme40.dll (SQL Compact 4.0 Managed Extensions)
• sqlceqp40.dll (SQL Compact 4.0 Query Processor)
• sqlcese40.dll (SQL Compact 4.0 Storage Engine)

The AMD64 project libraries folder should now look like this:

Step 2d – Copy the 32-bit C++ runtime assembly: Now we will copy the 32-bit versions of the C++ runtime. Go to the following folder on the development machine: C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private\x86.

Copy the following folder from that folder to the X86 folder in the target solution: Microsoft.VC90.CRT (the Microsoft C++ runtime assembly).

As before, copy the folder (and its contents), rather than just the contents of the folder.

Step 2e – Copy the 32-bit support assemblies: As before, copy the following four files from the same folder to the X86 folder in the target solution:

• sqlceer40EN.dll (SQL Compact 4.0 Native Error Strings and Resources)
• sqlceme40.dll (SQL Compact 4.0 Managed Extensions)
• sqlceqp40.dll (SQL Compact 4.0 Query Processor)
• sqlcese40.dll (SQL Compact 4.0 Storage Engine)

The X86 project libraries folder should now look like this:

At this point, we’ve got the files we will need for deployment in the project libraries folder. Now we turn to configuring the VS 2010 project to use SQL Compact 4.0.

Step 3: Add References

The next step in the process of setting up a SQL Compact 4.0 private deployment is to add references to the SQL Compact main assemblies to your project, and configuring those references.

Step 3a – Add references to main assemblies: We need to manually add references to the main SQL Compact assemblies to the project References list in the VS 2010 Solution Explorer. Right-click the References node in Solution Explorer and select Add reference from the context menu. Click the Browse tab and navigate to the SQL Compact folder in the project libraries folder:

Select the following files and click OK.

• System.Data.SqlServerCe.dll (SQL Compact 4.0 main assembly)
• System.Data.SqlServerCe.Entity.dll (Entity Framework 4.0 data provider)

Step 3b – Configure references: Select each reference from the preceding step and set its Copy Local property to True. This tells VS to copy the assemblies to the application output directory, despite the fact that SQL Compact is globally installed on your development computer. When VS 2010 builds your project, it will copy the assemblies to the application output directory, and it will also add them to the Detected Dependencies list in the setup project for your application.

Step 4: Add a Post-Build Event

There are a couple of ways to add the SQL Compact runtime support DLLs to the project. One is to add them explicitly, by creating folders in the project and creating links in these folders to the library files. Another is to use a post-build event to copy the files to the application output folder. I prefer to use the second method, because it reduces clutter in the project tree.

Step 4a – Add post-build event: When the solution builds, VS 2010 needs to create the AMD64 and X86 folders in the application output folder, then copy the support DLLs to these folders.

The main SQL Compact 4.0 assemblies are automatically copied to the application output folder, because the main project has references to these assemblies, in which the Copy Local property was set to True. We use a post-build command to copy the other files.

The post-build command is a batch command that we enter in the Build Events page of the target project’s property pages:

The batch command to create the required folders and copy the support DLLs looks like this:

xcopy /s /y "$(SolutionDir)Library\SqlCompact\*.*" "$(ProjectDir)$(OutDir)"

The /s switch specifies that subfolders and their contents should be copied, rather than just the files at the root level of the directory. The /y switch specifies that any existing copies of the files should be overwritten without prompting. If this switch is omitted, then the command will fail—xcopy will attempt to prompt for overwrite, which will crash the command.

Step 4b – Test post-build event: Perform a test build after the commands are entered. Verify that the project builds successfully. If the commands fails, it is probably due to a bug in the path. The VS 2010 error message will show the resolved path to the two folders. The paths are easier to work with if you copy the error message from VS 2010 to Notepad. In Notepad, you can copy each resolved path from the error message and test it in Windows Explorer to find the bug in the path. It is usually a misplaced space or something similar.

Step 5: Configure the App.config File

Step 5a – Add markup to App.config: We need to add a bit of workaround markup to the App.config file to enable SQL Compact to use Entity Framework 4. First, add the following markup to the <configuration> section of App.config:

<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>

This markup is explained in this blog post by Erik Jensen. Note that the version reference points to Version 4.0.0.1. That is the private deployment version of the SQL Compact runtime. Using this special assembly version provider will prevent assembly probing from picking up a newer version of the provider in the Global Assembly Cache.

Step 6: Configure the Installer

This step will typically be performed sometime after the initial configuration of your project, when you need to prepare a release of your project.

Step 6a – Remove SQL Compact from setup prerequisites: Select the root node for the setup project for your application in VS 2010, and select Properties from the context menu. Click the Prerequisites button on the project Property Pages dialog. If SQL Server Compact 4.0 is specified as a prerequisite for the app, then the Windows Installer will globally install it on the target machine. We are going to run SQL Compact from a private installation, which means we don’t want SQL Server Compact 4.0 to be globally installed. So, uncheck the item, if it is checked.

Step 6b – Create folders for support files in the setup project: In the setup project, open the File System Editor and create two folders under the Application Folder node:

• AMD64
• X86

Under each of those two folders, create the following folder:

• Microsoft.VC90.CRT

Step 6c – Add 64-bit support DLLs: In the File System Editor, right-click the AMD64 folder you created in Step 6b and select Add > File from the context menu. Navigate to the AMD64 subfolder in your project library folder and select all four support DLLs in that folder, then click Open.

Step 6d – Add 64-bit C++ runtime: In the File System Editor, right-click the AMD64\Microsoft.VC90.CRT folder you created in Step 6b and select Add > File from the context menu. Navigate to the same subfolder in your project library folder and select all three files in that folder, then click Open.

Step 6e – Add 32-bit support DLLs: In the File System Editor, right-click the X86 folder you created in Step 6b and select Add > File from the context menu. Navigate to the X86 subfolder in your project library folder and select all four support DLLs in that folder, then click Open.

Step 6f – Add 32-bit C++ runtime: In the File System Editor, right-click the X86\Microsoft.VC90.CRT folder you created in Step 6b and select Add > File from the context menu. Navigate to the same subfolder in your project library folder and select all three files in that folder, then click Open.

When you have finished configuring the File System Editor, it should look like the following screenshots:

• AMD64 folder selected:



• AMD64\ Microsoft.VC90.CRT folder selected:



• X86 folder selected:



• X86\Microsoft.VC90.CRT folder selected

The folder structure in the File System Editor should mirror the structure in your application output folder. Note that when you add your project’s application output to the Application Output folder, VS 2010 will automatically detect System.Data.SqlServerCe.dll and System.Data.SqlServerCe.Entity.dll, and it will add these components to the root level of the Application Folder in the File System Editor.

Step 7: Add a Database File Template to the Deployment Project

The next step is to add a database file to the installer. There are two situations that require this step:

• Template file: The application uses multiple database files and allows the user to create new files. Database files are selected by an Open File dialog, and new files are created using a New File dialog. The application uses xcopy to create a new file from a template (essentially, an empty SDF, configured for the application). The application needs to install the template file with the application for xcopy operations when a new file is requested.
• Unitary database file: The application uses a single database file, much like a SQL Server or a SQL Express application. The application needs to install the database file with the application for read and write operations.

Neither type of file can be stored in the Application Folder on the target machine, because Windows won’t let us access c:\Program Files for data storage or xcopy operations.

Step 7a – Add ProgramData folder: File templates can be stored in the Windows special folder named ‘Common Application Data Folder’, which maps (in Windows Vista and later) to c:\ProgramData. An application only needs read-only access to a template file to perform an xcopy on the file, so the CommonAppDataFolder works fine for this purpose.

To add the file template, go to the File System Editor in the deployment project:

In the left pane (see 1 above), right-click on ‘File System on Target Machine’ and select ‘Add Special Folder > Custom Folder’ from the context menu that appears. In the Properties pane (see 2 above), set the DefaultLocation property to [CommonAppDataFolder].

Step 7b – Add template file to ProgramData folder: Then, add the file template to this folder, in the same manner as above, when you added the SQL Compact support DLLs.

Step 8: Add a Unitary Database File to the Deployment Project

By default, users have read-only access to c:\ProgramData under Windows 7. In my opinion, that renders the folder unsuitable for storing unitary database files, which need read-write access. Privileges can be elevated for the app on that folder, but I avoid doing so at all costs.

The most sensible installation target for a read-write database is the Windows ‘public documents’ folder. In Windows Vista and later, the public documents folder is c:\Users\Public\Documents. .NET provides an environment variable, Environment.SpecialFolder.CommonDocuments, which maps to this folder, so it is easy to access from C# code.

Unfortunately, the Windows Installer doesn’t support the public documents folder—neither as a special folder, nor as a special folder the way we handled the ProgramData folder above. We can work around this limitation by having the installer look up the path to the public documents folder in the Registry on the installation machine, assigning that path to an installer variable, and then passing the variable to a custom folder.

Step 8a – Look up the location of the public documents folder: The first step is to look up the path to the public documents folder in the Windows Registry on the installation machine. We will use an installer search function to perform this task. Open the Launch Conditions Editor in your Visual Studio deployment project:

In the left pane (see 1 above):

• Right-click 'Search Target Machine' and select 'Add Registry Search' from the context menu.
• A new item will appear. Name it Get Common Documents Folder.

In the properties pane (see 2 above):

• Set the 'Property' property (the name of our variable) to COMDOCFOLDER;
• Set the 'Root' property (the Registry root key to search) to vsdrrHKLM; and
• Set the 'RegKey' property (the Registry key to find) to SOFTWARE\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders. Notice that we omitted the root key from the 'RegKey' property.
• Finally, set the 'Value' property (the name of the value we are searching for within the Registry key) to Common Documents.

The COMDOCFOLDER variable will now hold the path to the public documents folder.

Step 8b – Create a custom folder that points to the public documents folder: The next step is to create a custom folder that points to the path contained in the COMDOCFOLDER variable. Go to the File System Editor in the Visual Studio deployment project:

In the left pane (see 1 above):

• Right-click 'File System on Target Machine' and select 'Add Special Folder > Custom Folder' from the context menu.
• A new item will appear. Rename the item Common Documents.

In the properties pane (see 2 above):

• Set the 'Property' property to COMDOCFOLDER.
• Set the 'DefaultLocation' property to a fallback value to use in case the COMDOCFOLDER property returns a null value—this should never happen. I used a hard-coded path to the public documents folder, as found on Windows Vista and later. That way, the installer would still work on those machines.

The installer now has a Common Documents folder that points to the Windows public documents folder, as specified in the Windows Registry. The database application can use the .NET Environment.SpecialFolder.CommonDocuments variable to locate the database file in the Windows public documents folder.

Step 8b – Add unitary database file to ProgramData folder: Then, add the file template to this folder, in the same manner as above, when you added the SQL Compact support DLLs.

Step 9: Test the Deployment Project

Test the deployment project by compiling it and running it on a machine that does not have a global installation of SQL Compact 4.0. Verify that the SQL Compact 4.0 runtime files are copied to the correct folders in the application folder on the target machine.

Conclusion

The second part of this series will show how to use Entity Framework 4 with the SQL Compact 4 installation created in this article. The third part of the series will present a demo app that integrates the resulting EF 4/SQL Compact 4 framework into a WPF application designed around the MVVM pattern. As always, I welcome your comments and suggestions for improving this series. I find that the peer review provided by CodeProject readers is invaluable, and it is always appreciated.