Úvod do ADO.NET (1.)

V první části trojdílného seriálu zabývajícím se základy ADO.NET se podíváme na jeho základní třídy a metody. Povíme si něco o poskytovatelích připojení, connection poolingu. Uvidíme pár praktický příkladů, mimo jiné, jak do databáze vkládat a vybírat binární data( např. obrázky).

Z Obsahu:

• Poskytovatelé připojení
• Navázání připojení
• Otevírání a zavírání připojení
• Connection Pooling
• Třídy příkazů
• Vykonávání příkazů
• SqlCommand.ExecuteNonQuery
• SqlCommand.ExecuteScalar
• SqlCommand.ExecuteReader

Poskytovatelé připojení

Před jakoukoliv prací s databází je nutné se rozhodnout jakou databázi vlastně budete používat. Pokud chcete použít Microsoft SQL Server 7.0 nebo vyšší, použijte poskytovatele .NET SQL serveru. V zásadě platí pokud chcete použít jakoukoliv jinou databázi použijte poskytovatele .NET OLE DB( OLE DB je technologie pro přístup k datům používaná .NETem, je to podobné ODBC - Open Database Connectivity ). Tento poskytovatel je pomalejší než poskytovatel .NET SQL Serveru, který celou dobu běhu setrvává v řízeném kódu. Může se stát, že vámi používaná databáze nepůjde otevřít ani přes .NET OLE DB, pak je nutné někde získat funkčního poskytovatele pro tuto databázi nebo použít .NET ODBC společnosti Microsoft, tím se zde ale nebudeme zabývat.

Nás teď spíše zajímá jak s poskytovateli pracovat. Je to v podstatě jednoduché. Pokud používáte poskytovatele .NET SQL Serveru bude vás zajímat jmenný prostor System.Data.SqlClient a pro poskytovatele .NET OLE DB je to System.Data.OleDb. Je příjemné, že funkce v těchto jmenných prostorech se v zásadě lišší jen předponami, např. SqlConnection – OleDbConnection, SqlCommand – OleDbCommand, SqlException – OleDbException… a některé další třídy používané pro prácí s daty fungují se všemi poskytovateli, např. DataSet,DataTable,…

Navázání připojení

Než na databázi vykonáte jakýkoliv příkaz je nutné otevřít spojení. K tomu slouží třída SqlConnection.

SqlConnection pripojeni = new SqlConnection("server=localhost;database=mojeDatabaze;uid=sa;pwd=");

Následující kód je ekvivalentní:

SqlConnection pripojeni = new SqlConnection();
Pripojeni.ConnectionString = "server=localhost;database="+
                             "mojeDatabaze;uid=sa;pwd=";

Parameter server specifikuje adresu MSSQL serveru, v tomto případě je to localhost. Database nám určuje jméno databáze na které chceme provádět příkazy. Uid se dá rovněž zapsat jako User Id, což specifikuje uživatelské jméno a pwd je ekvivalentní s Password, čili heslo.

Tyto parametry rozhodně nejsou jediné. Jejich úplný výčet naleznete v dokumentaci vlastnosti SqlConnection.ConnectionString.

Navázaní připojení přes poskytovatele .NET OLE DB je obdobné, pouze je zde nutné zahrnout parameter provider a nastavit ho na sqloledb pro SQL server nebo případně na msdaora pro Oracle.

Otevírání a zavírání připojení

Ať už pro SqlConnection nebo pro OleDbConnection, chcete-li pracovat s databází, je nutné připojení otevřít.

Pripojeni.Open();

A následně také zavřít

Pripojeni.Close();

Jelikož operace vykonávané nad databází mohou vyvolat výjimky SqlException resp. OleDbException je velmi vhodné celou tuto režii uzavřít do bloku try.

SqlConnection prip = new SqlConnection("server=localhost;database=mojeDatabaze;uid=sa;pwd=");

try
{
   prip.Open();
   //Prikazy
}
catch(SqlException ex)
{
   //Osetreni vyjimky
}
finally
{
   prip.Close();
}

