.NET TransactionScope class and T-SQL TRAN COMMIT and ROLLBACK

Question

I am current writing an application that will require multiple inserts, updates and deletes for my business entity. I am using the TransactionScope class to guarantee all the stored procedures can commit or roll back as a single unit of work.

My question is, I am required to also use COMMIT TRAN and ROLLBACK TRAN is each of my stored procedures if I am using the TransactionScope class in my .NET class library?

Answer 1

You shouldn't need to - it should be handled within the TransactionScope. It does depend a little bit as to exactly what you are doing and how you are handling transaction (explicit or implicit) More HERE

Answer 2

Hi, ON 2005 its not necessary, on 2000 I would ,Also, i usually put the transactionscope in a "using" block.

There are some performance issues when using it on 2000 and older vs 2005.

See here

Thanks

Answer 3

No, you don't need explicit transactions if using TransactionScope for your transactions - however: important you should probably set Transaction Binding=Explicit Unbind; in the connection string. The full details are here, but otherwise you can end up with the first few operations getting rolled back, and the last few committing (or rather, running outside of any transaction).

Answer 4

If you are enlisted in a TransactionScope or CommittableTransaction then I would strongly recommend that you do NOT explicitly create your own local transactions using begin transaction or SqlConnection.BeginTransaction.

TransactionScope/CommittableTransaction are a different 'family' and are mutually exclusive from begin transaction/SqlTransaction

Therefore I would disagree with Saif Khan. It's true that System.Transactions has performance issues on Sql 2000, so it might be better to use SqlTransaction or begin transaction instead. However, if you do that then you should NOT also use a TransactionScope/CommittableTransaction.

By the way, the behaviour that Marc Gravell described has been changed in .Net 4.0. Even if you don't use Explicit Unbind, it is no longer possible to have some commands rolled back and some committed. (However he is correct that in older versions you should use Explicit Unbind).