Exécuter plusieurs commandes avec SqlBatch

Utilisez SqlBatch pour envoyer une collection ordonnée d’objets SqlBatchCommand via une connexion SqlConnection ouverte. Chaque commande batch possède son propre texte de commande, type de commande, paramètres, comportement de commande et résultat du nombre de lignes affectées.

À utiliser SqlBatch lorsqu’une application a des commandes distinctes qu’elle souhaite envoyer ensemble. Utilisez un élément paramétré SqlCommand lorsque l'opération consiste en une seule instruction ou en une procédure stockée. Utilisez SqlBulkCopy ou un paramètre à valeurs de table pour de grands ensembles de lignes.

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

Les exemples de C# nécessitent using Microsoft.Data.SqlClient;. Exemples qui utilisent SqlDbType ou CommandBehavior nécessitent using System.Data;également . Les exemples qui utilisent DbDataReader nécessitent using System.Data.Common;.

Prise en charge des versions et de la plateforme

Les exemples ciblent Microsoft.Data.SqlClient 7.1.0. Sélectionnez une version de package et un framework cible pris en charge par le cycle de vie de support de Microsoft.Data.SqlClient pour votre application.

SqlBatchdérive de DbBatch sur le .NET moderne. Microsoft.Data.SqlClient expose également SqlBatch dans ses ressources de package .NET Framework et .NET Standard 2.0. La surface publique peut varier selon le framework cible, alors compilez l’application pour chaque cible que vous distribuez.

Créer un lot

Créez un lot pour une connexion ouverte, puis ajoutez des commandes dans l’ordre d’exécution :

using var batch = new SqlBatch(connection);
batch.BatchCommands.Add(new SqlBatchCommand("""
    SELECT TOP (3) ProductID, Name
    FROM Production.Product
    ORDER BY ProductID;
    """));
batch.BatchCommands.Add(new SqlBatchCommand("""
    SELECT TOP (3) SalesOrderID, OrderDate
    FROM Sales.SalesOrderHeader
    ORDER BY SalesOrderID;
    """));

using DbDataReader reader = batch.ExecuteReader();
do
{
    while (reader.Read())
    {
        Console.WriteLine($"{reader.GetValue(0)} | {reader.GetValue(1)}");
    }
}
while (reader.NextResult());

CommandType.Text est la valeur par défaut. Définissez une commande batch à CommandType.StoredProcedure pour une procédure stockée. Le paramétrage de CommandType.TableDirect génère ArgumentOutOfRangeException.

Ajouter des paramètres aux commandes batch

Les paramètres appartiennent à une personne SqlBatchCommand. Le même nom d’espace réservé dans deux commandes batch ne partage pas de valeur :

using var batch = new SqlBatch(connection);

var products = new SqlBatchCommand("""
    SELECT ProductID, Name
    FROM Production.Product
    WHERE ProductSubcategoryID = @id
    ORDER BY ProductID;
    """);
products.Parameters.Add("@id", SqlDbType.Int).Value = 2;
batch.BatchCommands.Add(products);

var orders = new SqlBatchCommand("""
    SELECT SalesOrderID, OrderDate
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID = @id
    ORDER BY SalesOrderID;
    """);
orders.Parameters.Add("@id", SqlDbType.Int).Value = 279;
batch.BatchCommands.Add(orders);

using DbDataReader reader = batch.ExecuteReader();

Définissez le type SQL de chaque paramètre ainsi que la taille, la précision et l’échelle applicables à partir du schéma de base de données. Pour plus d’informations, voir Configurer les paramètres.

Exécuter des commandes modifiant les données

Utilisez ExecuteNonQuery ou ExecuteNonQueryAsync lorsque le lot modifie les données et que vous n’avez pas besoin de lignes de résultats. La valeur retournée correspond au total des lignes affectées lorsque le nombre de lignes est disponible. Après exécution, chaque commande RecordsAffected indique son propre nombre.

Cet exemple annule les modifications qu’il apporte:

using SqlTransaction transaction = connection.BeginTransaction();
using var batch = new SqlBatch(connection, transaction);

var firstUpdate = new SqlBatchCommand("""
    UPDATE Sales.SalesOrderHeader
    SET Status = @status
    WHERE SalesOrderID = @salesOrderId;
    """);
firstUpdate.Parameters.Add("@status", SqlDbType.TinyInt).Value = 2;
firstUpdate.Parameters.Add("@salesOrderId", SqlDbType.Int).Value = 43659;
batch.BatchCommands.Add(firstUpdate);

var secondUpdate = new SqlBatchCommand("""
    UPDATE Sales.SalesOrderHeader
    SET Status = @status
    WHERE SalesOrderID = @salesOrderId;
    """);
secondUpdate.Parameters.Add("@status", SqlDbType.TinyInt).Value = 2;
secondUpdate.Parameters.Add("@salesOrderId", SqlDbType.Int).Value = 43660;
batch.BatchCommands.Add(secondUpdate);

int totalRows = batch.ExecuteNonQuery();
Console.WriteLine($"Total rows: {totalRows}");

foreach (SqlBatchCommand command in batch.BatchCommands)
{
    Console.WriteLine(command.RecordsAffected);
}

transaction.Rollback();

Lire plusieurs résultats

