0

Oracle connections are not closing even if i rewrite the code like below. I have 3 types Oracle Connections like DataReader,DataAdapter and Normal.

Which part do i make mistake ?

Type:1 - Datareader connections

using (OracleConnection con = new OracleConnection(oradb))
                {
                    con.Open();

                    using (OracleCommand cmd1 = new OracleCommand("select  sql_fulltext \"SQL Text\" from gv$sqlarea where rownum<2 and sql_id='" + sqlid + "'", con))
                    {
                        OracleDataReader dr1 = cmd1.ExecuteReader();
                        dr1.Read();
                        rtbSQLDetails.Text = dr1.GetString(0);
                    }

                }

Type:2 Data Adapter Connections

using (odagetInstID = new OracleDataAdapter("select distinct INST_ID from gv$instance where status='OPEN'", oradb))
                {
                    odagetInstID.Fill(dtInstID);
                }

Type:3 Normal Connections

using (var con = new OracleConnection(oradb))
            {
                con.Open();
               //...
}
john true
  • 263
  • 1
  • 5
  • 26
  • 2
    Some providers deliberately keep an internal pool of underlying connections, to reduce the latency when opening connections. This means that the *underlying* connection will outlive the `DbConnection` instance. `SqlClient` *certainly* does this. Is it likely that `OracleConnection` is simply doing the same thing? If so, you can *probably* disable the pool in the connection-string, but that would usually be a bad idea. Edit: the "Using Connection Pooling" section [here](https://www.connectionstrings.com/oracle/) makes this sound very likely. – Marc Gravell Nov 27 '17 at 14:58
  • For speed and or caching I think this is what you are seeing .. there is no issue with your existing code. not to mention if the code were to be called many times, new instances would be created then Disposed when leaving the using(){ } code block. Oracle exception in regards to connection being open already would certainly throw an error if your code had open connection issues. – MethodMan Nov 27 '17 at 15:01

1 Answers1

0

The "using" close implies a "dispose" at the end of the loop. However, Microsoft recommends expressly calling a close.

See also... Close and Dispose - which to call? (The 2nd answer gives almost the exact same scenario)

Jason Geiger
  • 1,912
  • 18
  • 32
  • No, there is no reason to explicitly call `Close` in addition to `Dispose`. What's recommended is that you do one or the other, rather than waiting for the object to clean up its own resources on its own. – Servy Nov 27 '17 at 16:06
  • @Servy in practice I wholeheartedly disagree. Between garbage collection and connection pooling this connection will stay open. Expressly closing the database connection should give the desired results and prevent numerous new connections from the same machine. – Jason Geiger Nov 27 '17 at 16:30
  • Yes, and *the `using` does exactly that*. That's it's whole purpose. Claiming that a `using` isn't explicitly closing the resource is just factually wrong, as is even stated in your own links. That you personally disagree that with a proven fact doesn't really mean anything. It's not an opinion you can disagree with. – Servy Nov 27 '17 at 16:38