/// <summary>
/// Executes the <paramref name="batchCommandText"/> against the <paramref name="connection"/> and returns the number of rows affected for each individual command.
/// </summary>
/// <param name="connection">An open connection to the database.</param>
/// <param name="batchCommandText">The batch SQL command to execute.</param>
/// <param name="commandTimeoutInSeconds">OPTIONAL value with the wait time, in seconds, before terminating an attempt to execute the command and generating an error. DEFAULT is 30 seconds. A value of 0 indicates no limit (an attempt to execute a command will wait indefinitely).</param>
/// <param name="transaction">OPTIONAL transaction within which the command will execute. DEFAULT is null (no transaction).</param>
/// <remarks>
/// This method does not support parameters. Parameter object must be unique per command - parameters cannot be reused across commands.
/// All commands in the batch are expected to be Text commands.
/// </remarks>
/// <returns>
/// The number of rows affected for each individual command that is executed.
/// </returns>
public static async Task <IReadOnlyList <int> > ExecuteNonQueryBatchAsync(
this SqlConnection connection,
string batchCommandText,
int commandTimeoutInSeconds = 30,
SqlTransaction transaction = null)
{
if (batchCommandText == null)
{
throw new ArgumentNullException(nameof(batchCommandText));
}
if (string.IsNullOrWhiteSpace(batchCommandText))
{
throw new ArgumentException(Invariant($"'{nameof(batchCommandText)}' is white space"));
}
var statements = SqlBatchStatementSplitter.SplitSqlAndRemoveEmptyStatements(batchCommandText);
if (!statements.Any())
{
throw new InvalidOperationException(Invariant($"no individual commands found in {nameof(batchCommandText)}"));
}
var result = new List <int>();
foreach (var statement in statements)
{
var rowsAffected = await connection.ExecuteNonQueryAsync(statement, commandTimeoutInSeconds, transaction : transaction);
result.Add(rowsAffected);
}
return(result);
}
public static IReadOnlyList <int> ExecuteNonQueryBatch(
this string connectionString,
string batchCommandText,
int commandTimeoutInSeconds = 30,
SqlInfoMessageEventHandler sqlInfoMessageEventHandler = null)
{
if (batchCommandText == null)
{
throw new ArgumentNullException(nameof(batchCommandText));
}
if (string.IsNullOrWhiteSpace(batchCommandText))
{
throw new ArgumentException(Invariant($"'{nameof(batchCommandText)}' is white space"));
}
var statements = SqlBatchStatementSplitter.SplitSqlAndRemoveEmptyStatements(batchCommandText);
if (!statements.Any())
{
throw new InvalidOperationException(Invariant($"No individual commands found in {nameof(batchCommandText)}."));
}
using (var connection = connectionString.OpenSqlConnection(sqlInfoMessageEventHandler))
{
var result = connection.ExecuteNonQueryBatch(batchCommandText, commandTimeoutInSeconds);
connection.Close();
return(result);
}
}