SQL Server Compact 4

23. ledna 2011

Autor: Václav Dajbych, MSP

Microsoft minulý týden vydal novou verzi své embedded databáze. Předešlé verze této databáze se používaly jen s desktopovými aplikacemi. Nebyla vytvořena tak, aby mohla obsluhovat více požadavků zároveň. To se v nové verzi změnilo. Dá se bez obav z pádu či uváznutí používat z více vláken současně. Tím se pole její působnosti výrazně rozšířilo o nasazení na webových serverech. Databázi není potřeba instalovat, stačí pouze zkopírovat několik souborů.

Dá se dobře využít na webhostingu, který neposkytuje velký SQL Server. K běhu databáze stačí medium-trust prostředí a mohou nad ní být ORM frameworky jako NHibernate či Entity Framework. Lze ji použít i s desktopovou aplikací, protože nevyžaduje administrátorská práva. Její engine běží v procesu aplikace.

Knihovny

K práci se SQL Server Compact 4 je potřeba stáhnout a nainstalovat Visual Studio 2010 Service Pack 1, SQL CE Tools for Visual Studio a samotný SQL CE 4 (x86 | x64). Veškeré potřebné knihovny se pak budou nacházet ve složce C:\Program Files\Microsoft SQL Server Compact Edition\v4.0\Private. Pro použití s ASP.NET je potřeba obsah této složky kopírovat do adresáře bin webové aplikace a přidat referenci na knihovnu System.Data.SqlServerCe.dll. Databázový engine pak běží v procesu aplikace.

Přehled významu jednotlivých knihoven vystihuje následující schéma a tabulka.

Modrá – nezbytné části
Fialová – volitelné části na základě přístupového modelu
Zelená – volitelné části na základě použití

Nonmanaged knihovny se nacházejí v podadresáři s názvem architektury procesoru, pro kterou jsou zkompilovány. System.Data.SqlServerCe.dll si vybere podle stroje, na kterém běží. Pokud tedy poskytovatel webhostingu přesune application pool s vaší webovou aplikací z x86 stroje na x64, web si toho ani nevšimne.

Visual Studio

Visual Studio 2010 SP1 umožňuje vytvořit novou databázi stejně snadno, jako nový soubor. Databáze by měla být umístěna v adresáři App_Data.

Po zkopírování nezbytných knihoven do adresáře bin je potřeba přidat referenci na System.Data.SqlServerCe.dll. Entity Framework a LINQ ještě vyžadují referenci na System.Data.SqlServerCe.Entity.dll.

Posledním krokem je přidání connection stringu do souboru web.config, ze kterého se přečte příkazem:

ConfigurationManager.ConnectionStrings["CeDatabase"].ConnectionString

Pokud web nasazujete pomocí nástroje Web Deploy, potřebujete verzi 2.0, kterou snadno nainstalujete přes Web Platform Installer. Database Explorer samozřejmě umožňuje vytvářet a upravovat tabulky a nově i zobrazit data v tabulce.

web.config

Pokud na serveru není nainstalován SQL Server CE 4, je potřeba do souboru web.config přidat následující:

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

Connection string pak vypadá typicky takto.

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

Cesta k databázi začíná makrem |DataDirectory|, které se nahradí za cestu k adresáři App_Data. Je to podobné, jako zápis cesty ~/App_Data/ obvyklý v ASP.NET.

ADO.NET

Pokud používáte starý, dobrý, výpočetně skromný, avšak vývojově časově náročný přístup k databázi, pak stačí změnit using System.Data.SqlClient na using System.Data.SqlServerCe a třídy SqlConnection a SqlCommand přepsat na SqlCeConnection respektive SqlCeCommand. O zbytek už se postará klíčové slovo var.

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 je nastaven tak, aby se (pokud mu nepředáte connection string) pokusil připojit k databázi ./SQLEXPRESS. Zajímavé ale je, že pokud se connection string v souboru web.config jmenuje stejně, jako třída kterou dědíte z DbContext, použije se ten. Nové instanci třídy, která reprezentuje vaší databázi, tak nemusíte connection string předávat.

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 bude konečně podporovat stránkování záznamů pomocí jazyka SQL, jak to známe z MySQL pomocí klíčového slova LIMIT. Tuto funkci má SQL Server CE už nyní. Například pokud stránkujeme zákazníky po deseti, tento příkaz vrátí třetí stranu.

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

Vedle podpory transakcí je dobré zmínit, co naopak Compact edice neumí. Jedná se o pohledy, triggery a tím pádem i kaskádové mazání a integritní omezení. Nejde ani vykonat více příkazů najednou oddělených středníkem.

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

S podporou příkazů OFFSET a FETCH pro stránkování a paralelním zpracováním příkazů se SQL Server Compact 4 stává dobře použitelným v menších webových aplikacích. Protože ho podporuje Code-First Entity Framework, lze snadno a rychle vytvořit složitější web i na webhostingu, který nemá vlastní databázi. SQL Server CE používá i vývojové prostředí WebMatrix, které obsahuje nástroj k exportu dat na jednu z vyšších edic SQL Serveru. Není třeba se bát, že s přesunem dat bude problém, až SQL Server CE přestane stačit.

Článek byl sepsán pro Zdroják.