Ent Lib 4 not kill the connection properly

Topics: Data Access Application Block
Jan 31, 2009 at 1:07 PM
Hi all,

I am in web project which support Oralce/sql server. we are using EntLib 4 as DAAB in dalc layer. 
When we debug, eventhough using the Using Statment for reader (Using (IDataReader =..)
after finished the block the connection is still there.

It shows Awaiting Command in SQL Server (using wp_who) and Inactive in oracle.(using V$session)

I think the entlib database obejcts not closing the connection properly.

When we installed the DAL in IIS. the tester testing the application,  its working good for some time. after some hours, the conenction time out problem and TNS:Connection closed issues rising. If we check in V$Session in oracle it shows aspnet_wp.exe   120 inactive connection. Why the EntLib open this no of conection?

i searched in google past 50 hrs. Still i can't find the solution. I am very sure i closed all the readers and conection.

I highly appriciate your help.


By
Ramesh.p
Feb 2, 2009 at 5:47 AM
When the connection is closed, it is not actually destroyed.  It will be returned to the pool.  To address your problem of  having 120 inactive connections, configure the maximum connection in the pool as well as the connection timeout. 


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Feb 2, 2009 at 7:46 AM
Thank you for your reply.

Problem is it wont use the existing connection in pool. When i checked my DAL through NUnit tesing, it is not reusing the connection. the connection keep on growing. for ex. if it open 30 connection to finish a NUnit testing. It will take another 30 for next time. and another 30 for next time. keep on growing. If we close the nUnit UI. All the conenction will be cleared.  There is no inactive session.
(And i tried Max pool options Due to above problem its not workout)

If we set Pooling = no. then no inactive connection in database. But i think that is lead to performance issue. am i right?

Can you guess where is the problem?

Regards
Ramesh.p
Feb 2, 2009 at 7:59 AM
Yes, having connection pooling can improve the response time of your application.  It prevents the overhead of creating a connection each time you need to access your data store.  What is the value of your max pool connections when you executed your tests?  Try running an SQL Server Profiler, if you see "sp_reset_connection", this means that a connection was reused from the pool.


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Jan 12, 2011 at 8:06 PM
Edited Jan 12, 2011 at 8:07 PM

Hi ,

We are also facing the same problem.

Could you please let us know if you have found any resolution to your problem.

Thanks & Regards,
Sathish K

Jan 12, 2011 at 11:28 PM

What version of EntLib are you using?  In addition, are you also dealing with data readers?  If yes, please post your relevant code.

 

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

Jan 13, 2011 at 3:09 AM
Edited Jan 13, 2011 at 3:18 AM

Hi AvenadeSupport,

Our team is facing the connection time out issue

Ent Lib Version: 3.1
We are using DataReader.
Code snippet:

Database database = DatabaseFactory.CreateDatabase("ConnectionString");
using (DbCommand command = database.GetStoredProcCommand("storedProcedureName"))
{
database.AddInParameter(command, "@Param", System.Data.DbType.Guid, new Guid("DDB2D8B8-D7EB-48F9-B4FE-C91102D4AA41"));
using (IDataReader reader = database.ExecuteReader(command))
{
while (reader.Read())
{
//Read from Reader and fill up the value object
}
}
}
Exact Error : 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. 

Every now and then we keep checking the open connections in master.dbo.sysprocesses and running sp_who2, most of the times it is 4-5 open connections but at times it spikes to 102-105 connections and instantly we see the timeout error on the web page.

This is an intermittant problem and seems to crop up every now and then.  Please let us know if any one has a resolution to this problem.

Thanks in advance,

Manav,

 

Jan 13, 2011 at 3:47 AM

I'm not sure if you're encountering the same bug mentioned by Chris Tavares in this thread.  I don't have much information about that bug, do you think it's possible for you to try using EntLib 5.0?

 

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