Re: .NET connection pool problems
From: Peter Bromberg [C# MVP] (pbromberg_at_yahoo.com)
Date: 02/21/04
- Next message: Showjumper: "Is it possible to get all the column names in a database table?"
- Previous message: Philip Townsend: "detecting plugins / ServerVariables"
- In reply to: greg: ".NET connection pool problems"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 21 Feb 2004 10:21:28 -0500
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" <gregl@insauctionNOSPAM.com> wrote in message
news:OrRnQ$%239DHA.684@TK2MSFTNGP10.phx.gbl...
> 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
>
>
- Next message: Showjumper: "Is it possible to get all the column names in a database table?"
- Previous message: Philip Townsend: "detecting plugins / ServerVariables"
- In reply to: greg: ".NET connection pool problems"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
