BAZA ZNANJA

Kurs: Core Web Application Technologies

Modul: Komunikacija sa bazom podataka

Autor: Marko Stojićević

Naziv jedinice: Rad sa parametarskim upitima i uskladištenim procedurama

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šavanja komande mora se dodeliti vrednost svakom ulaznom parametru.

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

Najpre je instanciran objekat klase SqlCommand tako što mu je prosleđen tekst komande i objekat SqlConnection. Zatim je instanciran objekat klase SqlParameter. 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 (na primer 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. Prikazan je i način čitanja vrednosti parametara.

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

Parametru se pristupa ili na osnovu njegove pozicije u kolekciji koja je bazirana na indeksu nula ili na osnovu njegovog imena.

String konekcionString = @"Data Source=(local)SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";
SqlConnection mojaKonekcija = new SqlConnection(konekcionString);       
string SQLUpit = @"UPDATE Categories SET CategoryName=@CategoryName WHERE CategoryID=@CategoryID";
SqlCommand paramKomanda = new SqlCommand(SQLUpit,mojaKonekcija); SqlParameter param1 = new SqlParameter("@CategoryName", SqlDbType.NVarChar, 15);
SqlParameter param2 = new SqlParameter("@CategoryID",SqlDbType.Int);
paramKomanda.Parameters.Add(param1);
paramKomanda.Parameters.Add(param2);
param1.Value = TextKategorija.Text;
param2.Value = DropDownList1.SelectedIndex + 1 ;
………
paramKomanda.ExecuteNonQuery();

 

U prethodnom primeru 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 parametara budu u skladu sa odgovarajućim atributim u bazi podataka, s tim što se dodaje prefiks @. Obično se pri postavljanju vrednosti parametara 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.

Kreiranje uskladištenih procedura

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

U primeru 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.

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.

public void prikazi()
    {
        String konekcionString = @"Data Source=(local)SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";
        SqlConnection mojaKonekcija = new SqlConnection(konekcionString);
        string SQLUpit = "UpdateCategory"; // ime uskladistene procedure
        SqlCommand komanda = new SqlCommand(SQLUpit,mojaKonekcija);
        komanda.CommandType = CommandType.StoredProcedure;
        SqlParameter parametar1 = new SqlParameter("@CategoryName", SqlDbType.VarChar, 15);
        SqlParameter parametar2 = new SqlParameter("@CategoryID",SqlDbType.Int);
        komanda.Parameters.Add(parametar1);
        komanda.Parameters.Add(parametar2);
        parametar2.Value = 1;
        parametar1.Value = "Beverages";
        mojaKonekcija.Open();
        komanda.ExecuteNonQuery();
        sb.Append("Izvrsena promena " + "</br>");
        mojaKonekcija.Close();
    }

Uskladištene procedure sa izlaznim parametrima

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

U primeru 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.

Definisanje izlaznih parametara

// Konfigurisi komandu i dodaj ulazne parametre
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter param1 = new SqlParameter("@FullName", SqlDbType.NVarChar, 50);
        SqlParameter param2 = new SqlParameter("@Email", SqlDbType.NVarChar, 50);
        SqlParameter param3 = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
        cmd.Parameters.Add(param1);
        cmd.Parameters.Add(param2);
        cmd.Parameters.Add(param3);
        param1.Value = "John Smith";
        param2.Value = "john@mydomain.com";
        param3.Value = "opensesame";
        // Dodaj izlazni parametar
        SqlParameter param4 = new SqlParameter("@CustomerID", SqlDbType.Int);
        param4.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(param4);
        // Izvrsi komandu
        mojaKonekcija.Open();
        cmd.ExecuteNonQuery();
        sb.Append("ID upravo ubacenog korisnika je :" + param4.Value.ToString());
        mojaKonekcija.Close();

U ovom primeru je prikazano definisanje 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 kolekciju pomoću metode Add dodaje parametar tako što se specificira najpre njegov naziv, a zatim i tip podatka koji on predstavlja. Izlazni parametar označen sa @CustomerID se kreira na potpuno isti način kao i ulazni, s tim što se za odgovarajući SqlParametar objekat mora postaviti svojstvo Direction na vrednost ParameterDirection.Output.

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 Server-ove sistemski definisane povratne vrednosti. U primeru na slajdu povratna vrednost uskladištene procedure je ID poslednje ubačene vrste u tabeli.

CREATE Procedure CustomerAdd1
(
@FullName nvarchar(50),
@Email nvarchar(50),
@Password nvarchar(50)
)
AS
INSERT INTO Customers1 ( FullName, Email, 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 određuje 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.

public void prikazi()
    {
        String konekcionString = @"Data Source=(local)SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";
        SqlConnection mojaKonekcija = new SqlConnection(konekcionString);
        string procedure = "CustomerAdd1";
        SqlCommand cmd = new SqlCommand(procedure, mojaKonekcija);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter param;
        // Dodaj parametar koji predstavlja povratnu vrednost
        SqlParameter param1 = new SqlParameter("@CustomerID", SqlDbType.Int);
        param1.Direction = ParameterDirection.ReturnValue;
        // Dodaj ulazne parametre
        SqlParameter param2 = new SqlParameter("@FullName", SqlDbType.NVarChar, 50);
        SqlParameter param3 = new SqlParameter("@Email", SqlDbType.NVarChar, 50); 
        SqlParameter param4 = new SqlParameter("@Password", SqlDbType.NVarChar, 50);
        cmd.Parameters.Add(param1);
        cmd.Parameters.Add(param2);
        cmd.Parameters.Add(param3);
        cmd.Parameters.Add(param4);
      
        param2.Value = "John Smith";
        param3.Value = "john@mydomain.com";
        param4.Value = "opensesame";
        // izvrsi komandu
        mojaKonekcija.Open();
        cmd.ExecuteNonQuery();
        mojaKonekcija.Close();
        sb.Append("ID novog korisnika je " + param1.Value);
    }

Modifikovanje podataka u bazi korišćenjem DataAdapter-a

Potrebno je pored SELECT komande DataAdapter-a (koja služi za čitanje podataka iz baze i punjenje DataSet-a), definisati i INSERT, UPDATE i DELETE komande. Promene u DataSet-u se prosleđuju do baze pozivanjem Update() metode DataAdapter-a.

Definisanje Insert komande DataAdapter-a

Potrebno je kreirati SqlCommand objekat koji će predstavljati INSERT naredbu. Osim poziva konstruktora objekat SqlCommand je moguće kreirati i pozivom metode CreateCommand odgovarajućeg SqlConnection objekta. Uobičajeno je korišćenje parametarskog INSERT upita gde će vrednosti parametara definisati korisnik. Posle dodavanja parametara InsertCommand svojstvu objekta klase DataAdapter se pridružuje gore kreirani SqlCommand objekat.

String konekcionString = @"Data Source=(local)SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";
        SqlConnection mojaKonekcija = new SqlConnection(konekcionString);
        SqlCommand mojaInsertKomanda = new SqlCommand();
        mojaInsertKomanda.Connection = mojaKonekcija;
        mojaInsertKomanda.CommandText =
"INSERT INTO Customers(CustomerID, CompanyName, Address ) VALUES (@CustomerID, @CompanyName, @Address)";
        SqlParameter parametar1 = new SqlParameter("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
        SqlParameter parametar2 = new SqlParameter("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
        SqlParameter parametar3 = new SqlParameter("@Address", SqlDbType.NVarChar, 60, "Address");
        mojaInsertKomanda.Parameters.Add(parametar1);
        mojaInsertKomanda.Parameters.Add(parametar2);
        mojaInsertKomanda.Parameters.Add(parametar3);
        SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Customers", mojaKonekcija);
        da.InsertCommand = mojaInsertKomanda;

Ubacivanje zapisa u bazu podataka

Poziva se NewRow() metoda objekta DataTable čime se kreira DataRow objekat koji ima istu strukturu kao i tabela. Za objekat DataRow definišu se vrednosti za atribute te vrste. Ovako ažurirani objekat DataRow se dodaju u Rows kolekciju tabele. Otvara se konekcija sa bazom podataka. Poziva se Update metoda DataAdapter-a i prosleđuje mu se kao parametar objekat DataTable koji je promenjen. Na ovaj način se promene nastale u DataSetu upisuju trajno u bazu podataka. Pri završenom ažuriranju potrebno je zatvoriti konekciju.

// Korak 1: koriscenje  NewRow() metoda  objekta DataTable
// za kreiranje nove vrste- DataRow
  
DataRow novaVrsta = mojaTabela.NewRow();   
// Korak 2: postavi vrednosti za DataColumn objekte
// za novu vrstu - DataRow
novaVrsta["CustomerID"] = CustID;
novaVrsta["CompanyName"] = Company;
novaVrsta["Address"] = addr;
// Korak 3: Pozivanje Add() metode kroz Rows property
// da bi se dodao novi  DataRow objekta u objekat DataTable
mojaTabela.Rows.Add(novaVrsta);
 // Korak 4: Koriscenje Update() metode DataAdaptera
mojaKonekcija.Open();
brojVrsta = mojAdapter.Update(mojaTabela); 
mojaKonekcija.Close();

 

Update komanda DataAdapter-a

U primeru je prikazana UPDATE komanda DataAdptera. Uobičajeno je da se pronalaženje vrste za brisanje vrši po primarnom ključu.

SqlCommand myUpdateCommand = mySqlConnection.CreateCommand();
myUpdateCommand.CommandText =
"UPDATE Customers SET CompanyName = @NewCompanyName, Address = @NewAddress WHERE CustomerID = @OldCustomerID";
    
myUpdateCommand.Parameters.Add("@NewCompanyName", SqlDbType.NVarChar,40, "CompanyName"); 
myUpdateCommand.Parameters.Add("@NewAddress", SqlDbType.NVarChar,60, "Address");
    
myUpdateCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar,5, "CustomerID");   
    
mySqlDataAdapter.UpdateCommand = myUpdateCommand;

Ažuriranje podataka u bazi

Primarne ključeve tabela preuzimamo iz baze podataka postavljanjem svojstva MissingSchemaAction odgovarajućeg DataAdapter-a koji puni DataSet na vrednost  MissingSchemaAction.AddWithKey. Korišćenjem Find metode kolekcije DataRowCollection pronalazi se DataRow objekat sa željenom vrednošću primarnog ključa. U pomenutom redu se izvrši promena željenih atributa tako što se atributu unutar reda pristupa preko imena ili indeksa. Pozove se Update metoda DataAdapter-a i prosledi joj se ažurirani objekat DataTable.

// korak 1: postavi PrimaryKey svojstvo za objekat DataTable
mojaTabela.PrimaryKey = new DataColumn[] 
{
mojaTabela.Columns["CustomerID"]
};
// korak 2: koriscenjem Find() metode lociraj DataRow
DataRow myEditDataRow = mojaTabela.Rows.Find(idVrste);
// korak 3: promeni DataColumn vrednosti  objekta DataRow
myEditDataRow["CompanyName"] = kompanija;
myEditDataRow["Address"] = adresa;
// korak 4: koriscenje Update() metoda za
// azuriranje baze
   
mojaKonekcija.Open();
int brojVrsta = mojAdapter.Update(mojaTabela);
mojaKonekcija.Close();

Delete komanda DataAdapter-a

U primeru prikazana DELETE komanda DataAdpter-a. Uobičajeno je da se pronalaženje vrste za brisanje vrši po primarnom ključu.

SqlCommand myDeleteCommand = mySqlConnection.CreateCommand();
myDeleteCommand.CommandText =
"DELETE  FROM Customers WHERE CustomerID =  @OldCustomerID";
    
myDeleteCommand.Parameters.Add("@OldCustomerID", SqlDbType.NChar,5, "CustomerID");        
mySqlDataAdapter.DeleteCommand = myDeleteCommand;

 
Brisanje podataka u bazi

Primarne ključeve tabela preuzimamo iz baze podataka postavljanjem svojstva MissingSchemaAction odgovarajućeg DataAdapter-a koji puni DataSet na vrednost  MissingSchemaAction.AddWithKey. Drugi korak je korišćenje Find metode kolekcije vrsta za pronalaženje vrste za specificirani ID. Treći korak je brisanje vrste unutar DataTable objekta tj. brisanje vrste iz lokalne kopije. Četvrti korak je ažuriranje izvora podataka koristeći Update metodu DataAdapter objekta kome se kao parametar prosleđuje izmenjeni objekat DataTable.

// Korak 1: postavi PrimaryKey svojstvo za DataTable
 da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// Korak 2: koriscenje Find() metode za pronalzenje vrste
DataRow myRemoveDataRow = myDataTable.Rows.Find(id);
// Korak 3: koriscenje metoda Delete() za brisanje vrste
try
{
myRemoveDataRow.Delete();
sb.Append("myRemoveDataRow.RowState = " + myRemoveDataRow.RowState); 
// Korak 4: koriscenje Update() metode za brisanje vrste iz baze podataka 
mySqlConnection.Open();
int numOfRows = mySqlDataAdapter.Update(myDataTable);
sb.Append("brojVrsta = " + numOfRows);
sb.Append("myRemoveDataRow.RowState = " + myRemoveDataRow.RowState);
}
catch(Exception exc)
{
  sb.Append(exc.Message);
}
finally
{
mySqlConnection.Close();
}