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

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

 

[Miha Markic [MVP C#]]

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?

 

[David Browne]

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 using block will call SqlConnection.Dispose which will call
Connection.Close. You don't need an explicit call to close.

David

 

[W.G. Ryan eMVP]

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

 

[Jason]

Unfortunately, this only happens in production. We have never seen this in
test and have been unable to reporduce it.

 

[Patrice]

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


--

 

[Jason]

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.

 

[William \(Bill\) Vaughn]

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

 

[David Browne]

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.

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

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

David