TOP

Database Provider Factories: EF + SQL Compact

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.

Name Description Invariant
Odbc Data Provider .Net Framework Data Provider for Odbc System.Data.Odbc
Type System.Data.Odbc.OdbcFactory, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
OleDb Data Provider .Net Framework Data Provider for OleDb System.Data.OleDb
Type System.Data.OleDb.OleDbFactory, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
OracleClient Data Provider .Net Framework Data Provider for Oracle System.Data.OracleClient
Type System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
SqlClient Data Provider .Net Framework Data Provider for SqlServer System.Data.SqlClient
Type System.Data.SqlClient.SqlClientFactory, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Microsoft SQL Server Compact Data Provider .NET Framework Data Provider for Microsoft SQL Server Compact System.Data.SqlServerCe.3.5
Type System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
SQLite Data Provider .Net Framework Data Provider for SQLite System.Data.SQLite
Type System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.66.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139
Microsoft SQL Server Compact Data Provider 4.0 .NET Framework Data Provider for Microsoft SQL Server Compact System.Data.SqlServerCe.4.0
Type System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91

(Foto by Tim Morgan)

2 comments. Leave a Reply

  1. Thank for your post, I was having problems changing the provider to SQL Server Compact through code, and allow me to have a dynamic SDF database name through the DbContext, most of the examples out there used static definitions for the connection strings in app.config, which define both the connection string as well as the provider.

  2. John Vance

    It appears that the sql server compact nuget package is busted, at least for me. VS 2013, package version 4.0.8876.1, win 8.1 framework 4.5.1. No modifications made to the app.config file, no dlls installed. Crapola. Wish me luck getting this going manually!

    Thanks for your blog post, as it confirmed what I thought the nuget package should be doing, so I could determine that is wasn’t working.

Leave a Reply

Your email is never published nor shared.

You may use these HTML tags and attributes:<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>