SQL Connections and transactions

Do you have to keep a (the same) SQL connection open for the duration of a transaction, and do you have to call CommitTransaction and RollbackTransaction on the same connection that called BeginTransaction.

We have a database helper class with methods to begin- commit- and rollbacktransactions, as well as the usual select, execute stored proc, etc.

The helper class looks more or less like this:

public class DatabaseHelper
{
    public void BeginT开发者_运维问答ransaction()
    {
         // open connection, but dont close it.
    }

    public void CommitTransaction()
    {
         // close the connection
    }

    public void RollbackTransaction()
    {
         // close the connection
    }

So because of this we can NOT do the usual (which I would've preferred) such as this:

using (sqlCon = new SqlConnection(connectionString))
{
    // sql operations here
}

The helper class currently leaves the connection open as long as there is an existing transaction and closes it during commit or rollback. However it got me wondering if that is really the best way to do it? Does the connection have to stay open for the duration of the transaction?

Yes it will have to stay open. You can handle the OnDispose of your class and Dispose of the connection in that.

public class DatabaseHelper : IDisposable
{
    public void Dispose()
    {
        //close/dispose connection here
    }
}

Then you could do...

using(DatabaseHelper db = new DatabaseHelper())
{
}

I am pretty sure transaction are rolled back automatically if the connection is closed before committing. Your helper class does not have to create and dispose of the connection to manage the transactions and opening and closing of the connection, just have it take a connection parameter in a constructor, then you can use your usual using syntax.

e.g.:

using (sqlCon = new SqlConnection(connectionString))
{
    DatabaseHelper helper = new DatabaseHelper(sqlCon);
    helper.BeginTransaction();
    try
    {
        //Sql stuff
        helper.CommitTransaction();
    }
    catch(SqlException)
    {
         helper.RollbackTransaction();
    }
}