Jak využít uložené procedury v SQL Serveru pro efektivní správu dat

Uložené procedury jsou v SQL Serveru silným nástrojem pro zjednodušení, zrychlení a centralizaci opakovaných úkolů, které se týkají manipulace s databázemi. V této kapitole se podíváme na jednu konkrétní uloženou proceduru a jak ji využít pro filtrování produktů na základě ceny, a to nejen pomocí SQL, ale i v C# aplikaci.

sql
Copy code
CREATE PROCEDURE [dbo].[GetExpensiveProducts]
    @price money,
    @count int OUT
AS
    SELECT @count = COUNT(*) FROM Products WHERE UnitPrice > @price

    SELECT * FROM Products WHERE UnitPrice > @price

RETURN 0

V této proceduře je použito několik klíčových principů. Prvním je přiřazení výstupní hodnoty do parametru @count, který vrací počet produktů splňujících podmínku. Druhý SELECT příkaz vrací samotné produkty, jejichž cena je vyšší než uvedená hodnota. Tato struktura umožňuje efektivní práci s daty na serveru, čímž šetří čas při práci s velkými objemy dat.

Dalším krokem je implementace volání této procedury v aplikaci napsané v jazyce C#. V programu je uživateli nabídnuta možnost zvolit, zda použije textový SQL příkaz nebo uloženou proceduru. K tomu se používá následující část kódu:

csharp
Copy code
SqlCommand cmd = connection.CreateCommand();

WriteLine("Execute command using:");
WriteLine("  1 - Text");
WriteLine("  2 - Stored Procedure");
WriteLine();
Write("Press a key: ");
key = ReadKey().Key;
WriteLine();
WriteLine();
SqlParameter p1, p2 = new(), p3 = new();
if (key is ConsoleKey.D1 or ConsoleKey.NumPad1)
{
    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT ProductId, ProductName, UnitPrice FROM Products WHERE UnitPrice > @price";
    cmd.Parameters.AddWithValue("price", price);
}
else if (key is ConsoleKey.D2 or ConsoleKey.NumPad2)
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "GetExpensiveProducts";
    p1 = new() { ParameterName = "price", SqlDbType = SqlDbType.Money, SqlValue = price };
    p2 = new() { Direction = ParameterDirection.Output, ParameterName = "count", SqlDbType = SqlDbType.Int };
    p3 = new() { Direction = ParameterDirection.ReturnValue, ParameterName = "rv", SqlDbType = SqlDbType.Int };
    cmd.Parameters.Add(p1);
    cmd.Parameters.Add(p2);
    cmd.Parameters.Add(p3);
}
SqlDataReader r = await cmd.ExecuteReaderAsync();

Po dokončení čtení dat se pomocí následujícího kódu uzavírá data reader a zobrazují se výstupy:

csharp
Copy code
await r.CloseAsync();
WriteLine($"Output count: {p2.Value}");
WriteLine($"Return value: {p3.Value}");
await connection.CloseAsync();

Pokud procedura vrací výsledky a parametry, je nutné nejprve uzavřít čtecí mechanismus, než můžeme číst hodnoty výstupních parametrů.

Další klíčovou oblastí pro efektivní práci s databázemi je využití Entity Frameworku (EF Core), který je ORM nástrojem využívajícím ADO.NET pro práci s SQL Serverem. EF Core je vyšší úroveň abstrakce, což znamená, že může být snadnější pro použití, ale není tak efektivní jako přímé používání ADO.NET. S EF Core lze pracovat s různými typy databází, nejen s tradičními relačními databázemi, ale i s moderními cloudovými databázemi, jako je Azure Cosmos DB nebo MongoDB.

Při práci s EF Core existují dva hlavní přístupy:

1. Database First: Vytváříte model, který odpovídá již existující databázi.

2. Code First: Nejprve vytváříte model v kódu, a poté EF Core použije tento model k vytvoření databáze.

Pokud máte již existující databázi, můžete využít nástroje pro generování modelu na základě existujících tabulek, což je proces známý jako scaffolding. Tento přístup vám umožňuje generovat základní třídy, které odpovídají struktuře databázových tabulek, a následně je můžete upravovat podle potřeb aplikace. S nástrojem dotnet-ef můžete spustit příkazy pro generování modelu a také pro aplikování migrací, což je velmi užitečné při vývoji a správě databázových schémat.

Pokud jde o definici modelu v EF Core, používají se konvence, anotace a Fluent API pro správnou definici struktur. Například, název tabulky v databázi by měl odpovídat názvu DbSet vlastnosti v DbContext třídě, a názvy sloupců odpovídají názvům vlastností ve třídách entit.

Jak vytvořit a spravovat zdroje v Azure Cosmos DB: První kroky a operace CRUD

