CODEWITHSUNDEEP
c#asp.netsql-servertransactionstransactionscope
Kai
Kai

Reputation: 352

Multiple stored proc call in .Net under 1 transaction

I have a .net web page in which two stored procs are called. Both of these have begin-commit transaction in sql server.Also I am calling the second proc multiple times depending on some if conditions.

I want to wrap this whole process under a single transaction. I have looked around and found Sqltransaction and TransactionScope clases in C# will help me in this situation.

But I have never used them, always use transaction in sql server, and so do not know if the transactions in .Net will have problems as both my stored procs have their own Begin-commit transaction in Sql Server.

If they do conflict is there a way to get them to work under a single transaction?

Upvotes: 1

Views: 2129

Answers (1)

StuartLC
StuartLC

Reputation: 107267

Yes, it is possible to call (e.g. existing or legacy) Stored Procs from .Net which use manual BEGIN TRAN, COMMIT TRAN / ROLLBACKs under a .Net TransactionScope, or if you manage the transaction from a SqlTransaction. (Although to state the obvious, if you can avoid using multiple transaction technologies, do so).

For the 'happy case' scenario, what will happen is that the @@TRANCOUNT will be increased when the SPROC transactions are called (just as per nested transactions in SqlServer). Transactions are only committed when @@TRANCOUNT hits zero after the outermost commit on the connection. i.e. inner commits will simply decrease @@TRANCOUNT. Note however that the same is not true for ROLLBACKS - unless you are using SAVEPOINTS, any rollback will rollback the entire transaction. You'll need to be very careful of matching up @@TRANCOUNTs.

You sound a bit undecided about TransactionScope vs SqlTransaction. TransactionScope is more versatile, in that it can span both single phase and distributed transactions (using DTC). However, if you only need to coordinate the transaction on a single connection, same database, then SqlTransaction would also be fine.

Upvotes: 1

Related Questions