I recently had to create a small application that uses a database. I opted for SQL CE 4.0 because it uses the same T-SQL syntax as MS SQL, it’s portable and you can very easily use it with Entity Framework.
When the time came to deploy my application and create a Setup file, I hit the wall. Before I’ve created the setup file I tried copying my app files to another computer which had preinstalled SQL CE 4.0 dependencies. When I tried to start my application i got “the specified store provider cannot be found in the configuration” error.
After I’ve lost couple of hours searching everywhere I managed to get it to work. Here is how you can do it.
Taking care of managed references
Add the Assembly References
If you view your References you will notice that System.Data.SqlServerCe.dll isn’t listed. This is because providers are loaded by the Entity Framework at runtime based on the connection string in App.config so you need to manually add the references for Visual Studio to deploy these dlls.
- Choose Add Reference, choose Browse, browse to C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Desktop\ and select System.Data.SqlServerCe.Entity.dll
- Now from the same folder go to System.Data.SqlServerCe.Entity folder and choose System.Data.SqlServerCe.Entity.dll
- Be sure that the copied files were in Desktop folder, as noted above.
Change the managed references to be “local”
- In the References node, select System.Data.SqlServerCe and then select the property grid. Change Copy Local = True. This tells VS to copy the dll to the output directory, even though it’s GACd on your development computer. When VS builds your project, it will copy the dll to your output directory.
- Change the System.Data.SqlServerCe.Entity reference to Copy Local = True as well
Adding the native runtime for SQL Server Compact
Similar to the .NET Framework, SQL Server Compact achieves its speed, reliability and compact size by using native code for the storage engine and query processor. As of 3.5 SP1, Compact added 64bit support. To avoid your end users from having to figure out whether they have 32 or 64bit machines, only to then choose a 32 or 64bit install, you can include both the 32 and 64bit runtimes directly within your application so end users can get a single “install/copy” of your app and it will “just work” regardless of their bit alignment
- To add the 32bit runtime: Add a folder named X86 to your project
- Select Add Existing Item and navigate to: C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Private\X86\ folder.
- Using CTRL + Click, select all 7 dlls in the folder, then choose the little down arrow on the Add button and choose Add As Link
- Select the newly imported files within Solution Explorer and select Properties
- Change Copy To Output Directory = Copy If Newer
- Assuming you have a 64bit machine with SQL Server Compact 3.5 SP1 64bit installed you can add the 64bit binaries as well, add a folder named AMD64 to your project
- Do the same as above, only this time go to C:\Program Files (x86)\Microsoft SQL Server Compact Edition\v4.0\Private\amd64\ folder.
- Once again CTRL + Click, select all 7 files and Add As Link
- Also change Copy To Output Directory = Copy If Newer
Adding the Entity Provider Entry into App.config
On clients that don’t have SQL Server Compact centrally installed, you’ll need to add the entry for Provider Factory to find SQL Server Compact. You can’t add it directly to the Machine.config file because that’s a central resource which requires Admin rights. Instead, you can add it to app.config with a slight modifications.
- Open machine.config from: C:\Windows\Microsoft.NET\Framework\v2.0.50727\CONFIG
- Search for SQL Server Compact
- Copy the entire XML fragment for
<system.data> <DbProviderFactories> <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.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /> </DbProviderFactories> </system.data>
Resolving duplicates
The problem is essentially that we know we need an Entity provider for SQL Server Compact, but we don’t know if one is already on the machine. Turns out one of the easiest ways to resolve duplicate data is to delete one. The Config framework does provide a nice way to do this.
In the app.config file add the
<DbProviderFactories> <remove invariant="System.Data.SqlServerCe.4.0"></remove> <add name="Microsoft SQL Server Compact Data Provider 4.0"...
This simple line of XML tells ADO.NET Provider Factory to remove any entries for this invariant name if they exist in machine.config.
Your final app.config XML file should look something like this:
<?xml version="1.0" encoding="utf-8" ?> <configuration> <system.data> <DbProviderFactories> <remove invariant="System.Data.SqlServerCe.4.0"></remove> <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.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /> </DbProviderFactories> </system.data> </configuration>
The Deployment part
Your application is now ready to be deployed. If you build your application, and copy your Releas folder onto a client that has .NET 3.5 Framework installed, and try to run your application. Everything would work as expected
Inno Setup
As I really dislike OneClick Deploy,I usually use great free installer for Windows programs called Inno Setup. It’s very powerful and customizable. Along side with Inno I use Modular InnoSetup Dependency Installer which allows you to easily add Prerequisites for your application upon installation.
Final notes
- When deploying your application remember that you cannot deploy your SQL Compact database along side your application in Program Files because of admin rights needed to write to any file in Program Files. Instead, you should deploy your database inside {user}/AppData/Local folder. You can target this folder via C# with this snippet
Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData)
- When deploying your application make sure to include {your-app-name}.exe.config which contains your Entity Provider entry.
Comments
I followed this steps for SSCE 3.5 sp2 and its perfectly working..but when I tested this on 64 bit machine its not working...
this error comes...
Could not load file or assembly 'System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040
please suggest me if i'm doing anything wrong...
thanks in advance
By the way....thank you very very much ....you're a savior to me.....thanks :)...keep it up...
Firstly , Tons of Thanks for a very nice post. what to do in case the target machines don't have SQL Compact 4.0 Installed ?
Also what should be the connection string so that when the end users have installed the desktop app on their computer, the app automatically gets the database server and the included database ?
Would you please help in this regard ? Thanks in advance
this article explains just what to do when somebody doesn't have SQL Compact installed. You need to include it with your application when building installation package.
In regards to your other question, my connection string looks something like this: return @"metadata=res://*/dbDataContext.csdl|res://*/dbDataContext.ssdl|res://*/dbDataContext.msl; provider=System.Data.SqlServerCe.4.0;provider connection string='Data Source=" + path + "'"
In this case, I set my path variable to be the path to my application, or even better, you can install your SQL CE database file in your Local Application Data (in Users folder), and point this "path" variable there via connection string.
However if you want to have access to the files in Program Files (to have your db in) you can add this in Inno Setup :
[Dirs]
Name: "{app}\"; Permissions: everyone-modify