Pro efektivní práci s Azure Cosmos DB je klíčové porozumět základním operacím pro vytváření a správu databází a kontejnerů, a to jak v místním emulátoru, tak v cloudu. K vytvoření databáze, jako je například „Northwind“, a kontejneru, jako je „Products“, využíváme specifické metody a volání API, které nám umožňují nejen inicializaci těchto objektů, ale i jejich správu v reálném čase. V následujícím popisu se podíváme na příklad kódu v jazyce C#, který ukazuje proces tvorby těchto zdrojů, a zároveň na důležité kroky, které je třeba dodržet při používání Azure Cosmos DB.

Při práci s Cosmos DB se začíná voláním metody, která vytvoří požadované zdroje, tedy databázi a kontejner. V příkladu je použita třída CosmosClient z knihovny Microsoft.Azure.Cosmos, která slouží k připojení k databázi a následným operacím. Pro připojení k místnímu emulátoru nebo cloudu použijete konkrétní hodnoty pro endpointUri a primaryKey, přičemž pro emulátor jsou tyto hodnoty sdílené mezi všemi uživateli. Pokud se připojujete k cloudu, je nutné použít svůj vlastní účet a odpovídající klíč.

Po vytvoření připojení k databázi je nezbytné specifikovat název databáze a průchodnost v RUs (Request Units) za sekundu. Tento krok je zásadní pro optimalizaci výkonu a nákladů. V případě tohoto příkladu je použita hodnota 400 RU/s pro databázi „Northwind“. Poté, co je databáze vytvořena, přichází na řadu tvorba kontejneru. Tento kontejner musí mít definovaný název a cestu k partition key, což je klíčový prvek pro správu dat v systému Cosmos DB.

V případě kontejneru je také možné nastavit indexovací politiku, což určuje způsob, jakým budou data v kontejneru indexována. V tomto příkladu je použita konzistentní indexace, která zajistí, že všechny položky budou automaticky indexovány, pokud explicitně nebudou vyloučeny. Při vytváření kontejneru se opět definuje průchodnost (RU/s), která může být rozdělena mezi databázi a její kontejnery.

Jakmile jsou databáze a kontejnery vytvořeny, obvykle se objeví odpověď ve formě HTTP status kódu, která informuje o výsledku operace. Status kódy 200 OK znamenají, že požadovaný zdroj již existuje, a kód 201 Created signalizuje úspěšné vytvoření nového zdroje.

Po dokončení těchto základních kroků je nutné věnovat pozornost správě dat v Cosmos DB, což zahrnuje operace CRUD (Create, Read, Update, Delete) pomocí Core SQL API. Mezi nejběžnější metody patří ReadItemAsync, CreateItemAsync, DeleteItemAsync, PatchItemAsync, a ReplaceItemAsync. Tyto metody umožňují manipulovat s položkami v databázi, přičemž každá operace vrací odpověď, která obsahuje informace o požadavku, například množství požadavků (RequestCharge), status kód a diagnostické informace.

Aby se operace CRUD mohly správně provádět, je nezbytné chápat význam partition key, což je klíčový parametr, který slouží k rozdělení dat do různých fyzických oddílů (partitions). Partition key je důležitý nejen pro výkonnost a distribuci dat, ale také pro optimalizaci nákladů na operace v rámci Cosmos DB. Například pro kontejnery, jako je „Products“, je partition key definován na základě atributu productId. Tato volba pomáhá nejen při rozdělení dat, ale i při škálování aplikací a správě požadavků.

Vývojáři by měli rovněž věnovat pozornost diagnostickým informacím, které jsou součástí odpovědí na požadavky. Tyto informace jsou klíčové pro řešení problémů, optimalizaci výkonu a monitorování spotřeby prostředků. Správné nastavení indexování a zajištění efektivního používání RUs může výrazně ovlivnit náklady a výkon celé aplikace.

Jak pracovat s produkty v Azure Cosmos DB pomocí SQL dotazů

V práci s Azure Cosmos DB, databází, která podporuje NoSQL modely, hraje důležitou roli schopnost efektivně manipulovat s daty. Tento proces zahrnuje nejen vytváření, aktualizaci a mazání položek, ale také schopnost provádět komplexní dotazy nad těmito daty pomocí SQL, které je potřeba dobře pochopit, aby bylo možné vytvářet flexibilní a efektivní aplikace. V následujícím textu se podíváme na konkrétní kroky, jak efektivně pracovat s produkty v databázi Cosmos a jak implementovat SQL dotazy pro správu těchto položek.

Začněme vytvořením položek do kontejneru, který obsahuje produkty. Pokud používáte emulator Azure Cosmos DB nebo Azure portal Data Explorer, můžete ověřit, že v kontejneru Products je skutečně 77 položek produktů. Každá položka je v systému identifikována jedinečným ID a lze k ní přistupovat pomocí standardních SQL dotazů. Takto provedený dotaz se bude účtovat podle počtu zpracovaných jednotek RUs (Request Units), což je důležitý faktor pro výpočet nákladů a optimalizaci výkonu.

