Common Oracle Client Connection Issue with a Console .NET 4.7 program: Could not load file or assemb-CodePudding

I download the Oracle client was downloaded from https://www.oracle.com/database/technologies/oracle19c-windows-downloads.html and select the file "NT_193000_client.zip" under the heading "Oracle Database 19c Client (19.3) for Microsoft Windows (32-bit)".

I then installed it on my server. When I run a TNSPing command to check the version number and bit-ness of the Oracle Client installed, I get:

TNS Ping Utility for 32-bit Windows: Version 19.0.0.0.0 - Production on 20-OCT-2022 22:50:49

I then used SQLPlus to very that I could connect to the database.

Next, I compiled the following simple C# program that tests Oracle connectivity. The program was compiled as a 32-bit .NET 6 program.

using System;

namespace TestOracle
{
    internal class Program
    {
        static void Main(string[] args)
        {
            string cs = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=servernm.sys.myco.com)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MySERVICE.MYCO.com)));User Id=MYID;Password=MYPWD;Validate Connection=True;";

            try
            {
                int count = Db.TestConnectivity(cs);
                Console.WriteLine($"Count={count}");
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }

            var key = Console.ReadKey();
        }
    }
}

using Oracle.DataAccess.Client;
using System;
using System.Data;
using System.Text;

namespace TestOracle
{
    public static class Db
    {
        public static int TestConnectivity(string connectionString)
        {
            var sqlStringBuilder = new StringBuilder();
            sqlStringBuilder.AppendLine($"SELECT 1  AS RecordCount FROM DUAL");

            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                OracleCommand command = connection.CreateCommand();
                command.Connection = connection;
                var oleDbDataAdapter = new OracleDataAdapter(command);

                command.CommandText = sqlStringBuilder.ToString();
                DataSet dataset = new DataSet();
                oleDbDataAdapter.Fill(dataset);
                return Convert.ToInt32(dataset.Tables[0].Rows[0]["RecordCount"]);
            }
        }
    }
}

When I ran the program, I get this error:

System.IO.FileNotFoundException: Could not load file or assembly 'Oracle.DataAccess, Version=2.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies. The system cannot find the file specified.
File name: 'Oracle.DataAccess, Version=2.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342'
   at TestOracle.Db.TestConnectivity(String connectionString)
   at TestOracle.Program.Main(String[] args) in C:\Users\C39293\OneDrive - CIGNA\Documents\Visual Studio 2015\Projects\Dokmanovich.File2Table\TestOracle\Program.cs:line 13

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

I know thar this is a common error and that the common issue is a mismatch of bitness or a version problem. In my case, the bitness of my program, 32, matches the bitness of the client installed. Furthermore, I expect that if the program runs on my machine with an Oracle client of 12.1, that a higher level client, 19, should also work. Is this assumtpion true? I was unmable to find the exact same oracle Client.

What am I missing? Thanks you!

Update

When I looked in my C# project References, I saw that the Oracle.DataAccess.dll pointed to my 64 Oracle client folder on my machine, not my 32 bit one ( I have both clients). Since I want to run on a server that only has the 32 bit Oracle client, I changed the reference to point to the 32 bit dll and re-compiled it as before, as a 32 bit program.

When I reran the program on the server, I got a different error:

System.TypeInitializationException: The type initializer for 'Oracle.DataAccess.Client.OracleConnection' threw an exception. ---> Oracle.DataAccess.Client.OracleException: The provider is not compatible with the version of Oracle client
   at Oracle.DataAccess.Client.OracleInit.Initialize()
   at Oracle.DataAccess.Client.OracleConnection..cctor()
   --- End of inner exception stack trace ---
   at Oracle.DataAccess.Client.OracleConnection..ctor(String connectionString)
   at TestOracle.Db.TestConnectivity(String connectionString) in C:\Users\Myid\OneDrive - CIGNA\Documents\Visual Studio 2015\Projects\MyName.File2Table\TestOracle\Db.cs:line 15
   at TestOracle.Program.Main(String[] args) in C:\Users\MyId\OneDrive - CIGNA\Documents\Visual Studio 2015\Projects\MyName.File2Table\TestOracle\Program.cs:line 13

I guess this is suggesting that I have the wrong Client. What should I have installed so this code runs on the server?

Update 2

I had two version of the 32 Oracle.DataAccess.dll, version 2.121.20 and 4.121.2.0. The error was the same for both (the error message immediately above)

CodePudding user response：

You may consider to use the Oracle Data Provider for .NET, Managed Driver (ODP.NET Managed Driver), then you have to copy only the Oracle.ManagedDataAccess.dll file without any further dependencies. It works for both, 32bit and 64bit.

Apart from 32/64 bit you need to consider a few more things.

If you like to use Oracle.DataAccess Version=2.xxx, then you must compile to .NET Framework 2.0. If you compile to .NET Framework 4.0 or higher, then the application will try to load Oracle.DataAccess Version=4.xxx (unless you load assemblies manually with Assembly.Load())
The version of Oracle.DataAccess has to match exactly the version of the Oracle client. Using version 2.121.x or 4.121.x with a Oracle 19 client does not work.
Assemblies (i.e. *.dll files) are loaded either from GAC or from folder of the application .exe. Well, in reality it is more complex (see How the Runtime Locates Assemblies), but let's keep it simple.

The DLL files from GAC take always precedence over the local files. However, in 12.1 and later ODP.NET is not added to GAC anymore by the installer. You need to add them manually, for example with the gacutil tool.

Looks like on your machine you had a 64-bit Oracle client installed. If you like to use 32-bit and 64-bit client on the same machine, follow this instruction: BadImageFormatException. This will occur when running in 64 bit mode with the 32 bit Oracle client components installed

You may also have a look at my Oracle Connection Tester, it should give you a hint where the problem is.