Improved database / system performance: Connection.Close () vs Connection.Dispose

Browse all articles > Improved database / system performance: Connection.Close () vs Connection.Dispose

We always hear that we should drop a database connection rather than just close it because a database connection is an unmanaged resource (SqlConnection, OledbConnection, OdbcConnection). So you think the following code makes sense:

using(SqlConnection con= new SqlConnection())
{
//open database conncetion
//do database transaction
//close connection
con.Close();
}

Select all
Open in a new window

At first glance, it looks like nice, clean code. We close all the resources that we open as well as we dispose of them (the using block will make sure that Dispose is called for the con object) in the same way to make sure that there will be no memory leaks. .

But when we enter the SqlConnection.Dispose () method using reflector, we find that we call the same Close () method again.

protected override void Dispose(bool disposing)
{
    if (disposing)
    {
        this._userConnectionOptions = null;
        this._poolGroup = null;
        this.Close();
    }
    this.DisposeMe(disposing);
    base.Dispose(disposing);
}

Select all
Open in a new window

Dispose does more than just close the connection, such as making _poolGroup null. The best approach to manage connections is therefore:

using(SqlConnection con= new SqlConnection())
{
//open database conncetion
//do database transaction
//close connection
}

Select all
Open in a new window

Now the Dispose () method will make sure the connection will be closed.

Also, if we just call con.Close (), as Close () we will leave the connection to the connection pool, which will result in better performance. And then we can use Dispose () to remove the connection from the pool.

Here is the correct implementation of CloseConnection () which is called by the Close () method:

internal virtual void CloseConnection(DbConnection owningObject, DbConnectionFactory connectionFactory)
{
    Bid.PoolerTrace(" %d# Closing.n", this.ObjectID);
    if (connectionFactory.SetInnerConnectionFrom(owningObject, DbConnectionOpenBusy.SingletonInstance, this))
    {
        try
        {
            DbConnectionPool pool = this.Pool;
            Transaction enlistedTransaction = this.EnlistedTransaction;
            if ((null != enlistedTransaction) && (enlistedTransaction.TransactionInformation.Status != TransactionStatus.Active))
            {
                this.DetachTransaction(enlistedTransaction);
            }
            if (pool != null)
            {
                pool.PutObject(this, owningObject);
            }
            else
            {
                this.Deactivate();
                this.PerformanceCounters.HardDisconnectsPerSecond.Increment();
                this._owningObject.Target = null;
                if (this.IsTransactionRoot)
                {
                    this.SetInStasis();
                }
                else
                {
                    this.PerformanceCounters.NumberOfNonPooledConnections.Decrement();
                    if (base.GetType() != typeof(SqlInternalConnectionSmi))
                    {
                        this.Dispose();
                    }
                }
            }
        }
        finally
        {
            connectionFactory.SetInnerConnectionEvent(owningObject, DbConnectionClosedPreviouslyOpened.SingletonInstance);
        }
    }
}

Select all
Open in a new window

So my take is this: if performance isn’t an issue, you can always use Connection.Dispose (). But if you want better performance, use the Connection.Close () method I described.