SO WIRD´S GEMACHT: Ausführen von parametrisierten SQL-gespeicherten Prozeduren mit dem ODBC .NET-Provider und Visual C# .NET

Dieser schrittweise aufgebaute Artikel beschreibt, wie Sie eine parametrisierte SQL Server-gespeicherte Prozedur mit dem ODBC .NET Managed Provider und Visual C# .NET aufrufen können.

Obwohl sich die Ausführung einer parametrisierten gespeicherten Prozedur mit dem ODBC .NET-Provider nur geringfügig von der Ausführung der gleichen Prozedur mit dem SQL oder dem OLE DB-Provider unterscheidet, gibt es einen wichtigen Unterschied: die gespeicherte Prozedur muss über die ODBC CALL-Syntax statt über den Namen der gespeicherten Prozedur aufgerufen werden. Weitere Informationen zu dieser CALL-Syntax finden Sie unter dem Thema "Procedure Calls" in der ODBC-Programmer's Reference in der MSDN-Bibliothek.

Beispiele für Aufrufsyntax

1. Im folgenden sehen Sie ein Beispiel für eine Aufrufsyntax für eine gespeicherte Prozedur in der Northwind-Beispieldatenbank, die einen einzelnen Eingabeparameter erwartet:

   {CALL CustOrderHist (?)}