Utiliser ExecuteReader ou ExecuteReaderAsync quand une commande retourne des lignes. Le module de lecture expose les résultats dans l’ordre des commandes de traitement par lots. Appelez NextResult ou NextResultAsync jusqu’à ce qu’il renvoie false.

Une commande peut produire aucun ensemble de lignes, un seul ensemble de lignes, ou plusieurs ensembles de lignes. Ne supposez pas qu’un appel NextResult correspond à un SqlBatchCommand.

Comportement de la commande Utiliser

Passer un CommandBehavior au niveau du lot vers ExecuteReader ou ExecuteReaderAsync lorsque le lecteur a besoin d’un accès séquentiel ou doit fermer la connexion :

using var batch = new SqlBatch(connection);
batch.BatchCommands.Add(new SqlBatchCommand("""
    SELECT DocumentSummary
    FROM Production.Document
    WHERE DocumentSummary IS NOT NULL;
    """));

using DbDataReader reader =
    batch.ExecuteReader(CommandBehavior.SequentialAccess);

char[] buffer = new char[4096];
while (reader.Read())
{
    using TextReader summary = reader.GetTextReader(0);
    int count;
    while ((count = summary.Read(buffer, 0, buffer.Length)) > 0)
    {
        Console.Out.Write(buffer, 0, count);
    }
    Console.WriteLine();
}

Les surcharges de niveau batch n’acceptent que CommandBehavior.Default, CommandBehavior.SequentialAccess, CommandBehavior.CloseConnection, et les combinaisons de ces valeurs. D’autres valeurs valides, telles que SingleRow, KeyInfo et ArgumentOutOfRangeException, sont rejetées SchemaOnly lorsqu’elles sont passées à une surcharge de niveau batch. Les surcharges de comportement au niveau batch sont disponibles à partir de Microsoft.Data.SqlClient 7.1.

Définissez CommandBehavior pour une commande individuelle pour des options telles que SingleRow, SchemaOnly et KeyInfo. Utilisez la surcharge au niveau des lots pour SequentialAccess et CloseConnection.

Utiliser une transaction

SqlBatch ne rend pas ses commandes atomiques. Utilisez une transaction explicite lorsque toutes les commandes doivent être validées ou annulées ensemble. Cet exemple annule la transaction pour qu’AdventureWorks reste inchangé :

using SqlTransaction transaction = connection.BeginTransaction();
using var batch = new SqlBatch(connection, transaction);

batch.BatchCommands.Add(new SqlBatchCommand("""
    UPDATE Sales.SalesOrderHeader
    SET Status = 2
    WHERE SalesOrderID = 43659;
    """));
batch.BatchCommands.Add(new SqlBatchCommand("""
    UPDATE Sales.SalesOrderHeader
    SET Status = 2
    WHERE SalesOrderID = 43660;
    """));

batch.ExecuteNonQuery();
transaction.Rollback();

Appelez Commit plutôt que Rollback seulement lorsque l’application accepte le lot complet. Affectez la transaction avant l’exécution. La transaction et le lot doivent utiliser la même connexion. SqlClient ne remplace pas les méthodes de transaction asynchrones, donc BeginTransactionAsync, CommitAsync, et RollbackAsync complètent de façon synchrone. Pour plus d’informations, voir Utiliser des méthodes de transaction SQL basées sur des tâches.

Gérer les délais d’attente, les annulations et les erreurs

Réglez Timeout en secondes pour le lot. Le lot utilise le paramètre de la connexion Command Timeout quand vous en configurez une. Sinon, la valeur par défaut est de 30 secondes. Une valeur de 0 n’a pas de limite de temps, et les valeurs négatives provoquent ArgumentException. Passer un jeton d’annulation à une méthode d’exécution asynchrone lorsque l’appelant doit arrêter d’attendre, ou appeler Cancel. L’annulation est effectuée dans la mesure du possible. L’opération pourrait se terminer avant que l’annulation ne prenne effet.

Lorsque l’exécution échoue, détectez SqlException et inspectez chaque élément de sa Errors collection. BatchCommand identifie le SqlBatchCommand associé lorsque ces informations sont disponibles :

using var batch = new SqlBatch(connection);
batch.BatchCommands.Add(new SqlBatchCommand(
    "SELECT TOP (1) ProductID FROM Production.Product;"));
batch.BatchCommands.Add(new SqlBatchCommand("SELECT 1 / 0;"));

try
{
    using DbDataReader reader = batch.ExecuteReader();
    do
    {
        while (reader.Read())
        {
            Console.WriteLine(reader.GetValue(0));
        }
    }
    while (reader.NextResult());
}
catch (SqlException exception)
{
    Console.WriteLine(
        exception.BatchCommand is null
            ? "The batch failed."
            : $"Failed command: {exception.BatchCommand.CommandText}");
}

L’exécution par le lecteur est incrémentale. Une erreur provenant d’une commande batch ultérieure peut apparaître lorsque Read avance jusqu’à cette commande ou que NextResult y avance, donc traitez le lecteur à l’intérieur du bloc try.

Ne consignez pas les valeurs des paramètres contenant des identifiants, des jetons ou des données sensibles. Pour les champs d’erreur et les messages d’information, voir Gérer les erreurs et messages.

Contenu connexe

  • Dernière mise à jour :