which one is better aspx.cs page transaction vs database transaction?Why?

In an interview ,Interviewer asked me " where do u use transaction? at sp or code behind?" I told SP then he asked me why? why we don't use at code behind?

Sinisa Hajnal 21-Jan-16 2:52am

I prefer stored procedures if I can get everything inside since there is only one server round trip. Use code transaction if you have separate functions already working and need fairly simple and small update. If you use code transaction, it will lock the table for much longer (due to several round trips) then would SP

Avik Ghosh22 21-Jan-16 4:07am

Thank u. I got my answer . :) (Y)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

F-ES Sitecore · Answer 1 · 2016-01-20T23:02:00

The answer, as with many things, is "it depends". Both have pros and cons so neither is "right". What you have to do is understand the pros and cons so you can make a decision that is right for the situation you are currently facing, and interviewers will use "devil's advocate" tactics to see if you know the pros and cons of both. Had you have said code-behind he would have asked "Why? Why not in the SP?" He isn't asking this because you gave a "wrong" answer, he is seeing if you simply understand the difference and aren't just repeating nonsense you've read on the internet.

"MVC is better than webforms" "Why?" "Um...because internet?"

If you put transactions in the SP then you know that any code that calls that SP uses your transactions. However if you're nesting SP calls and they have their own transaction management then things can get tricky. Transactions in the SP probably perform better too, but I wouldn't quote me on that.

If you keep your transaction at the code-behind level then you can ensure that any and all SPs\SQL commands are in that same transaction, and if you don't want to use transactions then you can call the SPs without it. So if you have an Add_Money SP and a Remove_Money SP, and someone deposits money into an account you can call the Add_Money from your page. If they are moving money from one account to another you can create a transaction and call Add_Money and Remove_Money in that same transaction.

So at page-level you get the flexibility of if\when to use transactions, at the SP level you can force the use of transactions, but if you want to have multiple SPs in the same transaction it can get tricky. To add even more complexity into the mix there are also distributed transactions where SPs on multiple databases, and even other recourses that you have transaction support for like files etc, can all be enlisted in the same global transaction, which again isn't possible if your transactions are in the SP.