467,879 Members | 1,224 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,879 developers. It's quick & easy.

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

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()?

Dec 28 '06 #1
  • viewed: 10937
Share:
4 Replies
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
Dec 28 '06 #2
"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
Dec 29 '06 #3
"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
Dec 29 '06 #4
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
Dec 29 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by jb | last post: by
1 post views Thread by Victor | last post: by
4 posts views Thread by =?Utf-8?B?Vmlua2k=?= | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.