Konfigurieren Sie Parameter mit Microsoft. Data.SqlClient

Mit Parametern werden Werte an einen Befehl getrennt von dessen Transact-SQL-(T-SQL-)Text übergeben. Verwenden Sie SqlParameter Parameter anstelle von Verkettungen oder Interpolationen von Werten in SQL-Anweisungen. Parameter helfen, gegen SQL-Injektionen zu schützen und den Datenbanktyp des Wertes zu erhalten.

Die Beispiele in diesem Artikel gehen davon aus, dass eine offene SqlConnection namens connection die AdventureWorks-Beispieldatenbank anspricht.

Die C#-Beispiele erfordern using Microsoft.Data.SqlClient;. Beispiele, die auch SqlDbType verwenden, erfordern using System.Data;.

Eingabeparameter hinzufügen

Füge für jeden Wert-Platzhalter im Befehlstext einen Parameter hinzu. Geben Sie den SQL Server-Typ des Parameters an und weisen Sie seinen Wert zu:

const string sql = """
    SELECT ProductID, Name, ListPrice
    FROM Production.Product
    WHERE ProductSubcategoryID = @subcategoryId
      AND ListPrice >= @minimumPrice
    ORDER BY ListPrice;
    """;

using var command = new SqlCommand(sql, connection);
command.Parameters.Add("@subcategoryId", SqlDbType.Int).Value = 2;
command.Parameters.Add("@minimumPrice", SqlDbType.Money).Value = 1_000m;

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(
        $"{reader.GetInt32(0)}: {reader.GetString(1)} ({reader.GetDecimal(2):C})");
}

Verwenden Sie für einen Parameternamen im Befehlstext und im SqlParameter dieselbe Groß- und Kleinschreibung. SQL Server gleicht Variablennamen anhand der Serversortierung ab, sodass eine Nichtübereinstimmung auf einem Server mit einer groß-/kleinschreibungssensitiven Sortierung mit dem Fehler "Die Skalarvariable muss deklariert werden" fehlschlägt.

Arbeiten mit Platzhaltern für Parameter

Microsoft. Data.SqlClient verwendet benannte Parameter in der Form @parameterName. Positionsabhängige Fragezeichen-(?)-Platzhalter werden nicht unterstützt.

Parameter-Platzhalter stehen für Werte, nicht für SQL-Identifikatoren oder Syntax. Du kannst keinen Parameter für einen Tabellennamen, Spaltennamen, Sortierrichtung oder ein ganzes SQL-Fragment verwenden. Wenn eine Anwendung einen Bezeichner dynamisch auswählt, wird dieser mit einer Allowlist überprüft und erst nach der Validierung zum Befehlstext hinzugefügt. Übergebe weiterhin alle Datenwerte als Parameter.

Für Textbefehle: Microsoft. Data.SqlClient sendet parametrisierte Anweisungen an SQL Server über sp_executesql. SQL Server kann einen Ausführungsplan wiederverwenden, wenn der Anweisungstext und die Parameterdefinitionen gleich bleiben.

Angeben von Parameterdatentypen

Legen Sie SqlDbType explizit fest, wenn die Anwendung den Ziel-SQL Server-Typ kennt. Setzen Sie außerdem typspezifische Metadaten wie Size, Precision, und Scale wenn zutreffend.

Dieses Beispiel stimmt mit den nvarchar(50)- und datetime-Spalten in Person.Person überein:

const string sql = """
    SELECT BusinessEntityID, FirstName, LastName
    FROM Person.Person
    WHERE LastName = @lastName
      AND ModifiedDate >= @modifiedSince
    ORDER BY BusinessEntityID;
    """;

using var command = new SqlCommand(sql, connection);

SqlParameter lastName = command.Parameters.Add(
    "@lastName",
    SqlDbType.NVarChar,
    50);
lastName.Value = "O'Brien";

SqlParameter modifiedSince = command.Parameters.Add(
    "@modifiedSince",
    SqlDbType.DateTime);
