Performing Multiple Updates Within a Transaction

Retired Content
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
The latest Enterprise Library information can be found at the Enterprise Library site.

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 automatic (COM+) transactions. Automatic transactions simplify the programming model because they do not require that you explicitly begin a new transaction, commit to it, or abort it. Instead, at run time, you add declarative attributes to your .NET classes that specify your objects' transactional requirements. This allows you to easily configure multiple components to work within the same transaction, and it is particularly suited to transactions that must span multiple remote databases.

COM+ 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.

For more information about transactions and for guidance in selecting the appropriate model, see the .NET Data Access Architecture Guide.

QuickStart

For an extended example of how to perform multiple updates within a transaction, see the QuickStart walkthrough, Walkthrough: Performing Multiple Updates Within a Transaction.

Using ExecuteNonQuery in a Transaction

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

public bool Transfer(int transactionAmount, int sourceAccount, int destinationAccount)
{
  bool result = false;

  // Create the database object, using the default database service. The
  // default database service is determined through configuration.
  Database db = DatabaseFactory.CreateDatabase();

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

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

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

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

  using (DbConnection connection = db.CreateConnection())
  {
    connection.Open();
    DbTransaction transaction = connection.BeginTransaction();

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

      // Commit the transaction.
      transaction.Commit();

      result = true;
    }
    catch
    {
      // Roll back the transaction. 
      transaction.Rollback();
    }
    connection.Close();

    return result;
  }
} 

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

  Dim result As Boolean = False

  ' Create the database object, using the default database service. The
  ' default database service is determined through configuration.
  Dim db As Database = DatabaseFactory.CreateDatabase()

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

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

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

  Using connection As DbConnection = db.CreateConnection()
    connection.Open()
    Dim transaction As DbTransaction = connection.BeginTransaction()

    Try

      ' Credit the first account.
      db.ExecuteNonQuery(creditCommand, transaction)
      ' Debit the second account.
      db.ExecuteNonQuery(debitCommand, transaction)
      ' Commit the transaction.
      transaction.Commit()

      result = True
    Catch
      ' Roll back the transaction. 
      transaction.Rollback()
    End Try

    connection.Close()
    Return result
  End Using
End Function

Retired Content
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
The latest Enterprise Library information can be found at the Enterprise Library site.