DAAB, connection pooling, and ODP.Net

Topics: Data Access Application Block
Mar 9, 2007 at 10:56 PM
Planning to do some TechNet reading over the weekend, but thought someone might be able to shed some light on this.

How does the DAAB handle closing a connection? In reading through the code, it seems that before and after a call to something like LoadDataSet, the connection associated with the DbCommand object is in fact 'closed'. But, the code isn't (or maybe I'm not looking in the right place) explicitly running a .Close or .Dispose accept in the data of the DataReader methods.

I'm curious because after running a quick query (using ODP.Net as my provider), the connection shows up in V$SESSION on Oracle. The connection inside the DAAB looks closed after the call to the .LoadDataSet method. However, no matter how long I sit idle, V$SESSION still reports an INACTIVE session from my client application. It doesn't go away until I actually shut the entire app down.

Is the DAAB closing connections with each use?

Anybody else using ODP.Net and experience anything different?

Thanks,
Chris
Mar 9, 2007 at 11:18 PM
Edited Mar 9, 2007 at 11:19 PM
Hi Chris -

Yes the DAAB closes the connection on each use in most cases. The times when this does not happen are:
  • When you return a DataReader or XmlReader (since you can only access data when the connection is open)
  • When the command is executed in the context of a TransactionScope (v3 only)
  • When using the SQL Server CE provider (v3 only)

One more thing to keep in mind is that the ADO.NET managed provider may implement connection pooling of its own. For example, if the DAAB or you close a SqlConnection, by default it won't really be closed by the provider - it will be returned to the pool. I haven't used ODP.NET so I don't know if it's doing its own pooling or not, but it looks like this may be the case.

HTH
Tom

Mar 10, 2007 at 3:46 AM
Thanks, that may be the case, that ODP.Net is doing it's own pooling outside of the DAAB. I get the same behavior whether or not I use the DAAB (tried it tonight). Just using ODP.Net objects, it does the same thing, and the DAAB returns those same objects as well.

I'm going to do some more reading on ODP.Net's connection pooling and see what's going on w/ that. I read some stuff earlier than seemed to indicate that there are some things on the oracle server that control how long it allows connections to sit there 'inactive' before it drops them all-together, but i can't imagine that would be impacting connection pooling on the client.

this is just WIERD.
Mar 13, 2007 at 4:01 AM
FWIW, if anyone is interested. I was able to finally confirm how this is working, and that it's working as you'd expect, just not in the timeframe you'd expect based on anything in the connection string.

I setup an Oracle connection with min pool size = 0, connection lifetime of 5 seconds, then opened, closed, and disposed of the connection in code. I then flipped over and watched V$SESSION, and finally, after exactly 6 minutes (at 361 seconds), the connection dropped off Oracle. Nothing in the connection string would tie it to 6 minutes, so you got me. Without closing my app, I then re-ran my open, close, and dispose, and it came right back, no errors, and ran for another 6 minutes and dropped.

Anyway, I know that I watched another app for well over 6 minutes the other day, and it never dropped. So, it's either something configured on the Oracle side, by a DBA, or it could be that my client app and database are not playing nice at the client site where I'm working due to differences in versions of ODP.net and the database not matching up exactly.

I performed my test above with 10g express and ODP.net, all the most current updates so that everything was 'in sync'.

Chris