SQL Server Compact 4

Microsoft released a new version of its embedded database last week. Previous versions of this database were used only with desktop applications. It wasn't designed to handle multiple requests at the same time. This has changed in the new version. It can be used from multiple fibers at the same time without fear of falling or getting stuck. This significantly expanded its scope of activity to include deployment on web servers. There is no need to install the database, just copy a few files.

It can be used well on web hosting that does not provide a large SQL Server. A medium-trust environment is enough to run the database and ORM frameworks such as NHibernate or Entity Framework can be on top of it. It can also be used with a desktop application as it does not require administrator rights. Its engine runs in the application process.

Bookcases

An overview of the importance of individual libraries is described in the following diagram and table.

Blue – necessary parts

Purple - Optional Parts Based on Access Model

Green – optional parts based on use

Name	Library	Notes
Storage Engine	sqlcese40.dll
Query Processor	sqlceqp40.dll
DB Utilities	sqlcecompact40.dll
Managed Extensions	sqlceme35.dll
ADO.NET Provider	System.Data.SqlServerCe.dll	Also known as ADO.NET v2 Provider
Merge Replication	sqlceca40.dll	Client Agent
Remote Data Access	sqlceca40.dll	Client Agent
	sqlceer40EN.dll
LINQ/SQL		Included with the .NET FW in System.Data.Linq.dll
LINQ/Entities	System.Data.SqlServerCe.Entity.dll	Also known as ADO.NET v3 Provider or Entity Framework

Nonmanaged libraries are located in a subdirectory with the name of the processor architecture for which they are compiled. System.Data.SqlServerCe.dll is chosen according to the machine on which it is running. So if a web hosting provider moves application pool with your web application from x86 of the machine to x64, the website will not even notice.

Visual Studio

Visual Studio 2010 SP1 makes it easy to create a new database as you create a new file. The database should be located in the App_Data directory.

After copying the necessary libraries to the bin directory, it is necessary to add a reference to System.Data.SqlServerCe.dll. Entity Framework and LINQ still require a reference to System.Data.SqlServerCe.Entity.dll.

The last step is to add the connection string to the web.config file, from which it is read with the command:

ConfigurationManager.ConnectionStrings["CeDatabase"].ConnectionString

If you're deploying your site using Web Deploy, you need version 2.0, which you can easily install via Web Platform Installer. Of course, Database Explorer allows you to create and edit tables and now also display data in a table.

web.config

If SQL Server CE 4 is not installed on the server, the following must be added to the web.config file:

<runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
    <dependentAssembly>
      <assemblyIdentity name="System.Data.SqlServerCe" publicKeyToken="89845dcd8080cc91" culture="neutral"/>
      <bindingRedirect oldVersion="4.0.0.0-4.0.0.1" newVersion="4.0.0.1"/>
    </dependentAssembly>
  </assemblyBinding>
</runtime>
<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.1, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
</system.data>

The connection string then looks like this.

<connectionStrings>
  <add name="CeDatabase" connectionString="Data Source=|DataDirectory|database.sdf" providerName="System.Data.SqlServerCe.4.0"/>
</connectionStrings>

The path to the database begins with macro |DataDirectory|, which is replaced by the path to the directory App_Data. It is similar to writing down a journey ~/App_Data/ usual in ASP.NET.

ADO.NET

If you are using the old, good, computationally modest, but developmentally time-consuming approach to the database, then it is enough to change using System.Data.SqlClient to using System.Data.SqlServerCe and rewrite the classes SqlConnection and SqlCommand to SqlCeConnection and SqlCeCommand, respectively. The keyword var will take care of the rest.

string connString = ConfigurationManager.ConnectionStrings["CeDatabase"].ConnectionString;
using (var conn = new SqlCeConnection(connString)) {
  conn.Open();
  var command = new SqlCeCommand("SELECT [id] FROM [users] WHERE [age] > @age", conn);
  command.Parameters.AddWithValue("@age", age);
  using (var reader = command.ExecuteReader()) {
    while (reader.Read()) {
      id = (int)reader["id"];
    }
  }
}

Entity Framework

Code First Entity Framework is set to try to connect to the database ./SQLEXPRESS (unless you pass it the connection string). Interestingly, however, if the connection string in file web.config has the same name as the class you inherit from DbContext, that is used. You don't have to pass the connection string to a new instance of the class that represents your database.

public class CeDatabase : DbContext {
  public DbSet<TableClass> MyTable { get; set; }
}

<connectionStrings>
  <add name="CeDatabase" connectionString="data source=|DataDirectory|database.sdf" providerName="System.Data.SqlServerCe.4.0"/>
</connectionStrings>

using (var db = new CeDatabase()) {
  grid.DataSource = db.MyTable;
  grid.DataBind();
}

SQL

SQL Server Denali will finally support pagination of records using the SQL language, as we know it from MySQL using the keyword LIMIT. SQL Server CE already has this function. For example, if we page customers by ten, this command returns a third party.

SELECT * FROM Customers ORDER BY [Customer ID] OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

In addition to transaction support, it is good to mention what the Compact edition cannot do. These are views, triggers and thus cascade deletion and integrity constraints. It is also not possible to execute multiple commands at once separated by a semicolon.

var cmd = new SqlCommand("INSERT INTO [feed] (url) VALUES (@url); SELECT @@Identity", conn);
cmd.Parameters.AddWithValue("@url", url);
var id = (int)cmd.ExecuteScalar();

With the support of OFFSET and FETCH commands for pagination and the parallel processing of commands, SQL Server Compact 4 becomes well usable in smaller web applications. Because it is supported by Code-First Entity Framework, it is easy and fast to create a more complex website even on a web hosting that does not have its own database. SQL Server CE is also used in the WebMatrix development environment, which contains a tool to export data to one of the higher editions of SQL Server. There is no need to worry that there will be a problem with data transfer when SQL Server CE is no longer enough.

The article was written for Zdroják.