How to make sure the database connection is closed?

Discussion in 'ASP .Net' started by Wei Lu, Feb 15, 2009.

  1. Wei  Lu

    Wei Lu Guest

    Hi all,

    Our web project may hang sometime. After checking the memory dump of the
    process, we found that the process is hang on the class which try to get a
    connection from the database.

    The code sniper is like this:

    public static System.Data.DataSet getDataSet(string sSQL,string
    sConnection)
    {
    System.Data.DataSet _ds = new System.Data.DataSet();
    AseDataAdapter _da = new AseDataAdapter(sSQL.Trim(), sConnection);
    try
    {
    _da.Fill(_ds);
    return _ds;
    }
    catch(AseException)
    {
    return null;
    }
    finally
    {
    _ds.Dispose();
    _da.Dispose();
    }
    };
    So my question is, how could I may sure that my connection is close so that
    other thread will not hang for asking to the get the connection?

    Wei Lu
    Wei Lu, Feb 15, 2009
    #1
    1. Advertising

  2. Wei  Lu

    Steven Cheng Guest

    Hi Wei,

    As for ADO.NET connection, we suggest that you use using block(c#) or
    try..finally block to ensure the connection closing. For example:

    ====================
    using (SqlConnection conn = new SqlConnection(""))
    {
    //do data access
    }

    or

    try
    {
    SqlConnection conn = new SqlConnection("")
    }
    finally
    {
    conn.close();
    }
    ==============

    Also, for your code here, you're using DataAdapter to perform query, it is
    noticable that DataAdapter.Dispose does not gurantee the closing of the
    underlying connection. Therefore, it is better to explicitly close the
    connection. for example:

    ====================
    using (SqlConnection conn = new SqlConnection(""))
    {
    SqlDataAdapter = new SqlDataAdapter() {Connection = conn};

    //......
    }
    ===================

    here is a thread discussing on this:

    http://stackoverflow.com/questions/469459/does-sqldataadapter-dispose-actual
    ly-close-an-associated-sqlconnection

    Sincerely,

    Steven Cheng

    Microsoft MSDN Online Support Lead


    Delighting our customers is our #1 priority. We welcome your comments and
    suggestions about how we can improve the support we provide to you. Please
    feel free to let my manager know what you think of the level of service
    provided. You can send feedback directly to my manager at:
    .

    ==================================================
    Get notification to my posts through email? Please refer to
    http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

    Note: MSDN Managed Newsgroup support offering is for non-urgent issues
    where an initial response from the community or a Microsoft Support
    Engineer within 2 business day is acceptable. Please note that each follow
    up response may take approximately 2 business days as the support
    professional working with you may need further investigation to reach the
    most efficient resolution. The offering is not appropriate for situations
    that require urgent, real-time or phone-based interactions. Issues of this
    nature are best handled working with a dedicated Microsoft Support Engineer
    by contacting Microsoft Customer Support Services (CSS) at
    http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
    ==================================================
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------
    >From: "Wei Lu" <>
    >Subject: How to make sure the database connection is closed?
    >Date: Sun, 15 Feb 2009 16:24:10 +0800


    >
    >Hi all,
    >
    >Our web project may hang sometime. After checking the memory dump of the
    >process, we found that the process is hang on the class which try to get a
    >connection from the database.
    >
    >The code sniper is like this:
    >
    > public static System.Data.DataSet getDataSet(string sSQL,string
    >sConnection)
    > {
    > System.Data.DataSet _ds = new System.Data.DataSet();
    > AseDataAdapter _da = new AseDataAdapter(sSQL.Trim(), sConnection);
    > try
    > {
    > _da.Fill(_ds);
    > return _ds;
    > }
    > catch(AseException)
    > {
    > return null;
    > }
    > finally
    > {
    > _ds.Dispose();
    > _da.Dispose();
    > }
    > };
    >So my question is, how could I may sure that my connection is close so

    that
    >other thread will not hang for asking to the get the connection?
    >
    >Wei Lu
    >
    >
    Steven Cheng, Feb 15, 2009
    #2
    1. Advertising

  3. Wei  Lu

    Mr. Arnold Guest

    ""Steven Cheng"" <> wrote in message
    news:...
    > Hi Wei,
    >
    > As for ADO.NET connection, we suggest that you use using block(c#) or
    > try..finally block to ensure the connection closing. For example:
    >
    > ====================
    > using (SqlConnection conn = new SqlConnection(""))
    > {
    > //do data access
    > }
    >


    What happens here if the solution blows? The *using* is not going to close
    the connection if within the *using* the connection is open and some
    statement causes an abort. The connection is left open. I don't care what
    some book or article says about a *using* closing the connection. If it
    aborts within the *using*, the connection may be left opened. I have been
    there and done that.
    Mr. Arnold, Feb 15, 2009
    #3
  4. Wei  Lu

    Steven Cheng Guest

    Hi Arnold,

    I'm not quite sure about the "solution blows" case. The "using (resource)
    {...}" approach is the recommended means which guarantee resource
    cleanup(as long as the IDisposable interface is correctly implemented on
    that type). Also, "using(resource){...}" approach is the same as
    "try{...}finally{...}" and here is a web article describe this in detail:

    #Understanding the 'using' statement in C#
    http://www.codeproject.com/KB/cs/tinguusingstatement.aspx

    Sincerely,

    Steven Cheng

    Microsoft MSDN Online Support Lead


    Delighting our customers is our #1 priority. We welcome your comments and
    suggestions about how we can improve the support we provide to you. Please
    feel free to let my manager know what you think of the level of service
    provided. You can send feedback directly to my manager at:
    .
    -------------------
    >From: "Mr. Arnold" <MR. >
    >References: <>

    <>
    >In-Reply-To: <>
    >Subject: Re: How to make sure the database connection is closed?
    >Date: Sun, 15 Feb 2009 08:15:14 -0500


    >
    >""Steven Cheng"" <> wrote in message
    >news:...
    >> Hi Wei,
    >>
    >> As for ADO.NET connection, we suggest that you use using block(c#) or
    >> try..finally block to ensure the connection closing. For example:
    >>
    >> ====================
    >> using (SqlConnection conn = new SqlConnection(""))
    >> {
    >> //do data access
    >> }
    >>

    >
    >What happens here if the solution blows? The *using* is not going to close
    >the connection if within the *using* the connection is open and some
    >statement causes an abort. The connection is left open. I don't care what
    >some book or article says about a *using* closing the connection. If it
    >aborts within the *using*, the connection may be left opened. I have been
    >there and done that.
    >
    >
    Steven Cheng, Feb 16, 2009
    #4
  5. Wei  Lu

    Mr. Arnold Guest

    ""Steven Cheng"" <> wrote in message
    news:...
    > Hi Arnold,
    >
    > I'm not quite sure about the "solution blows" case. The "using (resource)
    > {...}" approach is the recommended means which guarantee resource
    > cleanup(as long as the IDisposable interface is correctly implemented on
    > that type). Also, "using(resource){...}" approach is the same as
    > "try{...}finally{...}" and here is a web article describe this in detail:
    >
    > #Understanding the 'using' statement in C#
    > http://www.codeproject.com/KB/cs/tinguusingstatement.aspx
    >


    I respectfully have to tell you that is not the case with the using
    statement, as I was using a console application on a timed thread that kept
    executing a using statement for an Oracle connection to the database. The
    solution was blowing-up each time the thread awoke and executed the method
    leaving numerous connections open until it ran out of connections to use. I
    kept using the *using* statement, but on the try/catch I made sure that the
    connection was closed on the catch.

    Also, the using statement is not recommended for using on a WCF client
    connection, as it can lead to trouble.

    The preferred method is to instantiate the WCF client without a using
    statement and close the client manually.
    Mr. Arnold, Feb 16, 2009
    #5
  6. Wei  Lu

    Steven Cheng Guest

    Thank you for your continue followup Arnold,

    If that is the case, I think it does be a quite serious issue. Is it
    possible to repro such scenario via some simplified code(such as a console
    client that connect database and throw some error in thread)? if so, I'll
    be glad to perform some research on this.

    Sincerely,

    Steven Cheng

    Microsoft MSDN Online Support Lead


    Delighting our customers is our #1 priority. We welcome your comments and
    suggestions about how we can improve the support we provide to you. Please
    feel free to let my manager know what you think of the level of service
    provided. You can send feedback directly to my manager at:
    .

    ==================================================
    Get notification to my posts through email? Please refer to
    http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.


    --------------------
    >From: "Mr. Arnold" <MR. >
    >In-Reply-To: <>
    >Subject: Re: How to make sure the database connection is closed?
    >Date: Mon, 16 Feb 2009 07:32:14 -0500

    t
    >
    >
    >""Steven Cheng"" <> wrote in message
    >news:...
    >> Hi Arnold,
    >>
    >> I'm not quite sure about the "solution blows" case. The "using (resource)
    >> {...}" approach is the recommended means which guarantee resource
    >> cleanup(as long as the IDisposable interface is correctly implemented on
    >> that type). Also, "using(resource){...}" approach is the same as
    >> "try{...}finally{...}" and here is a web article describe this in detail:
    >>
    >> #Understanding the 'using' statement in C#
    >> http://www.codeproject.com/KB/cs/tinguusingstatement.aspx
    >>

    >
    >I respectfully have to tell you that is not the case with the using
    >statement, as I was using a console application on a timed thread that

    kept
    >executing a using statement for an Oracle connection to the database. The
    >solution was blowing-up each time the thread awoke and executed the method
    >leaving numerous connections open until it ran out of connections to use.

    I
    >kept using the *using* statement, but on the try/catch I made sure that

    the
    >connection was closed on the catch.
    >
    >Also, the using statement is not recommended for using on a WCF client
    >connection, as it can lead to trouble.
    >
    >The preferred method is to instantiate the WCF client without a using
    >statement and close the client manually.
    >
    >
    >
    >
    Steven Cheng, Feb 18, 2009
    #6
    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. Keith G
    Replies:
    0
    Views:
    1,629
    Keith G
    Sep 8, 2003
  2. Replies:
    0
    Views:
    543
  3. SAL
    Replies:
    5
    Views:
    473
    Steven Cheng
    Oct 1, 2008
  4. Apu Nahasapeemapetilon

    Established connection aborted and underlying connection closed

    Apu Nahasapeemapetilon, Nov 6, 2006, in forum: ASP .Net Web Services
    Replies:
    0
    Views:
    177
    Apu Nahasapeemapetilon
    Nov 6, 2006
  5. Matt Kruse
    Replies:
    5
    Views:
    297
    Richard Cornford
    Sep 9, 2003
Loading...

Share This Page