Uzavření připojení v bloku finally se provede vždy. To nám ale nepřekáží, protože uzavření spojení, které není otevřené, ničemu nevadí.

Connection Pooling

Vytvoření spojení se SQL serverem vyžaduje velkou režii a proto je velmi časově nákladné. Je nutné se připojit k serveru, provést autentikaci(ověření uživatele) a vrátit platné spojení. Kdyby se mělo toto všechno provádět při každém otevření stránky, tak by se výkon takovéto aplikace při silnější zátěži dal měřit kalendářem. Proto je zde pooling, který uchovává otevřená připojení v tzv. fondech. Tím tato režie odpadá.

Maximální a minimální počet připojení se dá nastavit v ConnectionString pomocí „min pool size“ a „max pool size“ (výchozí hodnoty jsou 0 a 100). Nastavením prvního z těchto parametrů na hodnotu větší než nula zajistíte předběžné naplnění fondu připojení zadaným počtem připojení. Zrychlíte tak rozjezd aplikace u které se předpokládá vyšší zatížení.

Takže je nutné na tuto vlastnost pamatovat a pokaždé když otevřete nové připojení ať už přes SqlConnection nebo OleDbConnection byste měli zase v nějaké rozumně krátké době připojení uzavřít. Ve skutečnosti se totiž připojení neuzavře, spíše se Ado.Net dozví, že toto připojení je volné a může ho použít zase něco jiného.

Třídy příkazů

Když chceme v .NET zavolat nějaký SQL příkaz používáme k tomu třídu SqlCommand, resp. OleDbCommand.

Při vytvoření objektu command se nainicializuje jeho vlastnost CommandText a Connection z argumentů předaných konstruktoru.

Vykonávání příkazů

Nyní již máme příkaz vytvořen a je třeba ho vykonat. K tomu slouží 3 metody třídy SqlCommand: ExecuteNonQuery, ExecuteScalar a ExecuteReader. Podívejme se tedy na každou zvlášť.

Pro testovací účely si vytvořme databázi a do ní vložme jednu tabulku.

create database mojeDatabaze
go

use mojeDatabaze
go

create table zamestnanci
(
   id      int identity(1,1) not null,
   jmeno   varchar(30)       not null,
   pozice  varchar(30)       not null,
   adresa  varchar(50)       not null,
   telefon varchar(15)       not null,
   plat    money             not null,
   foto    image
)

SqlCommand.ExecuteNonQuery

Tato metoda slouží k vykonávání SQL příkazů od kterých nepožadujeme výstup. Jedná se o SQL příkazy jako INSERT,UPDATE,DELETE, CREATE DATABASE,CREATE TABLE, DROP TABLE,… Návratová hodnota je -1 pro všechny SQL příkazy kromě INSERT,UPDATE, DELETE. U těchto příkazů se nám vrátí počet řádků ovlivněných jejich vykonáním.

V následujícím příkladu přidáváme do tabulky zaměstnanců nového zaměstnance včetně jeho fotografie.

SqlConnection prip = newSqlConnection("server=localhost;database=mojeDatabaze;uid=sa;pwd=");

// Následující řádky nainicializují pole blob obrázkem načteným ze
// souboru foto.jpg
FileStream proud = new FileStream("foto.jpg",FileMode.Open);
byte[] blob = new byte[proud.Length];
proud.Read(blob,0,(int)proud.Length);

try
{
   prip.Open();
   SqlCommand prikaz = new SqlCommand
      ("INSERT INTO zamestnanci"+
       "(jmeno, pozice, adresa, telefon, plat, foto)"+
       "VALUES('Petr Ryba', 'poskok', 'Jemenska 17',"+
       "'555585', 12000, @foto)",prip);
   prikaz.Parameters.Add("@foto",blob);
   int i =prikaz.ExecuteNonQuery();

}
catch(SqlException ex)
{
   //Osetreni vyjimky
}
finally
{
   prip.Close();
}



Tento příklad nejen vkládá nový řadek s informacemi o zaměstnanci, ale rovněž do databáze zapisuje objekt BLOB( binary large object – binární velký soubor). V tomto konkrétním případě se jedná o obrázek, ale samozřejmě to může být cokoliv.  Používají se k tomu tzv. parametrizované příkazy, o nich si však povíme v dalším díle.

