Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server

Creating a Private Installation for SQL Compact

Rate me:
Please Sign up or sign in to vote.
4.83/5 (30 votes)
24 Sep 2010CPOL9 min read 137.6K   2.6K   75   32
This article discusses how to create a private installation for SQL Compact

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:

Image 1

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:

Image 2

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:

Image 3

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:

Image 4

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:

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

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

  • AMD64
  • X86

The File System Editor should look like this:

Image 5

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:

Image 6

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Foresight Systems
United States United States
David Veeneman is a financial planner and software developer. He is the author of "The Fortune in Your Future" (McGraw-Hill 1998). His company, Foresight Systems, develops planning and financial software.

Comments and Discussions

 
QuestionCreating a Private Installation for SQL Compact Pin
Member 1277789311-Oct-16 0:24
Member 1277789311-Oct-16 0:24 
QuestionWhat if .sdf file and sql ce binaries are located in different drives? Pin
sateesh munagala26-Mar-14 5:39
sateesh munagala26-Mar-14 5:39 
Answerthanks, thanks, thanks Pin
kamleshgs23-Dec-13 19:42
kamleshgs23-Dec-13 19:42 
GeneralMy vote of 5 Pin
Chirag B28-Jun-13 19:14
Chirag B28-Jun-13 19:14 
QuestionFantastic! Pin
lcombs7428-Feb-13 5:51
lcombs7428-Feb-13 5:51 
GeneralThank you so much! Pin
davidstanley1310-Jan-13 21:14
davidstanley1310-Jan-13 21:14 
Questionworked great Pin
annahmwangi27-Apr-12 5:20
annahmwangi27-Apr-12 5:20 
QuestionPublic vs. Private Deployment Pin
Thomas Haller29-Jan-12 23:19
Thomas Haller29-Jan-12 23:19 
QuestionErrors! KB 974247 Pin
jp2code2-Aug-11 6:17
professionaljp2code2-Aug-11 6:17 
QuestionAmazing! Pin
jp2code29-Jul-11 4:30
professionaljp2code29-Jul-11 4:30 
GeneralMy vote of 5 Pin
After205016-Mar-11 3:57
After205016-Mar-11 3:57 
QuestionDoes this work if Project is not in default project location? Pin
DTFan2-Dec-10 18:22
DTFan2-Dec-10 18:22 
AnswerRe: Does this work if Project is not in default project location? Pin
David Veeneman3-Dec-10 4:55
David Veeneman3-Dec-10 4:55 
GeneralRe: Does this work if Project is not in default project location? Pin
DTFan3-Dec-10 14:02
DTFan3-Dec-10 14:02 
GeneralMy vote of 5 Pin
Hardy Wang29-Oct-10 17:36
Hardy Wang29-Oct-10 17:36 
QuestionStill some issues Pin
Hardy Wang29-Oct-10 17:30
Hardy Wang29-Oct-10 17:30 
AnswerRe: Still some issues Pin
Hardy Wang29-Oct-10 17:35
Hardy Wang29-Oct-10 17:35 
GeneralGreat thank you! It would also be helpful to discuss copying the *.sdf file. Pin
davelowndes24-Sep-10 14:49
davelowndes24-Sep-10 14:49 
GeneralRe: Great thank you! It would also be helpful to discuss copying the *.sdf file. Pin
David Veeneman24-Sep-10 15:07
David Veeneman24-Sep-10 15:07 
Generalapp.config and registry entries Pin
ausadmin19-Mar-09 15:49
ausadmin19-Mar-09 15:49 
GeneralRe: app.config and registry entries Pin
byunrubyunru13-Jul-09 7:38
byunrubyunru13-Jul-09 7:38 
reference private (non-GAC) dll will also work


<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"/>
GeneralMinimal install Pin
seeblunt2-Mar-09 13:49
seeblunt2-Mar-09 13:49 
GeneralRe: Minimal install Pin
David Veeneman17-Mar-09 11:14
David Veeneman17-Mar-09 11:14 
GeneralRe: Minimal install Pin
Adrian Alexander13-Dec-09 23:38
Adrian Alexander13-Dec-09 23:38 
GeneralRe: Minimal install Pin
AlesSavli16-Jan-10 4:24
AlesSavli16-Jan-10 4:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.