Performing Multiple Updates Within a Transaction

When an application executes multiple operations against a database, a common requirement is that all of the operations must succeed or the database must roll back to its original state (that is, its state before the operations began). This all-or-nothing requirement is referred to as a transaction. Transactions ensure the integrity of a database system's state. For example, in a classic banking scenario, an application must debit one account and credit another with a particular amount of money. For proper accounting, it is essential that either both operations succeed or neither operation succeeds. This means that both operations should be performed in the context of a single transaction.

Typical Goals

The typical goal in this scenario is that all updates to a database must succeed or none of them should be performed.

Solution

There are several ways to perform database methods within a transaction. The solution shown here demonstrates how to use the overload of the ExecuteNonQuery method in the context of a manual transaction, established through ADO.NET transaction support.

You can also directly control manual transactions by using Transact-SQL statements in your stored procedures. For example, you could perform transactional operations with a single stored procedure that uses Transact-SQL statements such as BEGIN TRANSACTION, END TRANSACTION, and ROLLBACK TRANSACTION.

Another approach is to use the .NET TransactionScope mechanism to implement automatic transactions. This approach simplifies the programming model because it does not require that you explicitly begin a new transaction, commit to it, or abort it. Automatic transactions are particularly suited to transactions that span multiple remote databases. However, they incur additional run time overhead. You should take this into account when you consider how well you need your application to perform.

Using ExecuteNonQuery in a Transaction

The following code shows how to use the ExecuteNonQuery method in a transaction.

public bool Transfer(Database db, int transactionAmount, 
                     int sourceAccount, int destinationAccount)
{
  // The default database service is determined through configuration
  // and passed to the method as a parameter that can be generated 
  // automatically through injection when the application initializes.

  bool result = false;

  // Two operations: one to credit an account and one to debit another account.
  string sql = "CreditAccount";
  DbCommand creditCommand = db.GetStoredProcCommand(sql);

  db.AddInParameter(creditCommand, "AccountID", DbType.Int32, sourceAccount);
  db.AddInParameter(creditCommand, "Amount", DbType.Int32, transactionAmount);

  sql = "DebitAccount";
  DbCommand debitCommand = db.GetStoredProcCommand(sql);

  db.AddInParameter(debitCommand, "AccountID", DbType.Int32, destinationAccount);
  db.AddInParameter(debitCommand, "Amount", DbType.Int32, transactionAmount);

  using (DbConnection conn = db.CreateConnection())
  {
    conn.Open();
    DbTransaction trans = conn.BeginTransaction();

    try
    {
      // Credit the first account.
      db.ExecuteNonQuery(creditCommand, trans);
      // Debit the second account.
      db.ExecuteNonQuery(debitCommand, trans);

      // Commit the transaction.
      trans.Commit();
                    
      result = true;
    }
    catch
    {
      // Roll back the transaction. 
      trans.Rollback();
    }
    conn.Close();
                
    return result;
  }
} 

'Usage
Public Function Transfer(ByRef db As Database, _
                         ByRef transactionAmount As Integer, _
                         ByRef sourceAccount As Integer, _
                         ByRef destinationAccount As Integer) As Boolean

  ' The default database service is determined through configuration
  ' and passed to the method as a parameter that can be generated 
  ' automatically through injection when the application initializes.

  Dim result As Boolean = False

  ' Two operations: one to credit an account and one to debit another account.
  Dim sql As String = "CreditAccount"
  Dim creditCommand As DbCommand = db.GetStoredProcCommand(sql)

  db.AddInParameter(creditCommand, "AccountID", DbType.Int32, sourceAccount)
  db.AddInParameter(creditCommand, "Amount", DbType.Int32, transactionAmount)

  sql = "DebitAccount"
  Dim debitCommand As DbCommand = db.GetStoredProcCommand(sql)
  db.AddInParameter(debitCommand, "AccountID", DbType.Int32, destinationAccount)
  db.AddInParameter(debitCommand, "Amount", DbType.Int32, transactionAmount)

  Using conn As DbConnection = db.CreateConnection()

    conn.Open()
    Dim trans As DbTransaction = conn.BeginTransaction()

    Try
      ' Credit the first account.
      db.ExecuteNonQuery(creditCommand, trans)
      ' Debit the second account.
      db.ExecuteNonQuery(debitCommand, trans)
      ' Commit the transaction.
      trans.Commit()
      result = True
    Catch
      ' Roll back the transaction. 
      trans.Rollback()
    End Try
    conn.Close()

    Return result

  End Using

End Function