C# WinForm Oracle sessions hangs after connection closed

Question

I'm using multiple connections in different methods, but when I close the connection, the connection sessions hangs in database sessions as inactive until I terminate the .exe file. I'm using connections in using statement like:

using(OracleConnection con = new OracleConnection(constring))
{
con.Open();
}

And I also tried to close and dispose the connection manually and use OracleConnection.ClearPool(con) and OracleConnection.ClearAllPools() but none of them works.

the reference im using: System.Data.OracleClient version 2.0.0.0

connection string Data Source = ip/db; User Id = user; Password=pw;

Answer 1

This behaviour is normal because if you Dispose(); a connection the ConnectionPool from Oracle doesn't really destroy the connection but set the status to 'free'. So the next time you open a connection, you don't really open a new connection, you just look for a 'free' connection in your ConnectionPool and use this one. If no free Connection exists, a new one will be created.

This behaviour garants you a better performance because Open/Close Connections cost much afford.

If you use:

using(OracleConnection con = new OracleConnection(constring)) 
{
}

or

con.Close();

This is the same as you use Dispose();. Using just make sure that the Dispose is called on the end of your using block. That's why using only works on objects which implement IDisposable.

So far ok...But why doesn't OracleConnection.ClearAllPools()close the connections? It does but Oracle directly create a new Connection, so that your ConnectionPool is filled up to your MinPool-Size. This is the reason why BigTallJosh's code:

"Min Pool Size = 0; Max Pool Size = 16; Incr Pool Size = 2; Decr Pool Size = 1; Validate Connection = False; Connection Lifetime = 0;

stop this behaviour. A Min Pool Size of 0 will stop automatic creation of new connections.

Here you can find a good explanation about ClearAllPools(); Method.

So to finally answer your question. Set the MinPoolSize=0 and after that call connection.ClearAllPools();. This should work.