Použití SQl Server Compact ve Webové aplikaci

Jan Holan       18. 8. 2014       ADO.NET, ASP.NET/IIS       6067 zobrazení

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.

image

 

hodnocení článku

1 bodů / 1 hlasů       Hodnotit mohou jen registrované uživatelé.

 

Nový příspěvek

 

Příspěvky zaslané pod tento článek se neobjeví hned, ale až po schválení administrátorem.

Užitočný článok

Aspoň pre mňa.

nahlásit spamnahlásit spam 0 odpovědětodpovědět
                       
Nadpis:
Antispam: Komu se občas házejí perly?
Příspěvek bude publikován pod identitou   anonym.

Nyní zakládáte pod článkem nové diskusní vlákno.
Pokud chcete reagovat na jiný příspěvek, klikněte na tlačítko "Odpovědět" u některého diskusního příspěvku.

Nyní odpovídáte na příspěvek pod článkem. Nebo chcete raději založit nové vlákno?

 

  • Administrátoři si vyhrazují právo komentáře upravovat či mazat bez udání důvodu.
    Mazány budou zejména komentáře obsahující vulgarity nebo porušující pravidla publikování.
  • Pokud nejste zaregistrováni, Vaše IP adresa bude zveřejněna. Pokud s tímto nesouhlasíte, příspěvek neodesílejte.

Příspěvky zaslané pod tento článek se neobjeví hned, ale až po schválení administrátorem.

přihlásit pomocí externího účtu

přihlásit pomocí jména a hesla

Uživatel:  
Heslo:  

zapomenuté heslo

 

založit nový uživatelský účet

zaregistrujte se

 
zavřít

Nahlásit spam

Opravdu chcete tento příspěvek nahlásit pro porušování pravidel fóra?

Nahlásit Zrušit

Chyba

zavřít

feedback