Tento článek je volným navázáním na článek Komunikace s MSSQL databází (VB.NET, C#). V něm vysvětluji některé základní principy a termíny, jenž budu zde používat.
Úvod do problému předávání dat
Aplikace pracující s databází potřebuje v naprosté většině případů při komunikaci předávat nějaké parametry. Protože základním prvkem komunikace je textový SQL jazyk, tak nezodpovědní lidé jej zneužívají a vkládají hodnoty přímo do něj. Nebezpečnost a obecné nevýhody takového chování jsem popisoval v původním článku.
Microsoft SQL Server nabízí velmi pohodlné použití parametrů. Parametry jsou hodnoty, které předáváme bokem od SQL příkazu a při provádění jsou inicializovány jako proměnné nebo parametry uložené procedury přímo na serveru.
Vše funguje skvěle. Tedy alespoň dokud si nevymyslíte, že potřebujete získat hodnotu z databáze pro 1000 výrobků nebo naopak chcete zpět uložit 1000 hodnot. Kam to spěje je snad jasné – buď vyvolání 1000 příkazů z aplikace nebo vygenerování dlouhého příkazu s parametry pro každý z 1000 výrobků. Nemusím snad psát, že takové řešení je většinou velmi pomalé a neúsporné pro klientskou aplikaci i databázový server.
Jak z toho ven?
Řešení tu byla celá řada. Od těch na první pohled hrozivých (slepení hodnot do dlouhého textu a parsování na straně databázového systému), až po celkem rozumných a relativně výkonných (vytvoření tabulky uchovávající dočasná data pod specifickým klíčem, který se pak předal proceduře, jenž tyto data využila).
Naštěstí v Microsoft SQL Serveru 2008 je novinka označovaná jako table valued parameters – tedy parametry s hodnotou tabulek. Tato vlastnost dává možnost vytvořit na straně SQL Serveru datový typ představující seznam – plnohodnotnou tabulku. Smutné je, že tento výdobytek tu už nebyl v předchozí verzi – v SQL Server 2005 ještě není implementován!
Nejprve musíme datový typ reprezentující seznam definovat. Příklad kódu na vytvoření ukázkového typu MujTypTabulky se dvěma sloupci následuje. Všimněte si, že vnitřek závorky je identický s definicí běžné tabulky:
create type [MujTypTabulky] as table (
[sloupec1] int not null,
[sloupec2] bit not null
)
Vytvořme si nyní jednoduchou uloženou proceduru přijímající tento typ. Vrací všechny řádky z předané tabulky. Aby se s daty alespoň něco stalo, vynásobíme sloupec1 předanou hodnotou a vyfiltrujeme řádky podle predikátu sloupec2 = 1. Ačkoliv parametr přenášející tabulku definujeme zcela běžným způsobem, musíme uvést klíčové slovo READONLY – tabulka bude pouze pro čtení a její změny se nevrací na klienta. Pokud chcete vracet nějaké změny, vracejte je ve výsledku procedury.
create procedure [VynasobHodnoty] (
@nasobek int,
@data [MujTypTabulky] readonly
) as begin
select
[sloupec1] * @nasobek as [Hodnota],
[sloupec1] as [PuvodniHodnota]
from
@data
where
[sloupec2] = 1
end
Abychom si ověřili, že vše funguje, vyvolání následujícího kódu nadeklaruje v T-SQL proměnnou našeho vytvořeného tabulkového typu, naplní ji nějakými řádky a ty předá uložené proceduře VynasobHodnoty. Všimněte si práce s proměnnou @data. Je naprosto stejná jako s kteroukoliv jinou tabulkou (příkaz insert) a zároveň její deklarace a předání se shoduje se zápisem běžné proměnné:
-- deklarace parametru
declare @data [MujTypTabulky]
-- vložit data do parametrové tabulky
insert into @data ([sloupec1],[sloupec2])
values
(1, 1),
(2, 0),
(3, 1),
(4, 1),
(5, 0),
(6, 1),
(7, 0)
-- vyvolat proceduru
exec [VynasobHodnoty] 2, @data
Výsledkem bude provedení select dotazu uvnitř procedury nad data předanými proměnnou typu MujTypTabulky.
Výhody
Shrňme si základní výhody používání parametrů typu tabulka:
- Tyto typy vycházejí z tabulky a mohou obsahovat běžné rozšíření jako klíče nebo indexy
- Proměnné jsou silně typové – jejich používání je hlídáno a nelze například uložit proceduru, kde použijete neexistující sloupec v typu tabulky
- Předávání dat v tabulce je řádově rychlejší a úspornější než jiná řešení
- Vyvolání kódu v SQL Serveru lze z klienta provést najednou (předání tabulky jako parametru + vyvolání procedury na jeden zátah)
Table valued parameters z C# a VB.NET
Princip používání parametrů jsem již vysvětloval v minulém článku. Naprosto stejným způsobem předáváme i parametr typu tabulka. Pouze místo jednoduchého typu využijete pro tento účel zcela příhodně zvolený DataTable pro uchování tabulky. Dále je potřeba nastavit parametru typ parametru na SqlDbType.Structured – ten představuje parametr pro předání table valued parametru. Více v ukázce kódu.
Nejdříve musíme vytvořit a naplnit objekt tabulky, jenž předáme. Pokud ji nemáte silně typovou (vytvořenou pomocí DataSet designéru), lze ji nadefinovat za běhu. Signatura (sloupce) musí být identické s definovaným typem. Pro ukázkový typ tabulky například touto funkcí:
static DataTable GetDataTableWithData()
{
// vytvořit tabulku
DataTable retVal = new DataTable();
// vytvořit sloupce
retVal.Columns.Add("sloupec1", typeof(int));
retVal.Columns.Add("sloupec2", typeof(bool));
// naplnit nějakými daty (řádky)
for (int i = 0; i < 20; i++)
{
retVal.Rows.Add(i /* sloupec1 */, true /* sloupec2 */);
}
// vrátit výsledek
return retVal;
}
Public Function GetDataTableWithData() As DataTable
' vytvořit tabulku
Dim retVal As New DataTable()
' vytvořit sloupce
retVal.Columns.Add("sloupec1", GetType(Integer))
retVal.Columns.Add("sloupec2", GetType(Boolean))
' naplnit nějakými daty (řádky)
For i As Integer = 0 To 19
retVal.Rows.Add(i, True)
Next
' vrátit výsledek
Return retVal
End Function
Využíváme třídy z těchto jmenných prostor, přidejte si je tedy na začátek souboru s kódem:
using System.Data;
using System.Data.SqlClient;
Imports System.Data
Imports System.Data.SqlClient
Vizuální znázornění objektu DataTable vráceného naší funkcí vypadá takto:
Teď je potřeba tabulku předat jako parametr při volání uložené procedury:
using (var connection = new SqlConnection(@"server=.\SQLEXPRESS;Integrated Security=SSPI;Database=mojedb"))
{
// otevřít připojení
connection.Open();
using (var command = new SqlCommand())
{
// nastavíme připojení
command.Connection = connection;
// budeme vyvolávat uloženou proceduru "VynasobHodnoty"
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "VynasobHodnoty";
// předání parametru bez dalšího nastavování
command.Parameters.AddWithValue("nasobek", 2);
// vytvoření parametru pro předání strukturovaných dat
var parameter = new SqlParameter();
parameter.ParameterName = "data";
parameter.SqlDbType = SqlDbType.Structured;
parameter.Value = GetDataTableWithData(); // ziskat tabulku hodnot
command.Parameters.Add(parameter);
// vyvolat příkaz a číst hodnoty
using (var reader = command.ExecuteReader())
{
// projít všechny řádky
while (reader.Read())
{
Console.WriteLine("Čteme řádek | Hodnota: {0} | Původní hodnota: {1}",
reader["hodnota"],
reader["puvodniHodnota"]
);
}
}
}
}
// pokud počkáme na stisknutí klávesy enter
Console.ReadLine();
Using connection As New SqlConnection("server=.\SQLEXPRESS;Integrated Security=SSPI;Database=mojedb")
' otevřít připojení
connection.Open()
Using command As New SqlCommand()
' nastavíme připojení
command.Connection = connection
' budeme vyvolávat uloženou proceduru "VynasobHodnoty"
command.CommandType = System.Data.CommandType.StoredProcedure
command.CommandText = "VynasobHodnoty"
' předání parametru bez dalšího nastavování
command.Parameters.AddWithValue("nasobek", 2)
' vytvoření parametru pro předání strukturovaných dat
Dim parameter As New SqlParameter()
parameter.ParameterName = "data"
parameter.SqlDbType = SqlDbType.Structured
parameter.Value = GetDataTableWithData() ' ziskat tabulku hodnot
command.Parameters.Add(parameter)
' vyvolat příkaz a číst hodnoty
Using reader = command.ExecuteReader()
' projít všechny řádky
Do While reader.Read()
Console.WriteLine("Čteme řádek | Hodnota: {0} | Původní hodnota: {1}", _
reader("hodnota"), _
reader("puvodniHodnota") _
)
Loop
End Using
End Using
End Using
' pokud počkáme na stisknutí klávesy enter
Console.ReadLine()
Pokud si správně nastavíte připojení (databázový server a jméno databáze, do které jste vytvořili typ a proceduru), získáte po spuštění tento výsledek:
Uložená procedura zpracovala celý seznam najednou jediným voláním. Všechny hodnoty vynásobila číslem 2.
Závěr
Článek demonstruje způsob využití velmi užitečných typů tabulek v SQL Serveru 2008 nazývaných table valued parameters. A to jak z T-SQL kódu, tak z jazyků C# a VB.NET. Používání této techniky slouží k zrychlení a usnadnění předávání seznamů hodnot nebo dokonce celých tabulek.