24.3 Parametrisierte Abfragen

Die Suche nach einem bestimmten Datensatz einer Tabelle wird durch die WHERE-Klausel einer SELECT-Abfrage bestimmt:

SELECT ProductName FROM Products WHERE ProductName='Tunnbröd'

Die Hartcodierung dieser Abfrage ist nicht praxisgerecht. Was ist, wenn der Benutzer nicht nach dem Artikel Tunnbröd suchen möchte, sondern die Informationen über den Artikel Tofu benötigt? Die Abfrage sollte allgemeiner formuliert werden, und zwar so, dass der Anwender zur Laufzeit des Programms den Artikel beliebig bestimmen kann.

Die Lösung ist eine parametrisierte Abfrage. Berücksichtigen Sie bei den folgenden Ausführungen jedoch, dass die Wahl des .NET-Datenproviders maßgeblich die Syntax des SELECT-Statements und des Programmcodes einer parametrisierten Abfrage beeinflusst.

24.3.1 SqlClient-Datenprovider

Für den SqlClient-Datenprovider könnte das Statement wie folgt lauten:

SELECT * FROM Products WHERE ProductName = @Productname OR CategoryID = @CatID

@ProductName und @CatID sind benannte Parameter, denen das »@«-Zeichen vorangestellt wird. Dieses gilt jedoch nur für den SqlClient-Datenprovider. Die Datenprovider OleDb und Odbc unterstützen benannte Parameter nicht, sondern nur den generischen Parametermarker, das Fragezeichen »?«. Der Grund für diese Abweichung der Datenprovider ist sehr einfach. Während der OleDb- bzw. Odbc-Datenprovider eine datenbankunabhängige Syntax erlaubt, ist der SqlClient-Provider nur für den SQL Server gedacht, der benannte Parameter mit diesem Präfix unterstützt.

Die Parameter einer parametrisierten Abfrage werden vom Command-Objekt gesammelt. Dieses besitzt dazu eine Parameters-Auflistung, der die einzelnen Parameter hinzugefügt werden. Wenn Sie den SqlClient-Datenprovider verwenden, handelt es sich um den Typ SqlParameter. Sie können einen Parameter hinzufügen, indem Sie entweder die Add-Methode der Auflistung oder die Methode AddWithValue von SqlCommand aufrufen.

Das Beispiel Parametrisiert verwendet zum Hinzufügen die Methode Add. Gesucht wird dabei nach allen Artikeln, die der Kategorie 1 zugeordnet sind, und zusätzlich nach dem Artikel mit der Bezeichnung Konbu. Die beiden Parameter werden mit statischen Werten gefüllt. In der Praxis würden Sie die Werte beispielsweise dem Eingabestrom oder einer Textbox entnehmen.

'...\ADO\Datenbankabfragen\Parametrisiert.vb

Option Strict On 
Imports System.Data.Common, System.Data.SqlClient 
Namespace ADO 
  Module Parametrisiert 
    Sub Sql() 
      Dim con As DbConnection = New SqlConnection() 
      con.ConnectionString = "Data Source=(local);" & _ 
          "Initial Catalog=Northwind;Integrated Security=sspi"

      Dim cmd As DbCommand = New SqlCommand() 
      cmd.CommandText = "SELECT * FROM Products " & _ 
        "WHERE ProductName = @Productname OR CategoryID = @CatID" 
      cmd.Connection = con

      cmd.Parameters.Add(New SqlParameter("@Productname", "Konbu")) 
      cmd.Parameters.Add(New SqlParameter("@CatID", "1")) 
      con.Open() 
      Dim dr As DbDataReader = cmd.ExecuteReader() 
      While (dr.Read()) 
        Console.WriteLine("{0,-5}{1,-5}{2}", _ 
             dr("CategoryID"), dr("ProductID"), dr("ProductName")) 
      End While 
      dr.Close() 
      con.Close()

      Console.ReadLine() 
    End Sub 
    ... 
  End Module 
