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.

Membername Beschreibung
Input Der Parameter ist ein Eingabeparameter. Dies ist die Standardoption.
InputOutput Der Parameter kann sowohl für die Eingabe als auch für die Ausgabe verwendet werden.
Output Der Parameter ist ein Ausgabeparameter.
ReturnValue Der Parameter steht für einen Eingabewert aus einem Vorgang, wie z. B. einer gespeicherten Prozedur, einer integrierten Funktion oder einer benutzerdefinierten Funktion.

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.

Datenanbieter Syntax für Parameterbenennung
System.Data.SqlClient Verwendet benannte Parameter im Format @Parametername.
System.Data.OleDb Verwendet Positionsparametermarker, die durch ein Fragezeichen (?) gekennzeichnet sind.
System.Data.Odbc Verwendet Positionsparametermarker, die durch ein Fragezeichen (?) gekennzeichnet sind.
System.Data.OracleClient Verwendet benannte Parameter im Format :Parametername (oder Parametername).

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.

.NET Framework-Typ DbType SqlDbType OleDbType OdbcType OracleType
Boolean Boolean bit Boolean bit Byte
Byte Byte TinyInt UnsignedTinyInt TinyInt Byte
byte[] Binary VarBinary. Diese implizite Konvertierung kann nicht ausgeführt werden, wenn das Bytearray größer als die maximal zulässige Größe eines VarBinary (8.000 Byte) ist. Legen Sie für Bytearrays mit mehr als 8.000 Bytes explizit den SqlDbType fest. VarBinary Binary Raw
Char Das Ableiten von SqlDbType aus char wird nicht unterstützt. Char Char Byte
DateTime Datetime Datetime DBTimeStamp Datetime Datetime
DateTimeOffset DateTimeOffset "DateTimeOffset" in SQL Server 2008. Das Ableiten von SqlDbType aus DateTimeOffset wird erst ab SQL Server 2008 unterstützt. Datetime
Decimal Decimal Decimal Decimal Numeric Number
Double Double Float Double Double Double
Single Single Real Single Real Float
Guid Guid UniqueIdentifier Guid UniqueIdentifier Raw
Int16 Int16 SmallInt SmallInt SmallInt Int16
Int32 Int32 Int Int Int Int32
Int64 Int64 BigInt BigInt BigInt Number
Object Objekt Variant Variant Das Ableiten von OdbcType aus Object wird nicht unterstützt. Blob
String String NVarChar. Diese implizite Konvertierung kann nicht ausgeführt werden, wenn die Zeichenfolge die maximale NVarChar-Länge (4000 Zeichen) überschreitet. Legen Sie für Zeichenfolgen mit mehr als 4000 Zeichen explizit SqlDbTypefest. VarWChar NVarChar NVarChar
TimeSpan Time "Time" in SQL Server 2008. Das Ableiten von SqlDbType aus TimeSpan wird erst ab SQL Server 2008 unterstützt. DBTime Time Datetime
UInt16 UInt16 Das Ableiten von SqlDbType aus UInt16 wird nicht unterstützt. UnsignedSmallInt Int UInt16
UInt32 UInt32 Das Ableiten von SqlDbType aus UInt32 wird nicht unterstützt. UnsignedInt BigInt UInt32
UInt64 UInt64 Das Ableiten von SqlDbType aus UInt64 wird nicht unterstützt. UnsignedBigInt Numeric Number
AnsiString VarChar VarChar VarChar VarChar
AnsiStringFixedLength Char Char Char Char
Währung Money Währung Das Ableiten von OdbcType aus Currency wird nicht unterstützt. Number
Date "Date" in SQL Server 2008. Das Ableiten von SqlDbType aus Date wird erst ab SQL Server 2008 unterstützt. DBDate Date Datetime
SByte Das Ableiten von SqlDbType aus SByte wird nicht unterstützt. TinyInt Das Ableiten von OdbcType aus SByte wird nicht unterstützt. SByte
StringFixedLength NChar WChar NChar NChar
Time "Time" in SQL Server 2008. Das Ableiten von SqlDbType aus Time wird erst ab SQL Server 2008 unterstützt. DBTime Time Datetime
VarNumeric Das Ableiten von SqlDbType aus VarNumeric wird nicht unterstützt. VarNumeric Das Ableiten von OdbcType aus VarNumeric wird nicht unterstützt. Number
Benutzerdefinierter Typ (ein Objekt mit SqlUserDefinedAggregateAttribute) Object oder String, je nach Anbieter (SqlClient gibt immer ein Objekt zurück, ODBC immer eine Zeichenfolge, und der verwaltete OLE DB-Datenanbieter ist in der Lage, beide zu erkennen). SqlDbType.Udt, wenn SqlUserDefinedTypeAttribute vorhanden ist; andernfalls Variant OleDbType.VarWChar (wenn der Wert NULL ist); andernfalls OleDbType.Variant. OdbcType.NVarChar Nicht unterstützt

Hinweis

Beim Konvertieren von "decimal" in einen anderen Typ erhalten Sie nur eine annähernde Entsprechung, da der Wert auf die nächste Ganzzahl abgerundet wird. Wenn das Ergebnis der Konvertierung im Zieltyp nicht darstellbar ist, wird eine OverflowException ausgelöst.

Hinweis

Wenn Sie einen NULL-Parameterwert an den Server senden, müssen Sie DBNull und nicht null (in Visual Basic Nothing)+++ angeben. Der NULL-Wert im System ist ein leeres Objekt, das über keinen Wert verfügt. DBNull wird zur Darstellung von NULL-Werten verwendet. Weitere Informationen zu NULL-Werten bei Datenbanken finden Sie unter Handling Null Values.

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.

Hinweis

Das Ableiten von Parameterinformationen geht mit einem Leistungsverlust einher, weil zum Abrufen der Informationen ein zusätzlicher Roundtrip durch die Datenquelle erforderlich ist. Wenn die Parameterinformationen zur Entwurfszeit bekannt sind, können Sie die Leistung der Anwendung verbessern, indem Sie die Parameter explizit festlegen.

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.

Hinweis

Parametrisierte Anweisungen werden auf dem Server mit sp_executesql, ausgeführt, sodass die Wiederverwendung von Abfrageplänen möglich ist. Lokale Cursor oder Variablen im sp_executesql -Batch sind für den Batch, der sp_executesqlaufruft, nicht sichtbar. Änderungen am Datenbankkontext sind nur bis zum Ende der sp_executesql -Anweisung gültig. Weitere Informationen finden Sie unter sp_executesql (Transact-SQL).

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.

Hinweis

Statt die Objekte SqlCommand und SqlParameter zu erstellen und dann die Eigenschaften in separaten Anweisungen festzulegen, können Sie auch mit einem der überladenen Konstruktoren mehrere Eigenschaften in einer einzigen Anweisung festlegen.

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