locked
SqlConnection, Dispose() vs Close() RRS feed

  • Question

  • string

     

    connectionString = "Data Source=localhost; Initial Catalog=Test; Integrated Security=true;";

     

    using (SqlConnection connection =new SqlConnection(connectionString))

    {

    con.Open();

    }

    As you know, using statement will get translated into try/finally block after compilation and Dispose will be called in the finally block.

    I learned that calling Close() on connection returns it to connection pool while calling Dispose() does not return it to connection pool.

    So this means every time you use "using" statement with SqlConnection object, connection pool looses one connection.

    Is that true ? If so, then, using statement is fighting with connection pool then.

    Thanks very much.

     

    Thursday, April 15, 2010 4:19 PM

Answers

  • When you call "Dispose()" on a SqlConnection, internally, it calls "Close()", too.

     

    There is no worry - you are free to use Close() manually, or just let Dispose() do it for you.

     

     


    Reed Copsey, Jr. - http://reedcopsey.com
    Thursday, April 15, 2010 4:31 PM
  • The contract of Dispose is basically that there is an understanding that calling it may render the object unusable for any future operations.  This differs from Close in that Close can leave open the possibility of re-opening the object somehow.  From a resource release standpoint, either one should be sufficient.  In the specific case of SqlConnection, both return the underlying connection to the pool.

    (Another reason that there are two ways is to be friendly to "beginners" -- the concept of closing connections and files is familiar to programmers new to the .NET Framework who might otherwise overlook a Dispose method.  You don't have to like/agree with this aspect of the design!)

    Note well that although these are the general concepts, whether or not a specific type of object actually follows these conventions is up to that type of object.  For example, it seems like one can resurrect a disposed SqlConnection by resetting its ConnectionString property and calling Open.  You should not rely on trivia like this and treat Dispose as permanent.

    In most cases, you should be using the "using" block syntax and the differences between Close and Dispose should be of no consequence to you.

     

    • Marked as answer by SamAgain Friday, April 23, 2010 8:37 AM
    Saturday, April 17, 2010 6:23 PM
  •  

    In most cases, you should be using the "using" block syntax and the differences between Close and Dispose should be of no consequence to you.

     

    Typically, the two are identical.  Brad Abrams discussed this on his blog.   The design guidelines book recommends making Close and Dispose aliases for each other, so either can be used interchangably.

     


    Reed Copsey, Jr. - http://reedcopsey.com
    • Marked as answer by SamAgain Friday, April 23, 2010 8:37 AM
    Saturday, April 17, 2010 6:36 PM

All replies

  • When you call "Dispose()" on a SqlConnection, internally, it calls "Close()", too.

     

    There is no worry - you are free to use Close() manually, or just let Dispose() do it for you.

     

     


    Reed Copsey, Jr. - http://reedcopsey.com
    Thursday, April 15, 2010 4:31 PM
  • I don't know your source... The Connection Pooling article on MSDN states that:

    "...Connections are released back into the pool when you call Close orDispose on the Connection..."

    HTH
    --mc

    Thursday, April 15, 2010 4:54 PM
  • Thank you guys.

     

    Saturday, April 17, 2010 2:19 PM
  • But what's the basic difference between Close and Dispose ? Thanks

    Saturday, April 17, 2010 2:21 PM
  • The contract of Dispose is basically that there is an understanding that calling it may render the object unusable for any future operations.  This differs from Close in that Close can leave open the possibility of re-opening the object somehow.  From a resource release standpoint, either one should be sufficient.  In the specific case of SqlConnection, both return the underlying connection to the pool.

    (Another reason that there are two ways is to be friendly to "beginners" -- the concept of closing connections and files is familiar to programmers new to the .NET Framework who might otherwise overlook a Dispose method.  You don't have to like/agree with this aspect of the design!)

    Note well that although these are the general concepts, whether or not a specific type of object actually follows these conventions is up to that type of object.  For example, it seems like one can resurrect a disposed SqlConnection by resetting its ConnectionString property and calling Open.  You should not rely on trivia like this and treat Dispose as permanent.

    In most cases, you should be using the "using" block syntax and the differences between Close and Dispose should be of no consequence to you.

     

    • Marked as answer by SamAgain Friday, April 23, 2010 8:37 AM
    Saturday, April 17, 2010 6:23 PM
  •  

    In most cases, you should be using the "using" block syntax and the differences between Close and Dispose should be of no consequence to you.

     

    Typically, the two are identical.  Brad Abrams discussed this on his blog.   The design guidelines book recommends making Close and Dispose aliases for each other, so either can be used interchangably.

     


    Reed Copsey, Jr. - http://reedcopsey.com
    • Marked as answer by SamAgain Friday, April 23, 2010 8:37 AM
    Saturday, April 17, 2010 6:36 PM
  • Thank you guys very much.
    Sunday, April 18, 2010 2:42 AM