End Namespace

Wenn Sie den SqlClient-Provider benutzen, ist die Reihenfolge der Parameter innerhalb der Parameters-Auflistung des Command-Objekts unbedeutend, da sie anhand ihres Bezeichners eindeutig identifiziert werden können.

Bei beiden Parametern handelt es sich in diesem Beispiel um Zeichenfolgen, die auch als solche an die Datenbank weitergeleitet werden. Sie können Daten beliebigen Typs angeben, denn die jeweils zweiten Argumente von SqlParameter und AddWithValue sind vom Typ Object.

Diese Flexibilität hat jedoch einen Preis: Wenn Sie bei der Wertübergabe einen ungeeigneten Datentyp verwenden, behandelt die Datenbank die im Parameter gespeicherte Information nicht so, wie Sie es erwarten. Unter Umständen gibt der SQL Server sogar eine Ausnahme vom Typ SqlException zurück, weil der übermittelte Parameter mit der Typdefinition der entsprechenden Spalte nicht übereinstimmt. Sie können das sehr leicht selbst testen, indem Sie im Code des Beispiels anstelle des Artikelbezeichners Konbu eine Integerzahl eintragen.

Der Datenbank diese Verantwortung zu übertragen, ist keine gute Lösung. Der richtige Datentyp sollte bereits im Code des Clients sichergestellt sein. Dazu bieten sich andere Überladungen von SqlCommand.Add und SqlParameter an, die über den Parameterbezeichner hinaus auch den an die Datenbank übergebenen Datentyp steuern. Zudem gibt es noch die Möglichkeit, den Datentyp genauer zu spezifizieren. Beispielsweise können Zeichenfolgen eine unterschiedliche Länge aufweisen. Die Länge kann als drittes Übergabeargument bekannt gegeben werden. Das Kommando in unserem Beispiel oben könnte wie folgt geändert werden:

Dim SqlCmd As SqlCommand = New SqlCommand(...) 
SqlCmd.Parameters.Add("@Productname", SqlDbType.VarChar, 40).Value = "Konbu" 
SqlCmd.Parameters.Add("@CatID", SqlDbType.Int).Value = 1

Alternativ kann der Konstruktor von SqlParameter verwendet werden:

Dim SqlPar As New SqlParameter("@Productname", SqlDbType.VarChar, 40) 
SqlPar.Value = "Konbu" 
SqlCmd.Parameters.Add(SqlPar)

Übergeben Sie einem der beiden Parameter einen Integer, wird keine Ausnahme ausgelöst. Das Ergebnis erscheint im ersten Moment ernüchternd, und es scheint der vorher gemachten Aussage zu widersprechen, dass die Methode Add eine Typüberprüfung gewährleistet. Die Ursache ist allerdings einfach zu erklären: Die Integer-Zahl wird implizit als Zeichenfolge im Parameter eingetragen. Anders sieht es jedoch aus, wenn ein Parameter als Integer festgelegt wird und Sie versuchen, diesem eine Zeichenfolge zuzuweisen:

SqlCmd.Parameters.Add("@Param", SqlDbType.Int).Value = "White"

Beim Aufruf von ExecuteReader wird die Ausnahme FormatException ausgelöst. Diese stammt nicht vom SQL Server, sondern wird von ADO.NET in der Clientanwendung ausgelöst. Damit haben wir ein Ziel erreicht: die Entlastung der Datenbank.

Der Datentyp, den Sie der Add-Methode übergeben, stammt aus der Enumeration SqlDbType, die die Datentypen umfasst, die SQL Server standardmäßig bereitstellt.

24.3.2 SqlParameter

Solange nicht ausdrücklich Parameter hinzugefügt werden, ist die Parameters-Auflistung des Command-Objekts leer. Die Referenz auf die Auflistung erhalten Sie über die Eigenschaft Parameters. Ein Parameter wird durch Aufruf der Methode Add oder AddWithValue hinzugefügt. Alle anderen Methoden der Auflistung gleichen denen aller anderen üblichen Auflistungen von .NET: Mit Count ruft man die Anzahl der Parameter ab, mit Remove wird ein Parameter gelöscht usw.

