Verwenden von gespeicherten Prozeduren mit einem Befehl

Gespeicherte Prozeduren bieten zahlreiche Vorteile in datengesteuerten Anwendungen. Wenn Sie gespeicherte Prozeduren verwenden, können Datenbankoperationen in einem einzelnen Befehl zusammengefasst, für eine überzeugende Leistung optimiert und mit zusätzlicher Sicherheit ausgestattet werden. Während eine gespeicherte Prozedur problemlos aufgerufen werden kann, indem der Name der gespeicherten Prozedur gefolgt von Parameterargumenten als SQL-Anweisung übergeben wird, ermöglicht die Verwendung der Parameters-Auflistung des ADO.NET-Command-Objekts eine genauere Definition der Parameter der gespeicherten Prozedur sowie den Zugriff auf Ausgabeparameter und Rückgabewerte.

Zum Aufrufen einer gespeicherten Prozedur legen Sie für den CommandType des Command-Objekts den Wert StoredProcedure fest. Sobald Sie für den CommandType den Wert StoredProcedure festgelegt haben, können Sie die Parameters-Auflistung zur Definition von Parametern verwenden, wie im folgenden Beispiel gezeigt.

Hinweis   Wenn Sie eine gespeicherte Prozedur ausführen, müssen Sie für das OdbcCommand die vollständige ODBC CALL-Syntax angeben.

SqlClient

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;" & _
                                                   "Initial Catalog=northwind")

Dim salesCMD As SqlCommand = New SqlCommand("SalesByCategory", nwindConn)
salesCMD.CommandType = CommandType.StoredProcedure

Dim myParm As SqlParameter = salesCMD.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15)
myParm.Value = "Beverages"

nwindConn.Open()

Dim myReader As SqlDataReader = salesCMD.ExecuteReader()

Console.WriteLine("{0}, {1}", myReader.GetName(0), myReader.GetName(1))

Do While myReader.Read()
  Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1))
Loop

myReader.Close()
nwindConn.Close()
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

SqlCommand salesCMD = new SqlCommand("SalesByCategory", nwindConn);
salesCMD.CommandType = CommandType.StoredProcedure;

SqlParameter myParm = salesCMD.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15);
myParm.Value = "Beverages";

nwindConn.Open();

SqlDataReader myReader = salesCMD.ExecuteReader();

Console.WriteLine("{0}, {1}", myReader.GetName(0), myReader.GetName(1));

while (myReader.Read())
{
  Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1));
}

myReader.Close();
nwindConn.Close();

OleDb

Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;" & _
                                                       "Initial Catalog=northwind")

Dim salesCMD As OleDbCommand = New OleDbCommand("SalesByCategory", nwindConn)
salesCMD.CommandType = CommandType.StoredProcedure

Dim myParm As OleDbParameter = salesCMD.Parameters.Add("@CategoryName", OleDbType.VarChar, 15)
myParm.Value = "Beverages"

nwindConn.Open()

Dim myReader As OleDbDataReader = salesCMD.ExecuteReader()

Console.WriteLine("{0}, {1}", myReader.GetName(0), myReader.GetName(1))

Do While myReader.Read()
  Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1))
Loop

myReader.Close()
nwindConn.Close()
[C#]
OleDbConnection nwindConn = new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;" +
                                                "Initial Catalog=northwind");

OleDbCommand salesCMD = new OleDbCommand("SalesByCategory", nwindConn);
salesCMD.CommandType = CommandType.StoredProcedure;

OleDbParameter myParm = salesCMD.Parameters.Add("@CategoryName", OleDbType.VarChar, 15);
myParm.Value = "Beverages";

nwindConn.Open();

OleDbDataReader myReader = salesCMD.ExecuteReader();

Console.WriteLine("\t{0}, {1}", myReader.GetName(0), myReader.GetName(1));

while (myReader.Read())
{
  Console.WriteLine("\t{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1));
}

myReader.Close();
nwindConn.Close();

Odbc

Dim nwindConn As OdbcConnection = New OdbcConnection("Driver={SQL Server};Server=localhost;Trusted_Connection=yes;" & _
                                                       "Database=northwind")
nwindConn.Open()

Dim salesCMD As OdbcCommand = New OdbcCommand("{ CALL SalesByCategory(?) }", nwindConn)
salesCMD.CommandType = CommandType.StoredProcedure

Dim myParm As OdbcParameter = salesCMD.Parameters.Add("@CategoryName", OdbcType.VarChar, 15)
myParm.Value = "Beverages"

Dim myReader As OdbcDataReader = salesCMD.ExecuteReader()

Console.WriteLine("{0}, {1}", myReader.GetName(0), myReader.GetName(1))

Do While myReader.Read()
  Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1))
