Working with SQL Server Compact 4.0 using Entity Framework 6 and ADO.NET

SQL Server Compact 4.0 (CE 4.0) is a free SQL Server embedded database ideal for building standalone and occasionally connected applications for mobile devices, desktops, Web clients and others. In one of my projects, I used it as the database for logging errors, which assumes the errors will only occur occasionally. It is not very straightforward compared with working with SQL Server, but it is quite handy with the helps of some tools and components.

SQL Server Compact Toolbox

Visual Studio doesn’t build in the toolbox for SQL Server Compact. However you can install one AddIn “SQL Server Compact Toolbox” which can help you to create, manipulate and generated scripts for SQL CE databases. It even allows you to analyze the execution plans.

You can download this Addin from both MSDN or CodePlex.

CodePlex: http://sqlcetoolbox.codeplex.com/

This tool provides both database and table level functions, which enables you to generate EntityFramework models and SQL scripts.

Database level utilities include the following items:

The table level functions consist of :

ADO.NET and System.Data.SqlServerCe 4.0

You can install System.Data.SqlServerCe package from NUGET in Visual Studio. By referencing to the library System.Data.SqlServerCe.dll, you can communicate with SQL CE database as you did for SQL Server. There are a  number of derived classes which you can get familiar easily. E.g. SqlCeCommand, SqlCeConnection, SqlCeDataAdapter and SqlCeDataReader.

Learn more details and find more code samples at MSDN http://msdn.microsoft.com/en-us/library/system.data.sqlserverce(v=vs.100).aspx.

The configuration in your app.config/web.config is similar like the following:

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

Entity Framework 6.0

You can also use ORM tool like Entity Framework 6.0 to manipulate data in SQL CE databases.  Before you start, you need to install two packages in NuGet: EntityFramework.SqlServerCompact 6.0 and EntityFramework 6.0.

System.Data.Entity.SqlServerCompact. SqlCeProviderServices need to be configured in the providers of Entity Framework configurations.

<entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v12.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlServerCe.4.0" type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    </providers>
  </entityFramework>

If you forget to add this provider, you will encounter the following error:

No Entity Framework provider found for 'System.Data.SqlServerCe.4.0' ADO.NET provider. Make sure the provider is registered in the 'entityFramework' section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

If you configure it incorrectly, you will get the following error:

The Entity Framework provider type 'System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' for the 'System.Data.SqlServerCe.4.0' ADO.NET provider could not be loaded. Make sure the provider assembly is available to the running application. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

If you use “System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91” to specify the type in provider configuration you will get a different error (the reason is because SqlCeProviderFactory class is not inherited from DbProviderServices):

An exception of type 'System.InvalidOperationException' occurred in mscorlib.dll but was not handled in user code

Additional information: The 'Instance' member of the Entity Framework provider type 'System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' did not return an object that inherits from 'System.Data.Entity.Core.Common.DbProviderServices'. Entity Framework providers must inherit from this class and the 'Instance' member must return the singleton instance of the provider. This may be because the provider does not support Entity Framework 6 or later; see http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

Make sure you use the correct configurations and add the references to the assemblies to avoid all the above issues.

One you are done, you can generate the models using Wizard for the SQL Server Compact Toolbox:

Have fun in your development journey with SQL CE 4.0 + Entity Framework 6.0.