CODEWITHSUNDEEP
c#sqltransactions
Leo Nix
Leo Nix

Reputation: 2105

Where is the best place to handle transactions in stored procedure or in application?

When my C#.net application updates records in more than one table, I use transactions so if anything should fail during the transaction I can rollback.

Which one is a better practice?

-Use stored procedure with BEGIN TRANSACTION/ROLLBACK/COMMIT TRANSACTION; -Use TransactionScope in the application as below:


    using (TransactionScope ts = new TransactionScope())
    {
    }

Upvotes: 8

Views: 2801

Answers (6)

KM.
KM.

Reputation: 103579

Here are my 2 simple rules for when to use transactions:

  • if the procedure has more than one data changing statement, it will contain a transaction.
  • if the application calls more than one stored procedure that changes data, it will contain a transaction.

Upvotes: 1

Praesagus
Praesagus

Reputation: 2094

I would strongly recommend setting up one procedure for a page and managing all sql actions there. If there are multiple tasks to perform on the page that require multiple procedures, just have one procedure manage the other procedures. Your proceedure can always return multiple recordsets if needed.

  • You page will perform faster - not lots of data going back and forth, just one pull. All code on the sql is already compiled with executions plans.
  • You will be able to handle your errors much more efficiently - in one place as opposed to in two places - having two separate systems to decide if it's cirtical enough to fail - passing any errors back and forth to maintain your data integrity.
  • You minimize your points of failure. If the transaction is going well, but the web server hiccups, the sql server is left waiting for a response.
  • You will save tons of time troubleshooting and debugging.
  • It will help modularize your code on the sql server. You can reuse sprocs to perform like tasks and end up with a more fleixble scalable robust system. HTH

Upvotes: 1

AlexT
AlexT

Reputation:

If your transaction is going to one database, than it's better to do transaction in stored procedure.The other way can be caused only by logistic issue (DBA don't like you, or he is on vacation). If you call different transacted sources (SQL Server and Oracle) in one transaction - than there is no choice other than do transaction in code.

Upvotes: 1

Mufaka
Mufaka

Reputation: 3444

This isn't a business logic issue, it's a data integrity issue and I feel that is ok to do in the stored procedure. I like to keep transaction logic as close to the operations as possible to shorten their duration.

Upvotes: 4

Mike Cole
Mike Cole

Reputation: 14703

Or you can do it in both. Check the link: https://web.archive.org/web/20210513004758/https://www.4guysfromrolla.com/webtech/080305-1.shtml

Upvotes: 1

Andy White
Andy White

Reputation: 88345

TransactionScope is a really nice way to manage transactions in code. It allows you to nest transacted code across multiple methods, and automatically scales up to distributed mode, if necessary.

I prefer to use TransactionScope over stored proc transactions, because it gives you a lot more control in the code.

Upvotes: 4

Related Questions