How to connect to Oracle DB using ODAC and Oracle data access library in .net 7

Question

could anyone please let me know if there is any equivalent library for Oracle.Dataaccess.dll in .net 7. we are trying to migrate the existing .net application which uses the Unmanaged client library with ODAC to .net 7. Problem is I see the references that are related to only Oracle.ManagedDataAccess.core library in .net core. Please clarify whether unmanaged data access library available or not in .net core.

I suspect only option is to use Oracle.ManagedDataaccess in .net core.. but what kind of configuration would differ if I have to convert from from unmanged to managed.. Any reference links would be much helpful

Answer 1

Have a look at System Requirements

Oracle Data Provider for .NET, Unmanaged Driver requires the following:

Microsoft .NET Framework

• ODP.NET for .NET Framework 2.0 is only supported with Microsoft .NET Framework 3.5 SP 1 and later.

• ODP.NET for .NET Framework 4 is only supported with Microsoft .NET Framework 4.5.2, 4.6.x, and 4.7.x.

Looks like you cannot use the Unmanaged Driver. For differences see the same document at Configuration differences between ODP.NET, Managed Driver and ODP.NET, Unmanaged Driver

Answer 2

Here are two approaches:

1. Entity framework DB first (scaffolding)

   https://learn.microsoft.com/en-us/ef/core/managing-schemas/scaffolding/?tabs=dotnet-core-cli

services
   .AddDbContext<YourDbContext>(
            options => 
            { 
              options.UseOracle(configuration.GetConnectionString("YourOracleConnectionString")); 
            },
            ServiceLifetime.Scoped);

2. Nuget Oracle.ManagedDataAccess.Client

Example with stored procedure.

using var connection = new OracleConnection("your connection string");
connection.Open();
var dataAccess = new OracleDataAdapter();
var command = new OracleCommand
{
    CommandType = CommandType.StoredProcedure,
    CommandText = "procedure name",
    Connection = connection
};

command.Parameters.Add("procedure paramater 1", OracleDbType.Int32).Value = functionArgument1;
command.Parameters.Add("procedure paramater 2", OracleDbType.Varchar2).Value = functionArgument2;
dataAccess.SelectCommand = command;

await dataAccess.SelectCommand.ExecuteReaderAsync();

If you have and existing database, I would suggest going for option 1, but it depends on your needs of course.