Po přidání položek je možné provádět dotazy, které načítají všechny produkty v kontejneru. Příklad metody pro načítání produktů vypadá takto:

csharp
Copy code
static async Task ListProductItems(string sqlText = "SELECT * FROM c") {
    SectionTitle("Listing product items");
    try {
        using (CosmosClient client = new(accountEndpoint: endpointUri, authKeyOrResourceToken: primaryKey)) {
            Container container = client.GetContainer(databaseId: "Northwind", containerId: "Products");
            WriteLine("Running query: {0}", sqlText);
            QueryDefinition query = new(sqlText);
            using FeedIterator resultsIterator = container.GetItemQueryIterator(query);
            if (!resultsIterator.HasMoreResults) {
                WriteLine("No results found.");
            }
            while (resultsIterator.HasMoreResults) {
                FeedResponse products = await resultsIterator.ReadNextAsync();
                WriteLine("Status code: {0}, Request charge: {1} RUs.", products.StatusCode, products.RequestCharge);
                WriteLine("{0} products found.", arg0: products.Count);
                foreach (ProductCosmos product in products) {
                    WriteLine("id: {0}, productName: {1}, unitPrice: {2}", arg0: product.id, arg1: product.productName, arg2: product.unitPrice);
                }
            }
        }
    } catch (HttpRequestException ex) {
        WriteLine("Error: {0}", arg0: ex.Message);
        WriteLine("Hint: Make sure the Azure Cosmos Emulator is running.");
    } catch (Exception ex) {
        WriteLine("Error: {0} says {1}", arg0: ex.GetType(), arg1: ex.Message);
    }
}

Další operací, kterou často provádíme, je mazání položek. K tomu se využívá metoda pro iteraci přes produkty a jejich odstranění z kontejneru:

csharp
Copy code
static async Task DeleteProductItems() {
    SectionTitle("Deleting product items");
    double totalCharge = 0.0;
    try {
        using (CosmosClient client = new(accountEndpoint: endpointUri, authKeyOrResourceToken: primaryKey)) {
            Container container = client.GetContainer(databaseId: "Northwind", containerId: "Products");
            string sqlText = "SELECT * FROM c";
            WriteLine("Running query: {0}", sqlText);
            QueryDefinition query = new(sqlText);
            using FeedIterator resultsIterator = container.GetItemQueryIterator(query);
            while (resultsIterator.HasMoreResults) {
                FeedResponse products = await resultsIterator.ReadNextAsync();
                foreach (ProductCosmos product in products) {
                    WriteLine("Delete id: {0}, productName: {1}", arg0: product.id, arg1: product.productName);
                    ItemResponse response = await container.DeleteItemAsync(id: product.id, partitionKey: new(product.id));
                    WriteLine("Status code: {0}, Request charge: {1} RUs.", response.StatusCode, response.RequestCharge);
                    totalCharge += response.RequestCharge;
                }
            }
        }
    } catch (HttpRequestException ex) {
        WriteLine("Error: {0}", arg0: ex.Message);
        WriteLine("Hint: Make sure the Azure Cosmos Emulator is running.");
    } catch (Exception ex) {
        WriteLine("Error: {0} says {1}", arg0: ex.GetType(), arg1: ex.Message);
    }
    WriteLine("Total requests charge: {0:N2} RUs", totalCharge);
}

V tomto příkladu se po načtení všech produktů provádí jejich mazání a sledování nákladů na tuto operaci.

Pochopení SQL dotazů je nezbytné pro efektivní práci s daty v Cosmos DB. Kromě základních operací jako SELECT, WHERE, IN a ORDER BY lze používat i agregace jako COUNT, AVG, a SUM. Tyto funkce jsou užitečné pro analýzu velkých objemů dat a jejich efektivní vyhledávání. Důležité je také umět filtrovat a používat operátory jako LIKE pro vzorové hledání nebo BETWEEN pro specifikaci rozsahů hodnot.

Například dotaz, který vybírá pouze produkty v kategorii "Beverages", vypadá takto:

csharp
Copy code
await ListProductItems("SELECT p.id, p.productName, p.unitPrice FROM Items p WHERE p.category.categoryName = 'Beverages'");

Výstup z tohoto dotazu obsahuje pouze produkty, které spadají do kategorie "Beverages", což je příklad, jak efektivně filtrovat data a získat pouze relevantní položky.

Důležitým faktorem při práci s Cosmos DB je optimalizace nákladů na zpracování dotazů. Jedním z nástrojů, který nám v tomto ohledu pomůže, je metrika RUs. Každý dotaz a každá operace mají svoji cenu, která se měří v jednotkách RUs. Je proto důležité minimalizovat počet operací a optimalizovat dotazy tak, aby byly co nejefektivnější.