Exekvera flera kommandon med SqlBatch

Använd SqlBatch för att skicka en ordnad samling SqlBatchCommand objekt genom en öppen SqlConnection. Varje batchkommando har sin egen kommandotext, kommandotyp, parametrar, kommandobeteende och resultatet för antalet påverkade rader.

Använd SqlBatch när en applikation har olika kommandon som den vill skicka tillsammans. Använd en parameteriserad SqlCommand när arbetet är en instruktion eller en lagrad procedur. Använd SqlBulkCopy eller en tabellvärdesparameter för stora radmängder.

Exemplen i denna artikel förutsätter att en öppen SqlConnection med namnet connection riktar sig mot AdventureWorks-exempeldatabasen.

C#-exemplen kräver using Microsoft.Data.SqlClient;. Exempel som använder SqlDbType eller CommandBehavior kräver också using System.Data; Exempel som använder DbDataReader kräver using System.Data.Common;.

Versions- och plattformsstöd

Exemplen riktar sig till Microsoft.Data.SqlClient 7.1.0. Välj en paketversion och ett målramverk som Microsoft.Data.SqlClients supportlivscykel stöder för ditt program.

SqlBatch ärver från DbBatch på modern .NET. Microsoft.Data.SqlClient tillhandahåller också SqlBatch i paketresurserna för .NET Framework och .NET Standard 2.0. Den publika ytan kan variera beroende på target framework, så kompilera applikationen för varje mål du skickar ut.

Skapa en batch

Skapa en batch för en öppen anslutning och lägg sedan till kommandon i exekveringsordning:

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 används som standard. Ställ in ett batchkommando till CommandType.StoredProcedure för en lagrad procedur. Att ställa in CommandType.TableDirect utlöser ArgumentOutOfRangeException.

Lägg till parametrar i batchkommandon

Parametrarna tillhör en individ SqlBatchCommand. Samma platshållarnamn i två batchkommandon delar inte något värde:

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

Ställ in SQL-typen för varje parameter samt tillämplig storlek, precision och skala från databaskontraktet. Mer information finns i Konfigurera parametrar.

Utför datamodifierande kommandon

Använd ExecuteNonQuery eller ExecuteNonQueryAsync när batchen ändrar data och du inte behöver resultatrader. Det returnerade värdet är det totala antalet rader som påverkas när radräkningar är tillgängliga. Efter utförandet rapporterar varje kommando RecordsAffected sitt eget antal.

Detta exempel rullar tillbaka dess ändringar:

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

Läs flera resultat

Använd ExecuteReader eller ExecuteReaderAsync när valfritt kommando returnerar rader. Läsaren visar resultat i den ordning batchkommandot anger. Ring NextResult eller NextResultAsync tills den returnerar false.

Ett kommando kan producera ingen raduppsättning, en raduppsättning eller flera raduppsättningar. Anta inte att ett NextResult samtal motsvarar ett SqlBatchCommand.

Använd kommandoets beteende

Skicka ett batchnivå-CommandBehavior till ExecuteReader eller ExecuteReaderAsync när läsaren behöver sekventiell åtkomst eller när anslutningen måste stängas:

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

Överlagringarna på batchnivå tar endast emot CommandBehavior.Default, CommandBehavior.SequentialAccess, CommandBehavior.CloseConnection och kombinationer av dessa värden. Andra giltiga värden, såsom SingleRow, KeyInfo och ArgumentOutOfRangeException, kastar SchemaOnly när de skickas till en batchnivå-överbelastning. Överlagringar för beteende på batchnivå finns tillgängliga från och med Microsoft.Data.SqlClient 7.1.

Ställ CommandBehavior in på ett individuellt kommando för alternativ som SingleRow, SchemaOnly, och KeyInfo. Använd överlagringen på batchnivå för SequentialAccess och CloseConnection.

Använd en betalningstransaktion

SqlBatch gör inte kommandona atomiska. Använd en explicit transaktion när alla kommandon måste genomföras eller rullas tillbaka tillsammans. Detta exempel återställs så att AdventureWorks förblir oförändrat:

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

Ring Commit istället för Rollback bara när applikationen accepterar hela batchen. Tilldela transaktionen innan den körs. Transaktionen och batchen måste använda samma anslutning. SqlClient åsidosätter inte de asynkrona transaktionsmetoderna, så BeginTransactionAsync, CommitAsync, och RollbackAsync slutförs synkront. För mer information, se Använd uppgiftsbaserade SQL-transaktionsmetoder.

Hantera timeouts, avbrott och fel

Ställ in Timeout i sekunder för batchen. Batchen använder anslutningens Command Timeout inställning när du konfigurerar en. Annars är standardvärdet 30 sekunder. Ett värde på 0 har ingen tidsgräns, och negativa värden kastar ArgumentException. Skicka en annulleringstoken till en asynkron exekveringsmetod när anroparen behöver sluta vänta, eller anropa Cancel. Avbokning sker i mån av möjlighet. Operationen kan avslutas innan avbrytandet träder i kraft.

När exekveringen misslyckas, fånga SqlException och inspektera varje objekt i dess Errors samling. BatchCommand anger den associerade SqlBatchCommand när den informationen är tillgänglig:

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

Läsarens körning är inkrementell. Ett fel från ett senare batchkommando kan dyka upp när Read eller NextResult går vidare till det kommandot, så bearbeta läsaren inuti blocket try .

Logga inte parametervärden som innehåller autentiseringsuppgifter, tokens eller känslig data. För felfält och informationsmeddelanden, se Hantera fel och meddelanden.

Relaterat innehåll

  • Uppdaterades senast