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

Question

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

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

Solution 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()

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

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

Solution 5

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

MSDN - SqlConnection.Dispose()

View more solutions

Comments

[Jason Evans]

Sorry, I should have said that for most objects that implement IDisposable and have a Close() method, calling Close() ends up calling Dispose() behind the scenes for you anyway.

[odiseh]

@statenjason: could you please say that how do you take advantage of using disassemblers line .net reflector?

[statenjason]

@odiseh just download .NET Reflector, run reflector.exe, and you can open any .net DLL (including the standard library). It provides you with a tree structure similar to Visual Studio's object browser, however, you can right click on any class or method and click "disassemble" it will then return the source to you in C# or VB, whichever you've selected in the options.

[Town]

Isn't that the other way around - Dispose() calls Close(), not vice-versa?

[mlhDev]

+1 for MSDN link - I like reflector\ILspy like the next guy, but the docs are where I'd like to go to find my answers.

[Admin]

It's both, usually. For some reason they decided to implement that Close would call Dispose as well. For a SqlConnection this isn't a big deal, but StreamWriters will throw an exception if you close and then Dispose them. My guess would be they won't change that behaviour simply because it's what people have now come to expect.

[Royi Namir]

Does this._poolGroup = null; means that the connection is not getting back to the connection pool ? so i'll have n-1 connections ?