<

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

Published on
8,666 Points
2,666 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
Comment
Author:Naman Goel
1 Comment
 
LVL 13

Author Comment

by:Naman Goel
Thanks for editing this.

I looks fine to me.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

When you have multiple client accounts to manage, it often feels like there aren’t enough hours in the day. With too many applications to juggle, you can’t focus on your clients, much less your growing to-do list. But that doesn’t have to be the cas…
Did you know PowerShell can save you time with SaaS platforms? Simply leverage RESTfulAPIs to build your own PowerShell modules. These will kill repetitive tickets and tabs, using the command Invoke-RestMethod. Tune into this webinar to learn how…
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month