ODP.NET

It is very important to call the Dispose() method on all Oracle command objects otherwise you will quickly run out of available cursors when using pooling.

Without dispose (on the command/reader) the connection will be put back into the pool with open cursors still present from your previous calls. It'll grow and grow!!

If your using c# get into the habit of wrapping your connection and command objects with using{}. E.g.

using(OracleConnection objConn=new OracleConnection ())
{
//your code
using(OracleCommand objConn=new OracleCommand ())
{
//your code
}


objConn.Close();
}

Darren

hi Darren ,

I understand what you mean with disposing commands but in your example why did you dispose connection ?

IMHO its simply a good habit to get into. Looking at the Oracle examples within odpnet.pdf they regularly explicitly call Dispose() on their connections after calling Close()

D

Extract from Oracle Documentation:..........


"Oracle.DataAccess.Client Namespace 4-59"
// C#
...
OracleConnection con1 = new OracleConnection();
con1.ConnectionString = "User Id=myschema;Password=mypassword;" +
"Data Source=oracle;";
con1.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD"
// A new connection is created; A new Connection Pool X is created
con1.Dispose(); // Connection is placed back into Pool X
OracleConnection con2 = new OracleConnection();
con2.ConnectionString = "User Id=MySchema;Password=MyPassword;" +
"Data Source=oracle;";
con2.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD" A connection is
OracleConnection Class
// obtained from Pool X; A new connection is NOT created.
con2.Dispose(); // Connection is placed back into Pool X
OracleConnection con3 = new OracleConnection();
con3.ConnectionString = "User Id=\"MYSCHEMA\";Password=MYPASSWORD;" +
"Data Source=oracle;";
con3.Open(); // Attempts to connect as "MYSCHEMA/MYPASSWORD" A connection is
// obtained from Pool X; A new connection is NOT created.
con3.Dispose(); // Connection is placed back into Pool X

If pooling is enabled both Close() and Dispose() will place the connection back into the pool. However, the OracleCommand whose Dispose() has been called on will not be able to open a connection again, but if Close() is called, that OracleConnection has the ability to reopen a connection via Open() and obtain a connection from the pool (but not necessarily the same connection as before).

If pooling is not enabled, the Dispose() will internally call Close() as well as free all the resources that were allocated for the connection. In the case for the Close(), the connection is closed, but the resources are not freed. And hence, if Open() is called on it again, the resources that have been allocated are re-used and the Open() will simply establish a connection. If you're not going to re-open a connection in a non-pooled environment, Dispose() is recommended where you explicitly free the resources rather than have the CLR's Gargabge Collector clean up the unused resources at an undeterminate time.

Hi, why you say that. I mean,, the normal way should be Close() only, otherwise, the resources administration is not optimal...I want to know why oracle use Close() and Dispose() into the odp.net pdf documentation.

I only want to know what option is better.

if you read odp.net pdf, for example, from ODC 10.2..., you see:

con.Close();
con.Dispose();
Console.WriteLine("Connection is placed back into the pool");

What pool ??? if you call Dispose() directly the connection object go into the GC?

I appreciate any help. I am confused about documentation.

regards

gabriel

[url http://forums.oracle.com/forums/thread.jspa?threadID=515899]Here is a discussion about Close() and Dispose()