Using ODP.NET Core + Dapper to Operate Oracle Databases in .NET Core (C#)

Preface

Although people have long talked about “de-IOE,” in state-owned enterprises and government, Oracle’s historical baggage is simply too heavy—even a lot of business logic is implemented inside various Oracle stored procedures…

Our systems’ main tech stack is Django / Spring / AspNetCore. Needless to say, Java’s support for Oracle is definitely no problem; the key issue is that Django has Oracle version requirements and compatibility isn’t particularly good, and Oracle versions can’t be upgraded at will, so I thought of writing a middle layer in .NET Core so that other systems can conveniently use Oracle data and stored procedures…

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

This Oracle.ManagedDataAccess.Core is really convenient—no need to install an Oracle client. Compatibility, convenience—anyway, it’s out of the box; just go all in and you’re done.

Simple Usage

First install this Oracle.ManagedDataAccess.Core with NuGet, then you can perform all kinds of operations. Looking at the amount of code, though, it’s still rather tedious. Here’s 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 to execute the SQL statement select * from table_name. A bit long…… ugh

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

    // 输入参数
    command.Parameters.Add(new OracleParameter("id", "0001"));

    // 输出参数
    var vOut = new OracleParameter("v_out",
        OracleDbType.Varchar2,
        1000,
        "",
        ParameterDirection.InputOutput
    );

    command.Parameters.Add(vOut);

    var affectRows = command.ExecuteNonQuery();

    Console.WriteLine(vOut.Value);
}

Here I removed the error handling so it looks a bit shorter, but it’s still terribly cumbersome……

So here we’ll use Dapper, this lightweight ORM, to simplify operations.

Using Dapper

Not much to say—first, installing via NuGet is the usual drill; the package name is simply Dapper.

First, ordinary SQL statements like CRUD:

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);
}

You can see that after introducing Dapper, you just use the OracleConnection extension method Query to execute SQL~ The returned result is of type IEnumerable<dynamic>. Of course you can also specify the return type in the generic parameter of the Query method; I don’t specify it here for generality.

Continuing with stored procedures—very simple, pretty much the same as above, just one extra 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 it’s a stored procedure with parameters?

Very simple (code from the official example)~

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

ok, very convenient. For more operations, just look at the Dapper docs and you’re done (this is also my first time using Dapper; I used FreeSQL and EFCore before). That’s the end of this article~

Dapper project homepage: https://github.com/StackExchange/Dapper

References

• Using ODP.NET Core in .NET Core to Connect to and Operate an Oracle Database: https://www.cjavapy.com/article/271/
• Component Unboxing: Managed ODP.NET for Linux

Welcome to Discuss

Programming Lab focuses on exploring popular new internet technologies and team agile development practices. Reply with linux, flutter, c#, netcore, android, kotlin, java, python, etc. in the backend of the WeChat official account “程序设计实验室” to get related technical articles and materials. If you have any questions, you can also leave a message in the official account backend~