コード例 #1
0
        /// <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);
        }
コード例 #2
0
        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);
            }
        }