Transaction Processing in ADO.NET 2.0 - (Dotnet Tutorials)

Introduction:
It seems like just yesterday that Microsoft introduced a brand new data access technology that brought a ton of power as well as a decent sized learning curve. When ADO 2.xxx turned into ADO.NET, things changed dramatically. It took some getting used to to feel comfortable using the whole 'disconnected' model, but the "cool" factor made it all worth while. When .NET Framework 1.1 came out, very little changed in regard to what you needed to learn or what you could do with it. Well, we're turning another corner and right off in the distance is ADO.NET 2.0. The differences between ADO.NET 2.0 and < ADO.NET 2.0 are pretty profound, and you'll definitely have to spend some time learning new features if you want to take advantage of its power. I think you'll find that it's well worth the effort.
So you're probably thinking "Oh wow, big deal, new features available in a new release of the product, I never would have guessed." Well, there's a lot of cool new features all over the place, too much to discuss in a single article, but one area that really stands out is transaction processing. To say that there's a lot of bang for the buck would be a real understatement, and if you've had to work extensively with transactions, both local and/or distributed in the past, I think you'll really be impressed with what Microsoft has done.

Transactions :
One of the more significant areas of improvement is in transaction processing. It's still early in beta so nothing is written in stone, but by and large things got a LOT easier. In the original versions of ADO.NET, you could implement transactions a few different ways. If your implementation context was a single database, you could instantiate an instance of one of the IDBTransaction objects, attach it to your connection, process what you wanted, and either commit or rollback depending on the results. By virtue of the fact that this was done client side, many people found that it wasn't all that they were hoping. A similar method would entail rolling your transaction processing into a stored procedure and simply invoking the procedure. On the whole I think this produced some more reliable results, but it had some problems too - namely that it was highly coupled with the specific database implementation you were using. So if you needed to move a file for instance, send a success message to a MSMQ Message Queue, and then update a SQL Server database, you were going to have to do a fair amount of work. This process has been simplified so much it's hard to believe it actually works. Anyway, I'll dive into an example in a second, but let me make sure that the distinction I'm about to draw is clear: Now, just as before, you have two choices with regard to transactions, Local and Distributed. Distributed transactions span multiple items whereas local transactions typically span just one. Either way you can take advantage of the TransactionScope object to simplify your life.

