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();
}
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);
}
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
}
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);
}
}
}
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.
Comments are closed.