Issue when trying to connect to Oracle DB in C#

Question

I am trying to connect to an Oracle DB using the following method:

OracleConnection con = new OracleConnection();
con.ConnectionString = "User ID=ID;Password=PASSWORD;Data Source=SOURCE";
con.Open();
write("connected to oracle " + con.ServerVersion);

But every time I run the program I get the error:

[2018-11-01 09:29:19.705] System.BadImageFormatException: Could not load file or assembly 'Oracle.DataAccess, Version=2.112.1.0, Culture=neutral, PublicKeyToken=PUBLIC_KEY' or one of its dependencies. An attempt was made to load a program with an incorrect format. File name: 'Oracle.DataAccess, Version=2.112.1.0, Culture=neutral, PublicKeyToken=PUBLIC_KEY' at adrentech_previous_day.Program.processFile() at adrentech_previous_day.Program.Main() in DIRECTORY\program:line 25

=== Pre-bind state information === LOG: DisplayName = Oracle.DataAccess, Version=2.112.1.0, Culture=neutral, PublicKeyToken=PUBLIC_KEY (Fully-specified) LOG: Appbase = file:///DIRECTORY LOG: Initial PrivatePath = NULL Calling assembly : adrentech_previous_day, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null. === LOG: This bind starts in default load context. LOG: Using application configuration file: DIRECTORY\program.vshost.exe.Config LOG: Using host configuration file: LOG: Using machine configuration file from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\config\machine.config. LOG: Post-policy reference: Oracle.DataAccess, Version=2.112.1.0, Culture=neutral, PublicKeyToken=PUBLIC_KEY LOG: Attempting download of new URL file:///DIRECTORY/Oracle.DataAccess.DLL. ERR: Failed to complete setup of assembly (hr = 0x8007000b). Probing terminated.

Any suggestions?

1) You can use the Oracle Managed client, which has no external dependencies and isn't X86/64 specific. 2) Make sure you wrap your IDisposable objects such as OracleConnection in a using statement. — mason
I installed the Oracle Managed client and wrapped the OracleConnection in a using statement, but I am still getting the same error. — parrigin777
Did you make sure to remove the referenced to the non managed client? — mason
Okay yes I just did that, now I am getting a new error: Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-12154: TNS:could not resolve the connect identifier specified ---> OracleInternal.Network.NetworkException (0x00002F7A): ORA-12154: TNS:could not resolve the connect identifier specified — parrigin777
That's good, that means you got further. I suggest you use a connection string such as this: SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MyOracleSID))); uid=myUsername;pwd=myPassword; — mason

Wernfried Domscheit Wernfried Domscheit · Accepted Answer · 2018-11-01T15:34:17

Most likely your compile target is .NET Framework 4.0 (or higher) but you installed the ODP.NET version 2.x - they don't work together.

One solution is to use the ODP.NET Managed Driver - which you did already. However, there is also anther solution, see The provider is not compatible with the version of Oracle client

Now the second problem regarding ORA-12154: TNS:could not resolve the connect identifier specified

ODP.NET Managed Driver uses a different method to find the sqlnet.ora and tnsnames.ora (and ldap.ora, if used) files than the unmanaged driver, see Determining location of relevant tnsnames.ora file

The unmanaged drivers searches folder %ORACLE_HOME%\network\admin and reads Registry key HKLM\SOFTWARE\ORACLE\KEY_OraClient11g_home1\TNS_ADMIN, the ODP.NET Managed Driver does not.

The ODP.NET Managed Driver retrieves location of tnsnames.ora from your .NET config file, i.e. machine.config. You can modify the file manually (see Oracle Data Provider for .NET, Managed Driver Configuration) or use a batch script as below (pick x64 or x86 lines which are relevant for you and change folder names according to your machine)

set Oracle_x64=c:\oracle\product\11.2\Client_x64\odp.net
set Oracle_x86=c:\oracle\product\11.2\Client_x86\odp.net

OraProvCfg_x64=%Oracle_x64%\bin\4\OraProvCfg.exe
OraProvCfg_x86=%Oracle_x86%\bin\4\OraProvCfg.exe

set TNS_ADMIN=C:\oracle\network\admin

"%OraProvCfg_x64%" /action:config /product:odpm /frameworkversion:v4.0.30319 /providerpath:%Oracle_x64%\managed\common\Oracle.ManagedDataAccess.dll /set:settings\TNS_ADMIN:%TNS_ADMIN%
"%OraProvCfg_x86%" /action:config /product:odpm /frameworkversion:v4.0.30319 /providerpath:%Oracle_x86%\managed\common\Oracle.ManagedDataAccess.dll /set:settings\TNS_ADMIN:%TNS_ADMIN%

Or set TNS_ADMIN as Environment variable which should work in any case.

Issue when trying to connect to Oracle DB in C#

1 Answers