Connection of OracleDataAdapter doesn't close

  • Thread starter Thread starter Theodor Ramisch
  • Start date Start date
T

Theodor Ramisch

Hi NG,

i have a ASP.Net application and an Oracle 9i database. I use the
System.Data.OracleClient library to connect. I set the
Max Pool Size property to 2 and watched the opened connections which
increased after the application start to 2. The problem is that the
first connection is used to query all statements and the second
connection stops after the usage of an OracleDataAdapter. The status
of the second connection is INACTIVE, but no other query will use it.
After a while when the first connection is busy, i get the following
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.

But the second connection is still "INACTIVE" and the
"Current Statement" value is still the query of the DataAdapter I used
at the beginning:

string SQL="SELECT * FROM bla ORDER BY myName -- " +
DateTime.Now.ToLongTimeString();

using(OracleCommand Query = new OracleCommand(SQL,_Conn)) {
_Adapter=new OracleDataAdapter(Query);
_Cb=new OracleCommandBuilder(_Adapter);
if(_Users.Tables["bla"]!=null) _Users.Tables["bla"].Clear();
_Adapter.Fill(_Users,"bla");
_View=_Users.Tables["bla"].DefaultView;
}

_Conn is a property of my class, I dont't call .Open or .Close beacause
the OracleDataAdapter should do that. But why isn't the connection
reused?

Regards,

Theodor Ramisch
 
Hi Theodor,

It is not clear to me - are you using the same SqlConnection instance or
what?
 
Theodor

Just because the session is marked INACTIVE from V$Session does not
necessarily mean it is available for use in the pool. Status INACTIVE just
means that the session has not had activity for some time.

If you explicitly close the connection or if the Connection object is
reclaimed, the session/connection will be returned to the pool. Even though
the second conection object has gone out of scope, the conection will not be
returnedto the pool until a garbage collection runs

regards
roy fine
 
Hi,

Miha Markic said:
It is not clear to me - are you using the same SqlConnection instance
Click to expand...
or what?

Sorry for my bad english...
No, I always create a new connection (all connections are created with
using). Only the one for the DataAdapter is a property of my class which
just get opened and closed, but not destroyed like the other ones.

Regards,

Theodor
 
Hi Theodor,

Are you sure you are closing your connections?
using is fine for closing them.
Just be sure that you either Dispose(using..) or Close explicitly.
 
Hi,
Are you sure you are closing your connections?
using is fine for closing them.
Just be sure that you either Dispose(using..) or Close explicitly.
Click to expand...

yes, I'm sure that all other connections get disposed. Just the
connection of the Adapter is special, because the Adapter must
write the data back to the DB later. And if I Dispose the
adaper's connection, it would have no connection to update. Or?

Now I call .close manually after the adapter.Fill and adapter.Update,
but the connection wont't get reused.

Regards,

Theodor
 
Theodor Ramisch said:
Hi,


yes, I'm sure that all other connections get disposed. Just the
connection of the Adapter is special, because the Adapter must
write the data back to the DB later. And if I Dispose the
adaper's connection, it would have no connection to update. Or?
Click to expand...

You might Close it and after that re-Open it just before you need it.
If you don't close it, it will be active and won't return to thread pool
until you close it.
Now I call .close manually after the adapter.Fill and adapter.Update,
but the connection wont't get reused.
Click to expand...

That's strange. Are you using the exact same connection string?
Or better, I suggest you to create a simple test program where you can
create, open and close some connections just to see how it behaves.
 
Back
Top