How to disconnect in Oracle.ManagedDataAccess?

Question

I have a special situation where I must disconnect and reconnect from the Oracle database. (I must check whether my connection string is still working, i.e. whether my password is still valid.)

Unfortunately, though, connection.Close() doesn't close the session. When I reconnect with a new connection, I am getting my old session back.

Here is my code:

using Oracle.ManagedDataAccess.Client;

...

string connectionString = "Data Source=mydb;User Id=myuser;Password=\"mypwd\";";

using (OracleConnection connection = new OracleConnection())
{
  connection.ConnectionString = connectionString;
  connection.Open();
  using (OracleCommand command = new OracleCommand("DBMS_APPLICATION_INFO.SET_CLIENT_INFO", connection))
  {
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("input", OracleDbType.Varchar2, "hello", System.Data.ParameterDirection.Input);
    command.ExecuteNonQuery();
  }
  connection.Close();
}

using (OracleConnection connection = new OracleConnection())
{
  connection.ConnectionString = connectionString;
  connection.Open();
  using (OracleCommand command = new OracleCommand("DBMS_APPLICATION_INFO.READ_CLIENT_INFO", connection))
  {
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("output", OracleDbType.Varchar2, 4000, "", ParameterDirection.Output);
    command.ExecuteNonQuery();
    string clientInfo = command.Parameters["output"].Value.ToString();
    MessageBox.Show(clientInfo);
  }
  connection.Close();
}

This code results in a message box showing "hello", although my new session has never set the session variable and must hence not know this value.

So, how do I ensure in Oracle.ManagedDataAccess that my old session gets closed and I get a new session, whenever I want to?

(I know I could keep my old connection open and then open another one, but by opening an additional session every time, my programm would end up with probably hundreds of open sessions for a single user some time, where it should be only one, of course.)

Answer 1

When closing a connection it is by default return to the connection pool.

You can call ClearPool to remove all connections with a specific connection string from the pool.

// Create a new connection object
OracleConnection connNew = new OracleConnection(strConn);

// Clears the pool associated with Connection 'connNew'
// Since the same connection string is set for both the connections,
// connNew and conn, they will be part of the same connection pool.
// We need not do an Open() on the connection object before calling
// ClearPool
OracleConnection.ClearPool (connNew);

See https://docs.oracle.com/database/121/ODPNT/OracleConnectionClass.htm#CHDFJBAF