Ejecutar múltiples comandos con SqlBatch

Úsase SqlBatch para enviar una colección ordenada de objetos SqlBatchCommand a través de un flujo abierto SqlConnection. Cada comando por lotes tiene su propio texto, tipo de comando, parámetros, comportamiento del comando y resultado de filas afectadas.

Usa SqlBatch cuando una aplicación tiene comandos distintos que quiere enviar juntos. Utiliza un SqlCommand parametrizado cuando el trabajo es una sola sentencia o procedimiento almacenado. Usa SqlBulkCopy o un parámetro con valores de tabla para conjuntos grandes de filas.

Los ejemplos de este artículo asumen que una conexión abierta connection denominada SqlConnection apunta a la base de datos de ejemplo de AdventureWorks.

Los ejemplos de C# requieren using Microsoft.Data.SqlClient;. Ejemplos que usan SqlDbType o CommandBehavior también requieren using System.Data;. Los ejemplos que usan DbDataReader requieren using System.Data.Common;.

Compatibilidad con versiones y plataformas

Los ejemplos usan Microsoft.Data.SqlClient 7.1.0. Selecciona una versión del paquete y un framework de destino compatibles con el ciclo de vida de soporte de Microsoft.Data.SqlClient para tu aplicación.

SqlBatchderiva de DbBatch en el .NET moderno. Microsoft. Data.SqlClient también expone SqlBatch en los activos del paquete de .NET Framework y .NET Standard 2.0. La API pública puede variar según el framework objetivo, así que compila la aplicación para cada marco de destino que distribuyas.

Crear un lote

Crea un lote para una conexión abierta y luego añade comandos en orden de ejecución:

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 es el valor predeterminado. Establece un comando por lotes en CommandType.StoredProcedure para un procedimiento almacenado. Establecer CommandType.TableDirect lanza ArgumentOutOfRangeException.

Añadir parámetros a los comandos por lotes

Los parámetros pertenecen a una persona SqlBatchCommand. El mismo nombre de marcador en dos comandos por lotes no comparten 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();

Establece el tipo SQL de cada parámetro y el tamaño, precisión y escala aplicables según el contrato de la base de datos. Para obtener más información consulte Configurar parámetros.

Ejecutar comandos que cambian los datos

Usa ExecuteNonQuery o ExecuteNonQueryAsync cuando el lote cambia datos y no necesitas filas de resultados. El valor devuelto es el total de filas afectadas cuando hay recuentos de filas disponibles. Tras la ejecución, el RecordsAffected de cada comando informa su propio recuento.

Este ejemplo revierte sus cambios:

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

Leer múltiples resultados

Usar ExecuteReader o ExecuteReaderAsync cuando cualquier comando devuelva filas. El lector muestra los resultados en orden de comandos por lotes. Llama NextResult o NextResultAsync hasta que vuelva false.

Un comando puede no producir ningún conjunto de filas, un solo conjunto de filas o varios conjuntos de filas. No des por hecho que una NextResult llamada se corresponde con un SqlBatchCommand.

Usar el comportamiento de los comandos

Pasar un CommandBehavior de nivel de lote a ExecuteReader o ExecuteReaderAsync cuando el lector necesite acceso secuencial o deba cerrar la conexión:

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

Las sobrecargas a nivel de lote aceptan solo CommandBehavior.Default, CommandBehavior.SequentialAccess, CommandBehavior.CloseConnection, y combinaciones de esos valores. Otros valores válidos, como SingleRow, KeyInfo y ArgumentOutOfRangeException, generan SchemaOnly cuando se pasan a una sobrecarga de nivel de lote. Las sobrecargas de comportamiento a nivel de lote están disponibles a partir de Microsoft.Data.SqlClient 7.1.

Configura CommandBehavior para un comando individual para opciones como SingleRow, SchemaOnly y KeyInfo. Usa la sobrecarga a nivel de lotes para SequentialAccess y CloseConnection.

Utiliza una transacción

SqlBatch no hace que sus comandos sean atómicos. Utiliza una transacción explícita cuando todos los comandos deben confirmarse o revertirse juntos. Este ejemplo revierte para que AdventureWorks permanezca sin cambios:

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

Llama Commit en lugar de Rollback solo cuando la aplicación acepte el lote completo. Asigna la transacción antes de ejecutarla. La transacción y el lote deben usar la misma conexión. SqlClient no anula los métodos de transacción asíncronos, así que BeginTransactionAsync, CommitAsync, y RollbackAsync completan de forma sincrónica. Para más información, véase Usar métodos de transacciones SQL basados en tareas.

Gestiona tiempos de espera, cancelaciones y errores

Establece Timeout segundos para el lote. El lote usa la configuración de la conexión Command Timeout si configuras una. Si no, el valor por defecto son 30 segundos. Un valor de 0 no tiene límite de tiempo, y los valores negativos lanzan ArgumentException. Pasa un token de cancelación a un método de ejecución asíncrono cuando el llamante necesita dejar de esperar, o llama a Cancel. La cancelación se realiza en la medida de lo posible. La operación podría terminar antes de que la cancelación entre en vigor.

Cuando la ejecución falle, detecta SqlException e inspecciona cada elemento de su Errors colección. BatchCommand identifica el SqlBatchCommand asociado cuando esa información está disponible:

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

La ejecución del lector es incremental. Un error de un comando por lotes posterior puede aparecer cuando Read o NextResult avanzan a ese comando, así que procesa al lector dentro del bloque try.

No registres valores de parámetros que contengan credenciales, tokens o datos sensibles. Para campos de error y mensajes informativos, véase Manejar los errores y los mensajes.

Contenido relacionado

  • Se actualizó por última vez el