SqlParameterCollection überlädt die Methode Add vielfach, AddWithValue aber nicht. Jedoch liefern beide als Rückgabewert die Referenz auf das hinzugefügte SqlParameter-Objekt (Ausnahme: die einparametrige von DbParameterCollection übernommene Variante, die die Einfügeposition zurückgibt). Die beiden letzten Parameter von Add sind optional.

Public Function Add(parameterName As String, sqlDbType As SqlDbType, 
  size As Integer, sourceColumn As String) As SqlParameter

Meistens können Sie den Rückgabewert ignorieren. Er ist dann interessant, wenn man die Eigenschaften des Parameters auswerten oder vor dem Absetzen des SQL-Kommandos ändern möchte.

Zum Füllen des Parameters wird der Eigenschaft Value des DbParameter-Objekts der gewünschte Wert zugewiesen:

cmd.Parameters("@ParameterName").Value = "Chai"

Sie rufen den Indexer der DbParameterCollection auf und übergeben den Bezeichner des Parameters. Alternativ können Sie auch den Index des entsprechenden Parameter-Objekts in der Auflistung verwenden.

24.3.3 OleDb-Datenprovider

An dieser Stelle möchte ich Ihnen auch noch zeigen, wie eine parametrisierte Abfrage auf Basis des OleDb-Datenproviders formuliert wird.

'...\ADO\Datenbankabfragen\Parametrisiert.vb

Option Strict On 
Imports System.Data.Common, System.Data.SqlClient 
Namespace ADO 
  Module Parametrisiert 
    ... 
    Sub OleDb()

      Dim con As DbConnection = New OleDbConnection( _ 
        "Data Source=(local);Provider=SQLNCLI;" & _ 
        "Initial Catalog=Northwind;Integrated Security=sspi")

      Dim cmd As DbCommand = New OleDbCommand() 
      cmd.CommandText = "SELECT * FROM Products " & _ 
        "WHERE ProductName = ? OR CategoryID = ? " 
      cmd.Connection = con

      ' oder OleDbCmd.Parameters.Add("Art", OleDbType.VarChar, 40) 
      cmd.Parameters.Add(New OleDbParameter("Art", OleDbType.VarChar, 40)) 
      cmd.Parameters.Add(New OleDbParameter("ID", OleDbType.Integer)) 
      cmd.Parameters("Art").Value = "Konbu" 
      cmd.Parameters("ID").Value = 1

      con.Open() 
      Dim dr As DbDataReader = cmd.ExecuteReader() 
      While (dr.Read()) 
        Console.WriteLine("{0,-5}{1,-5}{2}", _ 
             dr("CategoryID"), dr("ProductID"), dr("ProductName")) 
      End While 
      dr.Close() 
      con.Close()

      Console.ReadLine() 
    End Sub 
  End Module 
End Namespace

Zunächst muss die Verbindungszeichenfolge um

Provider=SQLNCLI

ergänzt werden, um die Vorschriften des OleDb-Datenproviders zu erfüllen. Die Parameter der Abfrage werden nur durch »?« beschrieben und sind daher nur über ihre Reihenfolge identifizierbar, sodass beim Hinzufügen der Parameter zur Parameter-Auflistung des DbCommand-Objekts die Reihenfolge unbedingt beachtet werden muss. Beim Hinzufügen erhalten die Parameter Bezeichner und können dann entweder über den Index oder den Bezeichner angesprochen werden.

Ihre Meinung

Wie hat Ihnen das Openbook gefallen? Wir freuen uns immer über Ihre Rückmeldung. Schreiben Sie uns gerne Ihr Feedback als E-Mail an kommunikation@rheinwerk-verlag.de.