Walkthrough: Performing Multiple Updates Within a Transaction

Article
05/09/2013

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.

This walkthrough demonstrates how to perform multiple updates within a transaction. It assumes that the following stored procedures have been created in the database to which the connection string in the code refers.

CREATE PROCEDURE credit 
@AccountNo CHAR(20),
@Amount SMALLMONEY
AS
INSERT Credits
VALUES (@AccountNo, @Amount)
GO

CREATE PROCEDURE debit 
@AccountNo CHAR(20),
@Amount SMALLMONEY
AS
INSERT Debits
VALUES (@AccountNo, @Amount)
GO

To reproduce the demonstration

Configure the database. For the necessary steps, see "QuickStart Configuration" in Data Access QuickStart.
Create the database (when you are not using the Unity Integration approach). The following code uses the factory to create a Database object that has the default configuration.
```
Database db = DatabaseFactory.CreateDatabase();
```
```
'Usage
Dim db As Database = DatabaseFactory.CreateDatabase()
```

Create the command by adding the following code. It creates a DbCommand used with stored procedures. In this case, they are CreditAccount and DebitAccount. The stored procedures each take two input parameters, AccountID, and Amount.

DbCommand creditCommand = db.GetStoredProcCommand("CreditAccount");

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

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

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

'Usage
Dim creditCommand As DbCommand = db.GetStoredProcCommand("CreditAccount")

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

Dim debitCommand As DbCommand = db.GetStoredProcCommand("DebitAccount")

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

The following code shows how to start the transaction and then decide whether to commit to it or, if there was an error, perform a rollback.

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
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

Walkthrough: Performing Multiple Updates Within a Transaction

Additional resources