.NET connection pool problems

[greg]

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]

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.

 

[Peter Bromberg [C# MVP]]

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

 

[Steve Drake]

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

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

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]]

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

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

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

System.Configuration.ConfigurationSettings.AppSettings["connectionString"];

 

[Peter Bromberg [C# MVP]]

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.
----------------------------------------------------------------

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]

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();
}

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

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

System.Configuration.ConfigurationSettings.AppSettings["connectionString"];

 

[JiangZemin]

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