Operating the Oracle database with ODP. NET Core + Dapper in. NetCore of C

Preface

Although 1 is talking about "de-IOE", in state-owned enterprises and governments, the historical burden of Oracle is too heavy, and even many business logics are written in various stored procedures of Oracle...

The main technology stack of our system is Django/Spring/AspNetCore, Java need not say to support Oracle certainly no problem, the key lies in Django Oracle version requirements, compatibility is not particularly good, Oracle version can not be arbitrarily upgraded, so I think of using. Net Core to write a middle layer, so that other systems can easily use Oracle data and stored procedures …

ODP. NET Core is an ADO. NET driver that provides fast data access from the Microsoft. NET Core client to the Oracle database. It can run on Windows and Linux. ODP. NET consists of a 100% managed code dynamic link library, Oracle. ManagedDataAccess. dll, available through an NuGet installation.

This Oracle. ManagedDataAccess. Core is really convenient, no need to install Oracle client, compatibility, convenience, anyway is out of the box, a shuttle is finished

Simple use

First of all, install this Oracle. ManagedDataAccess. Core with nuget, and then you can perform various operations, but it is still cumbersome from the code amount. 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 = conn.CreateCommand()) {
    try {
      if (conn.State == ConnectionState.Closed) {
        conn.Open();
      }

      command.BindByName = true;
      command.CommandText = $"select * from table_name";

      using (var reader = command.ExecuteReader()) {
        while (reader.Read()) {
          Console.WriteLine(reader.GetString("DEPART_NAME"));
        }
      }
    }
    catch (Exception ex) {
      Console.WriteLine(ex.StackTrace);
      Console.WriteLine(ex.Source);
      Console.WriteLine(ex.Message);
    }
  }
}

This is the code that executes the SQL statement select * from table_name. It's a bit long... vomit

Let's take a look at the …

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)) {
  conn.Open();
  var command = new OracleCommand("proc_name", conn) {
    CommandType = CommandType.StoredProcedure
  };

  //  Input parameters 
  command.Parameters.Add(new OracleParameter("id", "0001"));

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

  command.Parameters.Add(vOut);

  var affectRows = command.ExecuteNonQuery();

  Console.WriteLine(vOut.Value);
}

Error handling is removed here, which is 1 point short, but it is still too troublesome...

So here we will use Dapper, a lightweight ORM, to simplify the operation

Using Dapper

Needless to say, first of all, nuget installation is a routine operation, and the package name is simple Dapper.

The first is to add, delete and check this kind of common SQL statement:

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 = cn.Query("select * from table_name");

foreach (var item in result) {
  Console.WriteLine(item);
}

It can be seen that after the introduction of Dapper, it is only necessary to use the extension method Query of OracleConnection to execute SQL ~ the result returned is IEnumerable < dynamic > Type, of course, you can also specify the return type in the generic parameter of Query method, which I don't specify here for generic purposes

Continue to look at the stored procedure, very simple, more about the above, 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 = cn.Query("proc_name", commandType: CommandType.StoredProcedure);

foreach (var item in result) {
  Console.WriteLine(item);
}

What if there are stored procedures with parameters?

Very simple (code from official example) ~

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

ok, very convenient, more operation see Dapper document is finished (I am also the first time to contact Dapper, before using FreeSQL and EFCore), this article is over ~

Dapper Project Home Page: https://github.com/StackExchange/Dapper

References

. NET Core Operates the Oracle database using ODP. NET Core connection: https://www.cjavapy.com/article/271/
Component box: Managed ODP. NET for Linux