Configura i parametri con Microsoft. Data.SqlClient

I parametri inviano valori a un comando separatamente dal suo testo Transact-SQL (T-SQL). Usa oggetti SqlParameter invece di concatenare o interpolare valori nelle istruzioni SQL. I parametri aiutano a proteggere contro l'iniezione SQL e a preservare il tipo di database del valore.

Gli esempi in questo articolo presuppongono che un oggetto connection aperto denominato SqlConnection faccia riferimento al database di esempio AdventureWorks.

Gli esempi di C# richiedono using Microsoft.Data.SqlClient;. Esempi che utilizzano SqlDbType richiedono anche using System.Data;.

Aggiungi parametri di input

Aggiungi un parametro per ogni segnaposto di valore nel testo del comando. Specifica il tipo di SQL Server del parametro e assegna il suo valore:

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

Usa la stessa combinazione di maiuscole e minuscole per il nome di un parametro nel testo del comando e nel SqlParameter. SQL Server abbina i nomi delle variabili usando la collation del server, quindi una discrepanza fallisce con un errore "Must declare the scalar variable" su un server con una collation con distinzione tra maiuscole e minuscole.

Utilizzare i segnaposto dei parametri

Microsoft. Data.SqlClient utilizza parametri nominati nella forma @parameterName. I segnaposto posizionali (?) non sono supportati.

I segnaposto dei parametri rappresentano valori, non identificatori SQL o sintassi SQL. Non puoi usare un parametro per il nome di una tabella, un nome di colonna, una direzione di ordinamento o un intero frammento SQL. Se un'applicazione seleziona dinamicamente un identificatore, validarlo rispetto a una lista di permessi e aggiungerlo al testo del comando solo dopo la validazione. Continua a passare tutti i valori dei dati come parametri.

Per i comandi testuali, Microsoft. Data.SqlClient invia istruzioni parametrizzate a SQL Server tramite sp_executesql. SQL Server può riutilizzare un piano di esecuzione quando il testo dell'istruzione e le definizioni dei parametri rimangono gli stessi.

Specificare i tipi di dati per i parametri

Impostare SqlDbType esplicitamente quando l'applicazione conosce il tipo di SQL Server di destinazione. Imposta anche metadati specifici per tipo come Size, Precision, e Scale quando applicabile.

Questo esempio corrisponde alle colonne nvarchar(50) e datetime 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);

Abbinare i metadati dei parametri alla colonna di destinazione evita conversioni implicite e impedisce che variazioni nella lunghezza della stringa o nella scala decimale modifichino la definizione del parametro.

Per i valori decimali, imposta esplicitamente la precisione e la scala. La Weight colonna in Production.Product usa 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;

Passare i valori NULL del database

Assegna Value per l'invio di un database NULL. C# null non rappresenta un SQL Server NULL per parametri scalari ordinari:

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;

Usa i metadati inferiti in modo selettivo

Il AddWithValue metodo deduce i metadati dei parametri dal valore di runtime. L'inferenza è comoda quando i metadati inferiti sono stabili e corrispondono al tipo di database. Può produrre definizioni di parametri diverse quando la lunghezza delle stringhe, la precisione decimale o altre caratteristiche di valore cambiano.

Preferisci Add con un esplicito SqlDbType per comandi di produzione il cui schema target è noto. Non passare C# null a AddWithValue; specifica il tipo e assegna DBNull.Value invece.

Riutilizza i parametri del comando

Crea un parametro una volta e cambialo solo Value quando esegui lo stesso comando con valori diversi:

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

Non aggiungere un altro parametro con lo stesso nome ad ogni esecuzione. Riutilizza il parametro esistente o cancella la collezione prima di ricostruirla.

Impostare la direzione del parametro

La Direction proprietà identifica come un comando utilizza un parametro. Impostarlo su un valoreParameterDirection:

Direction Utilizza
Input Invia un valore a un'istruzione o a una procedura archiviata. Si tratta del valore predefinito.
Output Riceve un valore da un parametro di output di una stored procedure.
InputOutput Invia un valore iniziale e riceve il valore aggiornato.
ReturnValue Riceve il valore intero da un'istruzione T-SQL RETURN .

Per i parametri di output, input/output e valore di ritorno, leggere Value solo dopo la fine del comando. Se un comando restituisce righe, chiudi o rilascia SqlDataReader prima di leggere quei valori.

Per esempi completi di stored procedure, vedi Modificare i dati con stored procedure.

Usa parametri con una procedura archiviata

Imposta StoredProcedure e usa i nomi dei parametri della procedura. Questo esempio chiama la dbo.uspGetEmployeeManagers procedura inclusa in 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)}");
}

Il nome del parametro deve corrispondere a quello dichiarato dalla procedura memorizzata.

Configurare i parametri con valori di tabella

Un parametro con valori di tabella invia più righe a una stored procedure o a un'istruzione parametrizzata. Per configurarne uno:

  1. Imposta SqlDbType.Structured.
  2. Impostare TypeName al tipo di tabella definito dall'utente del database.
  3. Assegna un'origine riga DataTable, DbDataReader o IEnumerable<SqlDataRecord>.

A DataTable con colonne definite e nessuna riga invia una tabella vuota. Un IEnumerable<SqlDataRecord> vuoto genera ArgumentException; assegna a C# null il valore necessario per inviare zero righe per quel modulo di input. Non assegnare DBNull.Value a un parametro con valori di tabella.

Per la creazione di tipi, la mappatura delle righe e esempi completi, vedi Parametri a valori di tabella.

Configura i valori di streaming

Assegna un Stream, TextReader o XmlReader a Value per incrementare gradualmente di un valore elevato. Per il trasferimento con memoria limitata, si utilizza un tipo di parametro (max), xml o json. Microsoft.Data.SqlClient memorizza la sorgente nel buffer prima dell'esecuzione per i tipi di caratteri e binari non-(max) e i tipi legacy di oggetti di grandi dimensioni.

Il tipo di parametro deve corrispondere al valore:

Value Valori compatibili SqlDbType
Stream Binary, VarBinary, o il tipo legacy Image
TextReader Char, NChar, NVarChar, VarChar, i tipi legacy Text e NText, Xml, o Json
XmlReader Char, NChar, NVarChar, VarChar, i tipi legacy Text e NText, Xml, o Json

Microsoft. Data.SqlClient lancia ArgumentException, InvalidCastException o SqlTypeException quando il tipo di oggetto, SqlDbType o le dimensioni dichiarate siano incompatibili. Per esempi completi di upload e download, vedi il supporto allo streaming SqlClient.

Derivare le informazioni sui parametri

DeriveParametersinterroga SQL Server per i metadati dei parametri di una procedura memorizzata e sostituisce la collezione di parametri esistente del comando:

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

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

DeriveParameters richiede un round trip aggiuntivo al database ogni volta che viene chiamato. Ricava i metadati durante lo sviluppo quando necessario, poi definisci esplicitamente i parametri nei percorsi critici per le prestazioni.

Contenuti correlati

  • Ultimo aggiornamento: