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

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();
                      }
                      

Select all 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);
                      }
                      

Select all 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
                      }
                      

Select all 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);
                              }
                          }
                      }
                      

Select all 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.