Executar múltiplos comandos com SqlBatch

Use SqlBatch para enviar uma coleção ordenada de objetos SqlBatchCommand através de um SqlConnection aberto. Cada comando batch tem o seu próprio texto de comando, tipo de comando, parâmetros, comportamento de comandos e resultado de linhas afetadas.

Utilize SqlBatch quando uma aplicação tem comandos distintos que quer enviar em conjunto. Utilize um SqlCommand parametrizado quando o trabalho for uma instrução ou procedimento armazenado. Utilize SqlBulkCopy ou um parâmetro com valores de tabela para conjuntos grandes de linhas.

Os exemplos neste artigo assumem que um connection aberto com o nome SqlConnection tem como alvo a base de dados de exemplo AdventureWorks.

Os exemplos de C# requerem using Microsoft.Data.SqlClient;. Exemplos que usam SqlDbType ou CommandBehavior também exigem using System.Data;. Exemplos que usam DbDataReader requerem using System.Data.Common;.

Suporte a versões e plataforma

Os exemplos têm como alvo o Microsoft.Data.SqlClient 7.1.0. Selecione uma versão do pacote e o framework de destino que seja suportado pelo ciclo de vida do suporte do Microsoft.Data.SqlClient para a sua aplicação.

SqlBatchderiva de DbBatch no .NET moderno. Microsoft.Data.SqlClient também expõe SqlBatch nos ativos do seu pacote .NET Framework e .NET Standard 2.0. A superfície pública pode variar consoante o framework de destino, por isso compila a aplicação para cada alvo que enviares.

Criar um lote

Crie um lote para uma ligação aberta e depois adicione comandos por ordem de execução:

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 é o padrão. Defina um comando em batch para um procedimento armazenado em CommandType.StoredProcedure. Definir CommandType.TableDirect gera ArgumentOutOfRangeException.

Adicionar parâmetros a comandos batch

Os parâmetros pertencem a um indivíduo SqlBatchCommand. O mesmo nome do marcador de posição em dois comandos em lote não partilha um valor:

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();

Defina o tipo SQL de cada parâmetro e o tamanho, precisão e escala aplicáveis a partir do contrato da base de dados. Para obter mais informações, consulte Configurar parâmetros.

Executar comandos que alteram dados

Usa ExecuteNonQuery ou ExecuteNonQueryAsync quando o lote altera dados e não precisas de linhas de resultados. O valor devolvido é o total de linhas afetadas quando as contagens de linhas estão disponíveis. Após a execução, cada comando RecordsAffected apresenta a sua própria contagem.

Este exemplo reverte as suas alterações:

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();

Leia vários resultados

Usar ExecuteReader ou ExecuteReaderAsync quando qualquer comando devolver linhas. O leitor apresenta os resultados em ordem de comandos por lote. Ligue NextResult ou NextResultAsync até que regresse false.

Um comando pode não produzir nenhum conjunto de linhas, produzir um conjunto de linhas ou produzir múltiplos conjuntos de linhas. Não assumas que uma chamada NextResult corresponde a uma SqlBatchCommand.

Usar comportamento do comando

Passe CommandBehavior ao nível do lote para ExecuteReader ou ExecuteReaderAsync quando o leitor precisar de acesso sequencial ou tiver de fechar a ligação:

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

As sobrecargas a nível de lote aceitam apenas CommandBehavior.Default, CommandBehavior.SequentialAccess, CommandBehavior.CloseConnection, e combinações desses valores. Outros valores válidos, como SingleRow, KeyInfo e ArgumentOutOfRangeException, geram SchemaOnly quando passados para uma sobrecarga ao nível do lote. As sobrecargas de comportamento ao nível de lote estão disponíveis a partir do Microsoft.Data.SqlClient 7.1.

Definir CommandBehavior num comando individual para opções como SingleRow, SchemaOnly, e KeyInfo. Utilize a sobrecarga de nível de lote para SequentialAccess e CloseConnection.

Use uma transação

SqlBatch não torna os comandos atómicos. Use uma transação explícita quando todos os comandos têm de ser confirmados ou revertidos em conjunto. Este exemplo reverte para que o AdventureWorks permaneça inalterado:

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();

Ligue Commit em vez de Rollback apenas quando a aplicação aceita o lote completo. Atribua a transação antes da execução. A transação e o lote devem usar a mesma conexão. O SqlClient não sobrepõe os métodos de transação assíncronos, por isso BeginTransactionAsync, CommitAsync, e RollbackAsync completam de forma síncrona. Para mais informações, veja Usar métodos de transação SQL baseados em tarefas.

Lidar com timeouts, cancelamentos e erros

Definir Timeout em segundos para o processamento em lote. O lote usa a definição da ligação Command Timeout quando configuras uma. Caso contrário, o padrão é 30 segundos. Um valor de 0 não tem limite de tempo, e valores negativos lançam ArgumentException. Passe um token de cancelamento para um método de execução assíncrono quando o chamador precisar de parar de esperar, ou chame Cancel. O cancelamento é efetuado numa base de melhor esforço. A operação pode terminar antes de o cancelamento entrar em vigor.

Quando a execução falhar, intercete SqlException e inspecione todos os itens da sua coleção Errors. BatchCommand identifica o SqlBatchCommand associado quando essa informação está disponível:

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

A execução do Reader é incremental. Um erro de um comando em lote posterior pode surgir quando Read ou NextResult avançarem para esse comando, por isso processa o leitor dentro do bloco try.

Não registe valores de parâmetros que contenham credenciais, tokens ou dados sensíveis. Para campos de erro e mensagens informativas, consulte Gerir erros e mensagens.

Conteúdo relacionado

• Executar um comando
• Configurar os parâmetros
• Recuperar dados com o SqlDataReader
• Programação assíncrona