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:

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

About Muhammad Mosa

Sorry, no bio is available This author has published 8 articles on DotNetSlackers. View other articles or the complete profile here.

---

Discussion

Subject	Author	Date
Excellent article	Gordon Shumway	10/31/2007 4:58 PM
Thankyou - this was very helpful. But one thing - shouldnt you you give the reference to the namespace where "TransactionScope" can be found? Or maybe its me thats missing something :-) Reply | Permanent link
Excellent article	Gordon Shumway	10/31/2007 4:58 PM
Thankyou - this was very helpful. But one thing - shouldnt you you give the reference to the namespace where "TransactionScope" can be found? Or maybe its me thats missing something :-) Reply | Permanent link
RE: Excellent article	Muhammad Mosa	10/31/2007 5:30 PM
it is in System.Transactions namespace Muhammad M. Mosa Soliman Software Engineer Reply | Permanent link
TransactionScope does not work for with Asp.net/Oracle	Anjum Rizwi	2/5/2008 1:51 AM
can you convert your demo app in asp.net/oracle. It is not working for me when I sent development server is IIS. Reply | Permanent link

---

Please login to rate or to leave a comment.