2. Im folgenden sehen Sie ein Beispiel für die Aufrufsyntax für eine gespeicherte Prozedur, die einen Eingabeparameter erwartet und einen Ausgabeparameter und einen Rückgabewert zurückgibt. Der erste Platzhalter steht für den Rückgabewert:

   {? = CALL Procedure1 (?, ?)

3. Der ODBC .NET Managed Provider verarbeitet Parameter wie der OLE DB-Provider nach der Ordinalposition (nullbasiert) und nicht nach dem Namen.

Projekt testen - Einzelner Eingabeparameter

1. Wenn nicht bereits erfolgt, downloaden Sie den ODBC. NET Managed Provider von der folgenden Microsoft-Website und installieren Sie ihn:
   http://www.microsoft.com/data

   (http://www.microsoft.com/data)
2. Starten Sie Visual Studio .NET und erstellen Sie ein neues Visual C# .NET Windows-Anwendungsprojekt mit einem Namen Ihrer Wahl.
3. Klicken Sie im Menü Projekt auf Verweis hinzufügen und doppelklicken Sie anschließend auf Microsoft.Data.ODBC.dll, um es zu der Liste mit ausgewählten Elementen hinzuzufügen. Schließen Sie das Dialogfeld Verweise.
4. Fügen Sie ganz oben im Codefenster die folgenden Anweisungen hinzu:

   using System.Data;
   using Microsoft.Data.Odbc;

5. Fügen Sie dem Standardformular eine Schaltfläche von der Toolbox hinzu.
6. Doppelklicken Sie auf die Befehlsschaltfläche, um zu dem Codefenster für das Click-Ereignis der Schaltfläche zu wechseln. Geben oder fügen Sie den folgenden Code in die Click-Ereignisprozedur ein, wobei Sie die SQL Server-Verbindungszeichenfolge wie erforderlich anpassen:

   OdbcConnection cn;
   OdbcCommand cmd;
   OdbcParameter prm;
   OdbcDataReader dr;
   
   try {
       //Change the connection string to use your SQL Server.
       cn = new OdbcConnection("Driver={SQL Server};Server=servername;Database=Northwind;Trusted_Connection=Yes");
   
       //Use ODBC call syntax.
       cmd = new OdbcCommand("{call CustOrderHist (?)}", cn);
   
       prm = cmd.Parameters.Add("@CustomerID", OdbcType.Char, 5);
       prm.Value = "ALFKI";
   
       cn.Open();
   
       dr = cmd.ExecuteReader();
   
       //List each product.
       while (dr.Read()) 
   	Console.WriteLine(dr.GetString(0));
   
       //Clean up.
       dr.Close();
       cn.Close();
   }
   catch (OdbcException o) {
       MessageBox.Show(o.Message.ToString());
   }

7. Führen Sie das Projekt aus. Dieser Code ruft die gespeicherte Prozedur "CustOrderHist" auf, wobei die Kunden-Nr. als einzelner Eingabeparameter übergeben wird, und gibt eine Ergebnismenge zurück. Im Ausgabefenster sollte die Liste der Produkte erscheinen, die der Northwind-Kunde ALFKI bestellt hat.
   
   Hinweis: Drücken Sie [Strg]+[Alt]+[O], um das Ausgabefenster zu öffnen.

Projekt testen - Mehrere Parametertypen

1. Erstellen Sie mit Query Analyzer die folgende gespeicherte Prozedur in der Northwind-Beispieldatenbank. Diese gespeicherte Prozedur akzeptiert eine Kunden-Nr. als Eingabeparameter und gibt eine Liste der von dem Kunden aufgegebenen Bestellungen, die durchschnittlichen von diesem Kunden bezahlten Frachtkosten pro Bestellung als Ausgabeparameter und die Anzahl der von diesem Kunden aufgegebenen Bestellungen als Rückgabewert zurück.

   CREATE PROCEDURE usp_TestParameters
   @CustID CHAR(5),
   @AvgFreight MONEY OUTPUT
   AS
   SELECT @AvgFreight = AVG(Freight) FROM Orders WHERE CustomerID = @CustID
   SELECT * FROM Orders WHERE CustomerID = @CustID
   RETURN @@ROWCOUNT

2. Wiederholen Sie die oben aufgeführten Schritte 1 bis 6, wobei Sie den folgenden Code in die Click-Ereignisprozedur der Befehlsschaltfläche einfügen:

   OdbcConnection cn;
   
   try {
       cn = new OdbcConnection("Driver={SQL Server};Server=servername;Database=Northwind;Trusted_Connection=Yes");
   
       OdbcCommand cmd = new OdbcCommand("{? = call usp_TestParameters (?, ?)}", cn);
   
       OdbcParameter prm = cmd.Parameters.Add("@RETURN_VALUE", OdbcType.Int);
       prm.Direction = ParameterDirection.ReturnValue;
   
       prm = cmd.Parameters.Add("@CustomerID", OdbcType.Char, 5);
       prm.Value = "ALFKI";
   
       prm = cmd.Parameters.Add("@AvgFreight", OdbcType.Double);
       prm.Direction = ParameterDirection.Output;
   
       cn.Open();
       OdbcDataReader dr = cmd.ExecuteReader();
   
       while (dr.Read())
            Console.WriteLine(dr.GetString(0));
   
       dr.Close();
       cn.Close();
   
       Console.WriteLine("Average Freight (output param): {0}", cmd.Parameters[2].Value);
       Console.WriteLine("Order Count (return value): {0}", cmd.Parameters[0].Value);
   
   }
   
   catch (OdbcException o) {
       MessageBox.Show(o.Message.ToString());
   }

3. Führen Sie das Projekt aus. Dieser Code ruft die im obigen Schritt 1 erstellte gespeicherte Prozedur "usp_TestParameters" auf, wobei die Kunden-Nr. als einzelner Eingabeparameter übergeben wird, und gibt eine Ergebnismenge, einen Ausgabeparameter und einen Rückgabewert zurück. Im Ausgabefenster sollten eine Liste der vom Northwind-Kunden ALFKI aufgegebenen Bestellungen, die vom Kunden bezahlten durchschnittlichen Frachtkosten pro Bestellung und die Anzahl der Bestellungen erscheinen.
   
   Hinweis: Drücken Sie [Strg]+[Alt]+[O], um das Ausgabefenster zu öffnen.

Problembehandlung

• Die gewöhnlich zum Aufrufen von gespeicherten Prozeduren verwendete ADO-Syntax, bei der der Name der Prozedur allein als Befehlstext dient, kann mit dem ODBC .NET Managed Provider nicht verwendet werden.
• Wenn eine gespeicherte Prozedur eine Ergebnismenge zurückgibt, sind der/die Ausgabeparameter und der Rückgabewert erst verfügbar, wenn auf die Ergebnismenge zugegriffen und sie geschlossen wurde. Wenn beispielsweise die Zeile "dr.Close()" in dem zweiten oben aufgeführten Beispiel weggelassen worden wäre, hätten die Werte für den Ausgabeparameter und den Rückgabewert nicht abgerufen werden können.
• Der ODBC .NET Managed Provider verarbeitet Parameter wie der OLE DB-Provider nach der Ordinalposition (nullbasiert) und nicht nach dem Namen.
• Der ODBC .NET Managed Provider ist nicht in Visual Studio .NET enthalten, sondern muss separat heruntergeladen werden.

Weitere Informationen zu der ODBC CALL-Syntax finden Sie unter dem Thema "Procedure Calls" in der ODBC-Programmer's Reference in der MSDN-Bibliothek.