Tuesday, July 10, 2007


Recently I was looking to TransactionScope class to see what advantages I can have using it.
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 0, 10)))
. . .
When you use TransactionScope with only one connection to a SQL Server 2005 database, a local transaction is started. But if you have more than one connection, your transaction will be promoted to a distributed transaction.

If you have two methods opening a connection and doing some stuff, you should expect to use a distributed transaction. Use local transactions to gain performance. Do not even try the distributed transactions. But there are some scenarios that this class is very handy.

- Imagine that your method is executing a stored procedure, depending on the result you have to do a file processing or some other processes not related to the database. You can use TransactionScope in your business method to rollback the changes if the tasks that are not related to database go wrong. But be careful, you should only be opening only one connection to the database. Otherwise your transaction will be promoted to be a distributed transaction.

- Writing unit test is very helpful to test the codes. But it requires leaving the database in the same state before the test. Sometimes developers start writing messy codes and attach a few other stored procedures just to help the creation of test data. Usually we have data creation methods in our business layers. Instead of relying some codes which do not exist in a project, we can purely use the codes that exist in the project. Just wrap everything using TransactionScope. As long as you do not call Complete method all the changes will be rolled back at the end ensuring that the database will be left in its previous state. You may need to update the security settings of your COM+ services since the transactions will be distributed using COM+.

Goto Component Services->Computers-> My Computer->Properties->MSDTC->Security Configuration. Make sure Network DTC access is enabled and the authentication level is as you want