Loop

myReader.Close()
nwindConn.Close()
[C#]
OdbcConnection nwindConn = new OdbcConnection("Driver={SQL Server};Server=localhost;Trusted_Connection=yes;" +
                                              "Database=northwind");
nwindConn.Open();

OdbcCommand salesCMD = new OdbcCommand("{ CALL SalesByCategory(?) }", nwindConn);
salesCMD.CommandType = CommandType.StoredProcedure;

OdbcParameter myParm = salesCMD.Parameters.Add("@CategoryName", OdbcType.VarChar, 15);
myParm.Value = "Beverages";

OdbcDataReader myReader = salesCMD.ExecuteReader();

Console.WriteLine("\t{0}, {1}", myReader.GetName(0), myReader.GetName(1));

while (myReader.Read())
{
  Console.WriteLine("\t{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1));
}

myReader.Close();
nwindConn.Close();

Ein Parameter-Objekt kann mit Hilfe des Parameter-Konstruktors oder durch Aufrufen der Add-Methode der Parameters-Auflistung eines Command-Objekts erstellt werden. Parameters.Add verwendet entweder Konstruktorargumente oder ein vorhandenes Parameter-Objekt als Eingabe. Wenn Sie für den Value eines Parameter-Objekts einen Nullverweis angeben, verwenden Sie DBNull.Value.

Für andere Parameter als Input-Parameter müssen Sie einen Wert für die ParameterDirection-Eigenschaft angeben, um den Parametertyp festzulegen: InputOutput, Output oder ReturnValue. Das folgende Beispiel veranschaulicht den Unterschied zwischen den Parametern Input, Output und ReturnValue.

SqlClient

Dim sampleCMD As SqlCommand = New SqlCommand("SampleProc", nwindConn)
sampleCMD.CommandType = CommandType.StoredProcedure

Dim sampParm As SqlParameter = sampleCMD.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
sampParm.Direction = ParameterDirection.ReturnValue

sampParm = sampleCMD.Parameters.Add("@InputParm", SqlDbType.NVarChar, 12)
sampParm.Value = "Sample Value"

sampParm = sampleCMD.Parameters.Add("@OutputParm", SqlDbType.NVarChar, 28)
sampParm.Direction = ParameterDirection.Output

nwindConn.Open()

Dim sampReader As SqlDataReader = sampleCMD.ExecuteReader()

Console.WriteLine("{0}, {1}", sampReader.GetName(0), sampReader.GetName(1))

Do While sampReader.Read()
  Console.WriteLine("{0}, {1}", sampReader.GetInt32(0), sampReader.GetString(1))
Loop

sampReader.Close()
nwindConn.Close()

Console.WriteLine(" @OutputParm: {0}", sampleCMD.Parameters("@OutputParm").Value)
Console.WriteLine("RETURN_VALUE: {0}", sampleCMD.Parameters("RETURN_VALUE").Value)
[C#]
SqlCommand sampleCMD = new SqlCommand("SampleProc", nwindConn);
sampleCMD.CommandType = CommandType.StoredProcedure;

SqlParameter sampParm = sampleCMD.Parameters.Add("RETURN_VALUE", SqlDbType.Int);
sampParm.Direction = ParameterDirection.ReturnValue;

sampParm = sampleCMD.Parameters.Add("@InputParm", SqlDbType.NVarChar, 12);
sampParm.Value = "Sample Value";

sampParm = sampleCMD.Parameters.Add("@OutputParm", SqlDbType.NVarChar, 28);
sampParm.Direction = ParameterDirection.Output;

nwindConn.Open();

SqlDataReader sampReader = sampleCMD.ExecuteReader();

Console.WriteLine("{0}, {1}", sampReader.GetName(0), sampReader.GetName(1));

while (sampReader.Read())
{
  Console.WriteLine("{0}, {1}", sampReader.GetInt32(0), sampReader.GetString(1));
}

sampReader.Close();
nwindConn.Close();

Console.WriteLine(" @OutputParm: {0}", sampleCMD.Parameters["@OutputParm"].Value);
Console.WriteLine("RETURN_VALUE: {0}", sampleCMD.Parameters["RETURN_VALUE"].Value);

OleDb

Dim sampleCMD As OleDbCommand = New OleDbCommand("SampleProc", nwindConn)
sampleCMD.CommandType = CommandType.StoredProcedure

Dim sampParm As OleDbParameter = sampleCMD.Parameters.Add("RETURN_VALUE", OleDbType.Integer)
sampParm.Direction = ParameterDirection.ReturnValue

sampParm = sampleCMD.Parameters.Add("@InputParm", OleDbType.VarChar, 12)
sampParm.Value = "Sample Value"

sampParm = sampleCMD.Parameters.Add("@OutputParm", OleDbType.VarChar, 28)
sampParm.Direction = ParameterDirection.Output

nwindConn.Open()

Dim sampReader As OleDbDataReader = sampleCMD.ExecuteReader()

Console.WriteLine("{0}, {1}", sampReader.GetName(0), sampReader.GetName(1))

Do While sampReader.Read()
  Console.WriteLine("{0}, {1}", sampReader.GetInt32(0), sampReader.GetString(1))
Loop

sampReader.Close()
nwindConn.Close()

Console.WriteLine(" @OutputParm: {0}", sampleCMD.Parameters("@OutputParm").Value)
Console.WriteLine("RETURN_VALUE: {0}", sampleCMD.Parameters("RETURN_VALUE").Value)
[C#]
OleDbCommand sampleCMD = new OleDbCommand("SampleProc", nwindConn);
sampleCMD.CommandType = CommandType.StoredProcedure;

OleDbParameter sampParm = sampleCMD.Parameters.Add("RETURN_VALUE", OleDbType.Integer);
sampParm.Direction = ParameterDirection.ReturnValue;

sampParm = sampleCMD.Parameters.Add("@InputParm", OleDbType.VarChar, 12);
sampParm.Value = "Sample Value";

sampParm = sampleCMD.Parameters.Add("@OutputParm", OleDbType.VarChar, 28);
sampParm.Direction = ParameterDirection.Output;

nwindConn.Open();

OleDbDataReader sampReader = sampleCMD.ExecuteReader();

Console.WriteLine("{0}, {1}", sampReader.GetName(0), sampReader.GetName(1));

while (sampReader.Read())
{
  Console.WriteLine("{0}, {1}", sampReader.GetInt32(0), sampReader.GetString(1));
}

sampReader.Close();
nwindConn.Close();

Console.WriteLine(" @OutputParm: {0}", sampleCMD.Parameters["@OutputParm"].Value);
Console.WriteLine("RETURN_VALUE: {0}", sampleCMD.Parameters["RETURN_VALUE"].Value);

Odbc

Dim sampleCMD As OdbcCommand = New OdbcCommand("{ ? = CALL SampleProc(?, ?) }", nwindConn)
sampleCMD.CommandType = CommandType.StoredProcedure

Dim sampParm As OdbcParameter = sampleCMD.Parameters.Add("RETURN_VALUE", OdbcType.Int)
sampParm.Direction = ParameterDirection.ReturnValue

sampParm = sampleCMD.Parameters.Add("@InputParm", OdbcType.VarChar, 12)
sampParm.Value = "Sample Value"

sampParm = sampleCMD.Parameters.Add("@OutputParm", OdbcType.VarChar, 28)
sampParm.Direction = ParameterDirection.Output

nwindConn.Open()

Dim sampReader As OdbcDataReader = sampleCMD.ExecuteReader()

Console.WriteLine("{0}, {1}", sampReader.GetName(0), sampReader.GetName(1))

Do While sampReader.Read()
  Console.WriteLine("{0}, {1}", sampReader.GetInt32(0), sampReader.GetString(1))
Loop

sampReader.Close()
nwindConn.Close()

Console.WriteLine(" @OutputParm: {0}", sampleCMD.Parameters("@OutputParm").Value)
Console.WriteLine("RETURN_VALUE: {0}", sampleCMD.Parameters("RETURN_VALUE").Value)
[C#]
OdbcCommand sampleCMD = new OdbcCommand("{ ? = CALL SampleProc(?, ?) }", nwindConn);
sampleCMD.CommandType = CommandType.StoredProcedure;

OdbcParameter sampParm = sampleCMD.Parameters.Add("RETURN_VALUE", OdbcType.Int);
sampParm.Direction = ParameterDirection.ReturnValue;

sampParm = sampleCMD.Parameters.Add("@InputParm", OdbcType.VarChar, 12);
sampParm.Value = "Sample Value";

sampParm = sampleCMD.Parameters.Add("@OutputParm", OdbcType.VarChar, 28);
sampParm.Direction = ParameterDirection.Output;

nwindConn.Open();

OdbcDataReader sampReader = sampleCMD.ExecuteReader();

Console.WriteLine("{0}, {1}", sampReader.GetName(0), sampReader.GetName(1));

while (sampReader.Read())
{
  Console.WriteLine("{0}, {1}", sampReader.GetInt32(0), sampReader.GetString(1));
}

sampReader.Close();
nwindConn.Close();

Console.WriteLine(" @OutputParm: {0}", sampleCMD.Parameters["@OutputParm"].Value);
Console.WriteLine("RETURN_VALUE: {0}", sampleCMD.Parameters["RETURN_VALUE"].Value);

Verwenden von Parametern mit einem SqlCommand

Wenn Sie Parameter mit einem SqlCommand verwenden, müssen die Namen der Parameter, die der Parameters-Auflistung hinzugefügt werden, mit den Namen der Parametermarkierungen in der gespeicherten Prozedur übereinstimmen. Der .NET Framework-Datenprovider für SQL Server behandelt die Parameter in der gespeicherten Prozedur als benannte Parameter und sucht nach den entsprechenden Parametermarkierungen.

Der .NET Framework-Datenprovider für SQL Server unterstützt keine Fragezeichenplatzhalter (?) für die Übergabe von Parametern an eine SQL-Anweisung oder gespeicherte Prozedur. In diesem Fall müssen Sie benannte Parameter verwenden, wie im folgenden Beispiel gezeigt.

SELECT * FROM Customers WHERE CustomerID = @CustomerID

Verwenden von Parametern mit einem OleDbCommand oder OdbcCommand

Wenn Sie Parameter mit einem OleDbCommand oder OdbcCommand verwenden, muss die Reihenfolge der der Parameters-Auflistung hinzugefügten Parameter mit der Reihenfolge der in der gespeicherten Prozedur definierten Parameter übereinstimmen. Der .NET Framework-Datenprovider für OLE DB und der .NET Framework-Datenprovider für ODBC behandeln Parameter in einer gespeicherten Prozedur als Platzhalter und wenden Parameterwerte der Reihe nach an. Darüber hinaus sind müssen Rückgabewertparameter als erste Parameter an die Parameters-Auflistung zurückgegeben werden.

Der .NET Framework-Datenprovider für OLE DB und der .NET Framework-Datenprovider für ODBC unterstützen keine benannten Parameter für die Übergabe von Parametern an eine SQL-Anweisung oder gespeicherte Prozedur. In diesem Fall müssen Sie den Fragezeichenplatzhalter (?) verwenden, wie im folgenden Beispiel gezeigt.

SELECT * FROM Customers WHERE CustomerID = ?

Dementsprechend muss die Reihenfolge, in der Parameter-Objekte der Parameters-Auflistung hinzugefügt werden, direkt der Position des Fragezeichenplatzhalters für den Parameter entsprechen.

Ableiten von Parameterinformationen

Parameter können auch mit der CommandBuilder-Klasse aus einer gespeicherten Prozedur abgeleitet werden. Sowohl die SqlCommandBuilder-Klasse als auch die OleDbCommandBuilder-Klasse liefert eine statische Methode, DeriveParameters, die die Parameters-Auflistung eines Command-Objekts automatisch mit den Parameterinformationen aus einer gespeicherten Prozedur füllt. Beachten Sie, dass die DeriveParameters-Methode alle vorhandenen Parameterinformationen für das Command-Objekt überschreibt.

Beim Ableiten von Parameterinformationen ist zusätzlicher Zugriff auf die Datenquelle erforderlich. Wenn die Parameterinformationen zur Entwurfszeit bekannt sind, können Sie die Leistung der Anwendung verbessern, indem Sie die Parameter explizit festlegen.

Im folgenden Codebeispiel sehen Sie, wie Sie die Parameters-Auflistung eines Command-Objekts mit der CommandBuilder.DeriveParameters-Methode füllen.

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;")
Dim salesCMD As SqlCommand = New SqlCommand("Sales By Year", nwindConn)
salesCMD.CommandType = CommandType.StoredProcedure

nwindConn.Open()
SqlCommandBuilder.DeriveParameters(salesCMD)
nwindConn.Close()
[C#]
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;");
SqlCommand salesCMD = new SqlCommand("Sales By Year", nwindConn);
salesCMD.CommandType = CommandType.StoredProcedure;

nwindConn.Open();
SqlCommandBuilder.DeriveParameters(salesCMD);
nwindConn.Close();

Siehe auch

Datenzugriff mit .NET Framework-Datenprovidern | OleDbCommand-Klasse | OleDbConnection-Klasse | OleDbDataReader-Klasse | OleDbParameterCollection-Klasse | OdbcCommand-Klasse | OdbcConnection-Klasse | OdbcDataReader-Klasse | OdbcParameterCollection-Klasse | SqlCommand-Klasse | SqlConnection-Klasse | SqlDataReader-Klasse | SqlParameterCollection-Klasse