Wykonaj wiele poleceń za pomocą SqlBatch

Użyj SqlBatch do przesyłania uporządkowanej kolekcji obiektów SqlBatchCommand przez jedno otwarte SqlConnection. Każde polecenie wsadowe ma własny tekst polecenia, typ polecenia, parametry, zachowanie polecenia oraz wynik dotyczący liczby wierszy, których dotyczyło polecenie.

Używaj SqlBatch , gdy aplikacja ma odrębne polecenia, które chce wysłać razem. Używaj jednego parametryzowanego SqlCommand, gdy operacja jest jedną instrukcją lub procedurą przechowywaną. Użyj SqlBulkCopy lub parametru tabelowego dla dużych zestawów wierszy.

Przykłady w tym artykule zakładają, że otwarte połączenie SqlConnection o nazwie connection wskazuje na przykładową bazę danych AdventureWorks.

Przykłady w języku C# wymagają using Microsoft.Data.SqlClient;. Przykłady, które używają SqlDbType lub CommandBehavior również wymagają using System.Data;. Przykłady, które używają DbDataReader , wymagają using System.Data.Common;.

Wsparcie dla wersji i platformy

Przykłady są skierowane do Microsoft.Data.SqlClient 7.1.0. Wybierz wersję pakietu i platformę docelową obsługiwane przez cykl pomocy technicznej Microsoft.Data.SqlClient dla Twojej aplikacji.

SqlBatch pochodzi od DbBatch w nowoczesnym środowisku .NET. Microsoft.Data.SqlClient udostępnia również SqlBatch w zasobach pakietu .NET Framework oraz .NET Standard 2.0. Publiczny interfejs API może się różnić w zależności od platformy docelowej, więc kompiluj aplikację dla każdej platformy docelowej, którą dostarczasz.

Utwórz partię

Utwórz batch dla otwartego połączenia, a następnie dodaj polecenia w kolejności wykonywania:

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

Wartość domyślna to CommandType.Text. Ustaw polecenie batch na CommandType.StoredProcedure dla procedury przechowywanej. Ustawienie CommandType.TableDirect powoduje ArgumentOutOfRangeException.

Dodaj parametry do poleceń wsadowych

Parametry należą do osoby SqlBatchCommand. Ta sama nazwa symbolu zastępczego w dwóch poleceniach batch nie ma wspólnej wartości:

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

Ustaw typ SQL każdego parametru oraz odpowiedni rozmiar, precyzję i skalę na podstawie kontraktu bazy danych. Aby uzyskać więcej informacji, zobacz sekcję Konfigurowanie narzędzia integracji.

Wykonaj polecenia zmieniające dane

Używaj ExecuteNonQuery lub ExecuteNonQueryAsync, gdy partia zmienia dane i nie potrzebujesz wierszy wyników. Wartość zwracana to łączna liczba wierszy, na które operacja wpłynęła, gdy liczba wierszy jest dostępna. Po wykonaniu każde polecenie RecordsAffected raportuje własną liczbę.

Ten przykład cofa własne zmiany:

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

Wyświetl wiele wyników

Używaj ExecuteReader lub ExecuteReaderAsync gdy jakiekolwiek polecenie zwraca wiersze. Odczytnik udostępnia wyniki w kolejności poleceń wsadowych. Zadzwoń NextResult lub NextResultAsync do momentu, aż zwróci false.

Polecenie może wygenerować żadnego zestawu wierszy, jeden zestaw wierszy lub wiele zestawów wierszy. Nie zakładaj, że jedno NextResult wywołanie odwzorowuje się na jedno SqlBatchCommand.

Użyj zachowania polecenia

Przekaż element CommandBehavior na poziomie partii do ExecuteReader lub ExecuteReaderAsync, gdy czytnik potrzebuje dostępu sekwencyjnego lub musi zamknąć połączenie:

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

Przeciążenia na poziomie partii akceptują tylko CommandBehavior.Default, CommandBehavior.SequentialAccess, , CommandBehavior.CloseConnectionoraz kombinacje tych wartości. Inne poprawne wartości, takie jak SingleRow, KeyInfo i ArgumentOutOfRangeException, powodują zgłoszenie SchemaOnly po przekazaniu do przeciążenia na poziomie partii. Przeciążenia zachowań na poziomie wsadowym są dostępne już od Microsoft.Data.SqlClient 7.1.

Ustaw CommandBehavior dla pojedynczego polecenia dla opcji takich jak SingleRow, SchemaOnly i KeyInfo. Użyj przeciążenia na poziomie wsadu dla SequentialAccess i CloseConnection.

Skorzystaj z transakcji

SqlBatch nie wykonuje swoich poleceń atomowo. Użyj jawnej transakcji, gdy wszystkie polecenia muszą zostać zatwierdzone lub wycofane razem. Ten przykład powoduje wycofanie zmian, więc AdventureWorks pozostaje bez zmian:

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

Wywołaj Commit zamiast Rollback tylko wtedy, gdy aplikacja zaakceptuje pełną partię. Przydziel transakcję przed wykonaniem. Transakcja i batch muszą korzystać z tego samego połączenia. SqlClient nie nadpisuje asynchronicznych metod transakcji, więc BeginTransactionAsync, CommitAsync i RollbackAsync są wykonywane synchronicznie. Więcej informacji można znaleźć w artykule Korzystanie z metod transakcji SQL opartych na zadaniach.

Obsługa limitów czasu, anulowania i błędów

Ustaw Timeout w sekundach dla przetwarzania wsadowego. Batch używa ustawienia połączenia Command Timeout, jeśli je skonfigurujesz. W przeciwnym razie domyślnie to 30 sekund. Wartość 0 nie ma limitu czasu, a wartości ujemne rzucają ArgumentException. Przekaż token anulowania do asynchronicznej metody wykonania, gdy wywołujący musi przestać czekać, lub wywołaj Cancel. Anulowanie jest realizowane w miarę możliwości. Operacja może zakończyć się, zanim anulowanie zostanie zastosowane.

Gdy wykonanie się nie powiedzie, przechwyć SqlException i sprawdź każdy element w kolekcji Errors. BatchCommand identyfikuje powiązane SqlBatchCommand, gdy te informacje są dostępne:

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

Wykonywanie przez czytnik jest przyrostowe. Błąd z późniejszego polecenia wsadowego może pojawić się, gdy Read lub NextResult przejdzie do tego polecenia, więc przetwarzaj czytnik wewnątrz bloku try.

Nie rejestruj wartości parametrów zawierających poświadczenia, tokeny lub wrażliwe dane. Informacje o polach błędów i komunikatach informacyjnych można znaleźć w sekcji Obsługa błędów i komunikatów.

Treści powiązane

  • Ostatnia aktualizacja o