Using Core+Dapper to operate Oracle databases in .NetCore(C#)

Preface

Although I have been saying "de-IOE", Oracle's historical burden is too heavy in state-owned enterprises and governments, and many business logic is even implemented in various stored procedures of Oracle...

The main technology stack of our system is Django / Spring / AspNetCore. Needless to say, Java supports Oracle. The key is that Django has requirements for Oracle versions, and the compatibility is not particularly good. Oracle versions cannot be upgraded at will, so I thought of using .Net Core to write an intermediate layer so that other systems can easily use Oracle's data and stored procedures...

Core is a driver that provides fast data access from Microsoft .NET Core clients to Oracle databases. It can run on Windows and Linux. Consisting of a 100% managed code dynamic link library, available via NuGet installation.

This is really convenient, you don't need to install the Oracle client, compatibility and convenience, anyway, it's just out of the box, and it's done with just one shuttle.

Simple use

First, install this with nuget, and then you can perform various operations, but it is still quite cumbersome in terms of code volume. Please add the code:

var connStr = $"DATA SOURCE=127.0.0.1/db_name; PASSWORD=password; PERSIST SECURITY INFO=True; USER ID=user_id";
using (var conn = new OracleConnection(connStr)) {
  using (var command = ()) {
    try {
      if ( == ) {
        ();
      }

       = true;
       = $"select * from table_name";

      using (var reader = ()) {
        while (()) {
          (("DEPART_NAME"));
        }
      }
    }
    catch (Exception ex) {
      ();
      ();
      ();
    }
  }
}

This is the code that executes the SQL statement select * from table_name, which is a bit long...

Let's take a look at the stored procedure...

var connStr = $"DATA SOURCE=127.0.0.1/db_name; PASSWORD=password; PERSIST SECURITY INFO=True; USER ID=user_id";
using (var conn = new OracleConnection(connStr)) {
  ();
  var command = new OracleCommand("proc_name", conn) {
    CommandType = 
  };

  // Enter parameters  (new OracleParameter("id", "0001"));

  // Output parameters  var vOut = new OracleParameter("v_out",
    OracleDbType.Varchar2,
    1000,
    "",
    
  );

  (vOut);

  var affectRows = ();

  ();
}

The error handling is removed here, which seems a little shorter, but it is still very troublesome...

So here we need to use Dapper, a lightweight ORM to simplify operations

Using Dapper

Without further ado, first of all, nuget installation is a regular operation, and the package name is a simple Dapper.

First, add, delete, modify and check ordinary SQL statements:

using Dapper;

var connStr = $"DATA SOURCE=127.0.0.1/db_name; PASSWORD=password; PERSIST SECURITY INFO=True; USER ID=user_id";
using var cn = new OracleConnection(connStr);

var result = ("select * from table_name");

foreach (var item in result) {
  (item);
}

You can see that after introducing Dapper, just use the extension method Query of OracleConnection to execute SQL~ The returned result is the IEnumerable<dynamic> type. Of course, you can also specify the returned type in the generic parameters of the Query method. I will not specify it for the sake of generalization.

Continue to look at the stored procedure, it's very simple. The above is similar, just one more parameter to tell the program what our SQL type is.

var connStr = $"DATA SOURCE=127.0.0.1/db_name; PASSWORD=password; PERSIST SECURITY INFO=True; USER ID=user_id";
using var cn = new OracleConnection(connStr);

var result = ("proc_name", commandType: );

foreach (var item in result) {
  (item);
}

What if there is a stored procedure with parameters?

Very simple (the code comes from the official example) ~

var user = <User>("spGetUser", new {Id = 1},
    commandType: ).SingleOrDefault();

OK, it's very convenient. Just read the Dapper documentation for more operations (I'm also the first time I've been in Dapper. I used FreeSQL and EFCore before). This article is over~

Dapper project homepage:/StackExchange/Dapper

References

.NET Core Use Core connection to operate the Oracle database:/article/271/
Component unboxing: Managed for Linux

This is the article about using Core+Dapper to operate Oracle databases in .NetCore (C#). For more related contents of operating Oracle databases, please search for my previous articles or continue browsing the following related articles. I hope everyone will support me in the future!