Transactions Question

[BG12424]

I am in a situation where I need to run a transaction from the front-end code using my command object. I am wondering if when I use the ado.net commit and rollback methods if I also (or shouldn't) use stored procedure transactions. Should I have the SQL in the stored procs let the front-end code handle the commital of the transactions in this case? Thanks

regards,
Brian

http://www.gainesconsulting.com

The problem with doing something right the first time is that nobody appreciates how difficult it was.

 

[BoulderBum]

You certainly have more flexibility if you handle transactions in the application instead of the SQL.

Let's say your transaction consists of 3 sprocs. Sure you may be able to combine them all into one statement, and handle transaction processing at the database level, but then you couldn't reuse the components.

If, on the other hand, you seperate the logic into three different sprocs and do transaction processing at the application tier, you have the flexibility to use sproc 1 in the transaction in one place, but can also use it elsewhere with or without transactions.

In short, you'd be making a reusable component that can be used in a variety of circumstances.

I feel it's a good design principle to separate business logic and data access as much as possible.