Read here about Kontext's Cookie and Privacy policy. Dismiss

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

10213 views 0 comments last modified about 4 years ago Raymond

In this page

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/

image

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:

image

The table level functions consist of :

image

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.

image

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:

image

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

Related pages

Querying Teradata and SQL Server - Tutorial 1: The SELECT Statement

27815 views   7 comments last modified about 4 years ago

SELECT is one of the most commonly used statements. In this tutorial, I will cover the following items: Two of the principal query clauses—FROM and SELECT Data Types Built-in functions CASE expressions and variations like ISNULL and COALESCE. * The functio...

View detail

Querying Teradata and SQL Server - Tutorial 2 Filtering and Sorting

2765 views   2 comments last modified about 4 years ago

Tutorial –1 For Teradata developers, if you have no SQL Server installed, please go to the following link to download the SQL Server 2014 Expression Edition. ...

View detail

Querying Teradata and SQL Server - Tutorial 0: Preparation

2573 views   0 comments last modified about 4 years ago

In this serial, I will compare Teradata SQL with T-SQL with samples provided. This is mainly prepared for SQL Server DBAs, Developers and other users to help them quickly master the common used SQLs in Teradata platform. Similar to T-SQL, Teradata SQL implements most part of the ANSI SQL with ext...

View detail

[SQL Server 2012] 使用自带功能简化SQL

706 views   0 comments last modified about 5 years ago

在低版本SQL Server中,对于一些常用的查询功能,我们可能需要运用复杂的逻辑,如临时表,游标等等来实现,这样可能会导致代码难维护,同时产生性能问题。本文将简述用自带的功能去简单的实现这些操作。 注意:一些功能仅在SQL Server 2012以及以上版本支持。 1.查询分页 (OFFSET, FETCH) 在很多Web应用程序中,我们需要实现分页功能,在过去,我们往往通过top语句实现或者通过ROW_NUMBER;在2012中加入了FETECH以及OFFSET标准SQL中支持的功能。下面的例子是查询第11到20行数据: ...

View detail

将远程 Sql Server 服务器上的数据库备份到本地的方法

498 views   0 comments last modified about 10 years ago

首先,必须知道数据库服务器的地址(IP),连接用户名和密码、其次,可以按照下面几种方式进行:

View detail

SQL Server 附加没有日志文件ldf的数据库mdf的方法

642 views   0 comments last modified about 9 years ago

Keywords: how to attach database without log file

View detail

Add comment

Please login first to add comments.  Log in New user?  Register

Comments (0)

No comments yet.