BAZA ZNANJA

Kurs: Core Windows Forms Technologies

Modul: Rad u konektovanom okruženju

Autor: Marko Stojićević

Naziv jedinice: Rad sa parametarskim upitima u konektovanom okruženju

Parametri komande

SQL komande i uskladištene procedure mogu imati ulazne i izlazne parametre. Uskladištene procedure mogu imati i povratne vrednosti. Objekti klase SqlCommand ili OleDbCommand imaju kolekciju Parameters. Ova kolekcija sadrži skup SqlParameter ili OleDbParameter objekata koji predstavljaju ulazne ili izlazne parametre komande. Pre izvršavanje komande mora se dodeliti vrednost svakom ulaznom parametru. U nastavku je najpre instanciran objekat klase SqlCommand tako što mu je prosleđen tekst komenade i objekat SqlConnection.Zatim je instanciran objekat klase SqlParameter na način predstavljen na slajdu. Kada se instancira parametar njegovo ime mora počinjati sa znakom @ i mora se specificirati tip vrednosti parametra i u zavisnosti od tipa prametra odgovarajuća dužina (npr SqlDbType.VarChar, 80). Svakom parametru se mora dodeliti vrednost korišćenjem svojstva Value. Instancirani parametar se dodaje u Parameters kolekciju SqlCommand objekta korišćenjem metode Add. U nastavku je prikazan i način čitanja vrednosti parametara. Parametru se pristupa ili na osnovu njegove pozicije u kolekciji koja je bazirana na indeksu nula ili na osnovu njegovog imena.

SqlCommand cmd = new SqlCommand(commandText, con);
SqlParameter param = new SqlParameter("@Description", SqlDbType.VarChar, 80);
param.Value = "This is the description";
cmd.Add(param);

// Uzmi prvi parametar
param = cmd.Parameters[0];
// Uzmi parametar sa imenom "@Description".
param = cmd.Parameters["@Description"];

Primer parametarskog upita

U nastavku je definisana Update komanda koja ima dva ulazna parametra. Prvi parametar je tip NVarChar dužine 15 i nazvan je  @CategoryName. Drugi parametar je tipa Int i nazvan je @CategoryID. Praksa je da nazivi parametar odgovarjau odgovarajućim atributima u bazi podataka stim što se dodaje prefiks @. Obično se pri setovanju vrednosti parametar vrši iščitavanje odgovarjućih tekst boksova u koje je korisnik uneo podatke. Na taj način se izvršava parametarski upit a vrednosti parametara definiše korisnik.

string connectionString = "Data Source=localhost;" +
"Initial Catalog=Northwind;Integrated Security=SSPI";
string SQL = "UPDATE Categories SET CategoryName=@CategoryName " + "WHERE CategoryID=@CategoryID";

SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(SQL, con);
SqlParameter param1 = new SqlParameter("@CategoryName",SqlDbType.NVarChar,15);
cmd.Parameters.Add(param1);
param1.Value = "Beverages";
SqlParameter param2 = new SqlParameter("@CategoryID",SqlDbType.Int);
cmd.Parameters.Add(param2);
param2.Value = 2;
// Izvrsi komandu
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
 con.Close();
// Prikazi rezultate
Console.WriteLine(rowsAffected.ToString() + " row(s) affected");
Console.ReadLine();

Kreiranje uskladištenih procedura

U nastavku je prikazano kreiranje uskladištene procedure UpdateCategory. Uskladištena procedura je skup SQL komandi koje se zajedno parsiraju. Uskladištene procedure se čuvaju na database serveru. Prikazana uskladištena procedura ima dva ulazna parametra.

CREATE PROCEDURE UpdateCategory
(
    @CategoryID int,
    @CategoryName nvarchar(15)
)
AS
UPDATE Categories SET CategoryName=@CategoryName
WHERE CategoryID=@CategoryID

Izvšavanje parametarskog upita korišćenjem
uskladištene procedure

Kada se kao SqlCommand objekat koristi odgovarajuća uskladištena procedura to se mora naznačiti korišćenjem svojstva CommandType. Vrednost ovog svojstva treba da bude CommandType.StoredProcedure. Svojstvo CommandText objekta SqlCommand je naziv uskladištene procedure. Sada se Command objektu dodaju ulazni parametri koji odgovaraju ulaznim parametrima uskladištene procedure.

string connectionString = "Data Source=localhost;" +
"Initial Catalog=Northwind;Integrated Security=SSPI";
string SQL = "UpdateCategory";

//Kreiraj ADO.NET objekte
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(SQL, con);

cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param;
param = cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15);
param.Value = "Beverages";

param = cmd.Parameters.Add("@CategoryID", SqlDbType.Int);
param.Value = 1;
// Izvrsi komandu
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();
// Prikazi rezultate operacije
Console.WriteLine(rowsAffected.ToString() + " vrsta promenjeno");
Console.ReadLine();

Uskladištene procedure sa izlaznim parametrima

U nastavku je prikazana procedura koja pored tri ulazna parametra ima i jedan izlazni parametar označen sa @CustomerID. Za označavanje izlaznih parametara koristi se ključna reč OUTPUT. Pošto je kolona CustomerID kolona identiteta (IDENTITY,autoincrement ili autonumbering) pri svakom ubacivanju podataka u tabelu vrednost atributa CustomerID se inkrementira a ta vrednost se može pročitati iščitavanjem sistemske promenljive @@Identity. Vrednost ove promenljive se dodeljuje izlaznom parametru uskladištene procedure.

