Förord

Några programmerare använder Dataparametrar (DataParameter) när de programmerar i .NET, andra gör det inte. Enligt min erfarenhet beror detta i ganska hög grad på att några tycker om dataparametrarna och andra inte tycker om dessa. Det verkar dock som om tyckandet inte baserar sig på så mycket fakta, och därför vill jag försöka bidra med just fakta. Det är fakta som borde ligga till grund för att besvara frågan: Borde man använda dataparametrar i ADO.NET? Förutom att besvara frågan kommer jag att visa exempel på hur man använder dataparametrar.

Innehåll

  » Att använda databaser i .NET
  » Sökvärden
  » Konkatenering av text
  » Datatyp
  » Skydd mot SQL-injection
  » Anrop till lagrade procedurer
  » Prestanda
  » Kan man alltid använda parametrar?
  » Fördelar
  » Nackdelar
  » Rekommendation
  » Resurser

Att använda databaser i .NET

Vi börjar med att titta på hur ett databasanrop med ADO.NET kan se ut. Databasen som används som exempel i artikeln är Northwind, en databas som följer med i SqlServer. Jag har använt SqlServer som exempel i artikeln, men om inget annat anges är resonemangen giltiga också för andra databaser. De tabeller i Nortwind som används är :

• Customer, med kolumnerna
  CustomerID (datatyp nchar längd 5, primärnyckel)
  CompanyName (datatyp nvarchar längd 40)
  City (datatyp nvarchar längd 15)
  
• Products, med kolumnerna
  ProductID (datatyp int, primärnyckel)
  UnitsInStock (datatyp smallint)
  

Kodraderna nedan visar hur man hämtar data från tabellen Customers:

C#

string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
string cmdText = "SELECT * FROM Customers";
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(cmdText,conn);
try{
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read()) {
Response.Write(reader["CustomerID"] + " " + reader["CompanyName"] + " " + reader["City"]);
}
}
catch (Exception ex) {
//hantera felet
Response.Write (ex.Message);
}
finally {
if (conn.State == ConnectionState.Open) {
conn.Close();
}
}


VB.NET

Dim connectionString As String = ConfigurationSettings.AppSettings("ConnectionString")
Dim cmdText As String = "SELECT * FROM Customers"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Dim cmd As SqlCommand = New SqlCommand(cmdText, conn)
Try
conn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
While reader.Read()
Response.Write(reader("CustomerID") & " " & reader("CompanyName") & " " & reader("City"))
End While
Catch ex As Exception
'hantera felet
Response.Write(ex.Message)
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try


I mitt fall fick jag ut 91 rader med raden

ID=ALFKI, Name=Alfreds Futterkiste, City=Berlin

som första rad.

Några kommentarer till koden ovan: Det är en mycket bra rekommendation att alltid köra sina databasanrop inom try och catch eftersom databasanrop kan gå fel. Ytterligare en rekommendation är att se till att databaskopplingen alltid stängs ner när jobbet är gjort, dvs i finally-blocket, eftersom öppna databaskopplingar belastar servern i onödan.
I fallet ovan används ExecuteReader för att få en DataReader (SqlReader), men artikeln gäller alla former av databasanrop.

Sökvärden

I den verkliga världen så vill vi oftast välja ut vilken/vilka rader vi skall hämta ut från våra databastabeller. I vårt exempel kanske vi är intresserade av att välja ut endast de rader i Customers som finns i en viss stad eller har ett visst företagsnamn. Om vi skall kunna välja olika rader från tabellen beroende på användarens val måste vi tala om detta i vårt databasanrop. Om man har textboxar som tillåter inmatning av ort och företagsnamn skulle koden som konstruerar vår command text kunna se ut så här:

C#

string cmdText = "SELECT * FROM Customers WHERE city='" +
txtCity.Text + "' OR CompanyName='" +
txtCompanyName.Text + "'";


VB.NET

Dim cmdText As String = "SELECT * FROM Customers WHERE city='" & _
txtCity.Text & "' OR CompanyName='" & _
txtCompanyName.Text & "'"


