Hi ,

I am using OracleConnection object from Oracle ODP.net provider and
following is the behaviour which i am finding bit strange :

To start with , my argument is based on following facts :

1. Connection object is a reference type object .
2. All reference types are passed by reference even when done without
using modifier like ref / out .

Have a look at the following code : (More details after the code)

Class A
{
   OracleConnection conn ; // class variable

   public static void Main()
   {
     A a = new A() ;
     string connectionString = <StandardStuff> ; // Pooling false
     a.c(connectionString);

     connectionString = <StandardStuff> ; // Pooling true
     a.c(connectionString);
   }

   public void c(string connectionString)
   {
     try
        {
      // Get a connection string

      if(conn == null)
        conn = new OracleConnection(connectionString)
      else
        conn.ConnectionString = connectionString ;

       conn.Open() ;

       CloseandDispose(conn,<Pooling Attrbute Value from connection
string>) ;
        }
     catch(OracleException ex)
      {
        // print exception message
      }
     catch(Exception ex)
      {
        // print exception message
      }

   }

   public void CloseandDispose(OracleConnection conn,bool flag)
   {
      conn.Close() ;

      if(!flag)
      {
        conn.dispose();
        conn = null ;
      }
   }
}

Now in above mentioned code :

Case 1 : (OracleException)
========

When conn object is class variable and method c is called from second
time from main method after changing Connection String then it leads to
exception .

Reason is --> during first call when CloseandDispose method is called ,
it enters the loop to dispose and nullify the connection object but
second time when method c is called it doesn't validates the condition
conn = null and when it goes down and tries to open connection , then it
leads to exception :

"Can't open a connection object already disposed"

Now there are cases when exception doesn't happen i.e :

Case 2: --> I pass Oracleconnection objects between methods using ref
keyword and operation is successful .

Case 3 --> Don't use OracleConnection object in CloseAndDispose method
and it will nullify class object and valiadte conn = nul condition .

Case 4 -->  Declare OracleConnection as a local variable in main method
and pass it in all methods and even then it success .

Now in present scenario it seems to be an issue with Connection object
as Case 1 also should have been successful .

Other Details regarding code :
===================================================================

Code that , i have pasted is part of a test suite , which tests
OracleConnectionStringBuilder introduced as a part of ADO.net 2.0 .

Now , logic behind calling method C twice from the main method is that
connection string builder class is used to change connection string at
runtime , where it changes the pooling attribute and reconnect .

Initially when Connection is created in Pooling = false mode then i make
sure that after closing connection object is disposed and nullified ,
however in case of Pooling = true it's just closed , so that it can
return to the connection pool .

Reason for implementing a separate CloseAndDispose method is that in
original test suite , conn object and the CloseAndDispose method are
part of a base class , as multiple derived classes uses same object and
finally any of them can close and dispose , so essentially a method is
implemented in base class to complete the task .

Now as the question goes , point of confusion for me remains that in
case 1 , when i am passing the conn object without any ref / out it
gives me the exception as mentioned earlier but in other cases it
doesn't , which is bit strange and could be a possible issue with
Connection object .

===================================================================

Share your comments , in case you need some other specific details let
me know that .

thanks ,

Mrinal

Hi Mrinal,

Do you mean that when you run the Main code, the exception was thrown? I
tried it on my machine with both VS.NET 2005 and VS.NET 2003, this doesn't
repro. Is there anything that I'm missing?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Hi Kevin ,

Exception is thrown when Method C is called for second time , as you can
go through the logic , during first call / iteration :

since it's pooling false so connection object gets closed , disposed and
nullified .

Now during scond call to method C it should validate the logic Conn =
null and re instantiate the object , but it doesn't happen and it just
goes to else part , assign the new connection string and tries to open ,
when exception comes .

Now , did you tried with OracleConnection or SqlConnection or some other
driver's Connection object .

what i am thinking is this could be a possible issue with
OracleConnection object , so needs verification .

thanks ,

Mrinal

Kevin Yu [MSFT] wrote:

Show quoteHide quote

Hi,

I tried it on my machine, but the exception wasn't thrown. I called
CloseandDispose with false for the first time and true on the second time.
Am I missing something here?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Hi ,

Are you using OracleConnection from ODP.net or some other client like
SQlConnection .

As , i am wondering it could be a possible issue with ODP.net , so wants
a confirmation whether other clients too behave similarly .

Logically this shouldn't happen at all .

thanks ,

Mrinal .

Kevin Yu [MSFT] wrote:

Show quoteHide quote

Oh, I'm using the OracleConnection in the Microsoft Oracle Provider, it
seems to be working fine.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Thanks for your reply Kevin , i am hoping you have done exactly same
implementation as mentioned by me in the code snippet initially .

This could be a possible issue with ODP.net

Kevin Yu [MSFT] wrote:

Show quoteHide quote

Logically as you have mentioned that implementation is right and it
should have worked .

Mrinal Kamboj wrote:

Show quoteHide quote

Hi Mrinal,

I think I have done exactly the same implementation you mentioned in your
post. It might be Oracle's ODP.NET issue. It doesn't happen on the MS
Oracle Data provider and SQL provider.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Saving DataTable as XML
Using Typed Dataset with Winform vs ASP.NET
Timeout expired - on a simple SqlDataReader:Read() call
DAAB ExecuteReader method causes Exception
What is the quickest/most efficient way to insert 100,000 Rows