Configureer parameters met Microsoft.Data.SqlClient

Parameters geven waarden door aan een opdracht, los van de Transact-SQL (T-SQL)-tekst. Gebruik SqlParameter objecten in plaats van waarden te concatenëren of te interpoleren in SQL-instructies. Parameters helpen om SQL-injectie te voorkomen en behouden het databasetype van de waarde.

De voorbeelden in dit artikel gaan ervan uit dat een open SqlConnection met de naam connection is gericht op de AdventureWorks-voorbeelddatabase.

De C#-voorbeelden vereisen using Microsoft.Data.SqlClient;. Voorbeelden die ook SqlDbType gebruiken, vereisen using System.Data;.

Voeg invoerparameters toe

Voeg voor elke waarde-placeholder een parameter toe in de commandotekst. Specificeer het SQL Server-type van de parameter en wijs de waarde toe:

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})");
}

Gebruik hetzelfde hoofdlettergebruik voor een parameternaam in de commandotekst en in de SqlParameter. SQL Server komt overeen met variabelenamen door gebruik te maken van de servercollatie, waardoor een mismatch faalt met een foutmelding "Must declare the scalar variable" op een server met een hoofdlettergevoelige collatie.

Werken met plaatsaanduidingen voor parameters

Microsoft. Data.SqlClient gebruikt benoemde parameters in de vorm @parameterName. Plaatshoudervraagtekens (?) worden niet ondersteund.

Parameter-plaatshouders vertegenwoordigen waarden, geen SQL-identifiers of syntaxis. Je kunt geen parameter gebruiken voor een tabelnaam, kolomnaam, sorteerrichting of een heel SQL-fragment. Als een applicatie een identifier dynamisch selecteert, valideer deze dan aan een toestaanlijst en voeg deze pas na validatie toe aan de commandotekst. Blijf alle datawaarden als parameters doorgeven.

Voor tekstcommando's stuurt Microsoft.Data.SqlClient geparametriseerde statements naar SQL Server via sp_executesql. SQL Server kan een uitvoeringsschema hergebruiken wanneer de instructietekst en parameterdefinities hetzelfde blijven.

Parametergegevenstypen opgeven

Stel SqlDbType expliciet in wanneer de applicatie het doeltype SQL Server kent. Stel ook type-specifieke metadata in zoals Size, Precision, en Scale indien van toepassing.

Dit voorbeeld komt overeen met de nvarchar(50)- en datetime-kolommen in Person.Person:

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);

Het afstemmen van de parametermetadata met de doelkolom voorkomt impliciete conversies en voorkomt dat veranderingen in stringlengte of decimale schaal de parameterdefinitie veranderen.

Voor decimale waarden stel je de precisie en schaal expliciet in. De Weight kolom in Production.Product gebruikt 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;

Geef database-NULL-waarden door

Wijs Value toe om een database NULL te verzenden. C# null vertegenwoordigt geen SQL Server NULL voor gewone scalaire parameters:

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;

Gebruik afgeleide metadata selectief

De AddWithValue methode leidt parametermetadata af uit de runtime-waarde. Inferentie is handig wanneer de afgeleide metadata stabiel is en overeenkomt met het databasetype. Het kan verschillende parameterdefinities opleveren wanneer tekenreekslengtes, decimale precisie of andere waardekenmerken veranderen.

Geef de voorkeur aan Add met een expliciet SqlDbType voor productiecommando's waarvan het doelschema bekend is. Geef C# null niet door aan AddWithValue; geef het type aan en wijs DBNull.Value toe.

Opdrachtparameters hergebruiken

Maak één parameter aan en verander alleen de Value ervan wanneer je hetzelfde commando met verschillende waarden uitvoert:

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)}");
    }
}

Voeg niet nog een parameter met dezelfde naam toe bij elke uitvoering. Hergebruik de bestaande parameter of maak de collectie schoon voordat je hem opnieuw opbouwt.

Parameterrichting instellen

De Direction eigenschap identificeert hoe een commando een parameter gebruikt. Stel deze in op een ParameterDirection waarde:

Voor output-, input/output- en return-value parameters, lees Value alleen nadat het commando is voltooid. Als een commando rijen teruggeeft, sluit of geef de SqlDataReader vrij voordat je die waarden leest.

Voor volledige voorbeelden van opgeslagen procedures, zie Gegevens wijzigen met opgeslagen procedures.

Gebruik parameters met een stored procedure

Stel StoredProcedure in en gebruik de parameternamen van de procedure. Dit voorbeeld roept de in AdventureWorks opgenomen dbo.uspGetEmployeeManagers-procedure aan:

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)}");
}

De parameternaam moet overeenkomen met de naam die door de opgeslagen procedure is opgegeven.

Tabelwaardeparameters configureren

Een parameter met tabelwaarde stuurt meerdere rijen naar een stored procedure of geparametriseerde statement. Om er één te configureren:

1. Stel SqlDbType.Structuredin.
2. Stel TypeName in op het door de gebruiker gedefinieerde tabeltype in de database.
3. Wijs een DataTable, DbDataReader, of IEnumerable<SqlDataRecord> rijbron toe.

A DataTable met gedefinieerde kolommen en geen rijen stuurt een lege tabel. Een leeg IEnumerable<SqlDataRecord> genereert ArgumentException; wijs C# null toe om nul rijen te sturen voor die invoer. Wijs DBNull.Value niet toe aan een tabelparameter.

Voor typecreatie, rijmapping en volledige voorbeelden, zie Table-valued parameters.

Streamingwaarden configureren

Wijs een Stream, TextReader, of XmlReader aan Value toe om een grote waarde stapsgewijs te verhogen. Voor overdracht met beperkt geheugenverbruik gebruik je een (max), xml- of json-parametertype. Microsoft. Data.SqlClient buffert de bron vóór uitvoering voor niet-(max)teken- en binaire typen en de legacy grote objecttypen.

Het parametertype moet overeenkomen met de waarde:

Microsoft.Data.SqlClient gooit ArgumentException, InvalidCastException of SqlTypeException wanneer het objecttype, SqlDbType of de gedeclareerde grootte incompatibel is. Voor volledige voorbeelden van uploaden en downloaden, zie SqlClient streaming support.

Parametergegevens afleiden

DeriveParametersvraagt SQL Server op naar de parametermetadata van een opgeslagen procedure en vervangt de bestaande parametercollectie van het commando:

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

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

DeriveParameters vereist elke keer dat het wordt aangeroepen een extra roundtrip naar de database. Leid metadata tijdens de ontwikkeling af wanneer nodig, en definieer vervolgens parameters expliciet in prestatiegevoelige paden.

Verwante onderwerpen

• Voer een commando uit
• Gegevens wijzigen met opgeslagen procedures
• Parameters met tabelwaarde
• Streamingondersteuning voor SqlClient