Esegui più comandi con SqlBatch

Usare SqlBatch per inviare una collezione ordinata di oggetti SqlBatchCommand attraverso un singolo SqlConnection aperto. Ogni comando batch ha il proprio testo di comando, tipo di comando, parametri, comportamento del comando e risultato del numero di righe interessate.

Usalo SqlBatch quando un'applicazione ha comandi distinti che vuole inviare insieme. Usa un SqlCommand con parametri quando il lavoro è una sola istruzione o una procedura memorizzata. Utilizzare SqlBulkCopy o un parametro con valori di tabella per insiemi di righe di grandi dimensioni.

Gli esempi in questo articolo presuppongono che un oggetto connection aperto denominato SqlConnection sia connesso al database di esempio AdventureWorks.

Gli esempi di C# richiedono using Microsoft.Data.SqlClient;. Esempi che usano SqlDbType o CommandBehavior richiedono using System.Data;anche . Gli esempi che utilizzano DbDataReader richiedono using System.Data.Common;.

Supporto di versioni e piattaforme

Gli esempi sono rivolti a Microsoft.Data.SqlClient 7.1.0. Seleziona una versione del pacchetto e un framework target che il ciclo di vita del supporto di Microsoft.Data.SqlClient supporti per la tua applicazione.

SqlBatchderiva da DbBatch su .NET moderno. Microsoft.Data.SqlClient espone anche SqlBatch nei propri asset di pacchetto .NET Framework e .NET Standard 2.0. L'API pubblica può variare a seconda del framework di destinazione, quindi compila l'applicazione per ogni destinazione distribuita.

Creare un batch

Crea un batch per una connessione aperta, poi aggiungi comandi in ordine di esecuzione:

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 è l'impostazione predefinita. Imposta un comando batch su CommandType.StoredProcedure per una stored procedure. L'impostazione di CommandType.TableDirect genera ArgumentOutOfRangeException.

Aggiungi parametri ai comandi batch

I parametri appartengono a un individuo SqlBatchCommand. Lo stesso nome segnaposto in due comandi batch non condivide lo stesso valore:

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

Imposta il tipo SQL di ogni parametro e la dimensione, la precisione e la scala applicabili in base al contratto del database. Per ulteriori informazioni, vedere Configurare parametri.

Eseguire comandi di modifica dei dati

Usa ExecuteNonQuery o ExecuteNonQueryAsync quando il batch cambia dati e non hai bisogno di righe di risultati. Il valore restituito è il numero totale di righe interessate dall'operazione quando sono disponibili i conteggi delle righe. Dopo l'esecuzione RecordsAffected , ogni comando riporta il proprio conteggio.

Questo esempio annulla le sue modifiche:

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

Leggi più risultati

Usa ExecuteReader o ExecuteReaderAsync quando qualsiasi comando restituisce righe. Il lettore mostra i risultati in ordine batch-command. Chiama NextResult o NextResultAsync finché non restituisce false.

Un comando può produrre alcun set di righe, un set di righe o più set di righe. Non dare per scontato che una chiamata NextResult corrisponda a una SqlBatchCommand.

Comportamento del comando Usa

Passare un elemento di livello batch CommandBehavior a ExecuteReader o ExecuteReaderAsync quando il lettore necessita di accesso sequenziale o deve chiudere la connessione:

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

I sovraccarichi a livello di batch accettano solo CommandBehavior.Default, CommandBehavior.SequentialAccess, CommandBehavior.CloseConnection, e combinazioni di questi valori. Altri valori validi, come SingleRow, KeyInfo e ArgumentOutOfRangeException, generano SchemaOnly quando vengono passati a un sovraccarico a livello di batch. I sovraccarichi di comportamento a livello batch sono disponibili a partire da Microsoft.Data.SqlClient 7.1.

Imposta CommandBehavior su un comando individuale per opzioni come SingleRow, SchemaOnly, e KeyInfo. Usa il sovraccarico a livello batch per SequentialAccess e CloseConnection.

Utilizza una transazione

SqlBatch non rende i suoi comandi atomici. Usa una transazione esplicita quando tutti i comandi devono effettuare il commit o eseguire il rollback insieme. Questo esempio esegue il rollback in modo che AdventureWorks rimanga invariato:

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

Invoca Commit invece di Rollback solo quando l'applicazione accetta il lotto completo. Assegna la transazione prima di eseguirla. La transazione e il batch devono utilizzare la stessa connessione. SqlClient non sovrascrive i metodi di transazione asincroni, quindi BeginTransactionAsync, CommitAsync e RollbackAsync completano in sincrono. Per maggiori informazioni, vedi Usa metodi di transazione SQL basati su attività.

Gestire timeout, cancellazioni ed errori

Imposta Timeout in secondi per il lotto. Il batch usa l'impostazione Command Timeout della connessione quando ne configuri una. Altrimenti, il valore predefinito è 30 secondi. Un valore di 0 non ha limite temporale, e i valori negativi lanciano ArgumentException. Passa un token di cancellazione a un metodo di esecuzione asincrono quando il chiamante deve smettere di aspettare, oppure chiama Cancel. La cancellazione avviene nei limiti del possibile. L'operazione potrebbe concludersi prima che la cancellazione entri in vigore.

Quando l'esecuzione fallisce, individua SqlException e ispeziona ogni elemento nella sua Errors collezione. BatchCommand identifica il/la SqlBatchCommand associato/a quando tali informazioni sono disponibili:

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'esecuzione del lettore è incrementale. Un errore da un comando batch successivo potrebbe emergere quando Read o NextResult avanzano fino a quel comando, quindi elabora il lettore all'interno del blocco try.

Non registrare i valori dei parametri che contengono credenziali, token o dati sensibili. Per i campi di errore e i messaggi informativi, vedi Gestire errori e messaggi.

Contenuti correlati

  • Ultimo aggiornamento: