Technology & Robotics

Now that Entity Framework 4.1 has been released and SQL Compact Edition 4.0 was released a while back, you can start using those technologies in your projects. I was working on a MVC 3 project when I discovered a NuGet package for SQL Compact Edition.

You have three option to install SQL Server Compact 4.0, using the Web Platform Installer, direct download, or you can add it as a NuGet package. I would suggest using the NuGet option, as that includes the required files with your source code. Make sure you get the documentation as well.

SqlServerCompact package: http://nuget.org/List/Packages/SqlServerCompact

With this package, you can start building code that queries and stores data in a file-based database. Yet, you still need one more package to make it work properly together with Entity Framework 4.1. Luckily, there is another NuGet package for this, EntityFramework.SqlServerCompact.

The SqlServerCompact package will try to modify your web.config/app.config with the following keys:

<configuration>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.4.0" />
      <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>

Also included in the package is the binaries for SQL Server Compact 4.0, located at the same level as your Visual Studio solution file, you should find this folder and within it all the assemblies needed to run: packages\SqlServerCompact.4.0.8482.1. Inside the lib folder is the System.Data.SqlServerCe.dll, which is the .NET assembly you need to work against SQL Server Compact.

The other package includes the assembly System.Data.SqlServerCe.Entity.dll, which contains code that generates proper queries against the SQL Server Compact. Additionally, it will add a source code file to your project inside the App_Start folder, that contains this code:

using System.Data.Entity;
using System.Data.Entity.Infrastructure;

[assembly: WebActivator.PreApplicationStartMethod(

typeof(InTheBoks.Test.Integration.App_Start.EntityFramework_SqlServerCompact), 

"Start")]

namespace InTheBoks.Test.Integration.App_Start {
    public static class EntityFramework_SqlServerCompact {
        public static void Start() {
            Database.DefaultConnectionFactory = 

              new SqlCeConnectionFactory("System.Data.SqlServerCe.4.0");
        }
    }
}

What happens here is that the default connection factory of the Entity Framework is changed to the provider specified in the web.config/app.config. While I was working on the previous builds of SQL Server Compact 4 and Entity Framework 4.1 CTPs, I did not have the provider changed in the web.config. The new key in the config made me wonder for what reasons it removed and added the provider again. So I investigated with an integration test project to see what really happens.

The default connection factory is SqlConnectionFactory. That means that we need to change the connection factory in the start up of our application, which is done with the code inside the Start method displayed above, which changes it to SqlCeConnectionFactory.

To investigate what the default configuration is, I had to access a list of factories using the API: DbProviderFactories.GetFactoryClasses(). This will give you a data table with rows for all the factories. I did this to figure out why the NuGet package made the changes to my web.config/app.config and to see if there was any difference to my already installed factory and the one added in the config. Conclusion was, there is no difference. The reason why the package adds the provider, is probably because the factory is not registered on the computer if you don’t install using the manual or Web Platform Installer – so it have to add it manually in the config. My suggestion is to keep the configuration key in place, this ensures that your application will work without the need to install SQL Server Compact.

Reference Table for DbProviderFactory Classes.

(Foto by Tim Morgan)