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.
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.
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
Sorry, no bio is available
This author has published 8 articles on DotNetSlackers. View other articles or the complete profile here.
Please login to rate or to leave a comment.