DbTransaction.Commit Rollback, causing disruptions in code?

So I have a complicated set of code (written by someone else), that has a function that conducts a number of database operations before finally Commit()ing the changes.

Here's the problem though, there are sub-functions in the code, that get called such as getThis or getThat, that have execute() and queryDatabase() functions. The code kept causing errors in some cases (not all cases), where it would freeze at this "execute" or "queryDatabase". Essentially, I think it's related to the fact that the code requires data from these queryDatabase commands before the changes are committed.

After deleting the passing of the dataAccess pointer to these sub functions, (thus 开发者_C百科they are not part of the commit), suddenly the code passes through successfully.

What is the proper way to use Commit() or should I just get rid of it and not use these transaction functions? Just let the code do everything instantly?

I can't even find the last error, just another vague "connection parameter null" SystemArgumentNullException that is somehow connected to it being a transaction.

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

It's hard to post code because it spans at least 20 different files (yeah, the original developer does not know KISS).

---

The Transaction.Connection will be set to null after the transaction is committed, which could be the reason why the connection is set to null if the code is passing around the Transaction.Connection as a reference to all the subfunctions. The transaction doesn't own the connection, so it shouldn't be passed around to other subfunctions.

It sounds like the connection is being passed around all over the place. A better way would be to place the connection in a using block and only place the commands that need that connection and assoicated transaction with it in tht using block.

Here's a basic pattern:

using(DbConnection connection = ...)

{

    connection.Open();

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

    {

        ... do stuff ...

        transaction.Commit();        

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

} // connection closed here

Transactions are necessary if the code is saving to multiple tables/records. The unit of work needs to commit as a whole and not partially. One way to get rid of the transactions is to have database manage them directly via a stored procedure. The BEGIN TRAN is done explicitly in the stored procedure, at that point the C# code is no longer managing it.

Also take a look at the System.Transactions namespace. I think it is easier to use than managing transactions on the connection itself.

---

After DbTransaction.Commit()/Rollback() transaction object goes into "zombie" state. If you plan to continue using some commands outside of the transaction scope they must be detached from the transaction by setting Transaction property to null

---

A transaction should generally be used for a set of operations that logically represent a single unit of work. In other words, if there are three actions performed in a transaction, and one of the actions fails or Rollback is called, the outcome should reflect that none of the three actions actually took place in the database.

So before you decide to remove the transaction, you will need to determine the intent of the original developer and the code (which may not be easy!). If the actions need to succeeed or fail together, you will need to continue using the transaction. If each action can succeed or fail on its own, you can likely get rid of the transaction altogether, as most database have implicit transactions for single calls.

Couple of rules of thumb: 1. transactions are expensive--start them as late as possible and commit them as early as possible; 2. it's often (not always) unnecessary to have reads/queries fall inside a transaction that includes writes.

Database Transactions

---

What is the proper way to use Commit() or should I just get rid of it and not use these transaction functions? Just let the code do everything instantly?

Don't get rid of transactions. They make sense are exactly designed to keep your data consistent.