Error when trying to connect to Oracle 10g database from C# program employing minimal set-up configuration

Question

I'm experiencing an error when trying to connect to a remote Oracle 10g database from a C# 2008 Express Edition application I'm developing. I'm trying to use a minimalist, non-intrusive approach to the development with a view to ClickOnce deployment to user workstations.

In respect of the above I've investigated the following documents (amongst others..) -

What is the minimal setup required to deploy a .NET application with Oracle client 11?

http://jeremybranham.wordpress.com/2011/04/25/oracle-instant-client-with-odp-net/

http://ora-00001.blogspot.com/2010/01/odpnet-minimal-non-intrusive-install.html

http://splinter.com.au/using-the-new-odpnet-to-access-oracle-from-c

Connect to Oracle with odp.net and the OCI from C#

In view of the error I've experienced I've created a simple test app. consisting of a single (wpf) page with one button. In the click-event of the button I attempt to create a connection to an Oracle database -

private void button1_Click( object sender, RoutedEventArgs e )
{
    OracleConnection oraConnect;

    // string previously used OK in other projects
    string connectionString = "Data Source=" +
           "(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = myServer)(PORT = 1521)))" +
           "(CONNECT_DATA =(SERVICE_NAME = myOracleDb)))" +
           ";Password=myPw;User ID=myID;";

    using ( oraConnect = new OracleConnection( connectionString ) )
    {
        try
        {
            if ( oraConnect.State == ConnectionState.Closed )
            {
                oraConnect.Open();
                MessageBox.Show( "oraConnect is attempting to open.." );
            }
            else
                MessageBox.Show( "oraConnect open to DB: " + oraConnect.ServerVersion.ToString() );
        }
        catch ( NullReferenceException nullExcept )
        {
            MessageBox.Show( "Caught error: ." + nullExcept.ToString() );
        }
        catch ( OracleException dbEx )
        {
            MessageBox.Show( "OraException - " + dbEx.Message.ToString());
        }
        catch ( Exception ex )
        {
            Exception current;
            current = ex;

            while ( current != null )
            {
                current = current.InnerException;
            }

            MessageBox.Show( "Db base exception - " + ex.GetBaseException().ToString() );
        }
        finally
        {
            oraConnect.Close();
        }
    }
}

Following the information in the above articles I've ensured that the following Dll's are in my "bin" folder -

• oci.dll
• ociw32.dll
• orannzsbb10.dll
• oraocci10.dll
• oraociicus.dll
• msvcr71.dll

(the last named in desperation...) and have referenced 'Oracle.DataAccess.dll'.

The error message (at 'catch ( OracleException dbEx )') is -

"Oracle.DataAccess.Client.OracleException was caught
  Message=""
  StackTrace:
       at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
       at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, Object src)
       at Oracle.DataAccess.Client.OracleConnection.Open()
       at OracleConnectionTest.Window1.button1_Click(Object sender, RoutedEventArgs e) in C:\Documents\Visual Studio 2008\Projects\OracleConnectionTest\OracleConnectionTest\Window1.xaml.cs:line 69
  InnerException: "

Line 69 is 'oraConnect.Open();'.

In addition, the following is reported -

"((Oracle.DataAccess.Client.OracleException)($exception)).DataSource' threw an exception of type 'System.NullReferenceException".

I'm assuming from the NullReferenceException within the datasource that the problem lies in one of the dlls' (?) as I 'new' the OracleConnection above before trying to reference it.

In addition, the code execution jumps the 'catch ( NullReferenceException nullExcept )' and goes straight to the OracleException catch.

Sorry for rambling on but hope this makes sense? Any help/advice appreciated!

Answer 1

OK, very late getting back to this for which many apologies!

In the interim, our DB has been upgraded(!) and in changing the dll list to include -

• oraocci11.dll
• oraociccus11.dll
• OraOps11w.dll
• orannzsbb1.dll

from the '10' versions, still with no success, I edited App.xaml (following an extensive search here and on the web) with the following -

    <system.data>
      <DbProviderFactories>
         <add name="OracleClient Data Provider"
              invariant="System.Data.OracleClient"
              description=".Net Framework Data Provider for Oracle"
              type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=##################"/>
      </DbProviderFactories>
   </system.data>

   <!-- publicKeyToken obtained using Reflector to investigate dll -->
   <runtime>
      <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
         <dependentAssembly>
            <assemblyIdentity name="Oracle.DataAccess"
                              publicKeyToken="##################"
                              culture="neutral"/>
            <bindingRedirect oldVersion="10.2.0.100" 
                             newVersion="2.112.2.0"/>
         </dependentAssembly>
      </assemblyBinding>
   </runtime>

The bindingRedirect did the trick!

The dll's appear to be extremely dependent on compatible version numbers

Wish I could say I really understood how this works but it does and I now have working connections...