.NET connection pool problems

Discussion in 'ASP .Net' started by greg, Feb 20, 2004.

  1. greg

    greg Guest

    Hi

    We have w2k, iis5, .NET/c#

    I periodically receive this message and the system freezes

    ++++++++++++++++++++++++++++++++++++++++++++++++++

    Timeout expired. The timeout period elapsed prior to obtaining a connection
    from the pool. This may have occurred because all pooled connections were in
    use and max pool size was reached.

    +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    in all data access objects i USE destructor that closes connection:

    public SBSite(String siteId){
    String cString =
    System.Configuration.ConfigurationSettings.AppSettings["connectionString"];
    conn = new SqlConnection(cString);
    conn.Open();
    }

    public DO_SOMETHING_USING_CONNECTION() {}

    ~SBSite()
    {
    conn.Close();
    }

    BUT the problem persists

    HELP
    also where connection pooling is enabled/disabled for .NET/c#?
    how can I increase pool size?

    THANKS
    GSL
    greg, Feb 20, 2004
    #1
    1. Advertising

  2. greg

    Steve Drake Guest

    Until your object is garbage collected the destructor will not get run, if
    you need your class to release unmanaged resource use should use the dispose
    pattern, for database connenection use the follow code (not tested or check
    for syntax, just typed into here):

    void mystuf()
    {
    using(SqlConnection conn = new SqlConnection () )
    {
    // DO STUFF
    }
    }

    when compiled it creates the equivalent code of doing :

    void mystuff()
    {
    SqlConnection conn;
    try
    {
    conn = new SqlConnection();
    }
    finally
    {
    conn.Displose();
    }
    }

    You should not rely use the destructors with .NET to release resources, this
    is taken from the help :

    Explicit Release of Resources
    If your application is using an expensive external resource, it is also
    recommended that you provide a way to explicitly release the resource before
    the garbage collector frees the object. You do this by implementing a
    Dispose method (from the IDisposable interface) that performs the necessary
    cleanup for the object. This can considerably improve the performance of the
    application. Even with this explicit control over resources, the destructor
    becomes a safeguard to clean up resources if the call to the Dispose method
    failed.


    "greg" <> wrote in message
    news:OrRnQ$#...
    > Hi
    >
    > We have w2k, iis5, .NET/c#
    >
    > I periodically receive this message and the system freezes
    >
    > ++++++++++++++++++++++++++++++++++++++++++++++++++
    >
    > Timeout expired. The timeout period elapsed prior to obtaining a

    connection
    > from the pool. This may have occurred because all pooled connections were

    in
    > use and max pool size was reached.
    >
    > +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    >
    > in all data access objects i USE destructor that closes connection:
    >
    > public SBSite(String siteId){
    > String cString =
    >

    System.Configuration.ConfigurationSettings.AppSettings["connectionString"];
    > conn = new SqlConnection(cString);
    > conn.Open();
    > }
    >
    > public DO_SOMETHING_USING_CONNECTION() {}
    >
    > ~SBSite()
    > {
    > conn.Close();
    > }
    >
    > BUT the problem persists
    >
    > HELP
    > also where connection pooling is enabled/disabled for .NET/c#?
    > how can I increase pool size?
    >
    > THANKS
    > GSL
    >
    >
    Steve Drake, Feb 20, 2004
    #2
    1. Advertising

  3. Probably not a good idea to call Dispose on a connection because it could
    actually prevent its return to the connection pool. Close() is quite
    sufficient.
    --Peter
    "greg" <> wrote in message
    news:OrRnQ$%...
    > Hi
    >
    > We have w2k, iis5, .NET/c#
    >
    > I periodically receive this message and the system freezes
    >
    > ++++++++++++++++++++++++++++++++++++++++++++++++++
    >
    > Timeout expired. The timeout period elapsed prior to obtaining a
    > connection
    > from the pool. This may have occurred because all pooled connections were
    > in
    > use and max pool size was reached.
    >
    > +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    >
    > in all data access objects i USE destructor that closes connection:
    >
    > public SBSite(String siteId){
    > String cString =
    > System.Configuration.ConfigurationSettings.AppSettings["connectionString"];
    > conn = new SqlConnection(cString);
    > conn.Open();
    > }
    >
    > public DO_SOMETHING_USING_CONNECTION() {}
    >
    > ~SBSite()
    > {
    > conn.Close();
    > }
    >
    > BUT the problem persists
    >
    > HELP
    > also where connection pooling is enabled/disabled for .NET/c#?
    > how can I increase pool size?
    >
    > THANKS
    > GSL
    >
    >
    Peter Bromberg [C# MVP], Feb 21, 2004
    #3
  4. greg

    Steve Drake Guest

    Dispose does call close, and it is the correct way to-do it, a connection is
    an unmanaged resource and the dispose pattern is there to help with
    unmanaged resources. I am thinking you wrote dispose but you meant to write
    : its not a good idea to use a destructor.

    Steve


    "Peter Bromberg [C# MVP]" <> wrote in message
    news:eY0uVeC#...
    > Probably not a good idea to call Dispose on a connection because it could
    > actually prevent its return to the connection pool. Close() is quite
    > sufficient.
    > --Peter
    > "greg" <> wrote in message
    > news:OrRnQ$%...
    > > Hi
    > >
    > > We have w2k, iis5, .NET/c#
    > >
    > > I periodically receive this message and the system freezes
    > >
    > > ++++++++++++++++++++++++++++++++++++++++++++++++++
    > >
    > > Timeout expired. The timeout period elapsed prior to obtaining a
    > > connection
    > > from the pool. This may have occurred because all pooled connections

    were
    > > in
    > > use and max pool size was reached.
    > >
    > > +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    > >
    > > in all data access objects i USE destructor that closes connection:
    > >
    > > public SBSite(String siteId){
    > > String cString =
    > >

    System.Configuration.ConfigurationSettings.AppSettings["connectionString"];
    > > conn = new SqlConnection(cString);
    > > conn.Open();
    > > }
    > >
    > > public DO_SOMETHING_USING_CONNECTION() {}
    > >
    > > ~SBSite()
    > > {
    > > conn.Close();
    > > }
    > >
    > > BUT the problem persists
    > >
    > > HELP
    > > also where connection pooling is enabled/disabled for .NET/c#?
    > > how can I increase pool size?
    > >
    > > THANKS
    > > GSL
    > >
    > >

    >
    >
    Steve Drake, Feb 21, 2004
    #4
  5. the MS Data Access Application Block "SqlHelper" class (which I would hope
    can be expected to be an example of "best practices" code) does not use
    Dispose at all. Connections are returned to the connection pool by simply
    calling the Close method.
    --Peter

    "Steve Drake" <> wrote in message
    news:u%2378LOF%...
    > Dispose does call close, and it is the correct way to-do it, a connection

    is
    > an unmanaged resource and the dispose pattern is there to help with
    > unmanaged resources. I am thinking you wrote dispose but you meant to

    write
    > : its not a good idea to use a destructor.
    >
    > Steve
    >
    >
    > "Peter Bromberg [C# MVP]" <> wrote in message
    > news:eY0uVeC#...
    > > Probably not a good idea to call Dispose on a connection because it

    could
    > > actually prevent its return to the connection pool. Close() is quite
    > > sufficient.
    > > --Peter
    > > "greg" <> wrote in message
    > > news:OrRnQ$%...
    > > > Hi
    > > >
    > > > We have w2k, iis5, .NET/c#
    > > >
    > > > I periodically receive this message and the system freezes
    > > >
    > > > ++++++++++++++++++++++++++++++++++++++++++++++++++
    > > >
    > > > Timeout expired. The timeout period elapsed prior to obtaining a
    > > > connection
    > > > from the pool. This may have occurred because all pooled connections

    > were
    > > > in
    > > > use and max pool size was reached.
    > > >
    > > > +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    > > >
    > > > in all data access objects i USE destructor that closes connection:
    > > >
    > > > public SBSite(String siteId){
    > > > String cString =
    > > >

    >

    System.Configuration.ConfigurationSettings.AppSettings["connectionString"];
    > > > conn = new SqlConnection(cString);
    > > > conn.Open();
    > > > }
    > > >
    > > > public DO_SOMETHING_USING_CONNECTION() {}
    > > >
    > > > ~SBSite()
    > > > {
    > > > conn.Close();
    > > > }
    > > >
    > > > BUT the problem persists
    > > >
    > > > HELP
    > > > also where connection pooling is enabled/disabled for .NET/c#?
    > > > how can I increase pool size?
    > > >
    > > > THANKS
    > > > GSL
    > > >
    > > >

    > >
    > >

    >
    >
    Peter Bromberg [C# MVP], Feb 21, 2004
    #5
  6. As I recall, the documentation especially from .NET 1.0 on this area was
    confusing. In 1.1, both Dispose and Close are acceptably best practices for
    closing a connection and returning it to the pool.

    Here is a snippet from the Data Access Architecture best practices article
    on MSDN along with some sample code. Note that they illustrate the use of
    both methods, with Dispose being called automatically for you at the end of
    a "using" block:
    ------------------------------------------------------------------
    Connection Usage Patterns
    Irrespective of the .NET data provider you use, you must always:

    Open a database connection as late as possible.
    Use the connection for as short a period as possible.
    Close the connection as soon as possible. The connection is not returned to
    the pool until it is closed
    through either the Close or Dispose method. You should also close a
    connection even if you detect that it
    has entered the broken state. This ensures that it is returned to the pool
    and marked as invalid. The
    object pooler periodically scans the pool, looking for objects that have
    been marked as invalid.
    To guarantee that the connection is closed before a method returns, consider
    one of the approaches
    illustrated in the two code samples that follow. The first uses a finally
    block.
    The second uses a C# using statement, which ensures that an object's Dispose
    method is called.

    The following code ensures that a finally block closes the connection.
    Note that this approach works for both Visual Basic .NET and C# because
    Visual Basic .NET
    supports structured exception handling.

    public void DoSomeWork()
    {
    SqlConnection conn = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand("CommandProc", conn );
    cmd.CommandType = CommandType.StoredProcedure;

    try
    {
    conn.Open();
    cmd.ExecuteNonQuery();
    }
    catch (Exception e)
    {
    // Handle and log error
    }
    finally
    {
    conn.Close();
    }
    }

    The following code shows an alternate approach that uses a C# using
    statement.
    Note that Visual Basic .NET does not provide a using statement or any
    equivalent functionality.

    public void DoSomeWork()
    {
    // using guarantees that Dispose is called on conn, which will
    // close the connection.
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
    SqlCommand cmd = new SqlCommand("CommandProc", conn);
    fcmd.CommandType = CommandType.StoredProcedure;
    conn.Open();
    cmd.ExecuteQuery();
    }
    }

    You can also apply this approach to other objects-for example, SqlDataReader
    or OleDbDataReader-
    which must be closed before anything else can be done with the current
    connection.
    ----------------------------------------------------------------

    "greg" <> wrote in message
    news:OrRnQ$%...
    > Hi
    >
    > We have w2k, iis5, .NET/c#
    >
    > I periodically receive this message and the system freezes
    >
    > ++++++++++++++++++++++++++++++++++++++++++++++++++
    >
    > Timeout expired. The timeout period elapsed prior to obtaining a

    connection
    > from the pool. This may have occurred because all pooled connections were

    in
    > use and max pool size was reached.
    >
    > +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    >
    > in all data access objects i USE destructor that closes connection:
    >
    > public SBSite(String siteId){
    > String cString =
    >

    System.Configuration.ConfigurationSettings.AppSettings["connectionString"];
    > conn = new SqlConnection(cString);
    > conn.Open();
    > }
    >
    > public DO_SOMETHING_USING_CONNECTION() {}
    >
    > ~SBSite()
    > {
    > conn.Close();
    > }
    >
    > BUT the problem persists
    >
    > HELP
    > also where connection pooling is enabled/disabled for .NET/c#?
    > how can I increase pool size?
    >
    > THANKS
    > GSL
    >
    >
    Peter Bromberg [C# MVP], Feb 21, 2004
    #6
  7. greg

    Steve Drake Guest

    If you read the microsoft help, it says dispose calls (see end of post),
    general it is good partice to be aware of dispose and it is even better to
    understand dispose calls close for a SQL connection object and its less code
    when using C# using statement even less code if you check if the connection
    is open before you try to close.

    Your post said dispose does not close the connection I was just pointing
    out that it does, to be honest I thought it was just a simple typo, eg your
    were thinking dispose when you wrote destructor.

    I would not be convied that MS building blocks are allways demetrations of
    best pratices, I have downloaded a example website the says it promotes good
    architecture for n tier web sites and it had loads of code that passed SQL
    Readers back to the presentation tier and hogged the connections. Also is
    the SQL helper aimed at more than one lang? remember even tho VB has the
    IDispose interface it does not have the using statement.

    FROM ON LINE HELP :

    Releases the resources used by the SqlConnection.

    Overload List
    Releases the unmanaged resources used by the SqlConnection and optionally
    releases the managed resources.

    Supported by the .NET Compact Framework.

    public void SqlConnectionHereAndGone()
    {
    SqlConnection myConnection = new
    SqlConnection("Initial Catalog=Northwind;Data
    Source=localhost;Integrated Security=SSPI;");
    myConnection.Open();
    //Calling Dispose also calls SqlConnection.Close.
    myConnection.Dispose();
    }

    "Peter Bromberg [C# MVP]" <> wrote in message
    news:OFXYAzI#...
    > the MS Data Access Application Block "SqlHelper" class (which I would hope
    > can be expected to be an example of "best practices" code) does not use
    > Dispose at all. Connections are returned to the connection pool by simply
    > calling the Close method.
    > --Peter
    >
    > "Steve Drake" <> wrote in message
    > news:u%2378LOF%...
    > > Dispose does call close, and it is the correct way to-do it, a

    connection
    > is
    > > an unmanaged resource and the dispose pattern is there to help with
    > > unmanaged resources. I am thinking you wrote dispose but you meant to

    > write
    > > : its not a good idea to use a destructor.
    > >
    > > Steve
    > >
    > >
    > > "Peter Bromberg [C# MVP]" <> wrote in message
    > > news:eY0uVeC#...
    > > > Probably not a good idea to call Dispose on a connection because it

    > could
    > > > actually prevent its return to the connection pool. Close() is quite
    > > > sufficient.
    > > > --Peter
    > > > "greg" <> wrote in message
    > > > news:OrRnQ$%...
    > > > > Hi
    > > > >
    > > > > We have w2k, iis5, .NET/c#
    > > > >
    > > > > I periodically receive this message and the system freezes
    > > > >
    > > > > ++++++++++++++++++++++++++++++++++++++++++++++++++
    > > > >
    > > > > Timeout expired. The timeout period elapsed prior to obtaining a
    > > > > connection
    > > > > from the pool. This may have occurred because all pooled connections

    > > were
    > > > > in
    > > > > use and max pool size was reached.
    > > > >
    > > > > +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    > > > >
    > > > > in all data access objects i USE destructor that closes connection:
    > > > >
    > > > > public SBSite(String siteId){
    > > > > String cString =
    > > > >

    > >

    >

    System.Configuration.ConfigurationSettings.AppSettings["connectionString"];
    > > > > conn = new SqlConnection(cString);
    > > > > conn.Open();
    > > > > }
    > > > >
    > > > > public DO_SOMETHING_USING_CONNECTION() {}
    > > > >
    > > > > ~SBSite()
    > > > > {
    > > > > conn.Close();
    > > > > }
    > > > >
    > > > > BUT the problem persists
    > > > >
    > > > > HELP
    > > > > also where connection pooling is enabled/disabled for .NET/c#?
    > > > > how can I increase pool size?
    > > > >
    > > > > THANKS
    > > > > GSL
    > > > >
    > > > >
    > > >
    > > >

    > >
    > >

    >
    >
    Steve Drake, Feb 22, 2004
    #7
  8. greg

    JiangZemin Guest

    "Steve Drake" <> wrote in message
    news:u#78LOF#...
    > Dispose does call close, and it is the correct way to-do it, a connection

    is
    > an unmanaged resource and the dispose pattern is there to help with
    > unmanaged resources. I am thinking you wrote dispose but you meant to

    write
    > : its not a good idea to use a destructor.
    >
    > Steve
    >


    agreed,
    we have found Dispose to be very necessary when using ODP.NET at least
    JiangZemin, Feb 23, 2004
    #8
    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. Grey

    connection pool in ADO.NET

    Grey, Apr 20, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    564
    Rick Strahl [MVP]
    Apr 20, 2004
  2. Lloyd Sheen

    Connection pool problems

    Lloyd Sheen, Sep 30, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    823
    Ken Dopierala Jr.
    Sep 30, 2004
  3. Linus Nikander
    Replies:
    5
    Views:
    537
  4. Wei  Lu
    Replies:
    2
    Views:
    5,859
    Wei Lu
    Feb 16, 2009
  5. Rick Lawson
    Replies:
    8
    Views:
    798
    Graham Dumpleton
    Jul 17, 2009
Loading...

Share This Page