Exécuter une commande

Utilisez SqlCommand pour exécuter des instructions Transact-SQL (T-SQL) ou des procédures stockées. Une commande nécessite une SqlConnection ouverte. Fermez la commande après utilisation et fermez tout lecteur avant de réutiliser la connexion.

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;. Exemples qui utilisent SqlDbType, CommandType, ou CommandBehavior nécessitent using System.Data;également .

Pour la configuration, l’authentification et le chiffrement de la connexion, voir Chaînes de connexion et Chiffrement et validation des certificats.

Choisir une méthode d’exécution

Choisissez la méthode qui correspond au résultat attendu :

Méthode À utiliser quand Résultat
ExecuteNonQuery La commande modifie les données ou le schéma. Le nombre de lignes affectées. -1 peut se produire lorsqu’aucun comptage n’est disponible, y compris lorsque SET NOCOUNT ON supprime les comptages.
ExecuteScalar Il vous faut une seule valeur. La première colonne de la première ligne, ou null lorsque le résultat n’a pas de lignes. Une base de données NULL est retournée sous la forme Value.
ExecuteReader Il faut des lignes, plusieurs ensembles de résultats ou un schéma. Un SqlDataReader.
ExecuteXmlReader La commande retourne XML sous la forme requise par ExecuteXmlReader. Élément XmlReader.

Chaque méthode dispose d’un pendant asynchrone basé sur des tâches. Passez un CancellationToken quand l’appelant doit arrêter d’attendre. Pour le comportement d’annulation et d’expiration, voir Programmation asynchrone.

Exécuter une commande texte

CommandType.Text est la valeur par défaut. Placez l’instruction dans CommandText et faites passer les valeurs à travers les paramètres :

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

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

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

Pour les types de paramètres, les tailles, les valeurs nulles et les directions, voir Configurer les paramètres. Ne concaténez pas de valeurs non approuvées dans CommandText.

Exécuter une procédure stockée

Définir CommandType.StoredProcedure et utiliser le nom de procédure qualifié par le schéma :

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

Microsoft. Data.SqlClient prend en charge CommandType.Text et CommandType.StoredProcedure. Le paramétrage de CommandType.TableDirect génère ArgumentOutOfRangeException.

Vérifiez ce que chaque méthode restitue

Utilisez ExecuteScalar quand vous avez besoin d’une valeur :

using var command = new SqlCommand(
    "SELECT COUNT(*) FROM Production.Product WHERE SellEndDate IS NULL;",
    connection);

int currentProductCount = (int)command.ExecuteScalar()!;
Console.WriteLine($"Current products: {currentProductCount}");

ExecuteScalar retourne null lorsque le résultat n’a pas de lignes et DBNull.Value lorsque la première valeur est une valeur de base de données NULL.

À utiliser ExecuteNonQuery pour les instructions modifiant les données. Cet exemple annule sa modification pour que AdventureWorks reste inchangé :

using SqlTransaction transaction = connection.BeginTransaction();

using var command = new SqlCommand("""
    UPDATE Sales.SalesOrderHeader
    SET Status = @status
    WHERE SalesOrderID = @salesOrderId;
    """, connection, transaction);
command.Parameters.Add("@status", SqlDbType.TinyInt).Value = 2;
command.Parameters.Add("@salesOrderId", SqlDbType.Int).Value = 43659;

int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine($"Rows affected: {rowsAffected}");

transaction.Rollback();

SET NOCOUNT ON supprime le nombre de lignes affectées, donc ExecuteNonQuery peut renvoyer -1 même lorsqu’une instruction modifie les données.

Utilisez ExecuteXmlReader pour un résultat FOR XML :

using var command = new SqlCommand("""
    SELECT TOP (3) ProductID, Name
    FROM Production.Product
    ORDER BY ProductID
    FOR XML PATH('Product'), ROOT('Products');
    """, connection);

using System.Xml.XmlReader reader = command.ExecuteXmlReader();
reader.MoveToContent();
Console.WriteLine(reader.ReadOuterXml());

Utiliser une transaction locale

Lorsqu’une connexion possède une transaction locale active SqlTransaction, attribuez cette transaction à chaque commande exécutée sur la connexion. Cet exemple annule sa modification pour que AdventureWorks reste inchangé :

using SqlTransaction transaction = connection.BeginTransaction();

using var command = new SqlCommand("""
    UPDATE Sales.SalesOrderHeader
    SET Status = @status
    WHERE SalesOrderID = @salesOrderId;
    """, connection, transaction);
command.Parameters.Add("@status", SqlDbType.TinyInt).Value = 2;
command.Parameters.Add("@salesOrderId", SqlDbType.Int).Value = 43659;

command.ExecuteNonQuery();
transaction.Rollback();

Appelez Commit au lieu de Rollback seulement lorsque l’application accepte la transaction complète. Une commande qui ne fait pas référence à la transaction active génère InvalidOperationException. L’exécution échoue également si la transaction appartient à une autre connexion. Pour les délimitations de transaction et la restauration, voir Transactions locales.

Définir un délai d’attente de la commande

CommandTimeout Limite l’exécution des commandes et l’attente cumulative de lecture réseau en secondes. Une commande utilise le réglage de la connexion Command Timeout lorsqu’elle est configurée. Sinon, la valeur par défaut est de 30 secondes. Une valeur de 0 n’a pas de limite de temps. Les valeurs négatives génèrent ArgumentException.

Lorsque le délai expire, la commande lève SqlException avec Number défini à -2:

using var command = new SqlCommand(
    "WAITFOR DELAY '00:00:05';",
    connection)
{
    CommandTimeout = 1
};

try
{
    command.ExecuteNonQuery();
}
catch (SqlException exception) when (exception.Number == -2)
{
    Console.WriteLine("The command timed out.");
}

Connect Timeout limite l’établissement de la connexion et les temps d’attente du pool de connexions. CommandTimeout commence après le début de l’exécution de la commande. Pour plus d’informations, voir Options de connexion.

Utilisez un délai d’expiration fini et un jeton d’annulation ensemble. Le délai fixe une limite à l’opération sur la base de données. Annuler le jeton peut rendre le contrôle à l’appelant avant l’expiration du timeout. Ne supposez pas que l’annulation et le délai d’attente produisent la même exception.

Réutiliser et préparer les commandes

Ajoutez un paramètre une fois, puis changez sa valeur après chaque exécution :

using var command = new SqlCommand("""
    SELECT TOP (1) Name, ListPrice
    FROM Production.Product
    WHERE ProductSubcategoryID = @subcategoryId
    ORDER BY ListPrice DESC;
    """, 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)}");
    }
}

Appelez Prepare seulement si les mesures le justifient. Prepare ne prépare pas de procédures stockées ni de commandes texte sans paramètres. Pour le texte paramétré, définissez des métadonnées complètes telles que Size, Scale et Prepare sur chaque paramètre avant d’appeler Precision.

Commandes de dépannage

Interceptez SqlException autour de l’opération de la base de données et inspectez sa collection Errors lorsque le serveur renvoie plusieurs erreurs. Pour les erreurs de commande, les avertissements et les messages d’information, voir Gérer les erreurs et messages.

Utilisez des compteurs de diagnostic, le traçage des sources d’événements et le guide de dépannage SqlClient lorsqu’une erreur nécessite plus de contexte. Appliquer la logique de réessai configurable uniquement lorsque répéter la commande est sûr.

Contenu connexe

  • Dernière mise à jour :