Lägg märke till att vi måste ha en ' innan texten och efter texten som hämtas från textboxarna. Detta behövs eftersom en text i SQL skrivs på detta viset: 'en text'. Om jag skriver in ”Alfreds Futterkiste” och ”Berlin” i textboxarna får jag 1 rad tillbaka, samma rad som den första raden i förra frågan.

Nu kan det emellertid uppstå problem. Vad om jag istället skrev in ”Alfred's Futterkiste” i den första textboxen? Då genereras ett exception när frågan körs i databasen. Felmeddelandet blir:

Line 1: Incorrect syntax near 's'.

Detta fel får jag eftersom tecknet ' i en text (t.ex. nvarchar) i SQL betyder att texten avslutas. För att command-texten skall bli korrekt måste jag se till att tecknet ' tolkas som ett tecken och inte avslutar texten. Sättet att göra detta på i SQL är att ange två ' istället för en '. Detta kan man åstadkomma genom att göra en replace enligt följande:

C#

txtCity.Text.Replace("'","''"); //byt ut varje ' mot två stycken '


VB.NET

txtCity.Text.Replace("'","''") 'byt ut varje ' mot två stycken '


Detta måste göras på alla sökvärden som innehåller text, och därför även potentiellt tecknet '. Då blir alltså koden som bygger vår command text så här:

C#

string cmdText = "SELECT * FROM Customers WHERE city='" +
txtCity.Text.Replace("'","''") + "' OR CompanyName='" +
txtCompanyName.Text.Replace("'","''") + "'";


VB.NET

Dim cmdText As String = "SELECT * FROM Customers WHERE city='" & _
txtCity.Text.Replace("'", "''") & "' OR CompanyName='" & _
txtCompanyName.Text.Replace("'", "''") & "'"


