Выполните несколько команд с помощью SqlBatch

Используйте SqlBatch для отправки упорядоченной коллекции SqlBatchCommand объектов через один открытый SqlConnection. Каждая пакетная команда имеет свой собственный текст команды, тип команды, параметры, поведение команды и результат, зависящий от строк.

Используйте SqlBatch его, когда приложение имеет отдельные команды, которые оно хочет отправить вместе. Используйте один параметризованный SqlCommand момент, когда работа представляет собой один оператор или сохранённую процедуру. Используйте SqlBulkCopy или параметр с таблицным значением для больших наборов строк.

Примеры в этой статье предполагают, что открытое SqlConnection имя connection нацелено на образцовую базу данных AdventureWorks.

Примеры C# требуют using Microsoft.Data.SqlClient;. Примеры, которые используют SqlDbType или CommandBehavior также требуют using System.Data;. Примеры, которые используют DbDataReader , требуют using System.Data.Common;.

Поддержка версий и платформы

Примеры нацелены на Microsoft. Data.SqlClient 7.1.0. Выберите версию пакета и целевую структуру, которую поддерживает Microsoft. Data.SqlClient для жизненного цикла вашего приложения.

SqlBatchпроисходит из DbBatch современного .NET. Microsoft. Data.SqlClient также предоставляет SqlBatch пакетные ресурсы в рамках .NET Framework и .NET Standard 2.0. Публичная поверхность может отличаться в зависимости от целевого фреймворка, поэтому компилируйте приложение для каждой цели, которую отправляете.

Создание пакета

Создайте пакет для открытого соединения, а затем добавьте команды в порядке выполнения:

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 — значение по умолчанию. Установите пакетную команду для CommandType.StoredProcedure сохранения процедуры. Сеттинг CommandType.TableDirect бросает ArgumentOutOfRangeException.

Добавляйте параметры в пакетные команды

Параметры принадлежат отдельному человеку SqlBatchCommand. Одно и то же имя заполнятеля в двух командах пакета не имеет общего значения:

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

Задайте SQL-тип каждого параметра и применимый размер, точность и масштаб по контракту базы данных. Дополнительные сведения см. в Настройка параметров.

Выполнение команд изменения данных

Используйте ExecuteNonQuery или ExecuteNonQueryAsync когда пакет меняет данные, и вам не нужны строки результатов. Возвращаемое значение — это общее количество строк, затронутых при доступном количестве строк. После выполнения каждая команда сообщает RecordsAffected свой собственный счёт.

В этом примере откатывают его изменения:

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

Читайте несколько результатов

Используйте ExecuteReader или ExecuteReaderAsync когда любая команда возвращает строки. Считыватель показывает результаты в порядке пакетных команд. Звоните NextResult или NextResultAsync пока он не вернётся false.

Команда может не создавать набор строк, набор одной строки или множества рядов. Не думайте, что один NextResult вызов соответствует другому SqlBatchCommand.

Использование командного поведения

Передайте пакетный CommandBehavior уровень в ExecuteReader или ExecuteReaderAsync когда считывателю нужен последовательный доступ или необходимо закрыть соединение:

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

Перегрузки на уровне пакетов принимают только CommandBehavior.Default, CommandBehavior.SequentialAccess, CommandBehavior.CloseConnectionи комбинации этих значений. Другие допустимые значения, такие SingleRowкак , SchemaOnly, и KeyInfo, при ArgumentOutOfRangeException перегрузке на уровне пакета. Пакетные перегрузки поведения доступны начиная с Microsoft. Data.SqlClient 7.1.

Установите CommandBehavior индивидуальную команду для опций, таких как SingleRow, SchemaOnly, и KeyInfo. Используйте перегрузку на уровне пакета для SequentialAccess и CloseConnection.

Используйте транзакцию

SqlBatch не делает его команды атомарными. Используйте явную транзакцию, когда все команды должны зафиксироваться или откатиться вместе. Этот пример возвращается назад, так что AdventureWorks остаётся без изменений:

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

Звоните Commit , а Rollback не только когда заявка принимает полный пакет. Назначайте транзакцию до её выполнения. Транзакция и партия должны использовать одно и то же соединение. SqlClient не переопределяет асинхронные методы транзакций, поэтому BeginTransactionAsync, CommitAsync, и RollbackAsync завершает синхронно. Для получения дополнительной информации см . Использование методов транзакций SQL, основанных на задачах.

Обрабатывайте тайм-ауты, отмену и ошибки

Готовится Timeout за считанные секунды для партии. Пакет использует настройки соединения Command Timeout при его настройке. В противном случае по умолчанию — 30 секунд. Значение не 0 имеет временного ограничения, а отрицательные значения выдают ArgumentException. Передайте токен отмены асинхронному методу выполнения, когда вызывающему нужно прекратить ожидание или вызвать Cancel. Отмена — лучшая попытка. Операция может завершиться до вступления в силу отмены.

Когда выполнение не удаётся, поймать SqlException и проверить каждый элемент в его Errors коллекции. BatchCommand определяет связанный SqlBatchCommand объект, когда эта информация доступна:

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

Реализация читателем происходит постепенно. Ошибка в более поздней команде batch может появиться при Read переходе к этой команде NextResult , поэтому обрабатывайте считыватель внутри try блока.

Не записывайте значения параметров, содержащие учетные данные, токены или конфиденциальные данные. Для полей ошибок и информационных сообщений см. раздел «Обработка ошибок и сообщений».

Связанные материалы

  • Последнее обновление: