Execute múltiplos comandos com SqlBatch

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

Use SqlBatch quando um aplicativo tiver comandos distintos que queira enviar juntos. Use uma consulta parametrizada SqlCommand quando o trabalho for uma única instrução ou procedimento armazenado. Use SqlBulkCopy ou um parâmetro com valores de tabela para conjuntos grandes de linhas.

Os exemplos deste artigo pressupõem que um connection aberto chamado SqlConnection tem como alvo o banco 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 exigem using System.Data.Common;.

Suporte a versões e plataforma

Os exemplos têm como alvo a Microsoft.Data.SqlClient 7.1.0. Selecione uma versão do pacote e o framework de destino compatíveis com o ciclo de vida de suporte do Microsoft.Data.SqlClient para sua aplicação.

SqlBatchderiva de DbBatch em .NET moderno. Microsoft.Data.SqlClient também expõe SqlBatch nos ativos do pacote do .NET Framework e do .NET Standard 2.0. A superfície pública pode variar conforme o framework de destino, então compile a aplicação para cada destino que você enviar.

Criar um lote

Crie um lote para uma conexão aberta e então adicione comandos na 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 batch para CommandType.StoredProcedure em um procedimento armazenado. A configuração CommandType.TableDirect gera ArgumentOutOfRangeException.

Adicionar parâmetros aos comandos batch

Os parâmetros pertencem a um indivíduo SqlBatchCommand. O mesmo nome do placeholder em dois comandos batch não compartilha 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, a precisão e a escala aplicáveis a partir do contrato de dados. Para obter mais informações, consulte Configurar parâmetros.

Executar comandos que alteram dados

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

Este exemplo desfaz 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

Use ExecuteReader ou ExecuteReaderAsync quando qualquer comando retornar linhas. O reader expõe os resultados em ordem de comandos em lote. Ligue NextResult ou NextResultAsync até retornar false.

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

Usar comportamento do comando

Passe um CommandBehavior no nível do lote para ExecuteReader ou ExecuteReaderAsync quando o leitor precisar de acesso sequencial ou precisar fechar a conexã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 em 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, lançam SchemaOnly quando passados para uma sobrecarga em nível de lote. As sobrecargas de comportamento no nível do lote estão disponíveis a partir do Microsoft.Data.SqlClient 7.1.

Defina CommandBehavior em um comando individual para opções como SingleRow, SchemaOnly, e KeyInfo. Use a sobrecarga em nível de lote para SequentialAccess e CloseConnection.

Usar uma transação

SqlBatch não torna seus comandos atômicos. Use uma transação explícita quando todos os comandos precisam ser confirmados ou revertidos. Este exemplo faz rollback 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 aceitar o lote completo. Associe 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, então BeginTransactionAsync, CommitAsync, e RollbackAsync completam de forma síncrona. Para mais informações, veja Usar métodos de transação SQL baseados em tarefas.

Gerencie tempos de espera, cancelamentos e erros

Defina Timeout em segundos para o processamento em lote. O lote usa a configuração da conexão Command Timeout quando você configura 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 parar de esperar, ou chame Cancel. O cancelamento é realizado da melhor forma possível. A operação pode ser concluída antes que o cancelamento entre em vigor.

Quando a execução falhar, capture SqlException e inspecione todos os itens na 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 lote posterior pode aparecer quando Read ou NextResult avançar para esse comando, então processe o leitor dentro do try bloco.

Não registre valores de parâmetros que contenham credenciais, tokens ou dados sensíveis. Para campos de erro e mensagens informativas, veja Como lidar com erros e mensagens.

Conteúdo relacionado

  • Última atualização em