SqlBatch Class

Definition

Namespace:

Microsoft.Data.SqlClient

Assembly:

Microsoft.Data.SqlClient.dll

Package:

Microsoft.Data.SqlClient v5.2.0

public ref class SqlBatch : System::Data::Common::DbBatch

public class SqlBatch : System.Data.Common.DbBatch

type SqlBatch = class
    inherit DbBatch

Public Class SqlBatch
Inherits DbBatch

Inheritance

DbBatch

SqlBatch

Examples

The following example creates a SqlConnection and a SqlBatch, then adds multiple SqlBatchCommand objects to the batch. It then executes the batch, creating a SqlDataReader. The example reads through the results of the batch commands, writing them to the console. Finally, the example closes the SqlDataReader and then the SqlConnection as the using blocks fall out of scope.

using Microsoft.Data.SqlClient;

class Program
{
    static void Main()
    {
        string str = "Data Source=(local);Initial Catalog=Northwind;"
        + "Integrated Security=SSPI;Encrypt=False";
        RunBatch(str);
    }

    static void RunBatch(string connString)
    {
        using var connection = new SqlConnection(connString);
        connection.Open();

        var batch = new SqlBatch(connection);

        const int count = 10;
        const string parameterName = "parameter";
        for (int i = 0; i < count; i++)
        {
            var batchCommand = new SqlBatchCommand($"SELECT @{parameterName} as value");
            batchCommand.Parameters.Add(new SqlParameter(parameterName, i));
            batch.BatchCommands.Add(batchCommand);
        }

        // Optionally Prepare
        batch.Prepare();

        var results = new List<int>(count);
        using (SqlDataReader reader = batch.ExecuteReader())
        {
            do
            {
                while (reader.Read())
                {
                    results.Add(reader.GetFieldValue<int>(0));
                }
            } while (reader.NextResult());
        }
        Console.WriteLine(string.Join(", ", results));
    }
}

Constructors

SqlBatch()	Initializes a new SqlBatch.
SqlBatch(SqlConnection, SqlTransaction)	Initializes a new SqlBatch.

Properties

BatchCommands	The list of commands contained in the batch in a SqlBatchCommandCollection.
Commands	The list of commands contained in the batch in a IList of SqlBatchCommand objects.
Connection	Gets or sets the SqlConnection used by this instance of the SqlBatch .
DbBatchCommands	Gets the collection of SqlBatchCommand objects.
DbConnection	Gets or sets the DbConnection used by this SqlBatch.
DbTransaction	Gets or sets the SqlTransaction within which this SqlBatch object executes.
Timeout	Gets or sets the wait time (in seconds) before terminating the attempt to execute the batch and generating an error.
Transaction	Gets or sets the SqlTransaction within which the SqlBatch commands execute.

Methods

Cancel()	Attempts to cancel the execution of a SqlBatch.
CreateDbBatchCommand()	Creates a new instance of a SqlBatchCommand object.
Dispose()	Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
ExecuteDbDataReader(CommandBehavior)	Executes the batch against its connection, returning a SqlDataReader which can be used to access the results.
ExecuteDbDataReaderAsync(CommandBehavior, CancellationToken)	This implementation invokes the ExecuteReaderAsync() method and returns a completed task. The default implementation will return a cancelled task if passed an already cancelled cancellation token. This method accepts a cancellation token that can be used to request the operation to be cancelled early.
ExecuteNonQuery()	Executes the batch against its connection object, returning the total number of rows affected across all the batch commands.
ExecuteNonQueryAsync(CancellationToken)	This is the asynchronous version of ExecuteNonQuery(). The implementation invokes the ExecuteNonQueryAsync(CancellationToken) method and returns a completed task. The default implementation will return a cancelled task if passed an already cancelled cancellation token. Do not invoke other methods and properties of the DbCommand object until the returned Task is complete.
ExecuteReader()	Sends the Commands to the Connection and builds a SqlDataReader .
ExecuteReaderAsync(CancellationToken)	An asynchronous version of ExecuteReader() , which sends the Commands to the Connection and builds a SqlDataReader. Exceptions will be reported via the returned Task object.
ExecuteScalar()	Executes the batch and returns the first column of the first row in the first returned result set. All other columns, rows and resultsets are ignored.
ExecuteScalarAsync(CancellationToken)	An asynchronous version of ExecuteScalar(), which executes the batch and returns the first column of the first row in the first returned result set. All other columns, rows and result sets are ignored.
Prepare()	Creates a prepared (or compiled) version of the batch, or of each of its commands, on the data source.
PrepareAsync(CancellationToken)	Asynchronously creates a prepared (or compiled) version of the batch, or of each of its commands, on the data source.