So I've got a complicated group of code (compiled by another person), which has a function that conducts numerous database procedures before finally Commit()ing the alterations.

Here's the issue though, you will find sub-functions within the code, that will get known as for example getThis or getThat, which have execute() and queryDatabase() functions. The code stored leading to errors in some instances (not every cases), where it might freeze only at that "execute" or "queryDatabase". Basically, I believe it's related that the code requires data from all of these queryDatabase instructions prior to the changes are committed.

After removing the passing from the dataAccess pointer to those sub functions, (thus they aren't area of the commit), all of a sudden the code passes through effectively.

What's the best way to use Commit() or must i just eliminate it and never begin using these transaction functions? Just allow the code fit everything in instantly?

I can not even discover the last error, yet another vague "connection parameter null" SystemArgumentNullException that's in some way attached to it as being a transaction.

System.ArgumentNullException: Value cannot be null
Parameter name: connection
at Data.Database.PrepareCommand(DbCommand command, DbConnection connection)

It's difficult to publish code since it spans a minimum of 20 different files (yeah, the initial developer doesn't know Hug).

After DbTransaction.Commit()/Rollback() transaction object adopts "zombie" condition. If you are planning to carry on with a couple instructions outdoors from the transaction scope they ought to be detached in the transaction by setting Transaction property to null

A transaction should generally be utilized for some procedures that realistically represent just one unit of labor. Quite simply, if you will find three actions carried out inside a transaction, and among the actions fails or Rollback is known as, the end result should reflect that none from the three actions really happened within the database.

So prior to deciding to take away the transaction, you will have to determine the intent from the original developer and also the code (which might not be easy!). When the actions have to succeeed or fail together, you will have to keep using the transaction. If each action can succeed or fail by itself, you are able to likely eliminate the transaction altogether, since many database have implicit transactions for single calls.

Handful of guidelines: 1. transactions are costly--start them as late as you possibly can and commit them as soon as possible 2. it's frequently (not necessarily) unnecessary to possess reads/queries fall in the transaction which includes creates.

Database Transactions

The Transaction.Connection is going to be set to null following the transaction is committed, which may be the reason the bond is placed to null when the code is passing round the Transaction.Connection like a mention of the all of the subfunctions. The transaction does not own the bond, therefore it should not be passed around with other subfunctions.

It may sound such as the connection has been passed around everywhere. An easy method is always to put the connection inside a using block and just put the instructions that require that connection and assoicated transaction by using it in tht using block.

Here is a fundamental pattern:

using(DbConnection connection = ...)



    using(DbTransaction transaction = connection.BeginTransaction(...))


        ... do stuff ...


    } // transaction rolled back here if an Exception is thrown before the call to Commit()

} // connection closed here

Transactions are essential when the code is saving to multiple tables/records. The system of labor must commit in general and never partly. One method to eliminate the transactions would be to have database manage them directly using a saved procedure. The BEGIN TRAN is performed clearly within the saved procedure, at that time the C# code is no more controlling it.

Also have a look in the System.Transactions namespace; I believe it is simpler to make use of than controlling transactions around the connection itself.