An introduction to SQL Server compact Edition

Few months ago Microsoft has been released a new version of Microsoft SQL Server Compact 4.0. It’s a free, embedded database that software developers can use for building ASP.NET websites and Windows desktop applications.

As of Microsoft, SQL Server Compact 4.0 enables new scenarios and includes a host of new features, including the following:

  • SQL Server Compact 4.0 is the default database for Microsoft WebMatrix,.
  • For enhanced development and debugging capabilities, including designer support.
  • Visual Studio can be used to develop ASP.NET web applications and websites using SQL Server Compact 4.0.
  • Enabled to work in the medium or partial trust environments in the web servers
  • Private deployment of SQL Server Compact further simplified by having all the required managed assemblies in a single private folder in the install location of SQL Server Compact.
  • Stronger data security with the use of the SHA2 encryption algorithms for encrypting the databases.
  • T-SQL syntax enhancement by adding support for OFFSET and FETCH that can be used to write paging queries and new APIs like SqlCeConnection.GetSchema that provides the metadata information of the database.
  • Support for ADO.NET Entity Framework with the code-first programming model.

Scenarios not enabled by SQL Server Compact 4.0:

The SQL Server Compact was focusing on enabling the new scenarios for ASP.NET Web Applications, and due to time and resource constraints the following scenarios have not been enabled in SQL Server Compact 4.0. For these scenarios the SQL Server Compact 3.5 SP2 is the right choice.

  1. Data replication with SQL Server: Compact 4.0 does not support data replication with SQL Server using Sync Framework, merge replication or remote data access (RDA).
  2. LINQ to SQL. However the ADO.NET Entity Framework 4 that ships in .NET FX 4 works with Compact 4.0, and additional features like support for code-first and server generated keys have been added in Compact 4.0 for EF 4.
  3. Windows Mobile, Windows Phone and Windows CE devices: Compact 4.0 does not have a release for Windows Mobile, Windows Phone or for Windows CE devices.
  4. Designers in the VB or C# Windows projects in Visual Studio 2010 SP1 Beta

More detailed information can be found at SQLCE Team Blog

Latest Version you can find here: SQLCE 4.0 Download


Working with SQLCE 4.0

Right click to the project window and add a new Item to the project. Select the DATA option and select Local Database option. Provide a database name and click OK. As a result you will find a file with SDF extension bellow.

To browse the Database, Right Click on Model.sdf and click Open. Then you will be able to get the table details. To add a new table Right click on Tables and click on Add table. In this case I have created 2 tables as shown in figure.

Eventually, you can use this connection for ADO.NET Entity Framework as usual database.

Enjoy and use it in your applications!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s