Boy this question will come up a lot atleast in the initial phases of .NET 2.0, so I thought it is worth writing a blog post on.

The first difference is, SqlTransaction will not work on multiple databases, whereas System.Transaction will work on either one database, or multiple databases.

But as far as a single database is considered, the biggest difference between a ADO.NET 2.0 System.Transactions based transaction and a regular SqlTransaction is "Isolation level". It is a big difference however. Let me come to Isolation level in a moment, but before that you've gotta realize that, in Sql 2k, the transaction will be managed by MSDTC even if you have ONE single database involved. For Sql2k5, the transaction is managed by LTM UNTIL the second SqlConnection connected to a different database comes into picture. When that DOES happen, the transaction is bumped up from LTM to MSDTC.

Why is this an important fact? You can get that information from these two posts - What is LTM? and Why MSDTC Sucks?

So coming back to Isolation level. Sql2000 will default the isolation level to serializable (performance pig), even if there is one single RM (One database) involved in the transaction.

Sql2k5 however will PROMOTE the transaction from LTM (Lightweight transaction manager) to MSDTC  --- AUTOMATICALLY for you .. thus changing the isolation level ONLY IF it needs to :-).

System.Transactions is NOT a replacement for SqlTransaction. For single database transactions in MOST cases you still want to use SqlTransaction. But can System.Transactions BE used in a single database scenario??? YES SURE they can be.

The differences are -

a) You donot explicitly enroll in the transaction (you can, but you might not) - so you don't know you are in a transaction or not - atleast you don't control it explicitly in every circumstance. If your code happens to fall inside a transaction scope, guess what Jose, you are in a transaction, maybe even Serializable pessimistic lock piggie transaction.
b) You don't control when your isolation level got escalated - bad bad.
c) It's an overkill :-)

To make the long story short -  Yes you can use System.Transactions for single db transactions. And NO you shouldn't (in most cases) use System.Transactions for single database transactions.

Hope this helps? :-)

Original Link