/// <summary> /// Asychronously executes the current collection of registered commands /// </summary> /// <remarks> /// Opens the connection, begins a transaction, initializes all commands, then serially awaits the execution of each command (if async.) Finally commits transaction. /// Upon failure, transaction is rolled-back. Null commands automatically return 1 for excution. /// </remarks> /// <returns>List of integers returned from each registered command</returns> public async Task <IEnumerable <int> > ExecuteAsync(System.Threading.CancellationToken cancellationToken = default) { List <int> retVal = new List <int>(); using (Microsoft.Data.SqlClient.SqlConnection conn = new Microsoft.Data.SqlClient.SqlConnection(_ConnectionString)) { await conn.OpenAsync(); var trans = (Microsoft.Data.SqlClient.SqlTransaction) await conn.BeginTransactionAsync(cancellationToken); try { int initialized = _Commands.Count; for (int i = 0; i < initialized; i++) { if (_Commands[i] != null) { _Commands[i].Initialize(conn, trans); } } for (int i = 0; i < _Commands.Count; i++) { if (_Commands[i] != null) { // This following line allows for chaining. // in other words, a executing command can add more commands. if (i >= initialized) { _Commands[i].Initialize(conn, trans); } if (_Commands[i] is ICommandAsync) { retVal.Add(await((ICommandAsync)_Commands[i]).ExecuteAsync(cancellationToken)); } else { retVal.Add(_Commands[i].Execute()); } } else { retVal.Add(1); } } await trans.CommitAsync(); } catch (Exception) { try { await trans.RollbackAsync(); } catch (Exception) { } throw; } finally { _Commands.Clear(); } } return((IEnumerable <int>)retVal); }