Konfigurieren von Parametern und Parameterdatentypen

Artikel
05/09/2023

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 mithilfe sp_executesqlvon Wiederverwendungsabfrageplänen ausgeführt. 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;

Freigeben über

Konfigurieren von Parametern und Parameterdatentypen

Bereitstellen der ParameterDirection-Eigenschaft

Verwenden von Parameterplatzhaltern

Angeben von Parameterdatentypen

Ableiten von Parameterinformationen

Verwenden von Parametern mit einem SqlCommand und einer gespeicherten Prozedur

Beispiel

Verwenden von Parametern mit einem OleDbCommand oder OdbcCommand

OLE DB-Beispiel

Beispiel zu "Odbc"

Siehe auch

Zusätzliche Ressourcen