Configurez les paramètres avec Microsoft. Data.SqlClient

Les paramètres envoient des valeurs à une commande séparément de son texte Transact-SQL (T-SQL). Utilisez des objets SqlParameter au lieu de concaténer ou d’interpoler des valeurs en instructions SQL. Les paramètres aident à protéger contre l’injection SQL et à préserver le type de base de données de la valeur.

Les exemples de cet article supposent qu’un connection ouvert nommé SqlConnection cible la base de données d’échantillons AdventureWorks.

Les exemples de C# nécessitent using Microsoft.Data.SqlClient;. Les exemples qui utilisent SqlDbType nécessitent aussi using System.Data;.

Ajouter des paramètres d’entrée

Ajoutez un paramètre pour chaque espace réservé de valeur dans le texte de commande. Spécifiez le type de SQL Server du paramètre et attribuez-lui la valeur :

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

Utilisez la même casse pour un nom de paramètre dans le texte de commande et dans le SqlParameter. SQL Server associe les noms des variables en utilisant la collation serveur, donc une non-correspondance entraîne un échec avec une erreur « Must declare the scalar variable » sur un serveur avec une collation sensible à la casse.

Utiliser des espaces réservés pour les paramètres

Microsoft. Data.SqlClient utilise des paramètres nommés sous la forme @parameterName. Les points d’interrogation positionnels (?) de marque-lieu ne sont pas pris en charge.

Les placeholders de paramètres représentent des valeurs, pas des identifiants SQL ou une syntaxe. Vous ne pouvez pas utiliser un paramètre pour un nom de table, un nom de colonne, une direction de tri ou un fragment SQL entier. Si une application sélectionne un identifiant dynamiquement, validez-le par rapport à une liste d’autorisation et ne l’ajoutez au texte de commande qu’après validation. Continuez à passer toutes les valeurs de données comme paramètres.

Pour les commandes texte, Microsoft. Data.SqlClient envoie des instructions paramétrées à SQL Server via sp_executesql. SQL Server peut réutiliser un plan d’exécution lorsque le texte de l’instruction et les définitions de paramètres restent les mêmes.

Spécifier les types de données des paramètres

Définissez SqlDbType explicitement lorsque l’application connaît le type cible de SQL Server. Définissez également des métadonnées spécifiques au type telles que Size, Precision, et Scale lorsque cela est applicable.

Cet exemple correspond aux colonnes nvarchar(50) et datetime dans 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);

Faire correspondre les métadonnées des paramètres à la colonne cible évite les conversions implicites et empêche les variations de longueur de chaîne ou d’échelle décimale de modifier la définition du paramètre.

Pour les valeurs décimales, définissez explicitement la précision et l’échelle. La Weight colonne dans Production.Product utilise décimal(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;

Transmettre les valeurs NULL de la base de données

Attribuez Value pour envoyer NULL à une base de données. C# null ne représente pas un type SQL Server NULL pour les paramètres scalaires ordinaires :

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;

Utiliser les métadonnées inférées de manière sélective

La AddWithValue méthode déduit les métadonnées des paramètres à partir de la valeur d’exécution. L’inférence est pratique lorsque les métadonnées inférées sont stables et correspondent au type de base de données. Il peut produire différentes définitions de paramètres lorsque la longueur des chaînes, la précision décimale ou d’autres caractéristiques de valeur changent.

Privilégiez Add avec un SqlDbType explicite pour les commandes de production dont le schéma cible est connu. Ne passez pas C# null à AddWithValue ; spécifiez le type et assignez DBNull.Value à la place.

Réutiliser les paramètres de commande

Créez un paramètre une fois et ne changez que sa Value lorsque vous exécutez la même commande avec des valeurs différentes :

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

N’ajoutez pas un autre paramètre avec le même nom à chaque exécution. Réutilisez le paramètre existant ou effacez la collection avant de la reconstruire.

Définir la direction du paramètre

Cette Direction propriété identifie comment une commande utilise un paramètre. Définissez-la sur une ParameterDirection valeur :

Direction	Utiliser
Input	Envoie une valeur à une instruction ou à une procédure stockée. Cette valeur est la valeur par défaut.
Output	Reçoit une valeur d’un paramètre de sortie d’une procédure enregistrée.
InputOutput	Envoie une valeur initiale et reçoit la valeur mise à jour.
ReturnValue	Reçoit la valeur entière à partir d’une instruction T-SQL RETURN.

Pour les paramètres de sortie, d’entrée/sortie et de valeur de retour, ne lisez Value qu’après la fin de la commande. Si une commande renvoie des lignes, fermez ou supprimez le SqlDataReader avant de lire ces valeurs.

Pour des exemples complets de procédures stockées, voir Modifier les données avec des procédures stockées.

Utiliser des paramètres avec une procédure stockée

Définir StoredProcedure et utiliser les noms des paramètres de la procédure. Cet exemple appelle la dbo.uspGetEmployeeManagers procédure incluse dans 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)}");
}

Le nom du paramètre doit correspondre à celui déclaré par la procédure stockée.

Configurer les paramètres de type table

Un paramètre à valeurs de table envoie plusieurs lignes à une procédure stockée ou à une instruction paramétrée. Pour en configurer un :

1. Définissez SqlDbType.Structured.
2. Définissez TypeName sur le type de table défini par l’utilisateur de la base de données.
3. Attribuez une source de ligne DataTable, DbDataReader ou IEnumerable<SqlDataRecord>.

Un DataTable avec des colonnes définies et aucune ligne envoie une table vide. Une valeur vide IEnumerable<SqlDataRecord> génère ArgumentException; affecter C# null pour envoyer zéro ligne pour ce formulaire d’entrée. N’attribuez pas DBNull.Value à un paramètre à valeur de table.

Pour la création de types, le mappage de lignes et des exemples complets, voir paramètres table.

Configurez les valeurs de diffusion en continu

Attribuer un Stream, TextReader ou XmlReader à Value pour augmenter fortement la valeur par incréments. Pour le transfert à mémoire limitée, utilisez un (max)type de paramètre xml ou json. Microsoft.Data.SqlClient met en mémoire tampon la source avant l’exécution pour les types de caractères et binaires autres que (max) ainsi que pour les anciens types d’objets volumineux.

Le type de paramètre doit correspondre à la valeur :

Value	Valeurs compatibles SqlDbType
Stream	Binary, VarBinary, ou le type hérité Image
TextReader	Char, NChar, NVarChar, VarChar, les types hérités Text et NText, Xml, ou Json
XmlReader	Char, NChar, NVarChar, VarChar, les types hérités Text et NText, Xml, ou Json

Microsoft.Data.SqlClient lève ArgumentException, InvalidCastException, ou SqlTypeException lorsque le type d’objet, SqlDbType, ou la taille déclarée est incompatible. Pour des exemples complets de téléversement et de téléchargement, voir le support du streaming SqlClient.

Dériver les informations de paramètre

DeriveParametersinterroge SQL Server pour obtenir les métadonnées de paramètres d'une procédure stockée et remplace la collection de paramètres existante de la commande :

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

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

DeriveParameters nécessite un aller-retour supplémentaire à la base de données à chaque appel. Dériver des métadonnées pendant le développement lorsque cela est nécessaire, puis définir explicitement les paramètres dans des chemins sensibles à la performance.

Contenu connexe

• Exécuter une commande
• Modification de données avec des procédures stockées
• Paramètres de type table
• Prise en charge du streaming SqlClient