ExecuteDataSet - need connection to stay OPEN!

Topics: Data Access Application Block
Apr 1, 2010 at 8:48 PM


We have a situation where we need to keep the connection to the database open and reuse it for the duration of our services lifetime.

We are using ExecuteDataSet, that, as I understand it, opens and closes the connection for me.

I have read things online about SqlHelper.ExecuteDataSet() which looks like it would allow me to pass the connection in but can't seem to find this reference.

Please advise on how we can achieve this? 



Apr 5, 2010 at 7:03 AM

Hi Jessica,

The way I understand your problem is that what you need is to have some kind of connection pooling implemented in your application.  Please let me know if this is indeed what you need.

Whereas in EntLib DAAB, ExecuteDataSet is supported to have this connection pooling which I think would fit your needs. 

If what you want is to have control when you want your connection to be closed then try looking in using ExecuteReader as another option, where you will be the one responsible on closing the reader which also closes the connection. See if this fit for you. Let me know if there are questions. HTH.


Apr 27, 2010 at 4:57 PM

Hi Ginkapitan,

I need the connection to stay open for the life of the service.

Can you send an example of how I can using pooling to do this?

If I use a datareader I need to put the return values from the stored procedure into a DataSet ultimately.  But it seems you are saying with pooling this would be unnecessary. Is tha correct?




Apr 28, 2010 at 1:13 AM

Actually no.  Connection pooling is just a way for database connections to be reused.  In your case as I understand it, you want a single instance of a connection to be used throughout your service.  There's no overload of ExecuteDataset which allows you to explicitly pass a connection but it allows you to pass a DbTransaction.  The connection that is used by the DbTransaction will be the same connection used to execute your command.

DbConnection connection = database.CreateConnection();
DbTransaction transaction = connection.BeginTransaction();
database.ExecuteDataSet(command, transaction);

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.