Poznáváme C# a Microsoft .NET – 57. díl – Použití uložených procedur

Volání uložených procedur a tvorba parametrických příkazů jsou témata, která se pokusím osvětlit v tomto díle seriálu. Uvidíme například jak je možné pomocí parametrů předávat argumenty pro uloženou proceduru nebo jak si případný výstupní argument v naší aplikaci vyzvednout.

Příkazy s parametry

Možná si vzpomínáte na minulý díl, kde jsem pro sestavení příkazu na základě hodnoty určitého parametru použil celkem přímočarý postup pomocí metody String.Format.

..
IDbCommand lCommand = lConnection.CreateCommand();
lCommand.CommandText = String.Format("delete from employee where ID={0}", id);
..

Myslím, že mnohem lepší postup by byl, kdybych využil příkazu s parametry. Obzvláště by se hodilo přístup pomocí parametrů použit v situaci, kdy do databáze vkládáme nikoliv znaky nebo číslo, ale například nějaká binární data (například obrázky). Každý příkaz v ADO .NET (implementace rozhraní IDbCommand) může být parametrický a to tak, že využijeme jeho vlastnosti Parameters, která je typu IDataParameterCollection.

Stejně jako u všech rozhraní v ADO .NET je i toto rozhraní implementováno jednotlivými data providery, takže například pro SQL server je to SqlParameterCollection, pro Oracle je to OracleParameterCollection a tak dále. Do této kolekce přidáme parametry, které jsou představovány instancemi konkrétního typu implementujícího rozhraní IDataParameter (SqlParameter, OracleParameter..).Všechny standardní (ty co jsou dodávány s .NET frameworkem) implementace tohoto rozhraní implementují také rozhraní IDbDataParameter, které rozšiřuje rozhraní IDataParameter. Takže pokud bychom chtěli použit parametry pro příkaz v tom nejjednodušším scénáři, kterým je dosazení hodnot do příkazu (jinak než pomocí String.Format) provedeme to následujícím způsobem.

public class SimpleParameters
{
  public static void Run()
  {
    IDbConnection lConnection = new SqlConnection(@"data source=`dellak\sql1`; initial catalog=`zive`; integrated security=`sspi`");
    IDbCommand lCommand = lConnection.CreateCommand();
    //vytvoreni prikazu s parametrem
    lCommand.CommandText = "select * from employee where
SurName=@surname";
    IDataParameterCollection lParameters = lCommand.Parameters;
    //definice parametru
    IDbDataParameter lParam = lCommand.CreateParameter();
    //nazev parametru
    lParam.ParameterName = "@surname";
    //hodnota parametru
    lParam.Value = "Fisar";
    lParam.DbType = DbType.String;
    lParameters.Add(lParam);
    IDataReader lReader = null;
    try
    {
      lConnection.Open();
      lReader = lCommand.ExecuteReader(CommandBehavior.CloseConnection);
      //vypsani vysledku
      while (lReader.Read())
      {
        for(int i = 0; i < lReader.FieldCount; i++)
        {
          Console.Write("{0} - {1}, ", lReader.GetName(i), lReader.GetValue(i));
        }
        Console.WriteLine();
      }
    }
    catch(SqlException ex)
    {
      Console.WriteLine("Nastala vyjimka : {0}", ex);
    }
    finally
    {
      if (lReader != null && !lReader.IsClosed)
      {
        lReader.Close();
      }
    }
  }

}

Pomocí instančních vlastností ParameterName a Value nastavíme jméno parametru respektive jeho hodnotu. Nastavení jména parametru (@surname) je v tomto případě nutné a musí být shodné s názvem specifikovaným v textu příkazu. Specifikace názvů parametrů často záleží na konkrétním datovém zdroji. V tomto příkladu, který je napsán pro použití nad databází MS SQL je potřeba použít název ve tvaru @nazevParametru jinak se nám parametrický dotaz nepodaří vykonat. Například při použití poskytovatele dat OLEDB (OLEDB data provider) se pro vyjádření parametrů v textu příkazu používají otazníky. Také je v častých případech potřebné nastavit datový typ hodnoty parametru, což zařídíme skrze instanční vlastnost DbType.

