Transaction Processing in ADO.NET 2.0

原创 2004年11月03日 08:28:00

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


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;<?XML:NAMESPACE PREFIX = O />

using (System.Transactions.TransactionScope ts = new System.Transactions.TransactionScope())


?/span>牋牋 SqlConnection cn = newSqlConnection(CONNECTION_STRING );

牋牋?string sql = "DELETE Categories";

牋牋?SqlCommand cmd = newSqlCommand(sql, cn);




牋牋?//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

牋牋 {


牋牋牋牋?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);




牋牋牋 cmd.ExecuteNonQuery();

牋牋牋 using(SqlConnection cnn = newSqlConnection(CONNECTION_STRING))

牋牋牋 {

牋牋牋牋牋?string sql_2 = "DELETE Categories";

牋牋牋牋牋?SqlCommand cmd2 = newSqlCommand(sql_2, cnn);




牋牋牋 }

牋牋牋牋牋 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);






牋牋牋牋牋牋 牋牋牋?/span>IsConsistent = true;



牋牋牋牋牋牋牋?catch (SqlException ex)


牋牋牋牋牋牋牋牋牋?//You can specify additional error handling here

牋牋牋牋牋?牋牋??//This is where you抎 rollback your transaction

牋牋牋牋牋牋牋牋牋?return (ex.ToString().Length < 1);






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.

About the Author

Bill is a Microsoft MVP in the .NET Compact Framework, part of the Microsoft ISV program w/ ADO.NET, and a member of the Macromedia Flash Advisory board. He works as a Mobile Devices developer at InfoPro in Augusta GA. He was one of the founders of and helps run Bill can be reached at

Transaction in 2.0 事务

Transaction in 2.0 Posted on 2005-08-15 20:28 idior 阅读(3907) 评论(12)  编辑 收藏 引用 网摘 所属分类: Windb...
  • longfeiwang
  • longfeiwang
  • 2007年03月28日 22:38
  • 644

Transaction in 2.0

在谈具体实现前 先介绍一下三种事务: 1. 单对象单资源 2. 多对象单资源 3. 多对象多资源(分布式事务, 使用两段提交协议) 在ADO.Net1.0下有两种使用Transaction的方法...
  • cxzhq2002
  • cxzhq2002
  • 2010年02月09日 17:07
  • 377

ADO.NET 2.0编程
  • sheismylife
  • sheismylife
  • 2008年03月01日 18:18
  • 2360

ADO.NET2.0技术内幕/微软技术丛书(奋斗的小鸟)_PDF 电子书

  • tjoy2005
  • tjoy2005
  • 2013年10月09日 14:01
  • 1004

Connection 和 Transaction 对象

在 ADO.NET 中,可以使用 Connection 和 Transaction 对象来控制事务。若要执行事务,请执行下列操作:   调用 Connection 对象的 BeginTra...
  • guoyang9108
  • guoyang9108
  • 2011年10月10日 21:02
  • 248


666.You are working in an online transaction processing (OLTP) environment. You use the FLASHBACK...
  • rlhua
  • rlhua
  • 2013年10月08日 11:39
  • 5250

事务处理 Transaction

  • wayne20018891
  • wayne20018891
  • 2010年09月12日 10:26
  • 557

51.In a system, large online transaction processing (OLTP) jobs run during the daytime that require

51.In a system, large online transaction processing (OLTP) jobs run during the daytime that require ...
  • dwj19830118
  • dwj19830118
  • 2016年07月22日 23:13
  • 743

TPC(Transaction Processing Performance Council)-事务处理性能委员会

TPC全称Transaction Processing Performance Council,即事务处理性能委员会。 根据wikipedia的解释:TPC是一个非盈利组织,成立于1988年,这个组织...
  • pan_tian
  • pan_tian
  • 2013年09月06日 13:45
  • 5417

OCP 1Z0 052 51

51.  In a system,  large online  transaction processing  (OLTP)  jobs  run during  the daytime  that...
  • jgmydsai
  • jgmydsai
  • 2014年06月12日 16:40
  • 676
您举报文章:Transaction Processing in ADO.NET 2.0