Do I have to Close() a SQLConnection before it gets disposed?

Per my other question here about Disposable objects, should we call Close() before the end of a using block?

using (SqlConnection connection = new SqlConnection())
using (SqlCommand command = new SqlCommand())
{
    command.CommandText = "INSERT INTO YourMom (Amount) VALUES (1)";
    command.CommandType = System.Data.CommandType.Text;

    connection.Open();
    command.ExecuteNonQuery();

    // Is this call necessary?
    connection.Close();
}

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Since you have a using block, the Dispose method of the SQLCommand will be called and it will close the connection:

// System.Data.SqlClient.SqlConnection.Dispose disassemble
protected override void Dispose(bool disposing)
{
    if (disposing)
    {
        this._userConnectionOptions = null;
        this._poolGroup = null;
        this.Close();
    }
    this.DisposeMe(disposing);
    base.Dispose(disposing);
}

Method 2

Disassembly of SqlConnection from using .NET Reflector:

protected override void Dispose(bool disposing)
{
    if (disposing)
    {
        this._userConnectionOptions = null;
        this._poolGroup = null;
        this.Close();
    }

    this.DisposeMe(disposing);
    base.Dispose(disposing);
}

It calls Close() inside of Dispose()

Method 3

The using keyword will close the connection correctly so the extra call to Close is not required.

From the MSDN article on SQL Server Connection Pooling:

“We strongly recommend that you always
close the connection when you are
finished using it so that the
connection will be returned to the
pool. You can do this using either the
Close or Dispose methods of the
Connection object, or by opening all
connections inside a using statement
in C#”

The actual implementation of SqlConnection.Dispose using .NET Reflector is as follows:

// System.Data.SqlClient.SqlConnection.Dispose disassemble
protected override void Dispose(bool disposing)
{
    if (disposing)
    {
        this._userConnectionOptions = null;
        this._poolGroup = null;
        this.Close();
    }
    this.DisposeMe(disposing);
    base.Dispose(disposing);
}

Method 4

Using Reflector, you can see that the Dispose method of SqlConnection actually does call Close();

protected override void Dispose(bool disposing)
{
    if (disposing)
    {
        this._userConnectionOptions = null;
        this._poolGroup = null;
        this.Close();
    }
    this.DisposeMe(disposing);
    base.Dispose(disposing);
}

Method 5

No, calling Dispose() on SqlConnection also calls Close().

MSDN – SqlConnection.Dispose()

Method 6

No, having the Using block calls Dispose() for you anyway, so there is no need to call Close().

Method 7

No, it is not necessary to Close a connection before calling Dispose.

Some objects, (like SQLConnections) can be re-used afer calling Close, but not after calling Dispose. For other objects calling Close is the same as calling Dispose. (ManualResetEvent and Streams I think behave like this)

Method 8

No, the SqlConnection class inherits from IDisposable, and when the end of using (for the connection object) is encountered, it automatically calls the Dispose on the SqlConnection class.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0