Managing Transactions using TransactionScope

Published: 06 Oct 2006
By: Muhammad Mosa

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.

Introduction

This article describes how to manage transactions in Microsoft .NET application where different data layers class should be used together. This article assume that you have a knowledge about how to build data layer and business rule layer as well as make them work together. Also you should have an idea about TransactionScope and know how to use it. Return to this reference for more information.

Usage Scenario

Suppose we have the following Database tables:

You want to make a form that enables the user to enter the department name, and name of three employees to be inserted for the first time.
You build 2 data layer classes for each table. Each class will contain Insert method. But both methods are separated and cannot be joined by normal SqlTransaction object as you may not have access to the connection object in your business rule layer. Also after you build your data layer, you may find that you need to call one of  the methods you already built in one data layer class in a transaction initiated by another class.
In our scenario, we need to insert a department and some of the employees in one transaction. For simplification, we are going to use TableAdapters as our data layer.

Building Table Adapters

 Using Visual Studio.Net 2005 DataSet Designer we created the following 2 DataTables with their TableAdapters:

Note: Attached to this article stored procedures used by the table Adapters.
Each TableAdapter has Insert method, each method inserts a record in the underlying database table. If you need to insert a department with set employees as one transaction, you need to call Insert method of the DepartmentAdapter, then make multiple calls to the Insert method of the EmployeeAdapter, but you need join the 2 methods in a transaction.
We are going to use TransactionScope object to manage this transaction without the need to create a SqlTransaction object and assigned to each TableAdapter Connection.

Using TransactionScope

TransactionScope object has a method named Complete() if this method is called, the transaction will be committed, if this method didn't call at all, the transaction will rollback after the TransactionScope object is dispose.
We will instantiate a TransactionScope object in using statement, and then make the calls inside of insert methods as the following:

public static int AddDepartmentWithEmployees(Department dept)
{

int res = 0;

DepartmentAdapter deptAdapter = new DepartmentAdapter();
EmployeeAdapter empAdapter = new EmployeeAdapter();
using (TransactionScope txScope = new TransactionScope())
{

res += deptAdapter.Insert(dept.DepartmentName);
//Custom method made to return Department ID after inserting the department "Identity Column"
dept.DepartmentID = deptAdapter.GetInsertReturnValue();
foreach(Employee emp in dept.Employees)
{

emp.EmployeeDeptID = dept.DepartmentID;
res += empAdapter.Insert(emp.EmployeeName, emp.EmployeeDeptID);

}
txScope.Complete();

}
return res;

}

If and exception occurred the transaction will rollback because the TransactionScope object will be disposed after the using statement is completed.
As you can see, you just need to make your methods call inside the using statement that instantiate a TransactionScope, and you can gain benefit of joining your existing methods in transaction without the need to build new method to support transaction.

Summary

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.

References

Managing Distributed Transactions with ADO.NET 2.0 using TransactionScope

Downloads

TransactionScopeDemo (Visual Studio 2005 project)
Stored procedures Scripts