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

cclickonceoracleoracleexception

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!

Best Answer

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...

Related Topic