The .NET 2.0 Framework introduces a new namespace called System.Transactions that makes transactional programming easier when dealing with ADO.NET, Sql Server, MSMQ, and the Microsoft Distributed Transaction Coordinator (MSDTC).
I came across some code this morning that was using System.Transactions to handle local transactions connecting to Sql Server 2000. The code was essentially like this:
using (TransactionScope scope =
new TransactionScope())
{
using (SqlConnection connection =
new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = "Insert....";
SqlCommand command2 = connection.CreateCommand();
command.CommandText = "Update....";
connection.Open();
command.ExecuteNonQuery();
command2.ExecuteNonQuery():
connection.Close();
}
scope.Complete();
}
Certainly you can use System.Transactions for local transactions, but it backfires on you with Sql Server 2000.
If you are using Sql Server 2000, the local transaction automatically becomes promoted to a distributed transaction managed by MSDTC, which is unecessary in this case and will cause a performance hit. Sql Server 2000 does not support “promotable transactions,“ which is needed to take advantage of the Lightweight Transaction Manager.
To prove it to yourself, run similar code in Visual Studio 2005 and set a breakpoint on command.ExecuteNonQuery. Checkout the component snap-in and you will see that a distributed transaction has been created:
You don't really want the performance hit of a distributed transaction at this point, so converting the code to use SqlTransaction is the better thing to do for local transactions and Sql Server 2000.
However, if you are using Sql Server 2005 instead of Sql Server 2000 above, a distributed transaction is not created. Sql Server 2005 supports "promotable transactions" and is smart enough to realize that a distributed transaction is not needed at this point. To the best of my knowledge, no performance hit occurs using System.Transactions over SqlTransaction for local transactions with Sql Server 2005.
I would be careful, however, about the code within the context of the TransactionScope. Resources can automatically / implicitly enlist themselves into the transaction, which is what is happening in the code above. If not careful, items can automatically enlist themselves into the transaction without your knowledge, which could effect the promotion of your transaction to a distributed transaction as well as change isolation levels without your knowledge. Hence, as you add code and make changes to your application over time, what you expected to be a part of the transaction could be much different.
I am still using SqlTransaction for all local database transactions, although I certainly love the code simplicity of using System.Transactions and TransactionScope.
Recent SQL Server and ADO.NET Free Tutorials:
Source: David Hayden ( .NET Developer )