32.5 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'

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

Die Lösung lautet: Wir müssen eine parametrisierte Abfrage formulieren. 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.

32.5.1 Parametrisierte Abfragen mit dem SqlClient-Datenprovider

Ist die Entscheidung auf den SqlClient-Datenprovider gefallen, könnte das Statement wie folgt lauten:

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

Listing 32.12 Parametrisierte SQL-Abfrage

@ProductName und @CatID sind benannte Parameter, denen das @-Zeichen vorangestellt wird. Dieses gilt jedoch nur im Zusammenhang mit dem SqlClient-Datenprovider. Die Datenprovider OleDb und Odbc unterstützen benannte Parameter nicht, sondern nur den generischen Parametermarker. Dabei handelt es sich um 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 für den SQL Server gedacht, der benannte Parameter mit diesem Präfix unterstützt.

Die Parameter einer parametrisierten Abfrage werden vom SqlCommand-Objekt gesammelt. Dieses besitzt eine Parameters-Auflistung, der die einzelnen Parameter hinzugefügt werden. Verwenden Sie den SqlClient-Datenprovider, 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 aufrufen.

Das Beispiel ParametrisierteAbfrage verwendet zum Hinzufügen die Methode AddWithValue. Die beiden Parameter werden mit statischen Werten gefüllt. In der Praxis würden Sie die Werte dem Eingabestrom oder beispielsweise einem Eingabefeld entnehmen.

// Beispiel: ..\Kapitel 32\ParametrisierteAbfrage
static void Main(string[] args){
  SqlConnection con = new SqlConnection("...");
  string strSQL = "SELECT * FROM Products " +
                  "WHERE ProductName = @Productname OR " +
                         "CategoryID = @CatID";
  SqlCommand cmd = new SqlCommand(strSQL, con);
  // Parameter hinzufügen und Werte übergeben
  cmd.Parameters.AddWithValue("@Productname", "Konbu");
  cmd.Parameters.AddWithValue("@CatID", "1");
  con.Open();
  SqlDataReader rd = cmd.ExecuteReader();
  while (rd.Read())
    Console.WriteLine("{0,-5}{1,-35}{2}",
               rd["ProductID"], rd["ProductName"], rd["UnitPrice"]);
  rd.Close();
  con.Close();
}

Listing 32.13 Parametrisierte Abfrage

Bei benannten Parametern ist die Reihenfolge der Parameter innerhalb der Parameters-Auflistung des SqlCommand-Objekts unbedeutend.

Bei beiden Parametern handelt es sich in diesem Beispiel um Zeichenfolgen, die auch als solche an die Datenbank weitergeleitet werden. Sie können hier jeden Datentyp angeben, denn das zweite Argument von AddWithValue ist vom Typ Object.

Einen Haken kann der sehr einfache Einsatz der Methode AddWithValue dennoch haben. Verwenden Sie bei der Wertübergabe einen ungeeigneten Datentyp, behandelt die Datenbank die im Parameter gespeicherte Information vielleicht 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 zumindest weitgehend im Code des Clients sichergestellt sein. Dazu bietet sich die vielfach überladene Methode Add an, die über den Parameterbezeichner hinaus auch den an die Datenbank übergebenen Datentyp steuert. 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. In unserem Beispiel oben könnten die beiden Anweisungen

cmd.Parameters.AddWithValue("@Name", "Konbu");
cmd.Parameters.AddWithValue("@CatID", 1);

durch

cmd.Parameters.Add("@Productname", SqlDbType.VarChar, 40).Value = "Konbu";
cmd.Parameters.Add("@CatID", SqlDbType.Int).Value = 1;

ersetzt werden.

Übergeben Sie einem der beiden Parameter einen Integer-Wert, wird keine Ausnahme ausgelöst. Das Ergebnis sieht im ersten Moment ernüchternd aus und 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 Integerzahl 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:

cmd.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.

32.5.2 Die Klasse »SqlParameter«

Solange nicht ausdrücklich Parameter hinzugefügt werden, ist die Parameters-Auflistung des SqlCommand-Objekts leer. Die Referenz auf die Auflistung erhalten Sie über die Eigenschaft Parameters. Ein Parameter wird unter 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.

