Introduction:

A transaction is a unit of work. You use transactions to ensure the consistency and integrity of a database. If a transaction is successful, all of the data modifications performed during the transaction are committed and made permanent. If an error occurs during a transaction, you can roll back the transaction to undo the data modifications that occurred during the transaction.

This article describes how to manage distributed transactions that can span multiple data sources in a Microsoft .NET application.

Distributed Transactions:

A distributed transaction spans multiple data sources. Distributed transactions enable you to incorporate several distinct operations, which occur on different systems, into an atomic action that either succeeds or fails completely.

Properties of a transaction:

Creating Distributed Transactions:

The .NET Framework 2.0 includes the System.Transactions namespace, which provides extensive support for distributed transactions across a range of transaction managers, including data sources and message queues. The System.Transactions namespace defines the TransactionScope class, which enables you to create and manage distributed transactions.

To create and use distributed transactions, create a TransactionScope object, and specify whether you want to create a new transaction context or enlist in an existing transaction context. You can also exclude operations from a transaction context if appropriate.

You can open multiple database connections within the same transaction scope. The transaction scope decides whether to create a local transaction or a distributed transaction. The transaction scope, automatically promotes a local transaction to a distributed transaction if necessary, based on the following rules:

Steps of creating distributed transaction:

  1. Instantiate a TransactionScope object.
  2. Open a connection with the database.
  3. Perform your database operations (insert, update & delete).
  4. If your operations completed successfully, mark your transaction as complete.
  5. Dispose The TransactionScope object.

If all the update operations succeed in a transaction scope, call the Complete method on the TransactionScope object to indicate that the transaction completed successfully. To terminate a transaction, call the Dispose method on the TransactionScope object. When you dispose a TransactionScope, the transaction is either committed or rolled back, depending on whether you called the Complete method:

TransactionScope How To:

A transaction scope defines a block of code that participates in a transaction. If the code block completes successfully, the transaction manager commits the transaction. Otherwise, the transaction manager rolls back the transaction. Bellow is a guide lines on how you can create and use a TransactionScope instance:

  1. Add a reference to the System.Transactions assembly.
  2. Import the System.Transactions namespace into your application.
  3. If you want to specify the nesting behavior for the transaction, declare a TransactionScopeOption variable, and assign it a suitable value.
  4. If you want to specify the isolation level or timeout for the transaction, create a TransactionOptions instance, and set its IsolationLevel and TimeOut properties.
  5. Create a new TransactionScope object in a using statement (a Using statement in Microsoft Visual Basic).
    Pass a TransactionScopeOption variable and a TransactionOptions object into the constructor, if appropriate.
  6. In the using block (Using block in Visual Basic), open connections to each database that you need to update, and perform update operations as required by your application. If all updates succeed, call the Complete method on the TransactionScope object
  7. Close the using block (Using block in Visual Basic) to dispose the TransactionScope object. If the transaction completed successfully, the transaction manager commits the transaction. Otherwise, the transaction manager rolls back the transaction.

Listing 1: TransactionScope C#:

using System.Transactions;

...

TransactionOptions options = new TransactionOptions();

options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;

options.Timeout = new TimeSpan(0, 2, 0);

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))

{

using (SqlConnection connA = new SqlConnection(connStringA))

{

using (SqlCommand cmdA = new SqlCommand(sqlStmtA, connA))

{

int rowsAffectedA = cmdA.ExecuteNonQuery();

if (rowsAffectedA > 0)

{

using (SqlConnection connB = new SqlConnection(connStringB))

{

using (SqlCommand cmdB = new SqlCommand(sqlStmtB, connB))

{

int rowsAffectedB = cmdB.ExecuteNonQuery();

if (rowsAffectedB > 0)

{

transactionScope.Complete();

}

} // Dispose the second command object.

} // Dispose (close) the second connection.

}

} // Dispose the first command object.

} // Dispose (close) the first connection.

} // Dispose TransactionScope object, to commit or rollback transaction.

Listing 2: TransactionScope VB.NET:

Imports System.Transactions
...
Dim options As New TransactionOptions()
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted
options.Timeout = New TimeSpan(0, 2, 0)
Using scope As New TransactionScope(TransactionScopeOption.Required, options)

Using connA As New SqlConnection(connStringA)

Using cmdA As New SqlCommand(sqlStmtA, connA)

Dim rowsAffectedA As Integer = cmdA.ExecuteNonQuery()

If (rowsAffectedA > 0) Then

Using connB As New SqlConnection(connStringB)

Using cmdB As New SqlCommand(sqlStmtA, connB)

Dim rowsAffectedB As Integer = cmdB.ExecuteNonQuery()

If (rowsAffectedB > 0) Then

transactionScope.Complete()

End If

End Using ' Dispose the second command object.

End Using ' Dispose (close) the second connection.

End If

End Using ' Dispose the first command object.

End Using ' Dispose (close) the first connection.

End Using ' Dispose TransactionScope object, to commit or rollback transaction.

Conclusion:

A transaction scope defines a block of code that participates in a transaction. If the code block completes successfully, the transaction manager commits the transaction. Otherwise, the transaction manager rolls back the transaction.

Also you should know that you can TransactionScope with any Data Provider such as Oracle or OleDB or ODBC.