Enterprise Library 2.0 - ExecuteDataSet doesn't closes the database connection - any idea?

[-pb-]

I am using .NET 2.0 along with Data Access Application Block of
Enterprise Library 2.0 in one of my wrapper class and what I observered
is once the connection is left open after execution of ExecuteDataSet
function of Database class.

I used Preformance counter for SQL user connection. When
DataFactroy.CreateDatabase is executed it create a connection but when
Database.ExecuteDataset doesn't closes the connection. Furthermore, if
I close by application then connection is also closed and I can see the
count going down by 1 in the Performace counter. This doesn't happen
when ExecuteDataSet is called.

Following is the code snippet I am using in my wrapper class.

private DataSet ExecuteSp(string storedProc)
{
Database db = DatabaseFactroy.CreateConnection();
DbCommand dbCommand = db.GetStoredProcCommand("any_sp");

//Set the parameter in the command object
db.AddInParameter(dbCommand, "Param1", DbType.String,
"Param_val");

//Execute the sp and generate the dataset
DataSet dataSet;
using (dataSet = db.ExecuteDataSet(dbCommand))

//reset the variables
dbParameter = null;
dbCommand = null;
db = null;

//return dataset
return dataSet;
}

According to documents, ExecuteDataSet closes the connection but as per
my observation on Performace counter it isn't

Does anyone have any idea on how to close the connection once created
by DataFactory.CreateDatabase()?

[-pb-]

Thanks...it works now...

I can now see the spike in the performance counter and also verified
the connection pooling...thanks a lot..

David Musgrove wrote:

Try disposing the DbCommand like this:

private DataSet ExecuteSp(string storedProc)
{
Database db = DatabaseFactroy.CreateConnection();
using (DbCommand dbCommand = db.GetStoredProcCommand("any_sp"))
{

//Set the parameter in the command object
db.AddInParameter(dbCommand, "Param1", DbType.String,
"Param_val");

//Execute the sp and generate the dataset
DataSet dataSet;
dataSet = db.ExecuteDataSet(dbCommand);
}

//reset the variables
dbParameter = null;
dbCommand = null;
db = null;

//return dataset
return dataSet;
}

D

[Carl Daniel [VC++ MVP]]

"David Musgrove" <Da***********@discussions.microsoft.comwrote in message
news:A2**********************************@microsof t.com...

Try disposing the DbCommand like this:

private DataSet ExecuteSp(string storedProc)
{
Database db = DatabaseFactroy.CreateConnection();
using (DbCommand dbCommand = db.GetStoredProcCommand("any_sp"))
{

//Set the parameter in the command object
db.AddInParameter(dbCommand, "Param1", DbType.String,
"Param_val");

//Execute the sp and generate the dataset
DataSet dataSet;
dataSet = db.ExecuteDataSet(dbCommand);
}

//reset the variables
dbParameter = null;
dbCommand = null;
db = null;

Setting local variables to null serves no purpose other than deliberate code
bloat. The variables become available for collection after the last
reference (even before the method returns), so there's nothing to be gained
by setting them to null just before the method ends.

-cd

[Chris Mullins]

"Carl Daniel [VC++ MVP]"

>//reset the variables
dbParameter = null;
dbCommand = null;
db = null;

Setting local variables to null serves no purpose other than deliberate
code bloat. The variables become available for collection after the last
reference (even before the method returns), so there's nothing to be
gained by setting them to null just before the method ends.

Even worse, setting them to null actually keeps them around longer. If
you're running in Release Mode, then the GC would clean those variables up
as soon as it hit the last line that references them. (In debug mode, this
isn't the case, as it would make debugging practically impossible, and so
they use scope instead).

By doing this, the originally poster is making that "last reference" further
down in the method, thereby extending the lifetimes of those variables.

--
Chris Mullins, MCSD.NET, MCPD:Enterprise
http://www.coversant.net/blogs/cmullins

[=?Utf-8?B?RGF2aWQgTXVzZ3JvdmU=?=]

Mea culpa!

I had only read far enough to identify the problem, and had persisted these
"set to null" lines by the powers of copy and paste.

D