Die Methode Add ist vielfach überladen, AddWithValue überhaupt nicht. Beiden ist aber eines gemeinsam: Der Rückgabewert ist die Referenz auf das hinzugefügte SqlParameter-Objekt. 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 SqlParameter-Objekts der entsprechende Wert zugewiesen:

cmd.Parameters["@ParameterName"].Value = "Chai";

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

32.5.3 Asynchrone Abfragen

Die Methoden ExecuteReader, ExecuteNonQuery oder ExecuteXmlReader arbeiten synchron. Das bedeutet, dass die Anwendung erst dann weiterarbeiten kann, wenn der SQL Server die Anfrage verarbeitet und die erste Datenzeile der Ergebnismenge zurückliefert. Dauert diese Operation eine längere Zeit, wirkt die Clientanwendung wie eingefroren.

ADO.NET löst dieses Problem durch die Bereitstellung asynchroner Methoden. Ergänzt werden die synchronen Methoden durch jeweils ein Methodenpaar: Eine Methode hat das Präfix Begin, die zweite das Präfix End. Beispielsweise lauten die asynchronen Varianten von ExecuteReader BeginExecuteReader und EndExecuteReader, die wie folgt definiert sind:

public IAsyncResult BeginExecuteReader(IAsyncResult, Object)
public SqlDataReader EndExecuteReader(IAsyncResult)

Mit BeginExecuteReader wird die asynchrone Operation gestartet. Der aufrufende Code wartet jedoch nicht darauf, bis das Resultat vorliegt, sondern führt die Anweisungen aus, die dem asynchronen Aufruf folgen. Es stellt sich nur noch die Frage, wie das Clientprogramm darüber informiert wird, dass die asynchrone Operation beendet ist, und wie die Ergebnismenge abgefragt werden kann. Dazu bieten sich zwei Möglichkeiten an:

• Sie fragen in einer Schleife permanent ab, ob die asynchrone Operation bereits beendet ist. Dieses Verfahren wird als Polling bezeichnet.
• Sie definieren eine Rückrufmethode (Callback-Methode), die aufgerufen wird, sobald das Ergebnis vorliegt.

Beide Varianten werde ich Ihnen gleich an einem Beispiel vorstellen.

Asynchrone Operationen sind per Vorgabe nicht aktiviert. Damit das SqlConnection-Objekt auch asynchrone Abfragen ermöglicht, muss die Verbindungszeichenfolge um

Asynchronous Processing=true

ergänzt werden. Alternativ steht Ihnen auch die Eigenschaft AsynchronousProcessing des SqlConnectionStringBuilder zur Verfügung.

Damit wir in den folgenden beiden Beispielprogrammen auch eine Verzögerung der SQL Server-Anfrage simulieren können, schreiben wir eine Batchabfrage, der wir als erste Anweisung WAITFOR DELAY übergeben. Dieser Anweisung teilen wir mit, wie lange die Batchabfrage blockiert werden soll, beispielsweise zwei Sekunden:

WAITFOR DELAY '00:00:02'

Das Polling-Verfahren

Das erste asynchrone Beispielprogramm stellt das Polling-Verfahren vor. Sehen Sie sich zuerst den Beispielcode an.

