Maintaining Transactions in Enterprise applications using ADO.NET Transactions

In ACID transactions, Atomicity refers to the ability to guarantee that either all of the tasks of a transaction are performed or none of them are. In .NET framework 2.0 Microsoft has introduces System.Transactions namespace, by using classes of that you can create and participate in a transaction (local or distributed) with one or multiple participants. This article explains usage of System.Transactions to update tables of two different databases with two different data providers (SqlClient and OleDB).

Transactions in .Net - Past, Present and Future

ADO.NET 1.x offers an explicit transaction management by connection object. The developer is responsible for explicitly starting and managing the transaction. You need to call the BeginTransaction method of the Connection object to mark the start of the transaction. The BeginTransaction method returns a reference to the Transaction. This reference is assigned to the Command objects that are enlisted in the transaction. After executing the SQL commands, you have to call the Commit method of the Transaction object to complete the transaction, or call the Rollback method to cancel the transaction. Problem with this approach is that it is applicable to a single database or a single transactional resource.

If you have to maintain the transaction within two databases, System.EnterpriseServices helps you for this. A serviced component is the mechanism that enables COM+ services to be available to .NET Framework classes. Problem with this approach is that its uses old COM+ model.

While System.EnterpriseServices is already there, .Net framework 2.0 comes with System.Transactions namespace. The System.Transactions infrastructure makes transactional programming simple and efficient throughout the platform by supporting transactions initiated in SQL Server, ADO.NET, MSMQ, and the Microsoft Distributed Transaction Coordinator (MSDTC). It provides both an explicit programming model based on the Transaction class, as well as an implicit programming model using the TransactionScope class, in which transactions are automatically managed by the infrastructure.

In .Net framework 3.0, Microsoft is going further by introducing Indigo, Unification of Microsoft's Distributed Computing Technologies. .NET Framework distributed technologies such as ASP.NET Web services, Enterprise Services, .NET Remoting, COM+/MSMQ can be used from within Indigo applications. Indigo can also interoperate on the wire with ASP.NET Web services, Enterprise Services, COM+/MSMQ, and any applications built on infrastructure that conforms to Web services standards. You can search more about Indigo on Microsoft web site.

Using System.Transactions

System.Transactions is a new feature of the .NET Framework 2.0 (Visual Studio 2005), which I like most. The most important class in System.Transactions is TransactionScope class. This is used to scope a code section with a transaction. Complete() method of TransactionScope is used to commit the transaction. If code does not able to reach Complete() method, transaction will be rolled back. You can also provide some extra instruction to TransactionScope by using TransactionOptions as we will see the example here.

Open Visual Studio 2005 and create a new web site. Add reference to System.Transactions.

To the web site, add new web page and name it as DBTransaction.aspx. Open the DBTransaction.aspx in HTML and paste the following code after @page directive (Remove all the auto generated code beside @page line).

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Untitled Page</title>

</head>

<body>

<div>

<asp:Label ID="lblHeading" runat="server" Font-Names="Verdana" Font-Size="Large"

Height="27px" Text="Transaction Result:" Width="181px"></asp:Label><br />

<asp:Label ID="lblResult" runat="server" Font-Names="Verdana" Font-Size="XX-Small"

Height="27px" Text="Label" Width="436px"></asp:Label> </div>

</form>

</body>

</html>

In the design pane you will see the output like this.

Open DBTransaction.aspx.cs files and add following lines to import namespaces

using System.Transactions;
using System.Data.SqlClient;
using System.Data.OleDb;

Enter the following code on Page_Load Event

protected void Page_Load(object sender, EventArgs e)

{

lblResult.Text = DataBaseTransaction();

}

Add private method DataBaseTransaction.

private String DataBaseTransaction()

{

String strResult = "Updated Successfully....";

int return1 = 0;

int return2 = 0;

TransactionOptions options;

TransactionScope scope = null;

try

{

options = new TransactionOptions();

options.IsolationLevel =

System.Transactions.IsolationLevel.ReadCommitted;

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

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

return1 = UpdateDatabase1UsingSqlClient();

if (return1 > 0)

{

return2 = UpdateDatabase2UsingOleDb();

if (return2 > 0)

{

scope.Complete();

}

catch (Exception exp)

{

strResult = exp.Message;

}

finally

{

scope.Dispose();

}

return strResult;

}

Add private method UpdateDatabase1UsingSqlClient.

private int UpdateDatabase1UsingSqlClient()

{

string sqlConnString = "Data Source=AnandThakur;Initial Catalog=Test1;user=sa;password=anand";

string sqlStatement = "insert into Products (Code,Description,Name)" +

" values ('0001','Monitor','Flat Monitor')";

SqlConnection sqlConn = new SqlConnection(sqlConnString);

sqlConn.Open();

SqlCommand sqlCmd = new SqlCommand(sqlStatement, sqlConn);

return sqlCmd.ExecuteNonQuery();

}

SQL for the table used by above method in database Test1 is as follows.

CREATE TABLE [dbo].[Products] (
[Code] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

Add private method UpdateDatabase2UsingOleDb.

private int UpdateDatabase2UsingOleDb()

{

string oleConnString = "Provider=SQLOLEDB.1;Data Source=AnandThakur;Initial Catalog=Test2; Password=anand;User ID=sa";

string oleStatement = "insert into Products (Code,Description,Name)" +

" values ('0001','Monitor','Flat Monitor')";

OleDbConnection oleConn = new OleDbConnection(oleConnString);

oleConn.Open();

OleDbCommand oleCmd = new OleDbCommand(oleStatement, oleConn);

return oleCmd.ExecuteNonQuery();

}

SQL for the table used by above method in database Test2 is as follows. We have same table structure, but we are keeping Code as a primary key here.

ALTER TABLE [dbo].[Products] ADD
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED
(
[Code]
) ON [PRIMARY]
GO

System.Transactions uses Microsoft Distributed Transaction Coordinator (MSDTC). Please make sure that you have started Microsoft Distributed Transaction Coordinator service from Windows Services

Run the web application by keeping DBTransaction.aspx as start page. You will get the following output. Now see the database tables. You will get one record in each of the tables

Run the web application by again. You will get the following output.

You will not find second record entry in either of the tables. As primary key exception is there in second table, At this point transaction object is forcing atomicity by not allowing entering the record in first table if the second table entry fails.

Conclusion

You can also update two databases, one in oracle (Oracle 10g) and one in SQL Server. When you develop a transactional application using the classes provided by System.Transactions, you do not need to worry about what kind of transactions you need, or the transaction manager or database involved. The System.Transactions infrastructure automatically manages these for you. Transactions give you the ability to incorporate several distinct operations occurring on different systems into a single pass or fail action.