modifiedSince.Value = new DateTime(2013, 1, 1);

Die Übereinstimmung der Parametermetadaten mit der Zielspalte vermeidet implizite Umwandlungen und verhindert, dass Änderungen der Zeichenkettenlänge oder der Dezimalskala die Parameterdefinition verändern.

Für Dezimalwerte die Präzision und Skalierung explizit festlegen. Die Spalte Weight in Production.Product verwendet decimal(8, 2):

const string sql = """
    SELECT ProductID, Name, Weight
    FROM Production.Product
    WHERE Weight >= @minimumWeight
    ORDER BY Weight;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter minimumWeight = command.Parameters.Add(
    "@minimumWeight",
    SqlDbType.Decimal);
minimumWeight.Precision = 8;
minimumWeight.Scale = 2;
minimumWeight.Value = 10.25m;

NULL-Werte an die Datenbank übergeben

Weisen Sie Value zu, um eine Datenbank NULL zu versenden. C# null repräsentiert keinen SQL Server NULL für gewöhnliche skalare Parameter:

const string sql = """
    SELECT SalesOrderID, OrderDate, SalesPersonID
    FROM Sales.SalesOrderHeader
    WHERE (@salesPersonId IS NULL OR SalesPersonID = @salesPersonId)
    ORDER BY SalesOrderID;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter salesPersonId = command.Parameters.Add(
    "@salesPersonId",
    SqlDbType.Int);

int? selectedSalesPersonId = null;
salesPersonId.Value = selectedSalesPersonId.HasValue
    ? selectedSalesPersonId.Value
    : DBNull.Value;

Verwenden Sie abgeleitete Metadaten selektiv

Die Methode AddWithValue leitet Parametermetadaten aus dem Laufzeitwert ab. Inferenz ist praktisch, wenn die abgeleiteten Metadaten stabil sind und mit dem Datenbanktyp übereinstimmen. Es kann verschiedene Parameterdefinitionen erzeugen, wenn sich Zeichenkettenlängen, Dezimalgenauigkeit oder andere Wertmerkmale ändern.

Bevorzugen Sie Add mit einem expliziten SqlDbType für Produktionsbefehle, deren Zielschema bekannt ist. Geben Sie C# null nicht an AddWithValue; geben Sie den Typ an und weisen Sie stattdessen DBNull.Value zu.

Befehlsparameter wiederverwenden

Erstelle einen Parameter einmal und ändere nur seinen Value, wenn du denselben Befehl mit unterschiedlichen Werten ausführst:

const string sql = """
    SELECT TOP (1) Name, ListPrice
    FROM Production.Product
    WHERE ProductSubcategoryID = @subcategoryId
    ORDER BY ListPrice DESC;
    """;

using var command = new SqlCommand(sql, connection);
SqlParameter subcategoryId = command.Parameters.Add(
    "@subcategoryId",
    SqlDbType.Int);

foreach (int id in new[] { 1, 2, 3 })
{
    subcategoryId.Value = id;

    using SqlDataReader reader = command.ExecuteReader();
    if (reader.Read())
    {
        Console.WriteLine($"{id}: {reader.GetString(0)}");
    }
}

Füge bei jeder Ausführung keinen weiteren Parameter mit demselben Namen hinzu. Verwenden Sie den bestehenden Parameter erneut oder leeren Sie die Sammlung, bevor Sie sie neu aufbauen.

Parameterrichtung festlegen

Die Eigenschaft Direction identifiziert, wie ein Befehl einen Parameter verwendet. Legen Sie ihn auf einen ParameterDirection Wert fest:

Richtung Verwenden
Input Sendet einen Wert an eine Anweisung oder eine gespeicherte Prozedur. Dies ist der Standardwert.
Output Erhält einen Wert aus einem gespeicherten Prozedur-Ausgabeparameter.
InputOutput Sendet einen Anfangswert und erhält den aktualisierten Wert.
ReturnValue Erhält den ganzzahligen Wert von einer RETURNT-SQL-Anweisung.

