Introduction
This article is a very simple “How To” that describes, step by step, how to create a private installation of SQL Server Compact Edition.
The procedure was developed for SQL Compact 3.5 SP2, and it supports Entity Framework 4. The procedure supports both 32-bit and 64-bit installations of SQL Compact. The procedure was developed in Visual Studio 2010.
The procedure outlined in this article was developed from a blog post by Steve Lasker and an MSDN troubleshooting article. The procedure diverges from the Lasker procedure in a couple of respects:
- Lasker imports the SQL Compact support DLLs into the project. The procedure in this article uses a post-build event instead, to reduce clutter in the project tree.
- Lasker's procedure is based on Windows ClickOnce technology. The procedure in this article uses the more tradional Windows Installer technology.
We are in the midst of a transition from a 32-bit to a 64-bit world. SQL Compact supports both types of installations, and it will choose the correct DLLs for the type of machine on which it is installed. If you are developing on a 64-bit machine, the global installation of SQL Compact 3.5 SP2 should include both a 64-bit version (in the Program Files folder) and a 32-bit version (in the Program Files (x86) folder). The procedure in this article installs both versions of the SQL Compact support DLLs in separate folders. If you are developing on a 32-bit machine and don't have access to the 64-bit support DLLs, you can omit the separate folders and install only the 32-bit versions. However, I don't recommend that approach, since our applications tend to have longer lives that we anticipate. Anything we write today is likely to survive well into the 64-bit age.
Public vs. Private Deployment
SQL Compact has been getting a lot more attention in the past year or so. Microsoft recently annointed it as the preferred database technology for desktop applications, and it works pretty well with Entity Framework 4. The official method of deploying SQL Compact is a global installation on the user's machine, just like SQL Express. But a lot of developers, including me, think that approach presents several disadvantages:
- First, it's another install that the user has to perform. In my experience, users hate installs, and they hate infrastructure installs most of all.
- Second, the version of SQL Compact that my application was developed against is liable to be removed from the user's machine. For example, the user may upgrade SQL Compact down the road. There is a risk that the upgrade will break my app.
For these reasons, many developers prefer to use a private installation of SQL Compact. A private installation can be performed by your app's installer in your application folder, and the user need not even know that SQL Compact is there. Your version of SQL Compact will work with your app, and your app only. The app won’t be broken by another installation on SQL Compact (even a public one), and your installation won’t break any other apps.
Private Installation Procedure
Here is the procedure for creating a private installation of SQL Compact SP2. The procedure assumes that you first copy the required DLLs from the global installation on your development machine to a Libraries folder at the root level of your solution folder, rather than working directly with the DLLs in the global installation. The advantage to this approach is that you will always have the DLLs your app was developed against. For example, if I upgrade my development machine to SQL Compact 4.0 when it is released, I will still have the Version 3.5 SP2 DLLs that I used in my app, in the Libraries folder for my app.
Step 1 – Create project libraries folders
First, we create folders in the project’s Libraries folder to hold the SQL Compact runtime. Create a Libraries folder at the root level of your solution, and create a SqlCompact folder within that one. Within the SqlCompact folder, create two subfolders, AMD64 and X86.
Step 2 – Copy main assemblies
Next, we copy the two main SQL Compact assemblies to the Libraries folder. These files are 32/64-bit agnostic, so we can copy them from ether the Program Files folder, or from the Program Files (x86) folder. Since we can use either version, we will arbitrarily choose the Program Files folder and copy them from there. But note, however, that we must copy the versions found in the Private subfolder. They are special versions of the assemblies that are designed for use in private deployments.
Go to the following folder on the development machine:
C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Private
Copy the following files from that folder to the root level of the SqlCompact project libraries folder:
- System.Data.SqlServerCe.dll
- System.Data.SqlServerCe.Entity.dll
Note that if you aren't using Entity Framework 4, you can omit the reference to System.Data.SqlServerCe.Entity.dll, here and below. The SqlCompact folder should now look like this:
Step 3 – Copy 64-bit support DLLs
The SQL Compact runtime requires seven support DLLs, which are separately compiled for 32 and 64-bit platforms. We will copy the 64-bit versions first. Go to the following folder on the development machine:
C:\Program Files\Microsoft SQL Server Compact Edition\v3.5
Copy the following files from that folder to the root level of the AMD64 project libraries folder:
- sqlceca35.dll
- sqlceca35.dll
- sqlcecompact35.dll
- sqlceer35EN.dll
- sqlceme35.dll
- sqlceoledb35.dll
- sqlceqp35.dll
- sqlcese35.dll
The AMD64 project libraries folder should now look like this:
Step 4 – Copy 32-bit support DLLs
Now we will copy the 32-bit versions of the runtime support DLLs. Go to the following folder on the development machine:
C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v3.5
Copy the same seven files from that folder to the root level of the X86 project libraries folder:
- sqlceca35.dll
- sqlceca35.dll
- sqlcecompact35.dll
- sqlceer35EN.dll
- sqlceme35.dll
- sqlceoledb35.dll
- sqlceqp35.dll
- sqlcese35.dll
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 deployment.
Step 5 – Add references to main assemblies
We need to manually add references to the main SQL Compact assemblies to the project References list in the Visual Studio 2010 Solution Explorer. Note that, as we saw above, these assemblies originated in the Private folder of the SQL Compact installation on the development machine.
Right-click the References node in Solution Explorer and select Add reference from the context menu. Click the Browse tab and navigate to the SqlCompact folder in the project libraries folder. Select the following files and click OK.
- System.Data.SqlServerCe.dll
- System.Data.SqlServerCe.Entity.dll
In the References node, select both references; then select the property grid. Change Copy Local = True. This tells VS to copy the assemblies to the application output directory, even though SQL Compact is installed on your development computer. When Visual Studio 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 6 – Add post-build event
There are a couple of ways to add the SQL Compact runtime support DLLs to the project. The first is to add them explicitly, by creating folders in the project and creating links in these folders to the library files. That's the approach Steve Lasker used in his blog posting. The second is to use a post-build event to copy the files to the application output folder. We are going to use the second method, because it reduces clutter in the project tree.
To add the files, we will need to create AMD64 and X86 folders in the application output folder; then we will copy the support DLLs to these folders. Here are the batch commands to do that:
mkdir "$(ProjectDir)$(OutDir)AMD64"
mkdir "$(ProjectDir)$(OutDir)X86"
copy "$(SolutionDir)Libraries\SqlCompact\AMD64\*.*" "$(ProjectDir)$(OutDir)AMD64"
copy "$(SolutionDir)Libraries\SqlCompact\X86\*.*" "$(ProjectDir)$(OutDir)X86"
The batch commands should be entered in the Post-build event command line box in the Properties pages for the main project:
Step 7 – 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 at the end of the <configuration> section of App.config:
<system.data>
<DbProviderFactories>
<remove
invariant="System.Data.SqlServerCe.3.5"></remove>
<add name="Microsoft
SQL Server Compact Data Provider"
invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data
Provider for Microsoft SQL Server Compact"
type="System.Data.SqlServerCe.SqlCeProviderFactory,
System.Data.SqlServerCe,
Version=3.5.1.0, Culture=neutral,
PublicKeyToken=89845dcd8080cc91"/>
</DbProviderFactories>
</system.data>
Steve Lasker’s blog entry explains the reasoning behind this markup.
Next, add the following markup just below the previous fragment:
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="System.Data.SqlServerCe"
publicKeyToken="89845dcd8080cc91" culture="neutral"/><bindingRedirect
oldVersion="3.5.1.0-3.5.1.50" newVersion="3.5.1.50"/>
</dependentAssembly>
</assemblyBinding>
</runtime>
The MSDN Troubleshooting article explains the reasoning behind this markup.
Step 8 – Remove SQL Compact from setup prerequisites
Select the root node for the setup project for your application in Visual Studio 2010, and select Properties from the context menu. Click the Prerequisites button on the project Property Pages dialog. If the entry for SQL Server Compact 3.5 SP2 is checked, it means that SQL Compact will have to be globally installed on the target machine in order to run the app. We are going to run SQL Compact from a private installation, so uncheck the item, if it is checked.
Step 9 – Create folders for support files in setup project
In the setup project, open the File System Editor and create two folders under the Application Folder node:
The File System Editor should look like this:
Step 10 – Add 64-bit support DLLs
In the File System Editor, right-click the AMD64 folder you created and select Add > File from the context menu. Navigate to the AMD64 subfolder in your program libraries folder and select all seven support DLLs in that folder, then click Open.
Step 11 – Add 32-bit support DLLs
Repeat Step 10 for the 32-bit DLLs to copy them from the X86 folder in your program libraries folder to the X86 folder you created in the setup project.
Step 12 – Compile
Compile the solution in Visual Studio. Assuming all is well, your app should compile without any errors. However, you will see seven warnings for the versions of the support DLLs that don't correspond to the platform (32-bit or 64-bit) to which you have targeted your app. The warnings will look like this:
You may ignore these warnings, since the SQL Compact runtime is intelligent enough to select the correct set of support DLLs for the system on which it is installed.
Conclusion
Hopefully, this article has clarified some of the confusion surrounding private installations of SQL Compact. Keep in mind that the procedure described in this article works with SQL Compact 3.5 SP2; it may not work with SQL Compact 4.0, when that version is released.
As always, I am looking for reader feedback. The peer review provided by those who read these articles is very helpful and always appreciated. If you have any questions, please post them below, and I'll answer those that I can.
