Konfigurieren von Parametern und Parameterdatentypen

Befehlsobjekte verwenden Parameter, um Werte an SQL-Anweisungen oder gespeicherte Prozeduren zu übergeben, und ermöglichen so Typüberprüfungen und Validierungen. Im Unterschied zu Befehlstext wird die Parametereingabe als Literalwert und nicht als ausführbarer Code behandelt. Dies hilft beim Schutz vor SQL Injection-Angriffen, bei denen ein Angreifer einen SQL-Befehl, der die Sicherheit auf dem Server gefährdet, in eine SQL-Anweisung einschleust.

Parametrisierte Befehle können außerdem die Leistung bei der Abfrageausführung verbessern, da sie den Datenbankserver dabei unterstützen, den eingehenden Befehl mit dem richtigen zwischengespeicherten Abfrageplan abzugleichen. Weitere Informationen finden Sie unter Zwischenspeichern und Wiederverwenden von Ausführungsplänen und Parameter und Wiederverwendung von Ausführungsplänen. Parametrisierte Befehle sind aber nicht nur aus Sicherheits- und Leistungsgründen vorteilhaft, sondern sie stellen auch eine bequeme Methode zum Organisieren von Werten dar, die an eine Datenquelle übergeben werden.

Ein DbParameter -Objekt kann mithilfe des zugehörigen Konstruktors erstellt werden, oder es wird durch Aufrufen der DbParameterCollection -Methode der Add -Auflistung zur DbParameterCollection hinzugefügt. Die Add -Methode verwendet als Eingabe je nach Datenanbieter Konstruktorargumente oder ein vorhandenes Parameterobjekt.

Bereitstellen der ParameterDirection-Eigenschaft

Beim Hinzufügen von Parametern müssen Sie für alle Parameter, die keine Eingabeparameter sind, die ParameterDirection -Eigenschaft bereitstellen. Die folgende Tabelle zeigt die ParameterDirection -Werte, die Sie mit der ParameterDirection -Enumeration verwenden können.

Verwenden von Parameterplatzhaltern

Die Syntax für Parameterplatzhalter ist abhängig von der jeweiligen Datenquelle. Die .NET Framework-Datenanbieter handhaben die Benennung und das Angeben von Parametern und Parameterplatzhaltern unterschiedlich. Diese Syntax wird an eine bestimmte Datenquelle angepasst, wie in der folgenden Tabelle beschrieben.

Angeben von Parameterdatentypen

Der Datentyp eines Parameters ist jeweils datenanbieterspezifisch. Durch das Angeben des Typs wird der Wert von Parameter in den Typ des .NET Framework-Datenanbieters konvertiert, bevor er an die Datenquelle übergeben wird. Der Typ eines Parameter kann auch in generischer Form angegeben werden, indem die DbType -Eigenschaft des Parameter -Objekts auf einen bestimmten DbTypefestgelegt wird.

Der .NET Framework-Datenanbietertyp eines Parameter-Objekts wird vom .NET Framework-Typ des Value des Parameter-Objekts oder vom DbType des Parameter-Objekts abgeleitet. Die folgende Tabelle zeigt den abgeleiteten Parameter -Typ basierend auf dem als Parameter -Wert übergebenen Objekt oder dem angegebenen DbType.

Ableiten von Parameterinformationen

Parameter können auch mit der DbCommandBuilder -Klasse aus einer gespeicherten Prozedur abgeleitet werden. Sowohl die SqlCommandBuilder -Klasse als auch die OleDbCommandBuilder -Klasse stellen die statische Methode DeriveParametersbereit, die automatisch die Parameterauflistung eines Befehlsobjekts füllt, das Parameterinformationen aus einer gespeicherten Prozedur verwendet. Beachten Sie, dass DeriveParameters alle vorhandenen Parameterinformationen für den Befehl überschreibt.

Weitere Informationen finden Sie unter Generieren von Befehlen mit CommandBuilder-Objekten.

Verwenden von Parametern mit einem SqlCommand und einer gespeicherten Prozedur

Gespeicherte Prozeduren bieten zahlreiche Vorteile in datengesteuerten Anwendungen. Mit gespeicherten Prozeduren können Datenbankoperationen in einem einzelnen Befehl zusammengefasst, für die beste 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-Objekts DbCommand eine genauere Definition der Parameter der gespeicherten Prozedur sowie den Zugriff auf Ausgabeparameter und Rückgabewerte.

Wenn Sie Parameter mit einem SqlCommand verwenden, um eine gespeicherte SQL Server-Prozedur auszuführen, müssen die der Parameters -Auflistung hinzugefügten Parameternamen mit den Namen der Parametermarkierungen in der gespeicherten Prozedur übereinstimmen. Der .NET Framework-Datenanbieter für SQL Server unterstützt keine Fragezeichenplatzhalter (?) für die Übergabe von Parametern an eine SQL-Anweisung oder gespeicherte Prozedur. Er behandelt die Parameter in der gespeicherten Prozedur als benannte Parameter und sucht nach den entsprechenden Parametermarkierungen. Nehmen wir z. B. an, die gespeicherte Prozedur CustOrderHist ist mit einem Parameter mit dem Namen @CustomerIDdefiniert. Wenn Ihr Code die gespeicherte Prozedur ausführt, muss er ebenfalls einen Parameter mit dem Namen @CustomerIDverwenden.

CREATE PROCEDURE dbo.CustOrderHist @CustomerID varchar(5)

Beispiel

