Deploying .NET apps that use EF and SQL CE 4.0

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

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