<

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

Published on
8,774 Points
2,774 Views
Last Modified:
Approved
We are always hearing that we should dispose of a database connection rather than just closing it because a database connection is an unmanaged resource (SqlConnection, OledbConnection, OdbcConnection). So you would think the following code makes sense:

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

Open in new window


At first glance it looks like nice, clean code. We are closing all the resources we are opening as well as disposing (the using block will make sure that Dispose will be called for con object) the same to make sure there will be no memory leaks.

But when we go inside SqlConnection.Dispose() method using reflector, we find that we are again calling the same Close() method.
protected override void Dispose(bool disposing)
{
    if (disposing)
    {
        this._userConnectionOptions = null;
        this._poolGroup = null;
        this.Close();
    }
    this.DisposeMe(disposing);
    base.Dispose(disposing);
}

Open in new window



Dispose is doing much more than simply closing the connection,  – like making _poolGroup null. So the better approach to managing connections is:
using(SqlConnection con= new SqlConnection())
{
//open database conncetion
//do database transaction
//close connection
}

Open in new window


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

In addition, if we are simply calling 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.

Following is the proper implementation of CloseConnection() which is called by the Close() method:
internal virtual void CloseConnection(DbConnection owningObject, DbConnectionFactory connectionFactory)
{
    Bid.PoolerTrace("<prov.DbConnectionInternal.CloseConnection|RES|CPOOL> %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);
        }
    }
}

Open in new window


So my take on this is: If performance is not an issue, you can still use Connection.Dispose(). But if you want better performance use the Connection.Close() method I have described.
0
Author:Naman Goel
Enjoy this complimentary article view.

Get unlimited access to our entire library of technical procedures, guides, and tutorials written by certified industry professionals.

Get 7 days free
Click here to view the full article

Using this article for work? Experts Exchange can benefit your whole team.

Learn More
COLLABORATE WITH CERTIFIED PROFESSIONALS
Experts Exchange is a tech solutions provider where users receive personalized tech help from vetted certified professionals. These industry professionals also write and publish relevant articles on our site.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Learn from the best.