Dieses Beispiel zeigt, wie Sie eine gespeicherte SQL Server-Prozedur in der Northwind -Beispieldatenbank aufrufen können. Der Name der gespeicherten Prozedur ist dbo.SalesByCategory , und die Prozedur besitzt einen Eingabeparameter mit dem Namen @CategoryName und dem Datentyp nvarchar(15). Der Code erstellt eine neue SqlConnection innerhalb eines verwendeten Blocks, sodass die Verbindung nach dem Ende der Prozedur verworfen wird. Es werden die Objekte SqlCommand und SqlParameter erstellt, und deren Eigenschaften werden festgelegt. Ein SqlDataReader führt den SqlCommand aus und gibt den Resultset aus der gespeicherten Prozedur zurück, wobei die Ausgabe im Konsolenfenster angezeigt wird.

static void GetSalesByCategory(string connectionString,
    string categoryName)
{
    using (SqlConnection connection = new(connectionString))
    {
        // Create the command and set its properties.
        SqlCommand command = new()
        {
            Connection = connection,
            CommandText = "SalesByCategory",
            CommandType = CommandType.StoredProcedure
        };

        // Add the input parameter and set its properties.
        SqlParameter parameter = new()
        {
            ParameterName = "@CategoryName",
            SqlDbType = SqlDbType.NVarChar,
            Direction = ParameterDirection.Input,
            Value = categoryName
        };

        // Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter);

        // Open the connection and execute the reader.
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Console.WriteLine("{0}: {1:C}", reader[0], reader[1]);
                }
            }
            else
            {
                Console.WriteLine("No rows found.");
            }
            reader.Close();
        }
    }
}

Shared Sub GetSalesByCategory(ByVal connectionString As String, _
    ByVal categoryName As String)

    Using connection As New SqlConnection(connectionString)

        ' Create the command and set its properties.
        Dim command As SqlCommand = New SqlCommand()
        command.Connection = connection
        command.CommandText = "SalesByCategory"
        command.CommandType = CommandType.StoredProcedure

        ' Add the input parameter and set its properties.
        Dim parameter As New SqlParameter()
        parameter.ParameterName = "@CategoryName"
        parameter.SqlDbType = SqlDbType.NVarChar
        parameter.Direction = ParameterDirection.Input
        parameter.Value = categoryName

        ' Add the parameter to the Parameters collection.
        command.Parameters.Add(parameter)

        ' Open the connection and execute the reader.
        connection.Open()
        Using reader As SqlDataReader = command.ExecuteReader()

            If reader.HasRows Then
                Do While reader.Read()
                    Console.WriteLine("{0}: {1:C}", _
                      reader(0), reader(1))
                Loop
            Else
                Console.WriteLine("No rows returned.")
            End If
        End Using
    End Using
End Sub

Verwenden von Parametern mit einem OleDbCommand oder OdbcCommand

Wenn Sie Parameter mit einem OleDbCommand oder einem OdbcCommandverwenden, muss die Reihenfolge der der Parameters -Auflistung hinzugefügten Parameter mit der Reihenfolge der in der gespeicherten Prozedur definierten Parameter übereinstimmen. Der .NET Framework-Datenanbieter für OLE DB und der .NET Framework-Datenanbieter für ODBC behandeln Parameter in einer gespeicherten Prozedur als Platzhalter und wenden Parameterwerte der Reihe nach an. Außerdem müssen der Parameters -Auflistung zuerst die Parameter für die Rückgabewerte hinzugefügt werden.

Der .NET Framework-Datenanbieter für OLE DB und der .NET Framework-Datenanbieter für ODBC unterstützen keine benannten Parameter für die Übergabe von Parametern an eine SQL-Anweisung oder gespeicherte Prozedur. In diesem Fall muss der Fragezeichenplatzhalter (?) verwenden. Dies wird im folgenden Beispiel dargestellt.

SELECT * FROM Customers WHERE CustomerID = ?

Dementsprechend muss die Reihenfolge, in der Parameter -Objekte der Parameters -Auflistung hinzugefügt werden, genau der Position des als Platzhalter für den Parameter fungierenden Fragezeichens (?) entsprechen.

OLE DB-Beispiel

Dim command As OleDbCommand = New OleDbCommand( _
  "SampleProc", connection)
command.CommandType = CommandType.StoredProcedure

Dim parameter As OleDbParameter = command.Parameters.Add( _
  "RETURN_VALUE", OleDbType.Integer)
parameter.Direction = ParameterDirection.ReturnValue

parameter = command.Parameters.Add( _
  "@InputParm", OleDbType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OleDbType.VarChar, 28)
parameter.Direction = ParameterDirection.Output

OleDbCommand command = new OleDbCommand("SampleProc", connection);
command.CommandType = CommandType.StoredProcedure;

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

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

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

Beispiel zu "Odbc"

Dim command As OdbcCommand = New OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection)
command.CommandType = CommandType.StoredProcedure

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

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12)
parameter.Value = "Sample Value"

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28)
parameter.Direction = ParameterDirection.Output

OdbcCommand command = new OdbcCommand( _
  "{ ? = CALL SampleProc(?, ?) }", connection);
command.CommandType = CommandType.StoredProcedure;

OdbcParameter parameter = command.Parameters.Add( _
  "RETURN_VALUE", OdbcType.Int);
parameter.Direction = ParameterDirection.ReturnValue;

parameter = command.Parameters.Add( _
  "@InputParm", OdbcType.VarChar, 12);
parameter.Value = "Sample Value";

parameter = command.Parameters.Add( _
  "@OutputParm", OdbcType.VarChar, 28);
parameter.Direction = ParameterDirection.Output;

Siehe auch

• Befehle und Parameter
• DataAdapter-Parameter
• Datentypzuordnungen in ADO.NET
• Übersicht über ADO.NET