Will a connection opened in a using block automatically close before being disposed

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

We are having problems where connections in a connection pool are being
exausted. We are using the Microsoft Application Block SQL Helper class and
are seeing the failure in the following method:

public static int ExecuteNonQuery(string connectionString, CommandType
commandType, string commandText, params SqlParameter[] commandParameters)
{
if( connectionString == null || connectionString.Length == 0 ) throw new
ArgumentNullException( "connectionString" );
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
return ExecuteNonQuery(connection, commandType, commandText,
commandParameters);
}
}

The application is Client/Server and it is difficult to understand why we
are running out of resources in the connection pool unless there is
something about the using block rather than explicity calling close that may
be causing the problem.
 
Hi Jason,

Are you sure that this method is the culprit?
Try explicitly calling connection.Close() and see what happens.
What does look like your connection string?
 
Jason said:
We are having problems where connections in a connection pool are being
exausted. We are using the Microsoft Application Block SQL Helper class
and
are seeing the failure in the following method:

public static int ExecuteNonQuery(string connectionString, CommandType
commandType, string commandText, params SqlParameter[] commandParameters)
{
if( connectionString == null || connectionString.Length == 0 ) throw
new
ArgumentNullException( "connectionString" );
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
return ExecuteNonQuery(connection, commandType, commandText,
commandParameters);
}
}
Click to expand...

The using block will call SqlConnection.Dispose which will call
Connection.Close. You don't need an explicit call to close.

David
 
As a matter of style, I call .Close() explicitly AND use the using
construct - however the main reason that I do this is just to explicitly
state what I'm doing as opposed to necessity. When .Dispose is called, the
connection string information is removed from the connection object and then
it calls .Close() - for all intents and purposes they are virtually
identical. As an aside, if you don't get in the habit of calling close()
explicitly you need to be darned sure you ALWAYS use using or you can easily
get yourself in some trouble.

I'd suggest that this isn't the culprit, at least not directly. Run a Trace
during a period that you usually see the resources dry up and make sure that
you aren't firing extra queries in a loop somewhere and that some other
component or application isn't the one eating up the connections.

HTH,

Bill
 
Unfortunately, this only happens in production. We have never seen this in
test and have been unable to reporduce it.
 
How this application handles DataReader ? Most of the time it's likely there
as the application block close the connection here. For DataReaders they
perhaps really on the calling code to close the connection ? Do they ?


Patrice


--
 
We have only seen the problemon update/insert (ExecuteNonQuery). However, we
do use the SQL Helper DataReader methods so it may be possible this is the
root cause.

I am also considering hanging PerfMon on one of the problem machines to
capture stats on pools, network, etc.
 
The second most common reason for the connection pool to overflow is too
much processing per operation. How long does each operation take? Your
system can only handle a finite load.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Jason said:
We have only seen the problemon update/insert (ExecuteNonQuery). However,
we
do use the SQL Helper DataReader methods so it may be possible this is the
root cause.
Click to expand...

This is probably it. Your DataReaders should all be opened in using blocks
too.

using (SqlDataReader dr=daab.ExecuteReader(...))
{
. . .
}

David
 
Back
Top