DAAB-Connection closing

Topics: Data Access Application Block
Dec 10, 2008 at 8:09 PM
Hi,

I went through some discussions and learnt that methods which we call on Database object will close connections for us.
ex: ExecuteNonQuerry will open and close connections for us. In case of DataReader, connection will be closed when user disposes DataReader.

With this assumption, my implementation does not dispose "Database" object. I executed a database request and opened to see if the session is closed on oracle server. I figured that the session is still open and  next time I tried executing the data base request it is reusing the same session( which seems good)

As dataaccess is behind service layer. each user will be creating ( instantiating mode is set to session and concurrency set to single) a connection. if  100 users are requesting a service, 100 sessions are opened for db and they are not closed.


I do not see any method to dispose "Database" object exposed in database application block.

I would appreciate if any one could point me in the right direction.


Thanks,
Dec 11, 2008 at 3:32 AM
If you create a Database object, it doesn't automatically open a connection.  Connections are only made when you call on Execute an SqlCommand, etc... And as you've said, connections are automatically being closed for you when you call on these commands.  Thus, you only need to make sure you dispose connection if you explicitly create a connection ( db.CreateConnection()) object.



Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Dec 11, 2008 at 4:07 AM
Thank you for the information provided.

I checked source for DataAccessApplicationBlock, this seems to be closing connection when connection is created by "ExecuteNonQuery". If we pass existing connection, it seems to be keeping it open...

In my case, I am creating database object and using it as mentioned below.

Database db =  DatabaseFactory.CreateDatabase("connectionstring"); 


using (DbCommand cmdgetdata=  db.GetStoredProcedureCommand("XYZ"))
{

    db.AddInParameter(cmdgetdata,"Parameter1",DbType.Int32,30);
    db.AddOutParameter(cmdgetdata,"Parameter2,Dbtype.string,int.maxvalue);
    db.ExecuteNonQuery(cmdgetdata);
   string returndata= db.GetParameterValue(cmdgetdata,"Parameter2").ToString();
}


I am not disposing the "db" object here. but when I checked v$sessions in oracle database, the session seems to be still open..


I would appriciagte if you could share your thoughts over it...
Dec 11, 2008 at 5:12 AM
Sorry, I'm not familiar with v$sessions in oracle database and I don't have an Oracle database installed so I can't check it out.  What I did was debug the same code you posted using the entlib pdbs and actually saw that the connection was being disposed in the ConnectionWrapper class. 


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

 

Dec 11, 2008 at 7:16 AM
I researched about the v$sessions but I can't determine if it pertains to a physical connection to the database.  But this might give you an idea.  If a connection is disposed, it's not actually being destroyed but returned to the connection pool so that others can pick it up.  It's like it's logically closed but still physically open.  


Sarah Urmeneta
Global Technology & Solutions
Avanade, Inc.
entlib.support@avanade.com
Dec 11, 2008 at 1:47 PM
Hi.. Thank you for the information provided. this answers why physical connections are still open even after closing connection.


Jan 31, 2009 at 1:04 PM
Hi msaivara,

Did u find any solution. I also faced the same problem.
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 obejct not closing the connection properly.

When we installed in IIS. the tester testing the application. It working good for some time. after some hourse, 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
Jan 31, 2009 at 10:04 PM
hi,

I went through the code of Enterprise library to see if the connection is closed once we execute the commands using EntLib provided classes. I see that all the connections are closed except when it returns Data Reader(which requires connection).

Enterprise Library code has "Connection.Close()". If I understand it correctly it will close connection but it will not dispose the physical connection. it iwill move connection in to the pool.


By default connection pooling is enabled, it should be re using the connection.I tried following thigs, this resolved the issue.

1. I made sure that all database connection and actions are done using  DAAB classes.I made sure that all connections are opened from a single connection string (pref.. from config file)
2. enabled instrumentation from DAAB to track connections opened from the application(performance counters)
3. configured connection poll settings(max connections in connection pool). This helped me to restrict max number of connections we can open and connection timeouts etc
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx