CODEWITHSUNDEEP
asp.netsql
brumScouse
brumScouse

Reputation: 3226

Should I use a transaction in my .NET code if I am defining transactions in the stored procedures that I am calling?

I'd hazard a guess that If I do it in BOTH places, there will be "limited" issues with transaction management.

I suppose a reason I can think of for having a transaction up at the application level is so that business specific logic, potentially not available in sprocs (i.e during the collation and computation of something derived from the data retrieved, or enforcment of "business rules integrity - for want of a better term) can force a rollback.

Thanks,

Upvotes: 0

Views: 79

Answers (2)

Chris
Chris

Reputation: 7369

It is okay to do that. If you'd use your stored procedures outside of your core business logic/services, you still might want to have them transaction safe. If your stored procedure is part of a set of operations, it then becomes a nested transaction.

Upvotes: 0

gbn
gbn

Reputation: 432672

You can have transactions at both levels, if each called layer is aware of an existing transactions.

So if you expect the stored proc to be reused, you can make this aware of a transaction started from the client code, another stored proc and don't do any of begin/commit/rollback.

If it detected no transaction it can then begin/commit/rollback on it's own.

This behaviour is important because SQL Server does not really have nested or autonomous transactions: so make each stored proc safe and you don't need to think about it.

The pattern in my answer here will detect a transaction and do exactly this:
Nested stored procedures containing TRY CATCH ROLLBACK pattern?

Upvotes: 2

Related Questions