Konfigurera parametrar med Microsoft. Data.SqlClient

Parametrar skickar värden till ett kommando separat från dess Transact-SQL (T-SQL) text. Använd SqlParameter objekt istället för att sammanfoga eller interpolera värden i SQL-satser. Parametrar hjälper till att skydda mot SQL-injektion och bevarar värdets databastyp.

Exemplen i denna artikel förutsätter att en öppen SqlConnection med namnet connection riktar sig mot AdventureWorks exempeldatabas.

C#-exemplen kräver using Microsoft.Data.SqlClient;. Exempel som använder SqlDbType kräver också using System.Data;.

Lägg till indataparametrar

Lägg till en parameter för varje platshållare för värde i kommandotexten. Ange parameterns SQL Server-typ och tilldela dess värde:

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

Använd samma versalisering för ett parameternamn i kommandotexten och i SqlParameter. SQL Server matchar variabelnamn genom att använda serverns sorteringsordning, så vid felaktig versalisering uppstår felet "Måste deklarera skalärvariabeln" på en server med en skiftlägeskänslig sorteringsordning.

Arbeta med parameterplatshållare

Microsoft.Data.SqlClient använder namngivna parametrar i formen @parameterName. Platshållare för positionsfrågetecken (?) stöds inte.

Parameterplatshållare representerar värden, inte SQL-identifierare eller syntax. Du kan inte använda en parameter för ett tabellnamn, kolumnnamn, sorteringsriktning eller ett helt SQL-fragment. Om en applikation väljer en identifierare dynamiskt, validera den mot en tillåtslista och lägg till den i kommandotexten först efter validering. Fortsätt att skicka alla datavärden som parametrar.

För textkommandon skickar Microsoft. Data.SqlClient parametriserade satser till SQL Server via sp_executesql. SQL Server kan återanvända en exekveringsplan när satstexten och parameterdefinitionerna förblir desamma.

Ange parameterdatatyper

Ange SqlDbType uttryckligen när applikationen känner till måltypen för SQL Server. Sätt även typspecifik metadata såsom Size, Precision, och Scale när det är tillämpligt.

Detta exempel matchar kolumnerna nvarchar(50) och datetime i 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);

Att matcha parametermetadata med målkolumnen undviker implicita konverteringar och förhindrar förändringar i stränglängd eller decimalskala från att ändra parameterdefinitionen.

För decimalvärden, ställ in precision och skala explicit. Kolumnen Weight i Production.Product använder 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;

Skicka databasens NULL-värden

Tilldela Value för att skicka en databas NULL. C# null representerar inte en SQL Server NULL för vanliga skalära parametrar:

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;

Använd härledd metadata selektivt

AddWithValue-metoden härleder parametermetadata från runtime-värdet. Härledning är bekvämt när den härledda metadatan är stabil och matchar databastypen. Den kan ge olika parameterdefinitioner när stränglängder, decimalprecision eller andra värdeegenskaper ändras.

Föredra Add med en explicit SqlDbType för produktionskommandon vars målschema är känd. Skicka inte C# null till AddWithValue; specificera typen och tilldela DBNull.Value istället.

Återanvänd kommandoparametrar

Skapa en parameter en gång och ändra bara dess Value när du kör samma kommando med olika värden:

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

Lägg inte till en annan parameter med samma namn vid varje exekvering. Återanvänd den befintliga parametern eller rensa samlingen innan du bygger upp den igen.

Ange parameterns riktning

Egenskapen Direction identifierar hur ett kommando använder en parameter. Ange det till ett ParameterDirection värde:

Direction Använd
Input Skickar ett värde till en instruktion eller en lagrad procedur. Det här värdet är standardvärdet.
Output Tar emot ett värde från en utdataparameter för en lagrad procedur.
InputOutput Skickar ett initialt värde och tar emot det uppdaterade värdet.
ReturnValue Tar emot heltalsvärdet från en T-SQL-RETURNsats.

För output-, input/output- och return-värde-parametrar, läs Value endast efter att kommandot är klart. Om ett kommando returnerar rader, stäng eller kassera SqlDataReader innan du läser dessa värden.

För kompletta exempel på lagrade procedurer, se Modifiera data med lagrade procedurer.

Använd parametrar med en lagrad procedur

Sätt StoredProcedure och använd procedurens parameternamn. Detta exempel anropar proceduren dbo.uspGetEmployeeManagers som ingår i AdventureWorks:

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

Parameternamnet måste matcha det namn som deklarerats av den lagrade proceduren.

Konfigurera tabellvärdesparametrar

En tabellvärdesparameter skickar flera rader till en lagrad procedur eller parameteriserad instruktion. Konfigurera en:

  1. Ange SqlDbType.Structured.
  2. Ange TypeName som databasens användardefinierade tabelltyp.
  3. Tilldela en DataTable, DbDataReader, eller IEnumerable<SqlDataRecord> radkälla.

En tabell DataTable med definierade kolumner och inga rader skickar en tom tabell. Ett tomt IEnumerable<SqlDataRecord> kastar ArgumentException; ange C# null för att skicka noll rader för det indataformuläret. Tilldela inte DBNull.Value till en tabellvärdesparameter.

För typskapande, radmappning och kompletta exempel, se tabellvärdesparametrar.

Konfigurera strömningsvärden

Tilldela ett Stream, TextReader eller XmlReader till Value för att stegvis öka värdet mycket. För överföring med begränsat minne, använd en (max), xml- eller json-parametertyp. Microsoft.Data.SqlClient buffrar källdata före exekvering för icke-tecken(max)- och binärtyper samt de äldre stora objekttyperna.

Parametertypen måste matcha värdet:

Value Kompatibla SqlDbType värden
Stream Binary, VarBinary, eller den äldre Image typen
TextReader Char, Text, NText, Xml, de äldre VarChar- och NVarChar-typerna, NChar, eller Json
XmlReader Char, Text, NText, Xml, de äldre VarChar- och NVarChar-typerna, NChar, eller Json

Microsoft.Data.SqlClient kastar ArgumentException, InvalidCastException eller SqlTypeException när objekttyp, SqlDbType eller deklarerad storlek är inkompatibel. För fullständiga exempel på uppladdning och nedladdning, se stöd för SqlClient streaming.

Härled parameterinformation

DeriveParametersfrågar SQL Server efter en lagrad procedurs parametermetadata och ersätter kommandots befintliga parametersamling:

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

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

DeriveParameters kräver en extra tur och retur till databasen varje gång den anropas. Härled metadata under utvecklingen när det behövs, och definiera sedan parametrar explicit i prestandakänsliga kodvägar.

Relaterat innehåll

  • Uppdaterades senast