Pokud ve webových aplikacích potřebujete uložit nějaká data do databáze, ale víte, že těchto dat nebude velké množství, můžeme použít embedded databázi SQL Server Compact 4.0. V tomto článku si na to ukážeme jednoduchý příklad. Pokud SQL Server Compact neznáte, podívejte se na dřívější článek zde od Tomáše Hercega.
Předem je nutno ještě zmínit, že Microsoft od SQL Server Compact v poslední době upouští, což osobně moc nechápu proč. Sice existuje LocalDB viz můj minulý článek, kde je i s SQL CE porovnání, tu ale chápu spíše jako vývojářský nástroj. Pro použití v jednoduchých webových aplikaci a prezentací je SQL Server Compact pořád ideální, protože nám například umožní její nasazení včetně databáze na jakýkoliv webový hosting, a nemusíme tak zřizovat a platit hosting SQL Serveru. Vše je totiž umístěno přímo v adresáři Bin a není potřeba žádná instalace.
Nyní již k příkladu. Do ASP.NET webové aplikace přidáme přístup na SQL Server Compact 4.0. V dnešní době to nejjednodušeji uděláme pomoci NuGet Balíčku Microsoft.SqlServer.Compact (PM> Install-Package Microsoft.SqlServer.Compact). Ten nám přidá vše potřebné, jak assembly System.Data.SqlServerCe.dll, tak soubory Native Binaries, a provede nastavení web.config.
V našem scénáři webové aplikace bude soubor databáze (sdf) umístěn v podadresáři App_Data. V případě, že databáze zde ještě nebude existovat, tak jí vytvoříme přímo kódem. Ve web.config si k tomu nachystáme tento connection string:
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=|DataDirectory|\WebSiteData.sdf" />
</connectionStrings>
Nejprve vytvoříme pomocnou třídu SqlCompactDatabaseHelper, do ní umístíme funkce pro načtení connection stringu, a vyhledání databázového souboru.
internal static class SqlCompactDatabaseHelper
{
public static string GetConnectionString(string connectionStringName)
{
var settings = ConfigurationManager.ConnectionStrings[connectionStringName];
if (settings == null)
{
return null;
}
return settings.ConnectionString;
}
public static string GetDataSourceFilePath(string connectionString)
{
var builder = new DbConnectionStringBuilder();
builder.ConnectionString = connectionString;
if (!builder.ContainsKey("Data Source"))
{
throw new ArgumentException("A 'Data Source' parameter was expected in the supplied connection string, but it was not found.");
}
return ResolveDataSourceFilePath(builder["Data Source"].ToString());
}
private static string ResolveDataSourceFilePath(string path)
{
var dirSeparators = new char[] { System.IO.Path.DirectorySeparatorChar };
if (path.StartsWith("~/"))
{
return HttpContext.Current.Server.MapPath(path);
}
if (!path.StartsWith("|DataDirectory|", StringComparison.OrdinalIgnoreCase))
{
return path;
}
string data = AppDomain.CurrentDomain.GetData("DataDirectory") as string;
if (string.IsNullOrEmpty(data))
{
data = AppDomain.CurrentDomain.BaseDirectory;
}
return data.TrimEnd(dirSeparators) + System.IO.Path.DirectorySeparatorChar + path.Substring("|DataDirectory|".Length).TrimStart(dirSeparators);
}
}
Tyto funkce nyní použijeme ve třídě SqlCompactDataSource, přes kterou budeme k databázi přistupovat. Zde umístíme metodu InitializeDatabase, která databázi vytvoří.
internal class SqlCompactDataSource
{
#region member varible and default property initialization
private string ConnectionString;
private static object s_SyncRoot = new object();
#endregion
#region constructors and destructors
public SqlCompactDataSource(string connectionStringName = "ConnectionString")
{
this.ConnectionString = SqlCompactDatabaseHelper.GetConnectionString(connectionStringName);
InitializeDatabase();
}
#endregion
#region private member functions
private void InitializeDatabase()
{
lock (s_SyncRoot)
{
if (!File.Exists(SqlCompactDatabaseHelper.GetDataSourceFilePath(this.ConnectionString)))
{
//Vytvoření databáze
using (var engine = new SqlCeEngine(this.ConnectionString))
{
engine.CreateDatabase();
}
using (var connection = new SqlCeConnection(this.ConnectionString))
{
using (var command = new SqlCeCommand())
{
connection.Open();
SqlCeTransaction transaction = connection.BeginTransaction();
//Vytvoření tabulky pokud neexistuje
try
{
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'Downloads'";
if (command.ExecuteScalar() == null)
{
command.CommandText = @"CREATE TABLE Downloads
([IDDownloads] int IDENTITY(1,1) NOT NULL CONSTRAINT [PK_Downloads] PRIMARY KEY,
[Url] nvarchar(200) NOT NULL,
[Count] int NOT NULL,
CONSTRAINT [U_Downloads_FileName] UNIQUE ([Url]))";
command.ExecuteNonQuery();
}
transaction.Commit(CommitMode.Immediate);
}
catch (SqlCeException)
{
transaction.Rollback();
throw;
}
}
}
}
}
}
#endregion
}
V našem příkladu v databázi vytvoříme tabulku Downloads (IDDownloads int IDENTITY(1,1) NOT NULL, Url nvarchar(200) NOT NULL, Count int NOT NULL), která bude sloužit k uchování počtu stažení dané URL.
Pro účely tohoto příkladu doplníme funkce pro čtení a zápis to této tabulky přímo do třídy SqlCompactDataSource. Ty mohou vypadat například takto:
public void AddDownload(string url)
{
lock (s_SyncRoot)
{
using (var connection = new SqlCeConnection(this.ConnectionString))
{
using (var command = new SqlCeCommand())
{
connection.Open();
SqlCeTransaction transaction = connection.BeginTransaction();
try
{
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = "SELECT Count FROM Downloads WHERE Url = @Url";
command.Parameters.AddWithValue("@Url", url.ToLowerInvariant());
if (command.ExecuteScalar() == null)
{
command.CommandText = @"INSERT INTO Downloads (Url, Count) VALUES (@Url, 1)";
command.ExecuteNonQuery();
}
else
{
command.CommandText = @"UPDATE Downloads SET Count = Count + 1 WHERE Url = @Url";
command.ExecuteNonQuery();
}
transaction.Commit(CommitMode.Immediate);
}
catch (SqlCeException)
{
transaction.Rollback();
throw;
}
}
}
}
}
public Dictionary<string, int> GetDownloadCounts()
{
var list = new Dictionary<string, int>();
using (var connection = new SqlCeConnection(this.ConnectionString))
{
using (var command = new SqlCeCommand())
{
connection.Open();
command.Connection = connection;
command.CommandText = @"SELECT Url, Count FROM Downloads";
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
list.Add((string)reader["Url"], Convert.ToInt32(reader["Count"]));
}
}
}
}
return list;
}
Pro takto jednoduchý scénář přistupujeme k tabulce přímo, ale nic mám nebrání použít například Entity Framework, který databázi SQL CE také podporuje.
Použití třídy pak může být následující:
//Get download counts
var downloadCounts = (new SqlCompactDataSource()).GetDownloadCounts();
//Add download count
(new SqlCompactDataSource()).AddDownload(url);
Při prvním přístupu k databázi bude databáze vytvořena a v podadresáři App_Data vznikne soubor WebSiteData.sdf.
Kompletní zdrojové soubory příkladu jsou dostupné zde.
Správa databáze
Možná víte, že od SQL Server 2012 se již pomoci Management Studia nelze k SQL Server Compact připojit. Starší verze Management Studia nám také nepomůže, protože nepodporuje SQL CE 4.0 (pouze 3.5). Jaké tedy máme v dnešní době možnosti pro správu sdf databáze?
Popravdě řečeno, moc jich není. Asi nejlepší co jsem našel je použít doplněk SQL Server Compact/SQLite Toolbox do Visual Studia dosupný na codeplexu nebo VS gallery. Přístup k databázi je pak přes okno SQL Server Compact Toolbox velice podobný jako v Managemet studiu.
