Home » Using, Dispose, Close and Connections

  • Using, Dispose, Close and Connections

    Using, Dispose, Close and Connections

    Please note that my explanation in this post is incorrect. I am sorry if I caused any trouble.

    In .NET 2.0 disposing the SqlConnection DOES return the connection into the connectionpool.

    In my experience .NET 1.1 acted differently; I will try to investigate this and let you know.

     

    In many talks, tutorials and books you will find C# code that explains the use of using like this:

    using(SqlConnection connection = new SqlConnection(connectionString))
    {
      …
    }

    Whenever and however execution leaves this using block (power outage disregarded) the connection will be closed because when leaving a using block the Dispose method will be called on the connection object.

    The MSDN states[1]:

    SqlConnection.Dispose Method

    Releases the resources used by the SqlConnection.

    And poses this example:

    public void SqlConnectionHereAndGone()
    {
      SqlConnection myConnection = new SqlConnection(“…”);
      myConnection.Open();

      //Calling Dispose also calls SqlConnection.Close.
          myConnection.Dispose(); }

    All this leads many developers to think that it doesn’t matter whether you call Dispose or Close.

    BUT IT DOES!

    Dispose looks like this (thanks to Reflector[2]):

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

    Yes it calls Close but: it also calls base.Dispose which releases all claimed resources INCLUDING the unmanaged resources that keep the connection in the connection pool alive.

    Lessons to be learned:

    1. Use Close and Dispose depending on the situation.
    2. Do NOT explain the using keyword by using an example that shows a DbConnection.

    It’s great to be a trainer and a geek[3].

    [1] SqlConnection.Dispose
    [2] Reflector
    [3] Geek

    Share this

4 comments

  1. Didn’t know that it does that, I use the using construct a lot when communicating with a database. Might be worthwhile to check if using close instead of the using block increases performance in my apps 🙂

    W.Meints

  2. Wat version of .NET do you refer to? I’ve checked the .NET 2.0 SqlConnection and I can NOT confirm your story. DbConnection does not implement (override) Dispose. base.Dispose calls Component.Dispose and there only site management and event raising takes place.

    Marc Jacobi

  3. Erno,

    This is utter nonsense! When Dispose calls Close() the underlying connection is put back in the pool.

    SqlConnection derives from DbConnection, which does not even override Dispose, but inherits this from System.ComponentModel.Component!

    Also, Dispose is called regardless of whether you call myConnection.Dispose() or whether this is done for you by the Garbage Collector when it calls the destructor method Finalize() on your myConnection object after loses scope.

    The only reason for using Close() is if you want to reopen the connection later. After using Dispose() you should not use the object anymore.

    The advantage of using Dispose() is that *you* can determine when you want to release resources used by your object rather than waiting for the GC to do this. When a class is IDisposable you should ALWAYS call Dispose() or use using(), there is never a reason for not doing this. This is a best practice, which is also why FxCop has a rule to check this.

    Gerard van der Land

  4. Marc, Gerard, you are right, I was wrong.
    Some remarks:
    1. it was not my intend to reuse the managed objects but the unmanaged objects in the connectionpool.
    2. I think this is a change in behaviour from 1.1 to 2.0 -> I’ll investigate.
    Thanks!

    ernow

Comments are closed.