Explicitly Dispose connection?

Topics: Data Access Application Block
Mar 23, 2011 at 4:50 PM

Hello,

I am using this..

DataAccessor<Type> myAccessor =  SqlDatabase.CreateSprocAccessor<Type>("spname", new MyParameterWrapper(), new My ResultMapper());

myAccessor.Execute(....);

I assume that connection that used by this will be disposed eventually by GC, but

is there any way that I can explicitly dispose the connection used for this selection?

Thanks,

Mar 24, 2011 at 2:16 AM

DAAB will automatically close and dispose the connection as soon as it finishes executing the sproc accessor, you need not to do that explicitly. 

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Mar 24, 2011 at 3:01 AM
Edited Mar 24, 2011 at 3:02 AM

Hello,

I understand that, but i thought that if i could dispose it explicitly, that may be better for connection management.

Sometimes, I see timed out exception error due to exceed connection pool....I thought that since the connection wasn't closed property and explicitly,

the connection was being held in that pool, so at some point, maximum number of the pool could have been reached...

(BTW, the connection string I was passing is exactly same)

Can you think of any other reason other than this about this error?

Here is my method that I am using...

List<Type> RetrieveData() {

 SqlDatabase db = new SqlDatabase("Data...ConString.");

 DataAccessor<Type> myAccessor = db .CreateSprocAccessor<Type>("spname", new MyParameterWrapper(), new My ResultMapper());

 List<Type> ret = myAccessor.Execute(....);

 return ret;

}

Anything I am missing in above method  as far as the connection management is concerned?

 

THanks,

Mar 24, 2011 at 3:38 AM

What is your maximum pool size?  Is your application opening connections rapidly which might cause the maximum to be reached and thus prompts you to increase it a bit?

I checked the logic of the Execute code and I can see that the data reader gets closed as there was the use of the using statement.  I would advise you to monitor your connections using tools like the Performance Monitor specifically using the .NET Data Provider  for SqlServer. 

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Mar 24, 2011 at 4:37 AM

Is your application opening connections rapidly which might cause the maximum to be reached and thus prompts you to increase it a bit?

   -> Can you elaborate a bit more about this sentence?

       What did you meant by opening connections rapidly?

Mar 24, 2011 at 4:54 AM

I mean if there are multiple connections being opened concurrently causing the maximum limit to be reached and the maximum pool size is just too small for how your application is expected to work. 

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

Mar 24, 2011 at 5:43 AM

Oh..I got it, but no multiple connections opened concurrently.

Test environment is just single web request done by me via web browsing.

I was just visiting each web page actually is using that retrieve method behind. I noticed that sometimes I saw that exception.

Also, it should not be that quick cuz I was manually visiting web page to see that behavior...

I believe default pool size is 100..I can't think of anything that can cause more than 100 opened connection(If the connection was closed properly)

Any more idea?

I appreciate your help.

Thanks,

Mar 24, 2011 at 8:05 AM

did you check the actual performance of the stored procedure when running it in Sql Management studio?  Does it take more than 30 seconds to finish?  It's the only other possibility I can think of right now; the actual sp is taking too long to finish but the DbCommand is only set to 30seconds to timeout.

 

Sarah Urmeneta
Global Technologies and Solutions
Avanade, Inc.
entlib.support@avanade.com

May 31, 2011 at 6:41 PM

Hum..Strange behavior I have seen..(BTW, as I said I am using Ent Lib 5.0) at some point..

Type : System.InvalidOperationException, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 Message : Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Source : System.Data

Help link :

Data : System.Collections.ListDictionaryInternal

TargetSite : System.Data.ProviderBase.DbConnectionInternal GetConnection(System.Data.Common.DbConnection)

Stack Trace :   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

   at System.Data.SqlClient.SqlConnection.Open()

   at Microsoft.Practices.EnterpriseLibrary.Data.Database.GetNewOpenConnection() in e:\Builds\EntLib\Latest\Source\Blocks\Data\Src\Data\Database.cs:line 1176

   at Microsoft.Practices.EnterpriseLibrary.Data.Database.GetWrappedConnection() in e:\Builds\EntLib\Latest\Source\Blocks\Data\Src\Data\Database.cs:line 1200

 

   ....

  ...

This error was basically from Execute method from below in my code.

            DataAccessor<...> da = db.CreateSprocAccessor<...>(sp,
                ...;

            pEntities = da.Execute(
        ....
            ).ToList();

 

I deployed my web application to IIS(5.1) hosted in Windows XP Prof.

As far as I know, this pool error message is database related error message....However, it seems that whenever I restarted the IIS, it looks like this pool issue went away.

How could this happen? I didn't restart the SQL server, but I just restarted the IIS, which means...I think that pool connection issue should still remain regardless of IIS restart..am I wrong?

Also my SQL script doesn't take long time at all....

1. Is there any way that I can explicitly close the connection for Execute method?

2. Could you explain me a bit if you can, how this pool error message went way whenever I restarted the IIS? I think IIS should not be matter of this error.

 

Thanks,

 

May 31, 2011 at 7:20 PM

The database connection is automatically closed when you finish iterating over the result set - in your case, by calling ToList(). I'm not sure where the leak it, but the accessor isn't it.

Feel free to look at the code for the accessor's Execute method if you want more details on how it works.

-Chris

May 31, 2011 at 7:54 PM

In the implementation of IResultSetMapper...

IEnumerable<TResult> MapSet(IDataReader reader) {

.....

..

 ...Inside this function, should I explicitly close the reader object?

.. I don't think so though cuz the caller will close the reader object...right?

}

May 31, 2011 at 10:17 PM

No, you don't need to close the reader, it'll get called after the MapSet call returns. Or, more specificially, it'll get closed after the IEnumerable that MapSet returns has been completely iterated through.

 

 

May 31, 2011 at 11:08 PM

That's exactly what I thought too..

Then..I really can't think of any other spot where possibly connection is being leaked now..

Here is strange behavior in the testing.

Let's say "A" is the connection string I am using..

I created a test console app ..in that app, it connects with exactly same connection string "A" like web application does...

Here is the step that I tested

1. Run web app several times to raise the pool exception error..

2. I run the console app that I just created for testing to see where I can consume any data from the same database (Like I said, I am using exactly same connection string that web app is using)

   Strangely, there is no issue..I can consume the data without having any issue.

3. Run the web app,..then boom again..it says the pool exception error..

It is strange...If the connection was not available in the pool, then the console app that I created for testing, how it could read data without having any issue???

That should be same pool cuz it uses same connection string...

Doesn't make sense at all..any idea?

Thanks,

 

Jun 1, 2011 at 11:16 PM

That test doesn't make sense. The connection pool is per-process, not per-connection string across the machine. The behavior in the web app shouldn't be affecting the behavior of a completely separate console application at all. And, based on your results, it is not.