reklama

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...
reklama
Určitě si přečtěte

UPC překopli páteřní kabel. V Brně i druhý den nejede internet ani kabelovka

UPC překopli páteřní kabel. V Brně i druhý den nejede internet ani kabelovka

** V Brně byl velký výpadek služeb UPC ** Důvodem je překopnutý páteřní kabel ** V některých lokalitách služby stále nefungují

5.  12.  2016 | Jakub Čížek | 100

17 expertek Microsoftu předpovědělo rok 2027. Splní se alespoň něco?

17 expertek Microsoftu předpovědělo rok 2027. Splní se alespoň něco?

** Zmizí klasické vyhledávače ** Budeme programovat buňky ** Kvantové počítače překonají šifry

6.  12.  2016 | Jakub Čížek | 34

ASUS ZenBook 3 se začal prodávat v Česku. Je ve všem lepší než MacBook, ale bude to stačit?

ASUS ZenBook 3 se začal prodávat v Česku. Je ve všem lepší než MacBook, ale bude to stačit?

** Novinka od Asusu míří přímo proti MacBooku od Applu ** Nabídne daleko více výkonu za stejné peníze

2.  12.  2016 | David Polesný | 144

11 tipů na dobrý stolní počítač: od základu po herní mašiny

11 tipů na dobrý stolní počítač: od základu po herní mašiny

** Postavte si stolní počítač! Máme pro vás 11 vzorových sestav s rozpisem komponent ** Většina tipů cílí na hráče, věnujeme se ale i základnímu PC a počítačům na střih videa ** Nadělte si nový počítač třeba pod stromeček

5.  12.  2016 | Adam Kahánek | 73


reklama