// Beispiel: ..\Kapitel 32\PollingSample
static void Main(string[] args) {
  SqlConnection con = new SqlConnection("...;
                      Asynchronous Processing=true");
  string strSQL = "WAITFOR DELAY '00:00:01';SELECT * FROM Products";
  SqlCommand cmd = new SqlCommand(strSQL, con);
  con.Open();
  // Asynchroner Aufruf
  IAsyncResult result = cmd.BeginExecuteReader();
  int counter = 0;
  while (!result.IsCompleted) {
    DoSomething(counter);
    counter++;
  }
  Console.WriteLine("Das Ergebnis liegt vor: .....");
  SqlDataReader rd = cmd.EndExecuteReader(result);
  while(rd.Read())
    Console.WriteLine(rd["ProductName"]);
  Console.ReadLine();
}
static void DoSomething(int counter) {
  Console.WriteLine(counter);
}

Listing 32.14 Das Polling-Verfahren

Beim Polling wird nicht darauf gewartet, bis das Vorliegen der Ergebnismenge der Clientanwendung signalisiert wird. Stattdessen wird in einer Schleife abgefragt, ob der Datenbankserver die Anfrage fertig bearbeitet hat. Der Aufruf der Methode BeginExecuteReader liefert ein Objekt zurück, das die Schnittstelle IAsyncResult implementiert. Dieses Objekt beschreibt den Status der asynchronen Abfrage, unter anderem durch die Eigenschaft IsCompleted. Der Wert lautet false, falls die Anfrage noch nicht beendet ist, ansonsten true. Beachten Sie, dass in diesem Beispiel die parameterlose Methode BeginExecuteReader eingesetzt wird.

Wir werten im Beispielprogramm IsCompleted aus. Solange die Ergebnismenge noch nicht vorliegt, wird von der Clientanwendung eine andere Aufgabe erledigt. Hierbei handelt es sich um den Aufruf der Methode DoSomething, die einen einfachen Zählerstand in das Konsolenfenster schreibt. Ist die Anfrage an den Datenbankserver beendet, kann das Ergebnis geholt werden. Dazu dient die Methode EndExecuteReader, die ihrerseits die Referenz auf ein SqlDataReader-Objekt bereitstellt, das wir zur Ausgabe der Spalte ProductName benutzen.

Bereitstellen einer Rückrufmethode

Während beim Polling fortwährend geprüft wird, ob der Datenbankserver die Anfrage bearbeitet hat, wird durch das Bereitstellen einer Rückrufmethode auf das Signal der Datenbank gewartet, dass die Operation beendet ist. Das Signal ist der Aufruf einer Methode im Client, der sogenannten Rückrufmethode.

Die »Adresse« der Rückrufmethode, die im folgenden Beispiel CallbackMethodSample heißt, wird dem ersten Parameter der überladenen Methode BeginExecuteReader übergeben. Es handelt sich dabei um einen Parameter vom Typ des Delegaten AsyncCallback, der eine Rückrufmethode vorschreibt, die einen Parameter vom Typ IAsyncResult hat und die ihrerseits selbst void ist. BeginExecuteReader definiert mindestens noch einen zweiten Parameter. Dieser ist vom Typ Object und akzeptiert somit jedes beliebige Objekt. Das Objekt wird der Eigenschaft AsyncState der IAsyncResult-Schnittstelle zugewiesen und kann in der Callback-Methode abgerufen werden. Im Beispiel wird die Referenz auf das SqlCommand-Objekt übergeben.

Nach Beendigung der asynchronen Operation wird die Rückrufmethode ausgeführt, aus der heraus EndExecuteReader aufgerufen wird. Das dazu notwendige SqlCommand-Objekt wurde dem zweiten Parameter der Methode BeginExecuteReader übergeben und kann nach Auswertung der Eigenschaft AsyncState des IAsyncResult-Parameters und vorheriger Konvertierung benutzt werden. Danach steht auch der DataReader zur Verfügung.

Nun aber das vollständige Beispiel:

// Beispiel: ..\Kapitel 32\CallbackMethodSample
class Program {
  static void Main(string[] args) {
    SqlConnection con = new SqlConnection("...;
                        Asynchronous Processing=true");
    string strSQL = "SELECT * FROM Products";
    SqlCommand cmd = new SqlCommand(strSQL, con);
    con.Open();
    // Delegaten initialisieren
    AsyncCallback callback = new AsyncCallback(CallbackMethod);
    // Asynchrone Operation starten
    cmd.BeginExecuteReader(callback, cmd);
    // Simulation eines asynchronen Szenarios
    for (int counter = 0; counter < 100; counter++)
    {
        Console.WriteLine(new string('-', 50));
        Console.WriteLine("Counter = {0}", counter);
    }
    Console.ReadLine();
  }
  static void CallbackMethod(IAsyncResult result) {
    SqlCommand cmd = (SqlCommand)result.AsyncState;
    // Ergenisliste holen
    SqlDataReader rd = cmd.EndExecuteReader(result);
    while(rd.Read())
      Console.WriteLine(rd["ProductName"]);
    rd.Close();
  }
}

Listing 32.15 Asynchrone Datenabfrage

Die Konsolenausgabe des Beispielcodes ist in Abbildung 32.2 zu sehen. Es ist sehr schön zu erkennen, wie beide Operationen parallel ablaufen.

Abbildung 32.2 Die Ausgabe des Beispielprogramms »CallbackMethodSample«

32.5.4 Gespeicherte Prozeduren (Stored Procedures)

Bei einer gespeicherten Prozedur (Stored Procedure) handelt es sich um eine Gruppe von SQL-Anweisungen, die kompiliert werden. Das hat einen entscheidenden Vorteil: Die Leistung verbessert sich deutlich, wenn die gespeicherte Prozedur ausgeführt wird, da die SQL-Anweisungen nicht bei jedem Aufruf neu kompiliert werden müssen.

Eine gespeicherte Prozedur ist nicht schwierig zu verstehen. Wir wollen uns das an einem Beispiel ansehen.

CREATE PROCEDURE SearchProducts
(
   @Price money,
   @OrderedUnits smallint
)
AS
   SELECT *
   FROM Products
   WHERE UnitPrice < @Price AND UnitsOnOrder = @OrderedUnits

Listing 32.16 Beispiel einer gespeicherten Prozedur

Diese gespeicherte Prozedur beschreibt eine Auswahlabfrage, die alle Artikel der Tabelle Products liefert, die eine bestimmte Preisgrenze unterschreiten und eine bestimmte Anzahl von Bestelleinheiten haben. Damit ist die Stored Procedure gleichbedeutend mit dem SqlClient-Datenprovider-Befehl:

SELECT *
FROM Products
WHERE UnitPrice < @Price AND UnitsOnOrder = @OrderedUnits

Gespeicherte Prozeduren bieten sich besonders an, wenn ein Kommando sehr häufig ausgeführt werden soll. Sie sind nicht nur leistungsfähiger als normale SQL-Kommandos, sondern bieten darüber hinaus weiter gehende Möglichkeiten: Stored Procedures können Berechnungen ausführen, Ein- und Ausgabeparameter entgegennehmen (ähnlich Wert- und Referenzparametern) oder ein Resultat an den Aufrufer liefern.

Mehr möchte ich Ihnen an dieser Stelle nicht zu den gespeicherten Prozeduren sagen. Es gibt viel Literatur zu diesem Thema, wenn Sie es vertiefen möchten. Ich werde Ihnen nachher an einem komplexeren Beispiel zeigen, wie gespeicherte Prozeduren, die die aufgeführten Features haben, mit ADO.NET-Code behandelt werden.

Gespeicherte Prozeduren in Visual Studio 2012 erstellen

Ein herkömmlicher SQL-Befehl wird vom Client gegen die Datenbank abgesetzt. Gespeicherte Prozeduren sind, soweit die Datenbank diese unterstützt, Elemente der Datenbank selbst, so wie beispielsweise die Tabellen oder Sichten. Wenn Sie wollen, können Sie sehr einfach aus Visual Studio 2012 heraus gespeicherte Prozeduren zu einer Datenbank hinzufügen. Öffnen Sie dazu den Server-Explorer in Visual Studio 2012. In diesem finden Sie den Knoten Datenverbindungen. Im Kontextmenü dieses Knotens wählen Sie Verbindung hinzufügen. Es öffnet sich ein Dialog, wie er in Abbildung 32.3 zu sehen ist.

Abbildung 32.3 Dialog zum Hinzufügen einer Datenbankverbindung

Tragen Sie im oberen Kombinationslistenfeld den Servernamen ein, auf dem die SQL Server-Datenbank installiert ist, zu der Sie Verbindung aufnehmen wollen. Per Vorgabe ist der Dialog bereits so voreingestellt, dass davon ausgegangen wird, es handle sich um SQL Server. Sie können die Verbindung natürlich auch zu einer anderen Datenbank aufbauen, müssen dann aber zuvor die eingetragene Datenquelle entsprechend anpassen.

Haben Sie die Installationsvorgaben des SQL Servers unverändert übernommen, so ist die Windows-Authentifizierung eingestellt, und Sie brauchen, soweit Sie mit entsprechenden administrativen Rechten ausgestattet sind, keine Änderungen an den Anmeldeinformationen vorzunehmen. Anschließend wählen Sie die gewünschte Datenbank aus. Sie können die eingestellten Verbindungsdaten nun testen.

Im Server-Explorer wird die neue Verbindung zur Datenbank eingetragen. Unter den datenbankspezifischen Knoten finden Sie nun auch Gespeicherte Prozeduren. Klicken Sie dann im Kontextmenü des Knotens auf Neue gespeicherte Prozedur hinzufügen.

Abbildung 32.4 Eine gespeicherte Prozedur der Datenbank hinzufügen

Im Code-Editor wird daraufhin ein weiteres Fenster geöffnet, in dem bereits die elementare Struktur der Stored Procedure vorgegeben ist. Eine gespeicherte Prozedur wird mit CREATE PROCEDURE eingeleitet; dem schließt sich der Bezeichner an. Einige Teile der Struktur sind mit /*...*/ auskommentiert. Dazu gehört auch der Block, in dem die Parameter angegeben werden. Hinter AS folgen die SQL-Anweisungen. Eine gespeicherte Prozedur wird mit dem optionalen RETURN, das den Rückgabewert der gespeicherten Prozedur angibt, abgeschlossen.

Abbildung 32.5 Das Fenster einer neuen gespeicherten Prozedur im Code-Editor

Als Erstes sollten Sie der Stored Procedure einen beschreibenden Namen geben, z. B. SearchProducts. Parameter werden im Block zwischen CREATE PROCDURE und AS definiert. Dabei wird zuerst der Parametername angegeben, der das Präfix @ haben muss. Dahinter folgt der Datentyp. Mehrere Parameter in einer Stored Procedure werden durch ein Komma getrennt.

Standardmäßig sind alle Parameter Eingabeparameter, die von der Stored Procedure zur Ausführung benötigt werden, selbst aber kein Resultat zurückliefern. Gespeicherte Prozeduren kennen aber auch Ausgabeparameter, die mit Referenzparametern vergleichbar sind. Diese liefern dem Aufrufer ein Ergebnis. Ausgabeparameter werden mit OUTPUT gekennzeichnet.

Nachdem der SQL-Code im Code-Editor eingetragen ist, können Sie die Stored Procedure speichern. Gespeichert wird die Stored Procedure allerdings nicht im Projekt, sondern in der Datenbank, was Sie auch sofort im Server-Explorer erkennen. Beim Speichervorgang wird die Syntax überprüft. Sollte die SQL-Syntax einen Fehler aufweisen, werden Sie mit einer Fehlermeldung darauf aufmerksam gemacht.

Damit hört die Unterstützung von Visual Studio 2012 aber nicht auf. Sie können Ihre neue gespeicherte Prozedur auch in der Entwicklungsumgebung testen. Dazu sollte sich der Mauszeiger über dem Code-Fenster der Stored Procedure befinden. Öffnen Sie dann das Kontextmenü, und wählen Sie Ausführen. Es öffnet sich ein Dialog, wie in Abbildung 32.6 zu sehen ist, in dem Sie in der Spalte Wert den Parametern die gewünschten Daten zuweisen.

Abbildung 32.6 Dialog, um den Parametern Werte zuzuweisen

Wenn Sie anschließend auf OK klicken, sehen Sie im Fenster Ausgabe das Ergebnis des Aufrufs, ähnlich wie in Abbildung 32.7 gezeigt.

Abbildung 32.7 Das Ergebnis des Aufrufs der Stored Procedure SearchProducts

Eine gespeicherte Prozedur aufrufen

Die soeben entwickelte gespeicherte Prozedur SearchProducts soll nun aufgerufen werden. Prinzipiell ist der Weg ähnlich dem, den wir beim Aufruf einer parametrisierten Abfrage beschritten haben. Es gibt aber einen ganz wichtigen Unterschied: Wir müssen dem SqlCommand-Objekt ausdrücklich mitteilen, dass es kein SQL-Kommando, sondern eine gespeicherte Prozedur ausführen soll. Um dem Objekt den Typ eines Kommandos mitzuteilen, wird der Eigenschaft CommandType die passende Information übergeben.

Die Eigenschaft ist vom Typ der gleichnamigen Enumeration, die angibt, wie das unter der Eigenschaft CommandText angegebene Kommando zu interpretieren ist.

Bisher haben wir die Eigenschaft CommandType nicht benutzt, weil wir immer ein SQL-Kommando abgesetzt haben, das durch die Standardeinstellung Text beschrieben wird. Da wir nun eine Stored Procedure ausführen wollen, müssen wir CommandType den Wert CommandType.StoredProcedure zuweisen. Das SqlCommand-Objekt benutzt diese Information, um die Syntax für den Aufruf der gespeicherten Prozedur zu generieren.

Komplexe gespeicherte Prozeduren

Eine gespeicherte Prozedur ist nicht immer so einfach aufgebaut wie SearchProducts, die nur Datensätze als Ergebnis der Ausführung zurückliefert. Eine gespeicherte Prozedur kann sowohl über die Parameterliste als auch über RETURN Werte an den Aufrufer zurückliefern. Dazu ein Beispiel:

CREATE PROCEDURE GetProduct
(
  @id int,
  @Artikel varchar(40) OUTPUT,
  @Preis money OUTPUT
)
AS
  SELECT @Artikel=ProductName, @Preis=UnitPrice
  FROM Products
  WHERE ProductID=@id
RETURN @@ROWCOUNT

Listing 32.17 Komplexe gespeicherte Prozedur

Die Stored Procedure definiert neben dem Eingabeparameter @id mit @Artikel und @Preis auch zwei Ausgabeparameter, denen beim Aufruf zwar kein Wert übergeben wird, die aber einen Wert zurückliefern. Der Rückgabewert @@ROWCOUNT ist eine Systemfunktion von SQL Server, die die Anzahl der Zeilen angibt, auf die sich die letzte Anweisung ausgewirkt hat.

// Beispiel: ..\Kapitel 32\KomplexeStoredProcedure
class Program {
  static void Main(string[] args) {
    SqlConnection con = new SqlConnection("...");
    // SqlCommand-Objekt definieren
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = con;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "GetProduct";
    // SqlParameter definieren
    cmd.Parameters.Add("@RetValue", SqlDbType.Int);
    cmd.Parameters.Add("@id", SqlDbType.Int);
    cmd.Parameters.Add("@Artikel", SqlDbType.VarChar, 40);
    cmd.Parameters.Add("@Preis", SqlDbType.Money);
    cmd.Parameters["@RetValue"].Direction = ParameterDirection.ReturnValue;
    cmd.Parameters["@Artikel"].Direction = ParameterDirection.Output;
    cmd.Parameters["@Preis"].Direction = ParameterDirection.Output;
    // Übergabewert angeben
    cmd.Parameters["@id"].Value = 1;
    con.Open();
    cmd.ExecuteNonQuery();
    // SqlParameterCollection auswerten
    if ((int)(cmd.Parameters["@RetValue"].Value) == 1) {
      Console.WriteLine("Zuname: {0}", cmd.Parameters["@Artikel"].Value);
      Console.WriteLine("Vorname:{0}", cmd.Parameters["@Preis"].Value);
    }
    Console.WriteLine("{0} Datensatz gefunden.",
                        cmd.Parameters["@RetValue"].Value);
    con.Close();
    Console.ReadLine();
  }
}

Listing 32.18 Eine komplexe gespeicherte Prozedur aufrufen

Im ersten Schritt wird nach dem Öffnen der Verbindung das SqlCommand-Objekt definiert. Anschließend wird für jeden Parameter der gespeicherten Prozedur der Parameter-Auflistung ein SqlParameter-Objekt hinzugefügt. Als Parameter wird auch der von RETURN gelieferte Rückgabewert verstanden. Damit benötigt der Aufruf insgesamt vier Parameter-Objekte.

SqlParameter können ein unterschiedliches Verhalten haben. Dies muss ADO.NET wissen, um die gespeicherte Prozedur richtig zu verarbeiten. Standardmäßig beschreibt ein SqlParameter-Objekt einen Eingabeparameter. Abweichungen davon müssen über die Direction-Eigenschaft des Parameter-Objekts festgelegt werden, die vom Typ ParameterDirection ist, einer Enumeration mit vier Konstanten.

Jetzt muss die Parameterliste gefüllt werden, um das SqlCommand-Objekt anschließend auszuführen. Dazu wird dem Parameter @id die Spalte ProductID zugewiesen, anhand derer der gesuchte Artikel identifiziert werden soll. Weil die gespeicherte Prozedur keine Datensatzliste zurückgibt, genügt der Aufruf der Methode ExecuteNonQuery auf das SqlCommand-Objekt. Das Ergebnis des Aufrufs kann danach ausgewertet werden, indem sowohl der Inhalt des Rückgabewertes als auch der Inhalt der Ausgabeparameter abgerufen 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.