Předávání tabulek do MSSQL 2008 (VB.NET, C#)

Tomáš Jecha, MVP, MCSD       01.01.2010       C#, VB.NET, SQL, ADO.NET, .NET       15576 zobrazení

Čas od času potřebujeme předat databázi místo jednoho parametru celý seznam. Lepších, či horších způsobů, jak to udělat, je celá řada. Nicméně teprve SQL Server 2008 přichází s funkcí table valued parameters, která dovoluje se seznamy pracovat velmi elegantně. A to jak přímo v kódu SQL Serveru, tak v našich oblíbených jazycích Visual Basic .NET nebo C#.

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

C#

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

VB.NET

     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:

C#

 using System.Data;
using System.Data.SqlClient;

VB.NET

 Imports System.Data
Imports System.Data.SqlClient

Vizuální znázornění objektu DataTable vráceného naší funkcí vypadá takto:

DataTable

Teď je potřeba tabulku předat jako parametr při volání uložené procedury:

C#

     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();

VB.NET

         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:

Console output - table valued parameter

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.

 

hodnocení článku

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

 

Mohlo by vás také zajímat

Jednoduchý scheduler v .NETu

Asi to znáte – máte nějaký složitější systém na zpracování velkého objemu dat a čas od času potřebujete vykovat nějakou automatizovanou údržbu – typicky smazat všechny položky starší než několika dní. Možností, jak toho dosáhnout, je hodně. Snažil jsem se vymyslet něco jednoduchého a efektivního.

Jeden antipattern, který dokáže asynchronní programování pořádně znepříjemnit

Co čeká webové vývojáře na platformě .NET, představení .NET Core 1.0

 

 

Nový příspěvek

 

Diskuse: Předávání tabulek do MSSQL (VB.NET, C#)

Tohle se bude hodit, o tom sem ani nevěděl. Jen by mě zajímalo, jestli návrhář typového DataSetu umí vytvořit kód pro aktualizaci dat právě s využitím těchto tabulkových hodnot (řekl bych že ne). Momentálně to nemůžu vyzkoušet protože nemám nainstalován SQL Server.

nahlásit spamnahlásit spam -2 / 2 odpovědětodpovědět

Také myslím, že to zatím nedokáže. A nečekám ani, že tuto funkčnost dodělají.

nahlásit spamnahlásit spam 0 odpovědětodpovědět

Diskuse: Předávání tabulek do MSSQL (VB.NET, C#)

Jen bych dodal, že kromě System.Data.DataTable je možné použít i IEnumerable<System.Data.Common.DbDataRecord> (tedy DbDataReader).

nahlásit spamnahlásit spam 1 / 1 odpovědětodpovědět

Díky za doplnění Michale.

Jak píšeš, jde předat vše, co implementuje IEnumerable pro abstraktní třídu reprezentující datový záznam DbDataRecord. To podporuje jak DataTable, tak DbDataReader.

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