Refactoring ADO.NET - SqlTransaction vs. TransactionScope

You won't immediately gain anything by switching your existing code to use TransactionScope. You should use it for future development because of the flexibility it provides. It will make it easier in the future to include things other than ADO.NET calls into a transaction.

BTW, in your posted example, the SqlCommand instances should be in using blocks.

I prefer TransactionScope. It doesn't work perfectly in every scenario, but in the one you describe, it's the better solution.

My reasoning:

1. Enlistment in the Transaction is automatic
2. Transaction rollback in the event of an Exception is automatic

Together, the result is a little less code and a generally more robust design, since the system is handling some of the details for me; it's one less thing I have to remember to do.

In addition, transparent Transaction enrollment can be particularly useful when you have a number of nested methods in your DAL -- although you do have to take care to not accidentally have your transaction turn into a distributed one that requires the DTC, which can happen if you use multiple SqlConnections, even if they point to the same DB.

Microsoft recommends using transaction scope:

http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

The basic idea is that transaction scope will manage the "ambient transaction context" for you. You start by talking to one database, you have an sql transaction, then you talk to database number 2, and the transaction is elevated to a distributed transaction.

Transaction scope does work for you, so that you can concentrate on the functionality of the system, rather than the plumbing.

EDIT

When you use a transaction scope everything within that scope is covered by the transaction. You therefore, save a line of code, where you connect the command to the transaction. This is a possible source of error, for example if there were one chance in 1000 that this line had been forgoten, how many would you be missing.

EDIT 2

Agree with comment on Triynko below. However, we use Entity Framework, EF will automatically close and reopen a connection in order to enlist it in a transaction. It does not physically close the connection more like, it releases it to the connection pool and gets a new one, which can be the same one or can be a different one.