Browse DevX
Sign up for e-mail newsletters from DevX


System.Transactions and ADO.NET 2.0 : Page 3

If you use DataAdapters and love their automatic connection-opening and closing capabilities—and ever use transactions, you should first understand what's really going on underneath the covers.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Everybody Likes a Demo
As hardcore techies, you no doubt like to see code to understand all this theory, so roll up your sleeves and dive in. I'll show you how to write a quick code sample demonstrating a distributed transaction involving two databases.

First you need two databases. You can easily set them up using the following T-SQL script.

Create Database db1 Go use db1; Create Table Test1 ( Test1 varchar(50) ); Create Database db2; Go use db2; Create Table Test2 ( Test2 varchar(50) );

With this database setup, suppose you want to insert a simple test value into both tables-Test1 and Test2. If either fails, you want to roll back the entire transaction.

The following code will insert into a single database. Note there is nothing transactional about this code yet. This is plain vanilla ADO.NET code.

using (SqlConnection conn1 = new SqlConnection(connStr1)) { SqlCommand cmd1 = conn1.CreateCommand(); cmd1.CommandText = "Insert Into Test1(Test1) values ('xxx')"; conn1.Open(); cmd1.ExecuteNonQuery(); conn1.Close(); }

If you have two such blocks—one for each database, how do you make the entire operation transactional? That, as it turns out, is rather straightforward. As you can see in the code below, you simply wrap two such blocks inside one TransactionScope block and put TransactionScope.Complete at the very end.

using (TransactionScope txScope = new TransactionScope()) { TransactionInformation info = Transaction.Current.TransactionInformation ; using (SqlConnection conn1 = new SqlConnection(connStr1)) { SqlCommand cmd1 = conn1.CreateCommand(); cmd1.CommandText = "Insert Into Test1(Test1) values ('xxx')"; conn1.Open(); cmd1.ExecuteNonQuery(); conn1.Close(); } using (SqlConnection conn2 = new SqlConnection(connStr2)) { SqlCommand cmd2 = conn2.CreateCommand(); cmd2.CommandText = "Insert Into Test2(Test2) values ('yyy')"; Console.WriteLine( "Before:" + info.DistributedIdentifier); conn1.Open(); Console.WriteLine( "After:" + info.DistributedIdentifier); cmd2.ExecuteNonQuery(); conn2.Close(); } txScope.Complete(); }

Using this methodology, your code doesn't change one bit—you just wrap everything inside a single TransactionScope instance. There is some diagnostic information that you're peeping into, however, using the TransactionInformation variable you declared. Now run the above code and check the output.

You should see the following output.

Before:00000000-0000-0000-0000-000000000000 After:376683f9-bde3-4303-81be-b820bbe18af9

Interestingly, there isn't a valid distributed identifier associated with the transaction until the second SqlConnection instance is opened. In fact, if you go to Control Panel, choose Administrative Tools, then choose Component Services—surprise surprise—the GUID shown next to the current running transaction matches the output your program produces. See Figure 1.

Figure 1: Transaction escalated to MSDTC showing a valid GUID.
At this point the transaction has been promoted to MSDTC and its isolation level has also been escalated. You can verify that by attempting to run a select query using the default ReadCommitted isolation level from SQL Server Management Studio. You will find that as soon as the second SqlConnection, conn2, is opened, the inserted records on conn1 are no longer accessible unless you use the NOLOCK query hint or the ReadUncommitted isolation level.

The important take-away from this example is the ease by which you can wrap a number of operations within one transaction. But there are a number of questions to consider:

  • If it is really this simple, why don't I wrap all my data access within a single transaction scope? If you think I should do that, why isn't my code transactional by default?
  • If the SqlConnection instance, conn1, has been closed before the transaction was actually committed, how in the world does it manage to roll the transaction back in the event of failure?
  • The above code demonstrated using ExecuteNonQuery, but what if I wanted to wrap DataAdapter.Update or use a TableAdapter and wrap those operations within a single transaction?
I can answer the first question rather easily. If it were that easy, then your code would have definitely been transactional by default. Unfortunately, a one-size-fits-all approach cannot work when you are dealing with disconnected data updates. This is evident when you call DataAdapter.Update, which ends up running multiple commands and saves your data in the database. Suppose that your DataAdapter tries to execute five commands and the third command errors out. Should you continue with the update? Should you roll back the first and second commands? Should you stop right there and do nothing? Should you revert the data back in the DataSet? Should you communicate the successful updates along with the failed rows? How do you extract the newly retrieved values out of the database and yet not mess up the row states for the rows that did not end up getting saved?

Oh my, that's a lot of questions and possibilities. Needless to say, you need enough control to fine tune the exact behavior for your needs, and a one-size-fits-all approach just cannot work.

I'll show you another practical example to answer the rest of the two questions.

Thanks for your registration, follow us on our social networks to keep up-to-date