Properly disposing of a DbConnection

Question

I have a class called DatabaseHelper that wraps a DbConnection. What's the proper way to setup this class for a using statement? I have implemented IDisposible, but I'm not sure when and where I should be calling Connection.Close() or Connection.Dispose().

When I simply call Connection.Dispose() in my own Dispose() method, I'll sometimes get a SocketException from my DbConnection object. I assume this is because old connections are being left open, but there's no details attached the to exception, so I can't know for sure.

Answer 1

Just to complete the IDisposable implementation pattern, it's convention to include a finalizer (destructor) for your class which calls the Dispose() method (passing false). This acts as a failsafe mechanism, allowing you to dispose of unmanaged objects if the consumer of the class fails to call Dispose().

    ~MyClass() 
    {
        Dispose(false);
    }

Answer 2

This destructor syntax is actualy the finalizer. The finalizer is calling Dispose(false) method.

    #region IDisposable Members
    private bool _isDisposed;

    private void ThrowIfDisposed()
    {
        if (_isDisposed)
            throw new ObjectDisposedException(this.GetType().Name);
    }

    protected virtual void Dispose(bool disposing)
    {
        if (!_isDisposed)
        {
            if (disposing)
            {
                //part 1 : disposing managed objects
                _command.Dispose();
                _command.Connection.Dispose();
                if (_command.Transaction != null)
                    _command.Transaction.Dispose();
            }
            //part 2: disposing unmanged objects. Here there are no unmanged objects.
            _isDisposed = true;
        }
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    //~DbCommandExecutor() //No need of finalize here. Because there is no unmanged objects in my class. ie, no code in part 2.
    //{
    //    Dispose(false);
    //}
    #endregion

There is no need of the finalizer(or destructor) syntax until your code has part 2 code. Otherwise it should be implemented for the safe side. ie, even though the programmer is not calling the dispose method properly, finalize should clean up the unmanaged resources.

compare the examples: From msdn

http://msdn.microsoft.com/en-us/library/system.idisposable.dispose.aspx & http://msdn.microsoft.com/en-us/library/fs2xkftw.aspx

Answer 3

Call connection.Dispose() from within your dispose method. You should look at the standard pattern for implementing IDisposable, which goes above and beyond simply implementing the IDisposable interface and allows for disposing unmanaged objects etc:

public void Dispose()
{
    Dispose(true);
    GC.SuppressFinalize(this);
}

protected virtual void Dispose(bool disposing)
{
    if (!disposed)
    {
        if (disposing)
        {
            // Dispose managed resources.
        }

        // There are no unmanaged resources to release, but
        // if we add them, they need to be released here.
    }
    disposed = true;

    // If it is available, make the call to the
    // base class's Dispose(Boolean) method
    base.Dispose(disposing);
}

(Taken from http://msdn.microsoft.com/en-us/library/system.idisposable.aspx).

Answer 4

According to this newsgroup:

Here is how IDbConnection.Dispose() is implemented (as Reflector utility shows):

SqlClient:

protected override void Dispose(bool disposing)
{
       if (disposing)
       {
             switch (this._objectState)
             {
                   case ConnectionState.Open:
                   {
                         this.Close();
                         break;
                   }
             }
             this._constr = null;
       }
       base.Dispose(disposing);
}

Odbc:
protected override void Dispose(bool disposing)
{
       if (disposing)
       {
             this._constr = null;
             this.Close();
             CNativeBuffer buffer1 = this._buffer;
             if (buffer1 != null)
             {
                   buffer1.Dispose();
                   this._buffer = null;
             }
       }
       base.Dispose(disposing);
}

OleDb:
protected override void Dispose(bool disposing)
{
       if (disposing)
       {
             if (this.objectState != 0)
             {
                   this.DisposeManaged();
                   if (base.DesignMode)
                   {
                         OleDbConnection.ReleaseObjectPool();
                   }
                   this.OnStateChange(ConnectionState.Open, ConnectionState.Closed);
             }
             if (this.propertyIDSet != null)
             {
                   this.propertyIDSet.Dispose();
                   this.propertyIDSet = null;
             }
             this._constr = null;
       }
       base.Dispose(disposing);
}

Your dispose method should only attempt to close the connection if it is open.