CREATE Procedure CustomerAdd
(
@FullName nvarchar(50),
@Email nvarchar(50),
@Password nvarchar(50),
@CustomerID int OUTPUT
)
AS
INSERT INTO Customers1 ( FullName, EMail, Password )
VALUES ( @FullName, @Email, @Password )
SELECT @CustomerID = @@Identity
GO

Definisanje izlaznih parametara

U nastavku je prikazan primer definisanja izlaznog parametra odgovarajućeg SqlCommand objekta. U Parameters kolekciju SqlCommand objekta se dodaju najpre ulazni parametri koji odgovaraju ulaznim parametrima uskladištene procedure. Kao što se vidi nije neophodno pozivati konstruktor SqlParametar klase za kreiranje parametara već se direktno u Parametars kolelciju pomoću metode Add dodaje parametar tako što se specificira najpre njegov naziv a zatim i tip podatak koji on predstavlja. Izlazni parametar označen sa @CustomerID se kreira na potpuno isti način kao i ulazni stim što se za odgovarajući SqlParametar objekat mora postaviti svojstvo Direction na vrednost ParameterDirection.Output.

string connectionString = "Data Source=localhost;" +
"Initial Catalog=Northwind;Integrated Security=SSPI";
string procedure = "CustomerAdd";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(procedure, con);
// Konfigurisi komandu i dodaj ulazne parametre
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param;
param = cmd.Parameters.Add("@FullName", SqlDbType.NVarChar, 50);
param.Value = "John Smith";
param = cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50);
param.Value = "john@mydomain.com";
param = cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 50);
param.Value = "opensesame";
// Dodaj izlazni parametar
param = cmd.Parameters.Add("@CustomerID", SqlDbType.Int);
param.Direction = ParameterDirection.Output;
// Izvrsi komandu
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Console.WriteLine("Novi korisnik ima ID " + param.Value);
Console.ReadLine();
Povratna vrednost uskladištene procedure

Osim ulaznih i izlaznih parametara uskladištena procedura može imati i povratnu vrednost. Ključna reč Return predstavlja bezuslovni izlazak iz uskladištene procedure. Povratna vrednost uskladištene procedure je 0 ukoliko je uskladištena procedura uspešno izvršena. Korisnički definisane povratne vrednosti imaju prednost u odnosu na SQL Serverove sistemski definisane povratne vrednost. U primeru u nastavku povratna vrednost uskladištene procedure je ID poslednje ubačene vrste u tabeli.

ALTER Procedure CustomerAdd
(
@FullName nvarchar(50),
@Email nvarchar(50),
@Password nvarchar(50),
)
AS
INSERT INTO Customers ( FullName, EMailAddress, Password ) VALUES ( @FullName, @Email, @Password )
RETURN @@Identity
GO

Definisanje povratne vrednosti

Definisanje parametra koji predstavlja povratnu vrednost uskladištene procedure je slično definisanju izlaznih parametara. U ovom slučaju se za svojstvo Direction odgovarajućeg objekta klase Parameters setuje vrednost ParameterDirection.ReturnValue. Povratna vrednost se može pročitati iz odgovarajućeg Command objekta na sledeći način: cmd.Parameters ["@RETURN_VALUE"].Value.

string connectionString = "Data Source=localhost;" +
"Initial Catalog=Northwind;Integrated Security=SSPI";
string procedure = "CustomerAdd";

SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(procedure, con);  
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param;

// Dodaj parametar koji predstavlja povratnu vrednost
param = cmd.Parameters.Add("@CustomerID", SqlDbType.Int);
param.Direction = ParameterDirection.ReturnValue;

// Dodaj ulazne parametre
param = cmd.Parameters.Add("@FullName", SqlDbType.NVarChar, 50);
  param.Value = "John Smith";

param = cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50);
  param.Value = "john@mydomain.com";

param = cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 50);
param.Value = "opensesame";
// izvrsi komandu
con.Open();
cmd.ExecuteNonQuery();
con.Close();
param = cmd.Parameters["@CustomerID"];
Console.WriteLine("ID novog korisnika je " + param.Value);
Console.ReadLine();

Izvođenje parametara

Ukoliko uskladištena procedura radi samo sa jednom tabelom moguće je umesto kreiranja parametara izvršiti njihovo iščitavanje iz baze podataka. To se radi korišćenjem klase SqlCommandBuilder i njene metode DeriveParameters. U kolekciji parametara kreiranoj na ovaj način na prvom mestu se nalazi povratna vrednost. U nastavku je najpre izvršeno izvođenje parametara, zatim je ulaznim parametrima dodeljena vrednost, izvršena je uskladištena procedura i pročitana je povratna vrednost uskladištene procedure. Treba napomenuti da izvođenje parametara usporava izvršavanje procedure jer je neophodna jedna komunikacija sa bazom u cilju iščitavanja parametara.

SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(procedure, con);

cmd.CommandType = CommandType.StoredProcedure; con.Open();
SqlCommandBuilder.DeriveParameters(cmd);

cmd.Parameters[1].Value = "Faria MacDonald"; cmd.Parameters[2].Value = "joe@mydomain.com"; cmd.Parameters[3].Value = "opensesame";

cmd.ExecuteNonQuery();
con.Close();
Console.WriteLine("Novi korisnik ima ID: " +
cmd.Parameters["@RETURN_VALUE"].Value);
Console.ReadLine();