Simple Transaction Under ADO.NET 2.0:
bool IsConsistent = false;
using (System.Transactions.TransactionScope ts = new System.Transactions.TransactionScope())
{
SqlConnection cn = newSqlConnection(CONNECTION_STRING );
string sql = "DELETE Categories";
SqlCommand cmd = newSqlCommand(sql, cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
//Based on this property the transaction will commit if
//successful. If it fails however, this property will
//not be set and the transaction will not commit.
ts.Consistent = IsConsistent;
}
Basically, I created a query which whacked and entire table, wrapped it in a transaction and ensured that it wouldn't commit. In doing so, the table remains fully in tact just as it was before calling ExcecutNonQuery. Now, what's so different about this? Well, notice that the connection itself is confined within the scope so it automatically participates in the transaction. All that is required to commit or rollback the transaction is specifying True or False for consistent. A more realistic example can be illustrated by making a few minor changes:

A Slightly Improved Implementation:
bool IsConsistent = false;
using (System.Transactions.TransactionScope ts = new System.Transactions.TransactionScope())
{
SqlConnection cn = newSqlConnection(CONNECTION_STRING );
string sql = "DELETE Categories";
SqlCommand cmd = newSqlCommand(sql, cn);
cn.Open();
try
{
cmd.ExecuteNonQuery();
IsConsistent = true;
}
catch (SqlException ex)
{
//You can specify additional error handling here
}
cn.Close();
//Again, since this was set to false originally it will only
//commit if it worked.
ts.Consistent = IsConsitent;
}
This example is more in line with the earlier version of ADO.NET's transaction processing, namely, if everything works then commit, else rollback. This is hardly climactic in any practical sense because even though it's a lot more concise than previous versions, you're not really talking about any dramatic reduction in complexity of code. To see the elegance and power of this object you really need to examine a distributed scenario. Say that you have some really complex situation where you have a table in a Yukon database that you want to clear, and then you have a corresponding table in a separate database that needs to be cleared as well. Furthermore, assume that this is an all or nothing deal and there has to be complete success or complete failure.
bool IsConsistent = false;
using (TransactionScope ts = newTransactionScope())
{
using (SqlConnection cn = newSqlConnection(YUKON_CONNECTION_STRING))
{
string sql = "DELETE Products";
SqlCommand cmd = newSqlCommand(sql, cn);
cn.Open();
try
{
cmd.ExecuteNonQuery();
using(SqlConnection cnn = newSqlConnection(CONNECTION_STRING))
{
string sql_2 = "DELETE Categories";
SqlCommand cmd2 = newSqlCommand(sql_2, cnn);
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
IsConsistent = true;
}
catch (SqlException ex)
{
//You can specify additional error handling here
}
cn.Close();
}
ts.Consistent = IsConsistent;
}
Now, what I'm about to discuss is pretty amazing, and I can't in clear conscience take credit for it. Angel Saenz-Badillos was the first one to tip me off to how all of this works and worked with me through a few examples. It's laughable at the moment, but the first time I heard of this, my initial response was something like "Ok, that'll save me 3 lines of code - great" I couldn't believe that it could possibly live up to the hype, and it took working with it a few times before my little brain could process it.
So here's the deal stated simply. Wrap everything in a TransactionScope object, and it takes care of everything else for you. What does that mean? Well, it will determine if you need a local or a distributed transaction, and it will react accordingly. It will enlist where necessary and process locally otherwise. Notice that the first connection string points to a Yukon (SQL Server 2005) database. As such, you can take advantage of "Delegation". This is a fancy way of saying "We don't need no stinking distributed transaction, we're using Yukon" and thereafter not using it unless it becomes necessary. Now, if you cut out the inner statements where you fire the query pointing to ANOTHER database, everything would be done under the purview of a local transaction. However, as soon as we try to hit another database, we're back in distributed transaction mode. Now, the natural assumption is that they are run under two different contexts, right? After all, you need to promote to DT mode once you try to hit the second database, but prior to that you were running locally. Actually, the answer is NO, you don't need to do squat. That's what's so amazing about it. As soon as the code gets to a point where it won't be running locally, everything is promoted accordingly. And you don't just have support for SQL Server here - Oracle and MSMQ are both currently supported, and there's a REALLY strong probability that File System support will be included in the final release.
So, does the same principle apply here if you were connecting to Oracle or MSMQ instead of SQL Server 2000? Yes, and for all intents and purposes the transactional component here would behave identically. If you've used COM+ before, then you no doubt realize how much easier this is. If you haven't, just put in Distributed Transaction COM+ into Google or read up on it, and you'll quickly see how much more simple this makes things. Even if you aren't familiar with either of those scenarios, just look to the unstable nature of client side transaction processing with ADO.NET and you'll quickly see this is pretty darned impressive.
As cool as this is, there's no doubt some folks out there won't be impressed. Well, fine. You aren't precluded from doing anything you otherwise would by employing the TransactionScope; heck you don't even have to use it. If you like writing tons of code, and you get a sense of security by doing unnecessary tasks, knock yourself out. Or even if you're not that much of a hard-core ludite, but you want to do things manually, here's how you do it:

Client Side Transaction under 1.x Framework:
privatebool OldSchool()
{
bool IsConsistent = false;
ICommittableTransaction oldSchoolTrans = Transaction.Create();
using (SqlConnection cn = newSqlConnection(CONNECTION_STRING))
{
string sql = "DELETE Categories";
SqlCommand cmd = newSqlCommand(sql, cn);
cn.Open();
cn.EnlistTransaction((ITransaction)oldSchoolTrans);
try
{
cmd.ExecuteNonQuery();
IsConsistent = true;
returntrue;
}
catch (SqlException ex)
{
//You can specify additional error handling here
//This is where you’d rollback your transaction
return (ex.ToString().Length < 1);
}
cn.Close();
}
}

Conclusion :
Anyway, as you can see, Transactions got a lot different in ADO.NET 2.0 and by different I mean unequivocally better. Ten years ago it wasn't uncommon to work in a small company that didn't have a very sophisticated network if they had one at all. Flat files and/or isolated data stores were pretty common. Message Queues? As the landscape evolved so did the sophistication requirements associated with data manipulation, and all of a sudden smaller and resource limited companies starting having features available to them that were previously only in the realm of the larger companies. And with the advent of features like the TransactionScope, its current support for Oracle, Microsoft's SQL Server and MSMQ (and if all goes well, File System support under Windows), sophisticated transaction processing will get much more proximate to a lot of people.