Does the .net TransactionScope handle very complicated database things?

I am tasked with proving or disproving the transaction support in .net. We all know that the TransactionScope can handle committing/rolling back simple things like inserting 3 records in 3 different tables and then deleting 5 records in 5 other tables.

My team is not so sure that TransactionScope can handle the transactio开发者_JAVA百科n properly if these inserts/deletes have triggers. Or if we execute a SP that has it's own nested transactions.

Does the .net transaction support handle these more complicated situations? Do you have to specify any not-so-obvious options to make it all work?

I've heard that some of our SPs could cause us problems, since a few commit sub transactions on their own. Does anyone know if this particular scenario is handle by TransactionScope?

We are using TransactionScope for some very complex and lengthy database operations involving thousands of rows across a dozen or more tables. It handles it just fine. However, IMO, you DO NOT want to be starting and committing transactions in stored procedures and/or triggers.You should let your calling (C#) code handle that. You lose a great deal of flexibility by handling transactions at the lower stored proc level.

Also, be careful, when using TransactionScope, to limit yourself to a single connection object. If you don't, the transaction will escalate to a MTC transaction, which requires the MTC service to be running on the client and server.