Closing SQL Connections

Discussion in 'ASP .Net' started by bannaman, Mar 21, 2006.

  1. bannaman

    bannaman Guest

    Please can someone help.

    I have a routine as follows

    Public Function dbConnection() As SqlConnection

    Dim dbConn As New SqlConnection
    Dim errorH As New errorHandle

    Try
    Try
    dbConn.ConnectionString =
    ConfigurationManager.ConnectionStrings(globalapp.currentSite).ConnectionString.ToString
    Catch ex As Exception
    errorH.move(ex)
    End Try
    If dbConn.State = ConnectionState.Closed Then
    dbConn.Open()
    End If
    Catch ex As SqlException
    errorH.move(ex)
    End Try

    Return dbConn


    End Function

    This creates my sqlconnection. I call this from other functions in my
    class in this format

    Try
    dim sqlconn as sqlconnection = dbconnection()

    Catch ex as exception

    Finally
    If db.State = ConnectionState.Open Then
    db.Close()
    dbConnection.Dispose()
    End If
    End try

    I recently only added the dbconnection.dispose because i read that i
    should be doing that not sure if i should. But i'm getting the usual
    error message of the maximum timeout has been reached or the maximum
    pool size has been reached. I don't know what to do. Can anyone advise.
     
    bannaman, Mar 21, 2006
    #1
    1. Advertising

  2. I don't know what you are closing, but it is not the connection your declare
    in your Try block.

    You need to declare the variable outside the Try block, but get the
    connection in it. Something like:

    Dim sqlconn as SqlConnection
    Try
    sqlconn = dbconnection()
    Catch

    Finally
    If Not IsNothing(sqlconn) Then
    sqlconn.Close()
    End If
    End Try
    "bannaman" <> wrote in message
    news:...
    > Please can someone help.
    >
    > I have a routine as follows
    >
    > Public Function dbConnection() As SqlConnection
    >
    > Dim dbConn As New SqlConnection
    > Dim errorH As New errorHandle
    >
    > Try
    > Try
    > dbConn.ConnectionString =
    > ConfigurationManager.ConnectionStrings(globalapp.currentSite).ConnectionString.ToString
    > Catch ex As Exception
    > errorH.move(ex)
    > End Try
    > If dbConn.State = ConnectionState.Closed Then
    > dbConn.Open()
    > End If
    > Catch ex As SqlException
    > errorH.move(ex)
    > End Try
    >
    > Return dbConn
    >
    >
    > End Function
    >
    > This creates my sqlconnection. I call this from other functions in my
    > class in this format
    >
    > Try
    > dim sqlconn as sqlconnection = dbconnection()
    >
    > Catch ex as exception
    >
    > Finally
    > If db.State = ConnectionState.Open Then
    > db.Close()
    > dbConnection.Dispose()
    > End If
    > End try
    >
    > I recently only added the dbconnection.dispose because i read that i
    > should be doing that not sure if i should. But i'm getting the usual
    > error message of the maximum timeout has been reached or the maximum
    > pool size has been reached. I don't know what to do. Can anyone advise.
    >
     
    Marina Levit [MVP], Mar 21, 2006
    #2
    1. Advertising

  3. bannaman

    Bruce Barker Guest

    if you call close you do not need to call dispose.

    -- bruce (sqlwork.com)


    "bannaman" <> wrote in message
    news:...
    > Please can someone help.
    >
    > I have a routine as follows
    >
    > Public Function dbConnection() As SqlConnection
    >
    > Dim dbConn As New SqlConnection
    > Dim errorH As New errorHandle
    >
    > Try
    > Try
    > dbConn.ConnectionString =
    > ConfigurationManager.ConnectionStrings(globalapp.currentSite).ConnectionString.ToString
    > Catch ex As Exception
    > errorH.move(ex)
    > End Try
    > If dbConn.State = ConnectionState.Closed Then
    > dbConn.Open()
    > End If
    > Catch ex As SqlException
    > errorH.move(ex)
    > End Try
    >
    > Return dbConn
    >
    >
    > End Function
    >
    > This creates my sqlconnection. I call this from other functions in my
    > class in this format
    >
    > Try
    > dim sqlconn as sqlconnection = dbconnection()
    >
    > Catch ex as exception
    >
    > Finally
    > If db.State = ConnectionState.Open Then
    > db.Close()
    > dbConnection.Dispose()
    > End If
    > End try
    >
    > I recently only added the dbconnection.dispose because i read that i
    > should be doing that not sure if i should. But i'm getting the usual
    > error message of the maximum timeout has been reached or the maximum
    > pool size has been reached. I don't know what to do. Can anyone advise.
    >
     
    Bruce Barker, Mar 21, 2006
    #3
  4. bannaman

    bannaman Guest

    Sorry the dim sqlconn as sqlconnection = dbconnection() was a typo it
    should have been dim db as sqlconnection = dbconnection().

    As i am creating a connection in another function and passing it back
    to my routine does it still close the conneciton that was opened when
    calling dbconnection. do i not need to do dbconnection.close or will
    db.close be enough?
     
    bannaman, Mar 21, 2006
    #4
  5. As Bruce said, close it. Don't dispose it.

    --
    HTH,

    Kevin Spencer
    Microsoft MVP
    Professional Numbskull

    Show me your certification without works,
    and I'll show my certification
    *by* my works.

    "bannaman" <> wrote in message
    news:...
    > Sorry the dim sqlconn as sqlconnection = dbconnection() was a typo it
    > should have been dim db as sqlconnection = dbconnection().
    >
    > As i am creating a connection in another function and passing it back
    > to my routine does it still close the conneciton that was opened when
    > calling dbconnection. do i not need to do dbconnection.close or will
    > db.close be enough?
    >
     
    Kevin Spencer, Mar 21, 2006
    #5
  6. bannaman

    tdavisjr Guest

    First, I would dim your sqlconn outside the Try block like a poster
    suggested.

    Second, when you say db = dbconnection() you are not actually creating
    a new connection object you are just getting a reference to the
    conneciton object that was created inside dbconnection(). So, when you
    say, db.close() you are actually closing the original connection that
    you opened and there is no need to do dbconneciton.close().

    Lastly, step through your code in debug mode to see if the connection
    is closing.
     
    tdavisjr, Mar 21, 2006
    #6
  7. re:
    > As Bruce said, close it. Don't dispose it.


    Interesting point for discussion, Kevin.

    There's a question as to the *unmanaged* resources which need freeing,
    over and above the need for freeing the *managed* .net resources.

    See Rocky Lhotka's article :

    http://www.lhotka.net/WeBlog/DisposeYourCommandObjects.aspx

    "However, it also turns out that some Command objects really do have non-managed
    resources that need to be disposed. Some don't. How do you know which do and
    which don't? You need to ask the dev that wrote the code.

    It turns out that SqlCommand has no un-managed resources, which is why most of us
    have gotten away with this so far. However, OleDbCommand and OdbcCommand
    do have un-managed resources and must be disposed to be safe."


    Comments ?

    btw, I just finished a fix for a client's application, developed by someone else,
    and for which I was brought in as trouble-shooter, which was having resource problems.

    It had 117 undisposed of OleDbCommand objects.

    I disposed all of them...and the problem vanished.





    Juan T. Llibre, asp.net MVP
    aspnetfaq.com : http://www.aspnetfaq.com/
    asp.net faq : http://asp.net.do/faq/
    foros de asp.net, en español : http://asp.net.do/foros/
    ===================================

    "Kevin Spencer" <> wrote in message
    news:...
    > As Bruce said, close it. Don't dispose it.
    >
    > --
    > HTH,
    >
    > Kevin Spencer
    > Microsoft MVP
    > Professional Numbskull
    >
    > Show me your certification without works,
    > and I'll show my certification
    > *by* my works.
    >
    > "bannaman" <> wrote in message
    > news:...
    >> Sorry the dim sqlconn as sqlconnection = dbconnection() was a typo it
    >> should have been dim db as sqlconnection = dbconnection().
    >>
    >> As i am creating a connection in another function and passing it back
    >> to my routine does it still close the conneciton that was opened when
    >> calling dbconnection. do i not need to do dbconnection.close or will
    >> db.close be enough?
    >>

    >
    >
     
    Juan T. Llibre, Mar 21, 2006
    #7
  8. I'll take your word for it, Juan. I haven't done any OleDb stuff in a couple
    of years. I would not be surprised if you were correct!

    --
    HTH,

    Kevin Spencer
    Microsoft MVP
    Professional Numbskull

    Show me your certification without works,
    and I'll show my certification
    *by* my works.

    "Juan T. Llibre" <> wrote in message
    news:%...
    > re:
    >> As Bruce said, close it. Don't dispose it.

    >
    > Interesting point for discussion, Kevin.
    >
    > There's a question as to the *unmanaged* resources which need freeing,
    > over and above the need for freeing the *managed* .net resources.
    >
    > See Rocky Lhotka's article :
    >
    > http://www.lhotka.net/WeBlog/DisposeYourCommandObjects.aspx
    >
    > "However, it also turns out that some Command objects really do have
    > non-managed
    > resources that need to be disposed. Some don't. How do you know which do
    > and
    > which don't? You need to ask the dev that wrote the code.
    >
    > It turns out that SqlCommand has no un-managed resources, which is why
    > most of us
    > have gotten away with this so far. However, OleDbCommand and OdbcCommand
    > do have un-managed resources and must be disposed to be safe."
    >
    >
    > Comments ?
    >
    > btw, I just finished a fix for a client's application, developed by
    > someone else,
    > and for which I was brought in as trouble-shooter, which was having
    > resource problems.
    >
    > It had 117 undisposed of OleDbCommand objects.
    >
    > I disposed all of them...and the problem vanished.
    >
    >
    >
    >
    >
    > Juan T. Llibre, asp.net MVP
    > aspnetfaq.com : http://www.aspnetfaq.com/
    > asp.net faq : http://asp.net.do/faq/
    > foros de asp.net, en español : http://asp.net.do/foros/
    > ===================================
    >
    > "Kevin Spencer" <> wrote in message
    > news:...
    >> As Bruce said, close it. Don't dispose it.
    >>
    >> --
    >> HTH,
    >>
    >> Kevin Spencer
    >> Microsoft MVP
    >> Professional Numbskull
    >>
    >> Show me your certification without works,
    >> and I'll show my certification
    >> *by* my works.
    >>
    >> "bannaman" <> wrote in message
    >> news:...
    >>> Sorry the dim sqlconn as sqlconnection = dbconnection() was a typo it
    >>> should have been dim db as sqlconnection = dbconnection().
    >>>
    >>> As i am creating a connection in another function and passing it back
    >>> to my routine does it still close the conneciton that was opened when
    >>> calling dbconnection. do i not need to do dbconnection.close or will
    >>> db.close be enough?
    >>>

    >>
    >>

    >
    >
     
    Kevin Spencer, Mar 21, 2006
    #8
  9. bannaman

    Bruce Barker Guest

    oledbcommand doesn't have a close so you must call its dispose to release
    unmanged resources.

    with SqlConnection, it has a Close method, which you can call instead of
    dispose (all dispose does is call Close), which can make the code more
    readable. also you can call open again, while it not recommened to reinit
    disposed objects.

    if you are using c# (or vb.net 2.0), you probably shoudl switch to using
    (which will call dispose even on an error)


    using (SqlConnection conn = new SqlConnection())
    {
    // my sql code here
    }


    -- bruce (sqlwork.com)




    "Juan T. Llibre" <> wrote in message
    news:%...
    > re:
    >> As Bruce said, close it. Don't dispose it.

    >
    > Interesting point for discussion, Kevin.
    >
    > There's a question as to the *unmanaged* resources which need freeing,
    > over and above the need for freeing the *managed* .net resources.
    >
    > See Rocky Lhotka's article :
    >
    > http://www.lhotka.net/WeBlog/DisposeYourCommandObjects.aspx
    >
    > "However, it also turns out that some Command objects really do have
    > non-managed
    > resources that need to be disposed. Some don't. How do you know which do
    > and
    > which don't? You need to ask the dev that wrote the code.
    >
    > It turns out that SqlCommand has no un-managed resources, which is why
    > most of us
    > have gotten away with this so far. However, OleDbCommand and OdbcCommand
    > do have un-managed resources and must be disposed to be safe."
    >
    >
    > Comments ?
    >
    > btw, I just finished a fix for a client's application, developed by
    > someone else,
    > and for which I was brought in as trouble-shooter, which was having
    > resource problems.
    >
    > It had 117 undisposed of OleDbCommand objects.
    >
    > I disposed all of them...and the problem vanished.
    >
    >
    >
    >
    >
    > Juan T. Llibre, asp.net MVP
    > aspnetfaq.com : http://www.aspnetfaq.com/
    > asp.net faq : http://asp.net.do/faq/
    > foros de asp.net, en español : http://asp.net.do/foros/
    > ===================================
    >
    > "Kevin Spencer" <> wrote in message
    > news:...
    >> As Bruce said, close it. Don't dispose it.
    >>
    >> --
    >> HTH,
    >>
    >> Kevin Spencer
    >> Microsoft MVP
    >> Professional Numbskull
    >>
    >> Show me your certification without works,
    >> and I'll show my certification
    >> *by* my works.
    >>
    >> "bannaman" <> wrote in message
    >> news:...
    >>> Sorry the dim sqlconn as sqlconnection = dbconnection() was a typo it
    >>> should have been dim db as sqlconnection = dbconnection().
    >>>
    >>> As i am creating a connection in another function and passing it back
    >>> to my routine does it still close the conneciton that was opened when
    >>> calling dbconnection. do i not need to do dbconnection.close or will
    >>> db.close be enough?
    >>>

    >>
    >>

    >
    >
     
    Bruce Barker, Mar 21, 2006
    #9
  10. heh, heh...

    I wouldn't be surprised if Rocky was correct!

    I practically worship the ground he walks on.

    ;-)




    Juan T. Llibre, asp.net MVP
    aspnetfaq.com : http://www.aspnetfaq.com/
    asp.net faq : http://asp.net.do/faq/
    foros de asp.net, en español : http://asp.net.do/foros/
    ===================================
    "Kevin Spencer" <> wrote in message
    news:...
    > I'll take your word for it, Juan. I haven't done any OleDb stuff in a couple of years. I would not
    > be surprised if you were correct!
    >
    > --
    > HTH,
    >
    > Kevin Spencer
    > Microsoft MVP
    > Professional Numbskull
    >
    > Show me your certification without works,
    > and I'll show my certification
    > *by* my works.
    >
    > "Juan T. Llibre" <> wrote in message
    > news:%...
    >> re:
    >>> As Bruce said, close it. Don't dispose it.

    >>
    >> Interesting point for discussion, Kevin.
    >>
    >> There's a question as to the *unmanaged* resources which need freeing,
    >> over and above the need for freeing the *managed* .net resources.
    >>
    >> See Rocky Lhotka's article :
    >>
    >> http://www.lhotka.net/WeBlog/DisposeYourCommandObjects.aspx
    >>
    >> "However, it also turns out that some Command objects really do have non-managed
    >> resources that need to be disposed. Some don't. How do you know which do and
    >> which don't? You need to ask the dev that wrote the code.
    >>
    >> It turns out that SqlCommand has no un-managed resources, which is why most of us
    >> have gotten away with this so far. However, OleDbCommand and OdbcCommand
    >> do have un-managed resources and must be disposed to be safe."
    >>
    >>
    >> Comments ?
    >>
    >> btw, I just finished a fix for a client's application, developed by someone else,
    >> and for which I was brought in as trouble-shooter, which was having resource problems.
    >>
    >> It had 117 undisposed of OleDbCommand objects.
    >>
    >> I disposed all of them...and the problem vanished.
    >>
    >>
    >>
    >>
    >>
    >> Juan T. Llibre, asp.net MVP
    >> aspnetfaq.com : http://www.aspnetfaq.com/
    >> asp.net faq : http://asp.net.do/faq/
    >> foros de asp.net, en español : http://asp.net.do/foros/
    >> ===================================
    >>
    >> "Kevin Spencer" <> wrote in message
    >> news:...
    >>> As Bruce said, close it. Don't dispose it.
    >>>
    >>> --
    >>> HTH,
    >>>
    >>> Kevin Spencer
    >>> Microsoft MVP
    >>> Professional Numbskull
    >>>
    >>> Show me your certification without works,
    >>> and I'll show my certification
    >>> *by* my works.
    >>>
    >>> "bannaman" <> wrote in message
    >>> news:...
    >>>> Sorry the dim sqlconn as sqlconnection = dbconnection() was a typo it
    >>>> should have been dim db as sqlconnection = dbconnection().
    >>>>
    >>>> As i am creating a connection in another function and passing it back
    >>>> to my routine does it still close the conneciton that was opened when
    >>>> calling dbconnection. do i not need to do dbconnection.close or will
    >>>> db.close be enough?
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
    Juan T. Llibre, Mar 21, 2006
    #10
  11. re:
    >all dispose does is call Close


    Yup...so calling either will work for SqlConnection objects.




    Juan T. Llibre, asp.net MVP
    aspnetfaq.com : http://www.aspnetfaq.com/
    asp.net faq : http://asp.net.do/faq/
    foros de asp.net, en español : http://asp.net.do/foros/
    ===================================
    "Bruce Barker" <> wrote in message
    news:...
    > oledbcommand doesn't have a close so you must call its dispose to release unmanged resources.
    >
    > with SqlConnection, it has a Close method, which you can call instead of dispose (all dispose does
    > is call Close), which can make the code more readable. also you can call open again, while it not
    > recommened to reinit disposed objects.
    >
    > if you are using c# (or vb.net 2.0), you probably shoudl switch to using (which will call dispose
    > even on an error)
    >
    >
    > using (SqlConnection conn = new SqlConnection())
    > {
    > // my sql code here
    > }
    >
    >
    > -- bruce (sqlwork.com)
    >
    >
    >
    >
    > "Juan T. Llibre" <> wrote in message
    > news:%...
    >> re:
    >>> As Bruce said, close it. Don't dispose it.

    >>
    >> Interesting point for discussion, Kevin.
    >>
    >> There's a question as to the *unmanaged* resources which need freeing,
    >> over and above the need for freeing the *managed* .net resources.
    >>
    >> See Rocky Lhotka's article :
    >>
    >> http://www.lhotka.net/WeBlog/DisposeYourCommandObjects.aspx
    >>
    >> "However, it also turns out that some Command objects really do have non-managed
    >> resources that need to be disposed. Some don't. How do you know which do and
    >> which don't? You need to ask the dev that wrote the code.
    >>
    >> It turns out that SqlCommand has no un-managed resources, which is why most of us
    >> have gotten away with this so far. However, OleDbCommand and OdbcCommand
    >> do have un-managed resources and must be disposed to be safe."
    >>
    >>
    >> Comments ?
    >>
    >> btw, I just finished a fix for a client's application, developed by someone else,
    >> and for which I was brought in as trouble-shooter, which was having resource problems.
    >>
    >> It had 117 undisposed of OleDbCommand objects.
    >>
    >> I disposed all of them...and the problem vanished.
    >>
    >>
    >>
    >>
    >>
    >> Juan T. Llibre, asp.net MVP
    >> aspnetfaq.com : http://www.aspnetfaq.com/
    >> asp.net faq : http://asp.net.do/faq/
    >> foros de asp.net, en español : http://asp.net.do/foros/
    >> ===================================
    >>
    >> "Kevin Spencer" <> wrote in message
    >> news:...
    >>> As Bruce said, close it. Don't dispose it.
    >>>
    >>> --
    >>> HTH,
    >>>
    >>> Kevin Spencer
    >>> Microsoft MVP
    >>> Professional Numbskull
    >>>
    >>> Show me your certification without works,
    >>> and I'll show my certification
    >>> *by* my works.
    >>>
    >>> "bannaman" <> wrote in message
    >>> news:...
    >>>> Sorry the dim sqlconn as sqlconnection = dbconnection() was a typo it
    >>>> should have been dim db as sqlconnection = dbconnection().
    >>>>
    >>>> As i am creating a connection in another function and passing it back
    >>>> to my routine does it still close the conneciton that was opened when
    >>>> calling dbconnection. do i not need to do dbconnection.close or will
    >>>> db.close be enough?
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
    Juan T. Llibre, Mar 21, 2006
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Do
    Replies:
    2
    Views:
    366
    srinivas moorthy
    Dec 9, 2003
  2. Patrice
    Replies:
    3
    Views:
    7,126
    Michael D. Long
    Sep 22, 2004
  3. Michael D. Long

    Re: Opening / Closing SQL Server connections

    Michael D. Long, Sep 22, 2004, in forum: ASP .Net
    Replies:
    6
    Views:
    489
    Michael D. Long
    Sep 24, 2004
  4. Michael D. Long

    Re: Opening / Closing SQL Server connections

    Michael D. Long, Sep 22, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    490
    Michael D. Long
    Sep 22, 2004
  5. jobs
    Replies:
    2
    Views:
    895
Loading...

Share This Page