Jak jste si jistě všimli, tak jsem se snažil příklad psát s minimálním použití konkrétních tříd a raději jsem programoval oproti rozhraní (samozřejmě v reálných aplikacích by se použili komplexnější přístupy). Pokud bychom ovšem psali aplikaci plně s použitím typů pro SQL server data provider mohli bychom například pro určení datového typu hodnoty parametru použít výčet (SqlDbType) umožňující zvolit datový typ přesně odpovídající typu našeho sloupce v databázi.

...
SqlCommand lCommand = new SqlCommand("select * from employee where
SurName=@surname", lConnection);
SqlParameter lParam = new SqlParameter("@surname", "Fisar");
lParam.SqlDbType = SqlDbType.NVarChar;
lCommand.Parameters.Add(lParam);
...

Použití uložených procedur

V aplikacích kde se pracuje s databázemi je poměrně časté, že se pro zajištění některých operací s daty využívají uložené procedury (samozřejmě pokud je námi zvolený databázový server podporuje) . Pokud se rozhodnete tento přístup ve vaší aplikaci využít, máte samozřejmě možnost tyto vámi napsané uložené procedury volat i z aplikace pro .NET framework. Pro náš příklad jsem vytvořil velmi jednoduchou uloženou proceduru pro MS SQL server v jazyku T-SQL, jejíž definice vypadá takto:

CREATE PROCEDURE FindEmployee (
@surname nvarchar(40)
) AS
begin
  select * from employee where
SurName=@surname
end

Tato ukázková procedura řeší stejnou funkčnost jako předchozí příklad tj. vyhledá záznam o zaměstnanci podle zadaného příjmení, které je předáno ve formě vstupního parametru. A jak tedy takovouto uloženou proceduru z naší aplikace zavoláme? To ukazuje následující příklad.

public class StoredProcedureCall
{
  public static void Run()
  {
    IDbConnection lConnection = new SqlConnection(@"data source=`dellak\sql1`;initial catalog=`zive`;integrated security=`sspi`");
    IDbCommand lCommand = lConnection.CreateCommand();
    //nastaveni nazvu procedury
    lCommand.CommandText = "FindEmployee";
    //nastaveni typu prikazu na ulozenou proceduru
    lCommand.CommandType = CommandType.StoredProcedure;
    IDataParameterCollection lParameters = lCommand.Parameters;
    //vytvoreni parametru
    IDbDataParameter lParam = lCommand.CreateParameter();
    //nazev parametru
    lParam.ParameterName = "@surname";
    //hodnota parametru
    lParam.Value = "Fisar";
lParam.DbType = DbType.String;
    lParameters.Add(lParam);
    IDataReader lReader = null;
    try
    {
      lConnection.Open(); 
      lReader = lCommand.ExecuteReader(CommandBehavior.CloseConnection);
      //vypsani vysledku
      while (lReader.Read())
      {
        for(int i = 0; i < lReader.FieldCount; i++)
        {
          Console.Write("{0} - {1}, ", lReader.GetName(i), lReader.GetValue(i));
        }
        Console.WriteLine();
      }
    }

    ...
   
  }
}

To že se jedná o volání uložené procedury, určíme pomocí instanční vlastnosti CommandType typu IDbCommand. Tato vlastnost je typu CommandType, což je výčet k určení druhu příkazu a v případě volání uložené procedury zvolíme hodnotu StoredProcedure (implicitně je nastaveno na Text). Název uložené procedury, která bude zavolána, specifikujeme vlastností CommandText, kterou jsme v případě standardního příkazu používali k definici textu příkazu v jazyku SQL. Po té je ještě potřeba předat hodnoty jednotlivých parametrů uložené procedury a následně je již možné proceduru zavolat a vypsat její výsledek.

Zpracování výstupních parametrů

Pokud jste již s uloženými procedurami pracovali, tak víte, že je možné definovat i takzvané výstupní parametry procedury (pomocí slova output). Pochopitelně je možné si hodnoty těchto výstupních parametrů v aplikaci pro .NET framework také vyzvednout. V příkladu bude volána tato jednoduchá uložená procedura, která pouze zjistí počet záznamu v tabulce a tuto hodnotu uloží do výstupního parametru.

CREATE PROCEDURE GetCount
(@result int output)
AS
begin
select @result =  count(*) from employee
end

Vyzvednutí hodnoty tohoto parametru v aplikaci zařídíme následujícím způsobem.

public class OutputParameter
{
  public static void Run()
  {
    IDbConnection lConnection = new SqlConnection(@"data source=`dellak\sql1`;initial catalog=`zive`;integrated security=`sspi`");
    IDbCommand lCommand = lConnection.CreateCommand();
    //nastaveni nazvu procedury
    lCommand.CommandText = "GetCount";
    //nastaveni typu prikazu na ulozenou proceduru
    lCommand.CommandType = CommandType.StoredProcedure;
    IDataParameterCollection lParameters = lCommand.Parameters;
    //vytvoreni parametru
    IDbDataParameter lParam = lCommand.CreateParameter();
    //nazev parametru
    lParam.ParameterName = "@result";
    //nastaveni typu parametru na vystupni
    lParam.Direction = ParameterDirection.Output;
    lParam.DbType = DbType.Int32;
    lParameters.Add(lParam);
    IDataReader lReader = null;
    try
    {
      lConnection.Open(); 
      lReader = lCommand.ExecuteReader(CommandBehavior.CloseConnection);
      //zobrazeni vysledku
      Console.WriteLine("Vysledek : {0}", lParam.Value);
    }

    ... 

  }
}

Při používání výstupních parametrů je důležité nastavit vlastnost Direction instance parametru na hodnotu výčtu ParameterDirection na Output. Po vykonání uložené procedury si hodnotu výstupního parametru vyzvedneme pomocí instanční vlastnosti Value.

Příklady jsou ke stažení zde.

Témata článku: Software, Microsoft, Programování, Public, From, Select, Catch

1 komentář

Nejnovější komentáře

  • Pavel Polívka 24. 11. 2007 13:50:00
    Programovou oflline verzi seriálu naleznete ke stažení na...
Určitě si přečtěte

Původní Starcraft: Brood War je nyní zdarma. Konec práce! Jde se pařit

Původní Starcraft: Brood War je nyní zdarma. Konec práce! Jde se pařit

** Legendární hra Starcraft je nyní k dispozici zdarma ** Chystá se i nová remasterovaná verze s hezčí grafikou

19.  4.  2017 | Jakub Čížek | 25

Brno otevřelo největší českou dílnu pro bastlíře. Kladívka, vrtačky, 3D tiskárny, laserové řezačky. Je tu vše

Brno otevřelo největší českou dílnu pro bastlíře. Kladívka, vrtačky, 3D tiskárny, laserové řezačky. Je tu vše

** Máte nápad, ale chybí vám stroje a pořádná dílna? ** Chcete postavit ptačí budku, nebo krabičku pro Arduino? ** Brno otevřelo svůj FabLab – laboratoř pro bastlíře

19.  4.  2017 | Jakub Čížek | 31

Český Google Překladač začal používat umělou inteligenci. Konec „drahoušků zákazníků“

Český Google Překladač začal používat umělou inteligenci. Konec „drahoušků zákazníků“

** Google ve svém překladači roky používal statistickou technologii ** Nyní zavádí strojové učení a neuronové sítě ** Rozdíl by měl být zvláště na větších textech patrný už nyní

20.  4.  2017 | Jakub Čížek | 31


Aktuální číslo časopisu Computer

První test AMD Ryzen

Velké testy: 22 powerbank a 8 bezdrátových setů

Radíme s koupí Wi-Fi routeru

Co dokáží inteligentní domy?