Published: 06 Oct 2006
By: Muhammad Mosa
Download Sample Code

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


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.


Managing Distributed Transactions with ADO.NET 2.0 using TransactionScope

<<  Previous Article Continue reading and see our next or previous articles Next Article >>

About Muhammad Mosa

Sorry, no bio is available

This author has published 8 articles on DotNetSlackers. View other articles or the complete profile here.

Other articles in this category

A Feature-driven Comparison of Entity Framework and NHibernate-2nd Level Caching
Where would you place caching in your layered solution? Two main patterns exist for caching in appli...
A Feature-driven Comparison of Entity Framework and NHibernate - Queries
Let's explore what Entity Framework and NHibernate has to offer when it comes to their query capabil...
A Feature-driven Comparison of Entity Framework and NHibernate—Fetch Plans
This article is about fetch plans-a recognized and common way for developers to instruct the O/RM ab...
A Feature-driven Comparison of Entity Framework and NHibernate - Self-tracking entities
In this article, Dino Esposito introduces self-tracking entities.
.NET type generation for NHibernate mapping collections
An overview of .NET type generation for NHibernate mapping collections.

You might also be interested in the following related blog posts

SOA patterns - Reservations read more
Simpler Transactions read more
Write Atomic Transactions in .NET read more
Quicken vs. Money read more
System.Transactions.TransactionScope and IDbTransaction read more
200: What is LTM: Lightweight Transaction Manager, and how does that help read more
100: System.Transactions - and why should you care to know about it. read more


Subject Author Date
placeholder Excellent article Gordon Shumway 10/31/2007 4:58 PM
Excellent article Gordon Shumway 10/31/2007 4:58 PM
placeholder RE: Excellent article Muhammad Mosa 10/31/2007 5:30 PM
TransactionScope does not work for with Anjum Rizwi 2/5/2008 1:51 AM

Please login to rate or to leave a comment.