472,961 Members | 2,760 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,961 software developers and data experts.

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
4 11237
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Mike Chamberlain | last post by:
Hi all. I'm trying to extend the Microsoft Enterprise Library Data Access Application Block (http://msdn.microsoft.com/library/en-us/dnpag2/html/daab.asp?frame=true) to work with a Borland...
0
by: ad | last post by:
I have used Data Application Access Block2 in my web application. Now I download the Library Data Access Application Block. and read the document. May be I am stupid, I can't migrate it to...
0
by: jb | last post by:
Is it possible to keep a connection to the database open whilst using the enterprise library Database methods like ExecuteDataSet(), ExecuteScalar() etc in a loop without using transactions (or...
7
by: galico | last post by:
Hi All, We are having a very strange problem with the above. We have designed an application in ASP.NET 2.0 that uses the enterprise library data application blocks amongst others. We seem to be...
0
by: TattedProgrammer | last post by:
Hello All, I am completely stuck, I have tried for an entire day to get a simple procedure call to my Oracle Db via the enterprise Library 06. I am not an oracle fan to start, but have to deal...
1
by: Victor | last post by:
Hi guys I have a question here. I am currently using Enterprise Library Jan 2006 version. I found out where i execute the Database.executeDataSet() the Enterprise Library will not close the...
0
by: nostradumbass77 | last post by:
Using Enterprise Library 2.0 (Jan 06) and .NET 2.0 (VB.Net 2005) Dim dbPF As Data.Common.DbProviderFactory Try dbPF = Data.Common.DbProviderFactories.GetFactory("System.Data.OleDb") db = New...
4
by: =?Utf-8?B?Vmlua2k=?= | last post by:
Hello Everyone, I downloaded enterprise library 2.0 , 2006 today . I followed all the instructions that were given in the documentation. I tried to follow everything that was mentioned in...
2
by: kellygreer1 | last post by:
I'm working on some internal company web services and I have a question about resource management within the Enterprise Data Access Block. I have about 20 methods which look similar to the one...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
2
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.