When using SQL Server CE as a local database engine the SQL Server CE components need to be installed locally. This could easily be done using the SQL Server CE setup package provided by Microsoft. But this requires the end user to manually install the SQL Server CE .msi as a prerequisite component.
As an alternative one could create a setup bootstrapper to include and automatically run the SQL Server CE installer during setup of the custom application. But this requires additional development effort.
What I wanted was to completely include the SQL Server CE runtime components with the setup installer of my application. All components should be installed locally. I managed to solve this with the help of several blog posts, namely:
- Troubleshooting: Privately deploying the SQL Server Compact 3.5 SP2 ADO.NET Entity Framework provider (System.Data.SqlServerCe.Entity.dll) in the application folder does not work
- SQL Server Compact 3.5 SP2 Private Deployment (2nd reply by author “Karlie”)
- Privately Deploying SQL Server Compact with the ADO.NET Entity Provider
I applied a combination of the techniques described in the previous blog posts. Essentially I did the following:
- Installed the following files from the %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5 directory to the application’s target directory. These are the files from SQL Server CE 3.5 SP2 which came with Visual Studio 2010, though the application was created using Visual Studio 2008 SP1 (targeting .NET Framework 3.5 SP1). The version number of the files is displayed as 3.5.8080.0.
- Installed the following files from the %ProgramFiles%\Microsoft SQL Server Compact Edition\v3.5\Private directory to the application’s target directory. It is important to use the files from the Private directory and not from the base and Desktop directories, otherwise other problems will arise.
- Added the following section to the application’s app.config file. This was taken from the before mentioned blog post.
<runtime> <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"> <dependentAssembly> <assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" culture="neutral"/> <bindingRedirect oldVersion="184.108.40.206-220.127.116.11" newVersion="18.104.22.168"/> </dependentAssembly> </assemblyBinding> </runtime>
- Added the following section to the application’s app.config file. This was copied from the machine.config file of a development machine where the SQL Server CE runtime was installed. The remove tag is required to make the configuration work on machines, where this entry is already contained in the machine.config file.
<system.data> <DbProviderFactories> <remove invariant="System.Data.SqlServerCe.3.5" /> <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=22.214.171.124, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /> </DbProviderFactories> </system.data>
To figure this out it was helpful to add the following entry to the registry to enable assembly binding logging. This entry causes detailed error information to be included with the exceptions thrown when assembly binding errors occur.
- HKLM\SOFTARE\Microsoft\Fusion\EnableLog=1 (DWORD)
This solution has been tested only on Windows XP SP3 so far. Additional steps might be required to make it work on 64-bit platforms.