Connect to Oracle database from .NET core application.

In this post, I go over using Oracle .NET core libraries to connect to Oracle database. In particular, we’ll look at the Oracle.ManagedDataAccess.Core and the Oracle.EntityFrameworkCore.

Using Oracle.ManagedDataAccess.Core library

The library provides a high level, intuitive API to connect and work with data in an Oracle database. From the nuget page,

Oracle Data Provider for .NET (ODP.NET) Core is an ADO.NET driver that provides fast data access from Microsoft .NET Core clients to Oracle databases. ODP.NET Core consists of a single 100% managed code dynamic-link library.

Oracle

Managed code refers to the immediate language codes which the common language runtime compiled into machine codes and manages its execution. The immediate language codes are the result of compiling the high level codes in C#, Visual Basic, F# and others.

ADO stands for ActiveX Data Objects which provide the high level abstraction to work with ODE DB, which is a set a set of Component Object Model (COM) that provide low level, high performance interface to a variety of data stores.

To get started using the library, install the nuget package. At the time of the writing, the latest version is 2.19.90.

Install-Package Oracle.ManagedDataAccess.Core -Version 2.19.90

In my projects, I only need to know a few main classes of the library to effectively interact with the data: OracleConnection, OracleCommand, OracleParameter and DbDataReader.

Below is an example of retrieving data from a table.

using(OracleConnection conn = new OracleConnection(_appConfig.MyDbConStr)) {
  try {
    await conn.OpenAsync();
    OracleCommand cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT * FROM Companies OFFSET :skip ROWS FETCH NEXT :limit ROWS ONLY";
    cmd.BindByName = true;
    cmd.Parameters.Add(new OracleParameter("skip", skip));
    cmd.Parameters.Add(new OracleParameter("limit", limit));
    DbDataReader reader = await cmd.ExecuteReaderAsync();
    ICollection < Company > companies = new Collection < Company > ();
    while (await reader.ReadAsync()) {
      Company c = new Company();
      c.Name = await reader.IsDBNullAsync(0) ? null: reader.GetString(0);
      c.Address = await reader.IsDBNullAsync(1) ? null: reader.GetString(1);
       ...
      companies.Add(c);
    }
    await reader.DisposeAsync();
    return cases;
  }
  catch(Exception e) {
    _logger.LogError(message: "Got an error while retrieving companies data", exception: e);
    throw e;
  }
}

In the above snippets, the connection string has this format:

User Id={user_id};Password={pwd};Data Source={hostname:portNumber}/{database}:{schema};

Below is another example of calling a stored procedure using the library:

using(OracleConnection conn = new OracleConnection(_appConfig.MyDbConStr)) {
  try {
    await conn.OpenAsync();
    OracleCommand cmd = new OracleCommand();
    cmd.Connection = con;
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    cmd.CommandText = "MySchema.My_Pkg.MyProcedure";

    // param 1
    OracleParameter oraP1 = new OracleParameter();
    oraP1.OracleDbType = OracleDbType.Int64;
    oraP1.Direction = System.Data.ParameterDirection.InputOutput;
    oraP1.ParameterName = "param name;
                oraP1.Value = "
    value ";
                cmd.Parameters.Add(oraP1);
                
                // a_retval_out
                OracleParameter oraP3 = new OracleParameter();
                oraP3.OracleDbType = OracleDbType.Int64;
                oraP3.Direction = System.Data.ParameterDirection.InputOutput;
                oraP3.ParameterName = "
    a_retval_out ";
                oraP3.Value = 0;
                cmd.Parameters.Add(oraP3);
                int cnt = 1;

                reader = cmd.ExecuteReader();

                List<Company> companies = new List<Company>();
} catch (Exception e) {
       // handle error
}

The Oracle.ManagedDataAccess.Core intuitive to use. But as the above examples show, the library only returns tabular data. Typically, you'll want to use a framework like Entity Framework to map the data into domain objects. Fortunately, Oracle recently released the library which supports using Entity Framework Core.

Using Oracle.EntityFrameworkCore

To connect to Oracle database using Entity Framework Core, reference the library in your project.

<PackageReference Include="Oracle.EntityFrameworkCore" Version="2.19.80" />

At the time of writing, Oracle does not yet support entity framework core 3, which is the latest major release. So in my codes, I’m currently using Entity Framework Core 2.

If you are not familiar with Entity Framework Core, checkout the Getting Started guide.

Basically, you need to create a database context class. Below is an example database context class.

public partial class MyDbContext : DbContext
    {
        public MyDbContext()
        {
        }

        public MyDbContext(DbContextOptions<MyDbContext> options)
            : base(options)
        {
        }

        public DbSet<MyDomain> entities { get; set; }
        
        public static readonly ILoggerFactory ConsoleLoggerFactory
           = LoggerFactory.Create(builder =>
           {
               builder
               .AddFilter((category, level) =>
                   category == DbLoggerCategory.Database.Command.Name && level == LogLevel.Debug)
               .AddConsole();
           });

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
                optionsBuilder
                    .UseLoggerFactory(ConsoleLoggerFactory)
                    .UseOracle("connection string to test db." );
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            
        }
    }

In the above snippets, because of the if condition, the configuration only takes place if the options builder has not been configured. Typically, you’ll want to configure the database context in the Startup class, and in prod environment, the connection string should come from a secure place, outside of the source code.

For testing purpose, I configure logging to the console by default.

Below is the sample snippets I have in Startup class:

services.AddDbContext<MyDbContext>(options => options.UseOracle(Configuration.GetConnectionString("MyDbConStr")));

Once you have everything setup, you can use entity framework to work with data in your Oracle database.

Following show an example of retrieving a record by id, and have entity framework convert it to a domain class of type T.

  public class CommonRepository<T> 
    {
        protected readonly MyDbContext _modelContext; 

        public CommonRepository(MyDbContext modelContext)
        {
            _modelContext = modelContext; 
        }

        public async Task<T> FindByIdAsync(int id)
        {
            return await _modelContext.FindAsync<T>(id);
        }
    }

References

Oracle.EntityFrameworkCore

Oracle.ManagedDataAccess.Core

Oracle Data Provider for .NET Entity Framework Core

Getting started with Entity Framework Core

ADO Overview and Benefits