Fog Creek Software
Discussion Board




Welcome! and rules

Joel on Software

Transactions: Stored Procedure vs ADO.NET

What are the pros and cons of performing a transaction in stored procedure vs doing it in ADO.NET? Your thoughts are appreciated.

Nathan Arunachalam
Thursday, March 25, 2004

Define for us what you think "doing a transcation in ADO.NET" means.

Brad Wilson (dotnetguy.techieswithcats.com)
Thursday, March 25, 2004

here is an example of transaction in ADO.NET:

SqlConnection conn = new SqlConnection(myConnString);
conn.Open();

SqlCommand cmd = conn.CreateCommand();

SqlTransaction trans;

// Start a local transaction
trans = myConnection.BeginTransaction();
cmd.Connection = conn;
cmd.Transaction = trans;

try
{
    ... sql params etc...
    cmd.Execute(... sql operation ...)
    trans.Commit();
}
catch (Exception e)
{
    trans.Rollback();
    ... other cleanup ...
}

how does this compare with a sql server stored procedure:

begin tran

... operation ...

if @@error <> 0
begin
    rollback
    return or raiserror
end

... operation ...
    ... error check, rollback, return ...

commit
end tran

Nathan
Thursday, March 25, 2004

My expectation is that they result in identical SQL code, but I'm not positive. You could look at the code for SqlTransaction and find out.

Brad Wilson (dotnetguy.techieswithcats.com)
Thursday, March 25, 2004

The exception/error handling is nicer in .NET than T-SQL.

If it's just one stored procedure, I put the transaction in the stored proc.

If there are several stored procedures that need to be chained together, it's a lot easier to handle the tran on the client rather than try to write a wrapper stored procedure that handles the tran.

Of course that means you have to remember which thing you did in which case.  Usually this is not a problem for me, but I do single-developer projects.

If I had to just choose one approach, I would handle all my transactions from the client.

Matt Conrad
Friday, March 26, 2004

Of course, one of the other drawbacks to using a stored procedure is that you are then more locked in to the DB you are using. For example, if you want to move to Oracle, you have to rewrite all of your SPs to Oracle.

But, if you do proper encapsulation, either way is probably just as painful as the other, so it may not matter much.

Finally, think about the capabilities of your app. Do you have thousands of requests coming into the database, or just 10 or 20? If you needed to make a change to something in one of your transactions, would you rather update your database or do a rebuild and update your clients? In the former question, if your users experience slowdowns you can help resolve it by throwing more hardware. But in the latter, all of your clients have to be updated. Not fun.

CF
Friday, March 26, 2004

"Of course, one of the other drawbacks to using a stored procedure is that you are then more locked in to the DB you are using. For example, if you want to move to Oracle, you have to rewrite all of your SPs to Oracle."

I saw this mentioned somewhere the other day and think I'll recycle it: does anyone actually switch databases once they've shipped?  Ever?

Jason McCullough
Friday, March 26, 2004

I agree with Jason, that argument is mostly a red herring.

Portable rdbms code is like portable c code....  it doesn't do anything interesting, but at least it won't do it everywhere.

$0.02

Rick
Saturday, March 27, 2004

The company I worked at switched from MSSQLServer to Oracle after they shipped, but it was a huge production and coupled with a major rewrite.

I don't think it's frequently done.

One drawback I see is that it's harder to move to COM+ transactions if you have transaction code littered through out the stored procs.

Many places I've worked at start out saying they won't need COM+ transactions and then sooner or later find out they do. If you're building anything non-trivial, I'd at least leave this option open.

Aaron Boodman
Sunday, March 28, 2004

Hmm, I guess I am in double-post mode today. Maybe I ought to think more before I answer.

Anyway, I was just going to say that I feel like doing it the SP way leads to a lot of duplication of code, which means a lot of room for error.

Assuming you've got a data access layer, doing transactions in ADO.Net means you can centralize that piece of code. Centralization is always good.

For instance, it's not hard to write code that allows you to start and end transactions with:

using (TransactionContext tx = new TransactionContext())
{
    try
    {
      // biz logic here...
    }
    catch (Exception e)
    {
      tx.Abort();
    }
}

This is surely better than putting a ton of transaction counting logic in each stored procedure you write. At least to my way of thinking.

I have code that does this for COM+ already written which I was planning on eventually posting on my website. If you're interested, let me know, and I'll get on that.

Aaron Boodman
Sunday, March 28, 2004

Thanks to everyone who replied.

Ours is a windows client app with not that many requests going in to database.

I see that most of you favor transactions in ADO.NET. We have some of our transactions in SPs. We will go and take a second look at them.

In the mean time, how do you handle network erros (client losing network connection to sql server) while doing a transaction (ADO.NET or SP)? What if we cant even do tx.Abort()? Isn't SP safer in this situation? Even though the client lost the connection, SP would have executed to completion in the server and leave the database in a known state.

thanks
Nathan

Nathan
Tuesday, March 30, 2004

I am just wondering if there is an easy way in ADO.NET to handle a transaction using Microsoft managed provider for Oracle

stan vuk
Saturday, April 17, 2004

*  Recent Topics

*  Fog Creek Home