locked
Correct use of SqlConnection.close() RRS feed

  • Question

  • User-1775661647 posted

    .Net v4.0, VS2010

    If I open an SqlConnection object inside a method, should I also close it within that method? 

    I ask because I use a meta tag to update a page every few seconds, creating a new SqlConnection (the handle is not assigned to a static variable.). After ten minutes the app. crashes apparently due to the exhaustion of resources and I think it may be these connection handles.

    Many thanks

    Friday, September 21, 2012 8:49 AM

Answers

  • User-802546231 posted

    yes I would close it inside the method, I generally close a connection as soon as possible, I basically do

    open
    query
    close

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 21, 2012 9:10 AM
  • User209514544 posted

    The app crashes after ten minutes may be because of the number of opened SqlConnections that exceeds the default SQLConnection pool size. You should call the Dispose() method once you are done using the SQLConnection.

    Following syntax in C# is an ideal practice for how you should use an SQLConnection (it automatically calls Dispose for you)

    using(SQLConnection conn = new SQLConnection(connectionString))
    {
        // do stuff with conn
    }



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 21, 2012 9:13 AM
  • User-158764254 posted

    Would it make more sense to create the connection in the OnLoad event and dispose of it in the UnLoad() event?

    No.

    close the connection in the same scope in which it was opened.

    the framework will pool the connections, so that when you close a connection, it's not truly closed quite yet.  instead, it's returned to the connection pool and is ready for reuse.  on the next call to open a connection, that pool is checked first for a matching connection thats available and already open.  if one is found you get it automatically from the pool.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 21, 2012 8:07 PM

All replies

  • User-802546231 posted

    yes I would close it inside the method, I generally close a connection as soon as possible, I basically do

    open
    query
    close

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 21, 2012 9:10 AM
  • User209514544 posted

    The app crashes after ten minutes may be because of the number of opened SqlConnections that exceeds the default SQLConnection pool size. You should call the Dispose() method once you are done using the SQLConnection.

    Following syntax in C# is an ideal practice for how you should use an SQLConnection (it automatically calls Dispose for you)

    using(SQLConnection conn = new SQLConnection(connectionString))
    {
        // do stuff with conn
    }



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 21, 2012 9:13 AM
  • User-1775661647 posted

    Thank you both!

    One other variant on this issue:

    I have to use the SqlConnection() handle in several routines on the same page. Would it make more sense to create the connection in the OnLoad event and dispose of it in the UnLoad() event? I am thinking about the cost of constantly creating and tearing down the connection in each function call.

    Many thanks!

    Friday, September 21, 2012 7:59 PM
  • User-158764254 posted

    Would it make more sense to create the connection in the OnLoad event and dispose of it in the UnLoad() event?

    No.

    close the connection in the same scope in which it was opened.

    the framework will pool the connections, so that when you close a connection, it's not truly closed quite yet.  instead, it's returned to the connection pool and is ready for reuse.  on the next call to open a connection, that pool is checked first for a matching connection thats available and already open.  if one is found you get it automatically from the pool.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, September 21, 2012 8:07 PM