.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

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 2

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