Bruke stored procedures i MS SQL med C#

Stored procedures er ferdig lagrede sql-setninger i SQL Server. Fordelen ved å bruke procedures fremfor SQL i koden er mange, men de viktigste grunnene er sikkerhet og ytelse. Dersom spørringene er store og komplekse vil det også av mange oppfattes mer ryddig å flytte denne delen inn i SQL-server fremfor å ha dette i selve koden.

For å ha litt data å teste med kan vi opprette en tabell vha koden

CREATE TABLE Products
(
   ProductId INT NOT NULL IDENTITY (1, 1),
   ProductName VarChar(255) NOT NULL
)
    

En helt enkel tabell med 2 kolonner hvorav den ene er ProductId (Primary key) og den andre kan inneholde litt tekst. For å ta ut data fra denne tabellen vil man bruke SELECT og evt sile data vha WHERE. Dersom man vil ha ut et spesifikt produkt vil man feks bruke "SELECT ProductName FROM Products WHERE ProductId=4". Mange ville da laget følgende kode mot databasen:

string sql = "SELECT * FROM Products WHERE ProductId=" + productId;
SqlConnection conn = new SqlConnection("SERVER=......");
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
  // hente data.....
}
conn.Close();
    

Dette er ikke spesielt lurt da det åpner for det som kalles "Sql injections". Skal ikke gå dypt inn på det, men kort forklart vil man i dette eksempelet kunne sende inn strengen "0; DELETE FROM Products;". Dette ville med koden i eksempelet gitt setningen "SELECT * FROM Products WHERE ProductId=0; DELETE FROM Products;" Det er ikke noe moro... Så uansett om du bruker stored procedures eller ikke så bør du hvertfall bruke SqlParametre. Det er en langt tryggere måte å sende inn data på. I eksempelet under gjør vi akurat det samme som over, men i stedet for variabler i SQL-setningen bruker vi SqlParameters.

string sql = "SELECT * FROM Products WHERE ProductId=@ProductId";
SqlConnection conn = new SqlConnection("SERVER=......");
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@ProductId", productId);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
  // hente data..... 
}
conn.Close();
    

For å dra eksmpelet enda lengre skal vi opprette en Stored Procedure og kalle den i stedet for å skrive T-SQL i selve koden. For å opprette en enkel prosedyre kan man feks skrive følgende kode:

CREATE PROCEDURE GetProduct
(
  @ProductId Int
)
AS
BEGIN
  SELECT  * FROM Products
  WHERE ProductId=@ProductId
END
    

Denne enkle prosedyren trenger variabelen @ProductId og vil returnere data om produkt med den angitte ProductId. For å kalle denne prosedyren fra C# kan man bruke følgende kode:

SqlConnection conn = new SqlConnection("SERVER=......");
SqlCommand cmd = new SqlCommand("GetProduct", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProductId", productId);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
  // hente data.....

}
conn.Close();
    

Koden er nesten lik koden vi brukte tidligere, men det er to ting som er endret: Vi har angitt navnet på prosedyren (GetProducts) i stedet for sql.setningen og vi har angitt egenskapen CommandType i Command objectet til CommandType.StoredProcedure. Koden vil gi eksakt det samme resultatet.

Men det er ved litt mer komplekse database-spørringer at man virkelig ser nytten av prosedyrer. Dersom man har en lang spørring med join's osv på over 1000 tegn blir det fort veldig rotete og uoversiktlig. En annen viktig fordel er bruken av output-parametre. I eksempelet over sender vi inn en parameter, men disse kan like greit sendes ut fra Sql serveren også.  Sett at man skal lagre et nytt produkt i databasen og man ønsker å få returnert ProductId med det samme. Da kan man bruke en output-paramter.

Først trenger vi en prosedyre som lagrer produkter:

CREATE PROCEDURE AddProduct
(
  @ProductId int OUTPUT,
  @ProductName VarChar(255)
)
AS
BEGIN
  INSERT 
  INTO Products 
    (ProductName)
    VALUES 
    (@ProductName)  
  SET @ProductId=SCOPE_IDENTITY()
END
    

Denne prosedyren setter inn et product i databasen og tar i bruk funksjonen SCOPE_IDENTITY() for å returnere ProductId til produktet som nettopp ble lagret. SCOPE_IDENTITY() er ny i SQL Server 2005, men man kan bruke @@IDENTITY dersom man bruker SQL Server 2000.

Når vi skal kalle opp denne prosedyren må vi altså sende inn 2 SqlParametre. En som sender inn produktnavnet og en som tar imot produktId.

SqlConnection conn = new SqlConnection("SERVER=......");
SqlCommand cmd = new SqlCommand("AddProduct", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProductName", "Mitt nye produkt");


//Legger inn vår output-parameter
cmd.Parameters.AddWithValue("@ProductId", DBNull.Value);
cmd.Parameters["@ProductId"].Direction = ParameterDirection.Output;
cmd.Parameters["@ProductId"].SqlDbType = SqlDbType.Int;
cmd.Parameters["@ProductId"].Size = 4;

//Kjører prosedyren
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

// Tar ut verdien fra output-parameter
int productId = Convert.ToInt32(cmd.Parameters["@ProductId"].ToString());           
    

I eksempelet legger vi inn 2 SqlParametre hvor den ene er en output-parameeter. Når man angir en slik parameter sender man inn verdien DBNull.Value og man angir at det er en output-parameter og hvilken datatype og størrelse (Int i SQL er Size 4)

Man kan selvfølgelig sende inn så mange parametre man vil og selve prosedyren kan utføre en rekke oppgaver. Jeg personlig foretrekker å gjøre alle sql-spørringer via Stored Procedures, men det er litt delte meninger om hva som er best. Det er liten tvil om at sikkerheten er bedre ved bruk av SP, men når det kommer til ytelse er det veldig mye uenighet om det er noe å tjene. En stor fordel i tillegg til ytelse er at man kan rette feil i sine sql-setninger uten å endre selve programmet.