4_proceduristocate

7/29/2019 4_ProceduriStocate

1/11

Proceduri stocate

Exploatarea bazei de date folosind obiecte de tip Command

Lucru cu procedurile stocate

Exploatarea bazei de date folosind obiecte de tip Command

Am observat pn acum c obiecte precum DataAdapter, respectiv SqlDataSource aucomenzi pe care le in ca proprieti ce sunt de fapt referine ctre obiecte de tip XxxCommand(SqlCommand, OleDbCommand, OdbcCommand etc.).

Obiectele de tip XxxCommand ofer n fond unica modalitate de acces la date, numai cele:

pot echipa un DataAdapter (sau DataSource) pe care l ajut, dup caz, s umple unDataSet cu nregistrri (metoda Fill a adaptorului) sau s opereze modificrile,tergerile sau inserrile n baza de date (metoda Update a adaptorului);

pot fi folosite independent, caz n care pot genera la execuie un DataReader,

container de date, care furnizeaz nregistrare cu nregistrare, read only;

Obiectele de tip Command sunt purttoare ale unor comenzi textuale de tip SELECT,INSERT, DELETE, UPDATE din limbajul SQL sau ale unor nume de proceduri, pe care le potlansa n execuie.

Comenzile de acces direct la baza de date sunt de trei tipuri : ExecuteReader, care ntoarce o colecie de tupluri de date, accesibile apoi linie cu linie; ExecuteScalar, care returneaz o singur valoare, de tip generic object ; ExecuteNonQuery, care execut actualizrile asupra bazei de date i returneaz un int,

indicnd numrul de nregistrri afectate prin modificri, tergeri sau inserri n baza de

date.Obiectele claselor de tip Reader ( SqlDataReader, OleDbDataReader ) pot fi generate

de ctre obiectele de tip Command prin apelul ExecuteReaderi furnizeaz un flux de date, read-only, disponibiliznd cte o nregistrare la fiecare apel al metodei Read(), spre deosebire demetoda Fill() a unui adaptor, care furnizeaz o colecie de nregistrri:

OleDbDataReader dr = myCmd.ExecuteReader();

Parcurgerea nregistrrilor cu DataReader este simpl i se aseamn cu citirea cte unei

nregistrri dintr-un fiier. Localizarea unui cmp n nregistrarea din DataReader se face fie prinindexare, fie cunoscnd numele coloanei din tabel.

textBox1.Text = dr["pret"]; sau textBox1.Text = dr[2];

O aplicaie care utilizeaz DataReader presupune parcurgerea urmtorilor pai:- se creaz o aplicaie nou, n care prin suprascrierea fiierelor webForm1.aspx.cs ,

webForm1.aspx se aduce codul surs prezentat mai jos;- se ataeaz delegaii pentru cele trei butoane, cci prin copiere nu s-au adus i legturile;- se face o copie pentru fiierul prod.mdb, cci va fi alterat prinExecuteNonQuery;- se ruleaz i comenteaz fiecare linie surs, constatnd efectele rulrii.

- se poate completa aplicaia cu un GridView, pentru vizualizarea simultan a tuturortuplurilor selectate;

28/02/131

DataReader-ul pointeaz aprioric pe BOF i e nevoie de o citire prealabilpentru a dispune de prima nregistrare.


2/11

- pe un butonpuneInGridse adaug codul de la funcia btnExecuteReader, iar n loc deparcurgere cu while a dataReader -ului, se pune cod de legare a dataReader-ului ca sursde date pentru gridView ( gridul va trebui s aib ID-ul sursei pe nul, cci va folosiDataSource n loc de DataSourceID : GridView1.DataSourceID = null;)

//while (dr.Read())

//{// textBox1.Text+="\r\n"+dr["codp"]+ " " + dr["denum"]+ " " + dr["pret"];//}GridView1.DataSource = dr; GridView1.DataBind(); dr.Close(); con.Close();

Legarea dataReader-ului ca DataSource pentru un grid funcioneaz numai pentru controlulGridView din Web Forms, nu i pentru cel din Windows Forms.