Für Ausgabe-, Eingabe-/Ausgabe- und Rückgabe-Parameter: Lesen Value Sie nur, nachdem der Befehl abgeschlossen ist. Wenn ein Befehl Zeilen zurückgibt, schließen oder geben Sie die SqlDataReader frei, bevor Sie diese Werte lesen.

Für vollständige Beispiele für gespeicherte Prozeduren siehe Daten mit gespeicherten Prozeduren modifizieren.

Verwenden Sie Parameter mit einer gespeicherten Prozedur

Legen Sie StoredProcedure fest und verwenden Sie die Parameternamen des Verfahrens. Dieses Beispiel ruft das dbo.uspGetEmployeeManagers in AdventureWorks enthaltene Verfahren auf:

using var command = new SqlCommand(
    "dbo.uspGetEmployeeManagers",
    connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(
    "@BusinessEntityID",
    SqlDbType.Int).Value = 6;

using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(
        $"{reader.GetString(2)} {reader.GetString(3)}");
}

Der Parametername muss mit dem von der gespeicherten Prozedur deklarierten Namen übereinstimmen.

Tabellenwertige Parameter konfigurieren

Ein Tabellenwertparameter sendet mehrere Zeilen an eine gespeicherte Prozedur oder parametrisierte Anweisung. So konfigurieren Sie eines:

  1. Legen Sie SqlDbType.Structured fest.
  2. Setzen Sie TypeName auf den benutzerdefinierten Tabellentyp der Datenbank.
  3. Weise eine DataTable, DbDataReader, oder IEnumerable<SqlDataRecord> Zeilenquelle zu.

A DataTable mit definierten Spalten und ohne Zeilen sendet eine leere Tabelle. Ein leeres IEnumerable<SqlDataRecord> löst ArgumentException aus; weisen Sie C# null zu, um null Zeilen für dieses Eingabeformular zu senden. Weisen Sie DBNull.Value keinem tabellenwertigen Parameter zu.

Für Typerstellung, Zeilenabbildung und vollständige Beispiele siehe tabellenwertige Parameter.

Konfigurieren Sie Streaming-Werte

Weisen Sie Stream, XmlReader oder ValueTextReader zu, um schrittweise einen großen Wert bereitzustellen. Für die speicherbeschränkte Übertragung verwenden Sie den Parametertyp (max), xml oder json. Microsoft.Data.SqlClient puffert die Quelle vor der Ausführung für Nicht-Zeichen-(max) und Binärtypen sowie die alten großen Objekttypen.

Der Parametertyp muss mit dem Wert übereinstimmen:

Wert Kompatible SqlDbType Werte
Stream Binary, VarBinary, oder der Legacy-Typ Image
TextReader Char, NChar, NVarChar, VarChar, die Legacy-Typen Text und NText, Xml, oder Json
XmlReader Char, NChar, NVarChar, VarChar, die veralteten Text- und NText-Typen, Xml, oder Json

Microsoft. Data.SqlClient wirft ArgumentException, InvalidCastException oder SqlTypeException, wenn der Objekttyp, SqlDbType, oder die deklarierte Größe inkompatibel sind. Für vollständige Beispiele für Upload und Download siehe SqlClient Streaming Support.

Ableiten von Parameterinformationen

DeriveParametersfragt SQL Server nach den Metadaten einer gespeicherten Prozedur ab und ersetzt die bestehende Parametersammlung des Befehls:

using var command = new SqlCommand(
    "dbo.uspGetEmployeeManagers",
    connection);
command.CommandType = CommandType.StoredProcedure;

SqlCommandBuilder.DeriveParameters(command);
command.Parameters["@BusinessEntityID"].Value = 6;

DeriveParameters erfordert jedes Mal einen zusätzlichen Datenbank-Roundtrip, wenn sie aufgerufen wird. Leiten Sie während der Entwicklung bei Bedarf Metadaten ab und definieren Sie dann explizit Parameter in leistungskritischen Pfaden.

Verwandte Inhalte

  • Zuletzt aktualisiert um