SqlCommand.ExecuteScalar

Tato metoda vrací první sloupec v prvním řádku, který vrátí SQL příkaz vykonaný touto metodou. Proto se používá hlavně pro SQL příkazy typu COUNT,MIN,MAX,AVG,SUM. Další častý způsob použití je získávání objektů BLOB. Podívejme se tedy na příklad, který činí právě toto.

public Bitmap GetFoto(int id)
{
   SqlConnection prip = new SqlConnection
      ("server=localhost;database=mojeDatabaze;uid=sa;pwd=");
   MemoryStream proud = new MemoryStream();
   Bitmap foto = null;    

   try
   {
      prip.Open();
      SqlCommand prikaz = new SqlCommand
         ("SELECT foto FROM zamestnanci WHERE id=@id",prip);
      prikaz.Parameters.Add("@id",id);
      byte[] blob = (byte[])prikaz.ExecuteScalar();
      proud.Write(blob,0,blob.Length);
      foto = new Bitmap(proud);
   }
   catch(SqlException ex)
   {
      //Osetreni vyjimky
   }
   finally
   {
      prip.Close();
   }   
   return foto;   
}

Tato metoda vrací instanci třídy Bitmap obsahující fotografii z tabulky zamestnanci podle argumentu id. Vytvoří se zde příslušný SQLCommand s parametrem id(co to přesně je se dovíte príště) a zavolá se metoda ExecuteScalar(), která vrátí binární data obrázku. Ty je nutné přetypovat na byte[], protože ExecuteScalar kvůli obecnosti vrací Object. Následně toto pole bytů zapíšeme do paměťového proudu MemoryStream, který pak předáme konstruktoru třídy Bitmap.

Dalším častým použitím metody ExecuteScalar je autentikace uživatele, jak uvidíte v příštím díle.

SqlCommand.ExecuteReader

Tato metoda vrací objekt SqlDataReader, resp. OleDbDataReader pokud je zavolána na objektu OleDbCommand.  Tento objekt se následně používá pro iterování výsledkem dotazu vykonaného na databázi. Je necachovaný, dopředný a pouze pro čtení. Je však velmi efektivní, přejímá pouze ty data, která chcete. I když bude výsledek SQL dotazu obrovský a vy si přečtete pouze pár záznamů, skutečně se jich přečte jen pár.

SqlConnection prip = new SqlConnection
   ("server=localhost;database=mojeDatabaze;uid=sa;pwd= ");

try
{
   prip.Open();
   SqlCommand prikaz = new SqlCommand
      ("SELECT jmeno FROM zamestnanci",prip);
    
   SqlDataReader ctenar = prikaz.ExecuteReader();    

   while (ctenar.Read())
      Console.WriteLine(ctenar["jmeno"]);             
}
catch(SqlException ex)
{
    //Osetreni vyjimky
}
finally
{
   prip.Close();
}

Každé volání metody Read načte jeden řádek z množiny výsledků. Pro přístup k položkám tohoto řádku můžete využít indexer tohoto objektu. Jako index lze použít jméno sloupečku nebo jeho index ( zde by to bylo ctenar[0] ).

Pokud používáte DataReader, nemůžete na tomto spojení vykonat žádnou jinou operaci. To platí dokud objekt DataReader nezavřete voláním metody Close. Tuto metodu nemusíte volat, pokud nemáte v úmyslu na tomto spojení žádné další operace vykonávat, ale samozřejmě se to doporučuje.

Objekt DataReader obsahuje i další zajímavé metody, jako například GetSchemaTable, která vrací objekt DataTable z něhož lze získat strukturu výsledku. Nebo pokud chcete například zjistit název určitého sloupečku o určitém indexu, zavolejte GetName. Třída má metod samozřejmě mnohem více, pro bližší informace nahlédněte do dokumentace této třídy v msdn.

Užitečné odkazy

Řetězce připojení pro nejrůznější existující databáze - www.connectionstrings.com