Comparaii ntre cele dou modaliti de folosire a obiectelor de tip xxxCommand:

Observaie. Se poate folosi metoda ExecuteReader() i n locul celorlalte metode (ExecuteNonQuery, ExecuteScalar), cci metoda se va adapta dup cerin.

n esen, obiectul de tip XxxCommand primete din construcie cele dou informaii de care aveanevoie i adaptorul: stringul SQL de executat i conexiunea:

OleDbCommandmyCmd = new OleDbCommand(strSql,con);

Ce se ntmpl ns dac pri din comand sunt variabile i nu se cunosc la momentulconstruirii obiectului xxxCommand, ci se schimb de la o execuie la alta ?

Se poate rezolva aceast problem n dou moduri:- prin concatenare de string-uri, la momentul execuiei, nainte de crearea obiectului

xxxCommand ( cci comanda n sine este static);

- prin ncrcare de parametri i folosirea lor la momentul execuiei comenzii n baza de date.

Obiectele de tip XxxCommand au o colecie de parametri, obiecte de tip Parameter, ce suntcaracterizate prin nume, tip, direcie i valoare; aceti parametri circul impreun cu comanda iconin valori n reprezentare intern; trebuie s coincid cu tipul celor din baza de date, cnd suntfolosii la stocare valori n BD, dar pot fi i de lat tip dac sunt folosii n alt scop ( spre exemplu,

parametru @PretMin poate fi i de tip int, deoarece el este folosit doar n comparaii, chiar dacpreul de referin este stocat n baza de date ca double.

privatevoid pretPeste200_Click(object sender, System.EventArgs e){

string strSql ="SELECT codp, denum, pret FROM produse where pret > @PretMin";

OleDbConnection con =new OleDbConnection

28/02/132

Folosite individual, obiectele de tipCommand:

1. permit doar programare soft;2. trebuie nchis /deschis conexiunea

explicit;3. acces mai rapid la baza de date;4. disponibilizeaz nregistrare cu nregistrare,

prin intermediul unui obiect dataReader.

Echipnd un dataAdapter, obiectele detip Command:

1. permit lucru vizual cu dataAdapter;2. dataAdapter-ul nchide/deschide

implicit conexiunea;3. acces mai lent la baza de date;4. disponibilizeaz tot setul de

nregistrri.


3/11

(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\prod.mdb");try { con.Open(); }catch(Exception ex) { mesaje.Text="Eroare open conexiune"+ex.Message;}

OleDbCommand myCmd = new OleDbCommand(strSql,con);OleDbParameter param1;param1 = myCmd.Parameters.Add

(new OleDbParameter("@PretMin", OleDbType.Integer) );param1.Direction = ParameterDirection.Input;myCmd.Parameters["@PretMin"].Value=200;OleDbDataReader dr =myCmd.ExecuteReader();

dg.DataSource = dr; dg.DataBind();dr.Close(); con.Close();

}

Observaie. Comanda poate face astfel referiri mai sofisticate la acel parametru, identificat dupnume; spre exemplu, putem s transferm ca parametru de intrare un BLOB Binary Large Objectce ar putea conine o imagine !

Se observ c nu se folosesc obiecte DataAdapter sau DataSet, ci doar obiecteXxxCommand i DataReader.

Pentru exemplificarea unei comenzi ExecuteScalar()am presupus c dorim s afimnumrul de produse distincte, aflate n tabela produse. n acest scop am pus pe un buton dinmacheta aplicaiei, urmtoarea funcie :

private voidbtnExecScalar_Click(object sender, System.EventArgs e)

{OleDbConnection conn = null;

OleDbCommandmyCommand = null;try

{conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\prod.mdb");conn.Open();myCommand = new OleDbCommand

( "SELECT COUNT(*) FROM produse", conn);int nrProd = (int)myCommand.ExecuteScalar();textBox1.Text="Numar produse comercializate: "+ nrProd;

}catch(Exception excpt)

{mesaje.Text=

"Esec pe ExecuteScalar: "+excpt.Message;}

}

Un apel ExecuteNonQuery()a fost exemplificat presupunnd c se dorete modificareadenumirii produselor care ncep cu p , prin adugarea prefixului txt . Funcia care realizeazacest lucru trateaz evenimentul Click al unui buton inscripionat sugestiv:

private voidbtnExecNonQuery_Click(object sender, System.EventArgs e)

{OleDbConnection conn = null;OleDbCommand command = null;

string ConnString ="Provider=Microsoft.Jet.OLEDB.4.0;"+

28/02/133


4/11

"Data Source=C:\\prod.mdb";string cmd =

"UPDATE produse SET denum = 'txt'&denum "+"where denum like 'p%'";

try{conn = new OleDbConnection(ConnString); conn.Open();command = new OleDbCommand(cmd, conn);int nrRec = command.ExecuteNonQuery();textBox1.Text="Numar inregistrari afectate: "+ nrRec;}catch(Exception excpt){

mesaje.Text="Esec pe ExecuteNonQuery: "+ excpt.Message;

}finally{if (conn.State == ConnectionState.Open) conn.Close();}

}

Pentru rulare sub Visual Studio 2008, cu baz de date sub SQL Server Express, codul surs esteurmtorul :

using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;using System.Data.Sql;using System.Data.SqlTypes;using System.Data.SqlClient;

publicpartialclass_Default : System.Web.UI.Page{ protectedvoid Page_Load(object sender, EventArgs e)

{

} protectedvoid btnIncarca_Click(object sender, EventArgs e)

{

string strSql = "SELECT codp, denum, pret FROM produse"; SqlConnection con = newSqlConnection("Data Source=DOCTORAT2006\\SQLEXPRESS;InitialCatalog=masterat;Integrated Security=True");

textBox1.Text = "Lista produselor disponibile"; try { con.Open(); } catch (Exception ex) { mesaje.Text = "Eroare open conexiune" +ex.Message; }

SqlCommand myCmd = newSqlCommand(strSql, con);

SqlDataReader dr = myCmd.ExecuteReader();

//while (dr.Read()) //{ // textBox1.Text += "\r\n" + dr["codp"] + " " +

// dr["denum"] + " " + dr["pret"];

28/02/134


5/11

//}

GridView1.DataSource = dr; GridView1.DataBind();dr.Close(); con.Close();

} protectedvoid btnExecScalar_Click (object sender, System.EventArgs e)

{ SqlConnection conn = null; SqlCommand myCommand = null; try

{conn = newSqlConnection(

@"Data Source=DOCTORAT2006\SQLEXPRESS;Initial`Catalog=masterat;Integrated Security=True");

conn.Open();myCommand = newSqlCommand ("SELECT COUNT(*) FROM produse", conn);

int nrProd = (int)myCommand.ExecuteScalar();textBox1.Text = "Numar produse comercializate: " + nrProd;

} catch (Exception excpt)

{mesaje.Text="Esec pe ExecuteScalar: " + excpt.Message;

}}

protectedvoid btnExecNonQuery_Click (object sender, System.EventArgs e){

SqlConnection conn = null; SqlCommand command = null;

string ConnString = "Data Source=DOCTORAT2006\\SQLEXPRESS;InitialCatalog=masterat;Integrated Security=True"; string cmd = "UPDATE produse SET denum = 'txt'+denum " + "wheredenum like 'p%'"; try

{conn = newSqlConnection(ConnString); conn.Open();command = newSqlCommand(cmd, conn);

int nrRec = command.ExecuteNonQuery();textBox1.Text = "Numar inregistrari afectate: " + nrRec;


{mesaje.Text="Esec pe ExecuteNonQuery: " + excpt.Message;

} finally

{ if (conn.State == ConnectionState.Open) conn.Close();

}}

}

Pagina web cu rezultatele rulrii arat astfel :

28/02/135


6/11

Lucru cu procedurile stocate

Procedurile stocate sunt cereri frecvent folosite asupra unei baze de date, grupate sub unnume de apel. Ele se concretizeaz sub forma unor poriuni de cod, compilabile separat, existente peserver.

Avantajele folosiriiprocedurilor stocate: procedurile sunt deja compilate i deci nu mai conin erori de compilare, fiind direct

lansabile n execuie; fiind cunoscute de sever beneficiaz i de utilizarea statisticilor

interne pentru optimizarea accesului la datele unei tabele; aflndu-se pe server, procedurile stocate nu au nevoie s fie compuse i retransmise prin

reea de fiecare dat cnd sunt invocate; astfel accesul este mai rapid deoarece se transmitdoar parametrii de apel i numele procedurii;

au un grad de securitate mai ridicat, deoarece nu este vizibil codul surs ( a se vedea SQLinjection ca element de insecuritate); controlm mai bine accesul, dnd dreptul de a executa

proceduri stocate, dar nu i instruciuni individuale.Observaii.

- A se vedea ce a pus n baza de date (Queries / Design / View SQL code pentru MicrosoftAccess, respectiv Stored procedures pentru SQL Server).

- Parametrii trebuie s aib acelai tip cu ce s-a declarat la crearea procedurilor, iar dac ei seasociaz unor cmpuri din baza de date trebuie s corespund ca tip cu acetia sau s fieconvertii la momentul folosirii (vezi webBdProc1, cu pret float comparat cu param int ).

- Concordana la tipurile asociate cu DateTime este delicat existnd multe formate de datcalendaristic, diferind ca lungime i ca moment de referin ales.

Pentru nceput vom crea i folosi o procedur fr parametri; pentru simplitatepresupunem c avem funcii distincte care creaz, respectiv apelez procedura, la apsarea cte unuibuton inscripionat corespunztor. Funcia de creare ncearc mai nti s tearg o eventualversiune mai veche a aceleeai proceduri, pentru a ne permite mbuntirea progresiv a procedurii.

protectedvoid btnCreareProc_Click(object sender, System.EventArgs e){

string strCreare = "CREATE PROCEDURE ProdScumpe AS "

28/02/136


7/11

+ "Select * From produse where pret > 615 Order By denum"; SqlConnection con = newSqlConnection( "Data Source=orion;Initial Catalog=doctorat;User ID=sa;Password=as;"

);con.Open();

SqlCommand myCmd = newSqlCommand();myCmd.CommandType = CommandType.Text; myCmd.Connection = con;myCmd.CommandText = "DROP PROCEDURE ProdScumpe";

try { myCmd.ExecuteNonQuery(); } catch { TextBox1.Text = "Procedura de sters nu exista!!"; }

myCmd.CommandText = strCreare; try

{myCmd.ExecuteNonQuery();TextBox1.Text = "Procedura creata cu succes!!";

} catch { TextBox1.Text = "Procedura nu a fost creata!!"; }

con.Close();}

Se pot folosi diverse modaliti de a apela o procedur stocat; cel mai simplu mod este cantr-un obiect de tip Command, s nlocuim fraza SELECT cu numele procedurii stocate:

protectedvoid btnApelProc_Click(object sender, System.EventArgs e){

SqlConnection con = newSqlConnection( "Data Source=orion;Initial Catalog=doctorat;User ID=sa;Password=as;"

);

con.Open();

SqlCommand myCmd = newSqlCommand("ProdScumpe", con);myCmd.CommandType = CommandType.StoredProcedure;

SqlDataReader dr = myCmd.ExecuteReader();TextBox1.Text = "Lista produselor scumpe";

while (dr.Read()){

TextBox1.Text += "\r\n" + dr["codp"] + " " +dr["denum"] + " " + dr["pret"];

}dr.Close(); con.Close();

}

Dac procedura ar lucra cu parametri de intrare, spre exemplu numai produsele cu preulpeste o valoare, dat ca text ntr-un TextBox al formei, procedura s-ar schimba, deoarececomanda trebuie s declare acum i parametri de intrare / ieire; n acest caz este nevoie s lucrm icu obiecte de tip Parameter.

Aa cum spuneam, obiectele Command dispun de o colecie numit Parameters, n careprin metodaAdd, adugm parametrii pregtii anterior sau furnizm metodei informaia necesarpentru a construi ea aceti parametri:

param1 = cmdMea.Parameters.Add(newSqlParameter("@PretMin", SqlDbType.Int));param1.Direction = ParameterDirection.Input;cmdMea.Parameters["@PretMin"].Value = Convert.ToInt32(txtPret.Text);

MetodaAdda coleciei Parameters are mai multe versiuni suprancrcate, n funcie denumrul de argumente folosite la construirea unui obiect de tip parametru. Se observ c, n afara

28/02/137


8/11

faptului c metoda a construit i a adugat un parametru de tip int la o comand, ea returneaz ireferina parametrului, astfel nct acesta s poat fi ulterior echipat i cu alte proprieti, spreexemplu direcia:

param1.Direction = ParameterDirection.Input;param2.Direction = ParameterDirection.Output;

Un parametru mai este accesibil i prin metoda de indexare a coleciei, care localizeaz unparametru dup poziie sau dup numele su; construcia de mai jos stocheaz drept valoare pentruparametru de intrare n procedur, preul rezultat prin conversia n int a textului introdus deutilizator la rulare, n cmpul txtPret:

sau

Reamintim c n ADO.NET parametrii trebuie s se numeasc la fel cu cei definii nprocedura stocat i s aib acelai tip i aceeai lungime.

Direcia unui parametru poate fi:

Input parametru de intrare n procedur; Output - parametru de ieire, returnat de procedur i care nu poate conine date de intrare

pentru procedur; InputOutput- parametru folosit n acelai timp i pentru intrare i pentru ieire, n comunicarea

cu procedura; ReturnValue- parametru de ieire, returnat de subprogramele de tipfunction.

Pot exista mai muli parametri de tip Input, Output, InputOutput, dar doar un singurparametru de tip ReturnValue.

Funciile de creare, respectiv folosire a unei proceduri stocate cu un parametru de intrare,arat acum astfel:


string strCreare = "CREATE PROCEDURE ProdPestePret(@pretMin INT) AS "+

"SELECT * FROM produse WHERE pret > @pretMin "; SqlConnection con = newSqlConnection( "Data Source=orion;Initial Catalog=doctorat;User ID=sa;Password=as;"

);con.Open();

SqlCommand cmdMea = newSqlCommand();cmdMea.CommandType = CommandType.Text; cmdMea.Connection = con;

// daca exista, o sterge pentru a crea o noua versiunecmdMea.CommandText = "DROP PROCEDURE ProdPestePret";

try { cmdMea.ExecuteNonQuery(); }

catch (Exception excpt){TextBox1.Text = "Stergere esuata: " + excpt.Message;

}

28/02/138

cmdMea.Parameters["@PretMin"].Value =Convert.ToInt32(txtPret.Text);

cmdMea.Parameters[0].Value =Convert.ToInt32(txtPret.Text);


9/11

// refolosire comanda pentru a crea noua proceduracmdMea.CommandText = strCreare;cmdMea.CommandType = CommandType.Text;

try{

cmdMea.ExecuteNonQuery();TextBox1.Text = "Creare cu succes!!";


{TextBox1.Text = "Creare esuata: " + excpt.Message;

}con.Close();

}

protectedvoid btnTestareProc_Click(object sender, System.EventArgs e){

SqlConnection con = newSqlConnection( "Data Source=orion;Initial Catalog=doctorat;User ID=sa;Password=as;"

);

con.Open();

SqlCommand cmdMea = newSqlCommand("ProdPestePret", con);cmdMea.CommandType = CommandType.StoredProcedure;

SqlParameter param1;

param1 = cmdMea.Parameters.Add(newSqlParameter("@PretMin", SqlDbType.Int));

param1.Direction = ParameterDirection.Input;

cmdMea.Parameters["@PretMin"].Value = Convert.ToInt32(txtPret.Text);

SqlDataReader dr = null; try

{dr = cmdMea.ExecuteReader();TextBox1.Text = "Lista produselor peste "

+ txtPret.Text + " EUR \r\n\r\n"; while (dr.Read())

{TextBox1.Text += "\r\n" + dr["codp"] + " " +

dr["denum"] + " " + dr["pret"] + " EUR";

}dr.Close();


{TextBox1.Text = excpt.Message;

}con.Close();

}

De reinut c parametrii de ieire sunt accesibili doar dup nchiderea DataReader-ului.

Exerciiu. Transformai procedura din aplicaia de mai sus, astfel nct s returneze i cteproduse sunt n baza de date, cu preul cuprins ntre dou valori date.

28/02/139


10/11

Rezolvare. Se declar un alt treilea parametru, de data aceasta de tip Output i se folosete can textul surs de mai jos.


SqlConnection myCon = newSqlConnection(

"Data Source=orion;Initial Catalog=doctorat;User ID=sa;Password=as;");

SqlCommand myCmd = newSqlCommand();myCmd.CommandType = CommandType.Text; myCmd.Connection = myCon;

// daca procedura exista, o sterge pentru a crea o noua versiunemyCon.Open();myCmd.CommandText = "DROP PROCEDURE ProdIntre2Preturi";

try { myCmd.ExecuteNonQuery(); tbMesaje.Text ="Creare procedura Ok"; }

catch (Exception excpt){

tbMesaje.Text = "Esec la stergere procedura: " + excpt.Message;}

finally { myCon.Close(); }

// refolosire comanda pentru inregistrare procedura string strCreare = "CREATE PROCEDURE ProdIntre2Preturi(@pretMin FLOAT ,@pretMax FLOAT, @CateProd INT OUTPUT ) AS " + "SELECT * FROM produse WHERE pret > @pretMin AND pret < @pretMax " + "SELECT @CateProd = COUNT (*)FROM produse WHERE pret > @pretMin ANDpret < @pretMax ";

myCmd.CommandText = strCreare;myCmd.CommandType = CommandType.Text;myCon.Open();

try { myCmd.ExecuteNonQuery(); tbMesaje.Text ="\r\nCreare procedura OK"; } catch (Exception excpt)

{tbMesaje.Text = "Exceptie scriere procedura " + excpt.Message;

}myCon.Close();

}

protectedvoid btnApelProc_Click(object sender, System.EventArgs e){

SqlConnection MyCon = newSqlConnection( "Data Source=orion;Initial Catalog=doctorat;User ID=sa;Password=as;"

);

if (MyCon.State == ConnectionState.Closed) MyCon.Open(); SqlCommand myCmd = newSqlCommand("ProdIntre2Preturi", MyCon);

myCmd.CommandType = CommandType.StoredProcedure;

SqlParameter param1, param2, param3;

param1 = myCmd.Parameters.Add(newSqlParameter("@PretMin", SqlDbType.Float));

param1.Direction = ParameterDirection.Input;

28/02/1310


11/11

myCmd.Parameters["@PretMin"].Value = double.Parse(tbMin.Text);

param2 = myCmd.Parameters.Add(newSqlParameter("@PretMax", SqlDbType.Float));

param2.Direction = ParameterDirection.Input;myCmd.Parameters["@PretMax"].Value = double.Parse(tbMax.Text); ;

param3 = newSqlParameter("@CateProd", SqlDbType.Int);param3.Direction = ParameterDirection.Output;myCmd.Parameters.Add(param3);

SqlDataReader dr = null;

try{

dr = myCmd.ExecuteReader(); while (dr.Read())

{tbMesaje.Text += "\r\n" + dr["codp"] + " " +

dr["denum"] + " costa " + dr["pret"];}


{tbMesaje.Text = "Exceptie la executie procedura" + excpt.Message;

} finally

{ if (dr != null) dr.Close();

MyCon.Close();}

tbCateProd.Text = myCmd.Parameters["@CateProd"].Value.ToString();

}

28/02/1311

4_proceduristocate

Documents