ADO.NET Irks Me

There is one thing in ADO.NET that just irks me, and well its not by .NET design.  During a lot of our Business Logic methods in our projects at work, we have to call Insert, Update, and Select procedures from SQL Server.  Of course, the proper way of doing this is by wrapping your logic in a transactional support manner... no biggie, we've got this working, and I might add, it works SUPERBLY!  Well, that is, if you remember that each call to the database while in a transaction must use the same connection and transaction.

I'm not really sure how many times I've done this, but there's been a few that I'm aware of, one of which was today.  The BLL method calls some Inserts, some Updates, then it needs to retrieve some data to do more Inserts and Updates.  Well, for that 1 select query I ran, I forgot to pass in the connection and transaction objects.  I couldn't figure out why the app was just hanging time and time again until it dawned on me. Yup, thats right...it was a good smack my head kinda realization.

So I guess the point of this rant is if you're using .NET transactional support with databases, make sure that every call to the database has the same connection and transaction object, otherwise you'll spend a good half hour to hour banging your head against the wall and blaming the SQL Architect that his Stored Procedure is causing the problems...only to find out, you're missing a few extra parameters!  Arrh!

11 Comments

  • I completely relate. I have found myself here a few times and each time I could have sworn that I had somehow screwed up something in the database only to find out that I was in the wrong transaction. The reason I hate this sooooo much though is that it takes forever to debug only because the transaction itself will only fail because it times out (at least it reacted this way for me). Which means that you have to go get your favorite beverage and visit your closest relative before you can move on through your error handling. So here, have my sacrificial code to sacrifice to the code god that can give us back this time we all have wasted.

  • Couldn't you just create an object that joins your transaction & connection together?



    This way you would not talk to SQL without the proper transaction. It would be easy enough to have a factory object that creates these if you need to share a connection between transactions.

  • The problem arises on how you execute commands against SQL. If you want transactional support, you need to pass in a separate SqlTransaction object when running ExecuteNonQuery, etc.

  • cmd.CommandText = sqlStmt;

    cmd.Connection = sqlConn;

    cmd.Transaction = sqlTran;

    cmd.ExecuteNonQuery();



    ... Would change to ...



    cmd.CommandText = sqlStmt;

    slqTranConn.SetupCommand(cmd);

    cmd.ExecuteNonQuery();





    If no transaction was necessary then SetupCommand() would assign cmd.Transaction = null.



    Maybe I am looking at it all wrong. It is easy to be a backseat coder when you don't have to maintain your claims.

  • Right, but you can only create a transaction by calling SqlConnection.BeginTransaction() ... you cannot create an instance of the Transaction.

  • It seems to me that your BLL is in the wrong place. The entire transaction should be performed by a single sproc...



    I only use "coded" transactions for distributed transactions....

  • nay, breaking things out to easy to manage stored procedures is the way, or at least the way of my work. I have absolutely no say on what is created for me, I just use what is given to me.



    Besides, its not a bad method or way of doing things...besides, most of what the BLL does is more intense than what SQL can provide me. Thats the whole idea behind a BLL layer.

  • >>>Right, but you can only create a transaction by calling SqlConnection.BeginTransaction() ... you cannot create an instance of the Transaction. <<<



    How does this hinder a Connection/Transaction class? It seems that this is the reason why you would create such a class.



    1) Instantiate SqlConnTran object

    2) SqlConnTran.BeginTran(...) - Connects, starts transaction & stores copy of SqlTransaction in member variable.



    3) Call Update 1 with SqlConnTran

    4) Call Update 2 with SqlConnTran

    5) Call Update 3 with SqlConnTran



    6) SqlConnTran.Commit / SqlConTran.Rollback - closes connection to make sure object is not reused accidentally.











  • Well that would work, if I was going to re-architect all of our projects and database helper. Unfortunately, that can't be done, so I have to deal with what I have.

  • >>>Well that would work, if I was going to re-architect all of our projects and database helper.<<<



    Don't tell me you are wasting your nights away by sleeping!



  • Heck, if I got paid for working all night long, I would...but, since I dont things will just have to work the way they do now.

Comments have been disabled for this content.