I dataparametrar sköts båda dessa sakerna (' runt texter och ersättning av ' till två ' inuti texter) per automatik. Syntaxen blir då lite annorlunda för command-texten och vi behöver dessutom lägga till parametrar till vårt command-objekt enligt följande:

C#

string cmdText = "SELECT * FROM Customers WHERE city=@City OR CompanyName=@CompanyName";
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(cmdText,conn);
cmd.Parameters.Add("@City",txtCity.Text);
cmd.Parameters.Add("@CompanyName",txtCompanyName.Text);


VB.NET

Dim cmdText As String = "SELECT * FROM Customers WHERE city=@City OR CompanyName=@CompanyName"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Dim cmd As SqlCommand = New SqlCommand(cmdText, conn)
cmd.Parameters.Add("@City", txtCity.Text)
cmd.Parameters.Add("@CompanyName", txtCompanyName.Text)


Konkatenering av text

Att konkatenera text (lägga ihop strängar till en större sträng) är en tidsödande operation i .NET. Detta beror på att varje sträng är ett eget objekt och att strängarna i sig inte kan ändras. Med andra ord, varje gång du vill lägga till något i en sträng skapas en ny stäng.
I exemplet ovan måste alltså hela 5 string-objekt skapas, en för varje del som läggs ihop och en för resultatet av konkateneringen .

Datatyp

En viktig del i sökparametrar är att ta hand om datatyp. Vissa sökvärden skall ha datatypen nvarchar/varchar, andra datatypen Date eller int eller float eller något annat. Detta kan man kontrollera enkelt innan databasanropet om man använder sig av dataparametrar.

C#

string cmdText = "SELECT UnitsInStock FROM Products WHERE ProductID=@ProductID";
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(cmdText,conn);
cmd.Parameters.Add("@ProductID",SqlDbType.Int).Value = txtProductID.Text;


VB.NET

Dim cmdText As String = "SELECT UnitsInStock FROM Products WHERE ProductID=@ProductID"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Dim cmd As SqlCommand = New SqlCommand(cmdText, conn)
cmd.Parameters.Add("@ProductID", SqlDbType.Int).Value = txtProductID.Text


Detta gör att man kollar innan databasanropet att datatypen är korrekt. Om datatypen inte är korrekt så behövs inget anrop till databasen som ändå (förmodligen) kommer resultera i ett exception från databasen. Om datatypen är fel när vi använder dataparametrar genereras ett exception innan databasen kontaktas. Vi har alltså sparat tid och belastat databasen mindre.

Om man inte anger datatyp när parametern skapas väljs datatyp beroende på det värdet som skickas med till parametern. Om stringen ”Alfreds Futterkiste” skickas med får alltså parametern datatypen string som i databasen motsvaras av varchar/nvarchar.

Skydd mot SQL-injection

SQL-injection handlar om att en hacker försöker få din kod att göra oväntade saker. Genom att skriva in SQL i textboxen där företagsnamnet borde skrivas in kan en hacker potentiellt åstadkomma stor skada. Här vill jag inte ge några detaljrika exempel utan bara konstatera att en hacker skulle kunna påverka data och/eller strukturen i din databas med hjälp av skicklighet och lite tur. Om du inte skyddat din applikation mot SQL-injection vill säga! När man försöker skriva in SQL i en textbox gör man oftast ett tecken som avbryter sqlkommandot. Detta är oftast en ' eftersom detta kommenterar bort resten av raden. För att skydda sig mot detta kan man byta ut varje ' mot två ', som vi gjorde tidigare. I SqlServer får man ytterligare skydd mot detta om man använder dataparametrar. Detta beror på att en command text (dvs sql som inte är ett anrop till en lagrad procedur) med parametrar körs genom att lämna dels en command text samt parametrarna separat till en system procedure i SqlServer som heter sp_executesql. I lagrade procedurer i SqlServer behandlas parametrar endast som parametrar, om parametern innehåller Sql så körs detta inte som sql. Undantag till denna regel finns, men inte när det gäller sp_executesql. Även andra databaser än SqlServer kan ha liknande skydd.

Anrop till lagrade procedurer

Anrop till lagrade procedurer måste i .NET göras med parametrar, om man har några sökvärden vill säga. Om man anropar en lagrad procedur så sätter man sin command text och dessutom command type. Detta gör att man inte kan lämna med värden i sin command text. Om man t.ex. skriver in ”GetData 10”, dvs vill anropa en lagrad procedur som heter ”GetData” med 10 som värde till den 1:a parametern, så kommer detta inte att fungera. Då letar istället databasen efter en procedur som heter ”GetData 10” som den säkerligen inte kommer hitta. Det enda sättet att skicka med värden till en lagrad procedur är alltså via parametrar. Om man utvecklar utan lagrade procedurer, men kan komma att i ett senare skede använda lagrade procedurer är det alltså en fördel att direkt använda parametrar. Detta gör att övergången till lagrade procedurer medför mindre ändringar i koden.

För att anropa en lagrad procedur måste du också ändra på command-objektets comand type enligt följande:

c#

cmd.CommandType = CommandType.StoredProcedure;


VB.NET

cmd.CommandType = CommandType.StoredProcedure


Prestanda

Prestanda är alltid något man vill förbättra. Jag gjorde ett enkelt test för att se vad som gick snabbast av att konkatenera ihop en select och att använda parametrar. Jag kollade tiderna från det att selectsatsen började byggas till dess att frågan hade körts i databasen. Med den enkla frågan som användes ovan när vi använde två sökvärden (för City och CompanyName) så gick det mer än dubbelt så fort att använda parametrar!

Kan man alltid använda parametrar?

Det finns fall där det är lite jobbigare att använda paramtetrar:

”SELECT * FROM Products WHERE ProductID IN (1,2,3,4,5)”

Om man vill åstadkomma detta med parametrar kan man givetvis skriva

”SELECT * FROM Products WHERE ProductID IN (@P1,@P2,@P3,@P4,@P5)”

Men att ta hand om fallet när vi vill tillåta många värden i en och samma parameter fungerar inte utan vidare. Nedan en kod som man skulle kunna tänka sig att skriva om man låter en användare skriva in en kommaseparerad lista på produktnummer i en textbox.

C#

string cmdText = "SELECT UnitsInStock FROM Products WHERE ProductID IN (@ProductIDs)";
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(cmdText,conn);
cmd.Parameters.Add("@ProductIDs",txtProductIDs.Text); //fungerar inte för mer än ett värde


VB.NET

Dim cmdText As String = "SELECT UnitsInStock FROM Products WHERE ProductID IN (@ProductIDs)"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Dim cmd As SqlCommand = New SqlCommand(cmdText, conn)
cmd.Parameters.Add("@ProductIDs", txtProductIDs.Text) 'fungerar inte för mer än ett värde


Det finns minst två lösningar på detta problem. Det ena är att man i databasen kan parsa textsträngen med flera värden, plocka ut dessa och lägga dem i en tabell (hellst en tabell-variabel alternativt temptabell) och sedan använda en selectsats mot tabellen. Ett annat sätt att lösa detta är att i SqlServer skicka en parameter med datatypen XML till databasen och sedan använda XQuery till att hämta ut värden från XMLen. Så här kan det se ut om man väljer den senare lösningen:

C#

string cmdText = "SELECT UnitsInStock FROM Products WHERE ProductID IN (SELECT products.value('id[1]','int') FROM @ProductIDs.nodes('//product') as R(products))";
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(cmdText,conn);
SqlParameter param = new SqlParameter("@ProductIDs",SqlDbType.Xml);
param.Value = "225";
cmd.Parameters.Add(param);


VB.NET

Dim cmdText As String = "SELECT UnitsInStock FROM Products WHERE ProductID IN (SELECT products.value('id[1]','int') FROM @ProductIDs.nodes('//product') as R(products))"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Dim cmd As SqlCommand = New SqlCommand(cmdText, conn)
Dim param as SqlParameter = new SqlParameter("@ProductIDs",SqlDbType.Xml)
param.Value = "225"
cmd.Parameters.Add(param)


Fördelar

Dataparametrar har alltså följande fördelar:

• Tar hand om ' i och runt varchar,datum och andra datatyper som behöver detta
  
• Verifierar att datatypen är korrekt
  
• Eliminerar resurskrävande konkatenering
  
• Skyddar mot Sql injection
  
• Måste användas när värden skall skickas till lagrade procedurer
  
• Ökar prestandan
  

Det viktiga här är inte bara att vi inte behöver komma ihåg att skriva kod för att ta hand om detta. Koden som körs för att ta hand om dessa sakerna är inte bara skriven den är testad och optimerad.

Nackdelar

Ja, jag ser inte direkt några stora nackdelar med dataparametrar. Några skulle kanske uppleva det som en nackdel att man måste lära sig att använda parametrarna eller att man genom att konkatenera alla värden direkt kan skriva koden på färre antal rader. Dessa nackdelarna får man leva med. Personligen tycker jag att fördelarna väger mycket tyngre än nackdelarna (åtminstone de nackdelar jag har kommit på).

Rekommendation

Min rekommendation är väl inte så svårt att gissa sig till vid det här laget? Använd i så stor utsträckning som möjligt dataparametrar när värden skall skickas med vid databasanrop.

Resurser

Det finns lite olika syntaxer för att använda databasparametrar beroende på vilken .NET DataProvider man använder sig av. I vissa fall får man använda ? istället för parameternamn och sedan lägga in parametrarna i rätt ordning. Nedan finns länkar som visar exempel på syntax för de dataproviders som ingår i .NET Framework 1.1.

• Databasparametrar för Odbc
  
  
• Databasparametrar för OleDb
  
  
• Databasparametrar för Oracle
  
  
• Databasparametrar för SqlServer
  
  
• Databasparametrar för SqlServer CE
  
  

Lycka till!
Herbjörn

1 Kommentarer

1. 
   Stefan Norinder
   

   8 jan 2006

   Det är den andra artikeln av Herbjörn jag läser. Båda har varit väldigt bra. Intressant innehåll beskrivet så att en nybörjare kan hänga med.

Skriv en kommentar på artikeln

Ditt betyg på artikeln

UtmärktLäsvärdIntressantMindre braDålig

Kommentar: