<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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

Published on
8,719 Points
2,719 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

Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Join & Write a Comment

Discover the basics of using Outlook 2016 from office 365.
See the Basics of Office 365's Note Taking app, OneNote
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month