ASP.NET Core Web APIとストアドプロシージャで実装するシンプルで高速なDBトランザクション処理（on ORACLE）

.Net 6.0 の ASP.NET Core Web APIから、Dapper+Oracle.EntityFrameworkCore NuGetパッケージを使い、ORACLEデータベースのストアドプロシージャ（PL/SQL）を使用する場合の、DBトランザクション処理を行うサンプルを作成しました。
ソースコードはGitHubで公開しています。

ソースコード構成

ASP.NET Core Web APIで実装するシンプルで高速なDBトランザクション処理（on ORACLE）で作成したソースコードに、下記の修正を加えました。

ソースコード変更内容を解説

/SQL/SQL_TableA.cs

C#側に定数として持っていた、Select文、Insert文をストアドプロシージャ側へ移したので、C#側のSQL文は削除し、Dapperにはストアドプロシージャ名を渡すように変更。


using System.Data;
using WebApplication1.Model;
using WebApplication1.Shared;
using Dapper;
using Microsoft.Extensions.Logging;
using Oracle.ManagedDataAccess.Client;

namespace WebApplication1.SQL
{
    public class SQL_TABLE1
    {
        public static void Transaction1(TABLE1 insertTABLE1)
        {
            using (var conn = new OracleConnection(SharedData.ConnectionString))
            {
                conn.Open();
                using (var tran = conn.BeginTransaction(IsolationLevel.ReadCommitted))
                {
                    try
                    {
                        var table1List = Select(conn);

                        Insert(conn, tran, insertTABLE1);

                        var table1List2 = Select(conn);

                        tran.Commit();
                    }
                    catch
                    {
                        tran.Rollback();
                        throw;
                    }
                }
            }
        }

        public static IEnumerable<TABLE1> Select(OracleConnection conn)
        {
            return conn.Query<TABLE1>("SP_TABLE1_SELECT", null, commandType: CommandType.StoredProcedure);
        }

        public static void Insert(OracleConnection conn, OracleTransaction tran, TABLE1 insertTABLE1)
        {
            var sqlParam = new
            {
                id = insertTABLE1.ID,
                code = insertTABLE1.CODE,
                value_string = insertTABLE1.VALUE_STRING,
                value_date = insertTABLE1.VALUE_DATE
            };

            conn.Execute("SP_TABLE1_INSERT", sqlParam, tran, commandType: CommandType.StoredProcedure);
        }
    }

}

using System.Data;

using WebApplication1.Model;

using WebApplication1.Shared;

using Dapper;

using Microsoft.Extensions.Logging;

using Oracle.ManagedDataAccess.Client;

namespace WebApplication1.SQL

{

public class SQL_TABLE1

{

public static void Transaction1(TABLE1 insertTABLE1)

{

using (var conn = new OracleConnection(SharedData.ConnectionString))

{

conn.Open();

using (var tran = conn.BeginTransaction(IsolationLevel.ReadCommitted))

{

try

{

var table1List = Select(conn);

Insert(conn, tran, insertTABLE1);

var table1List2 = Select(conn);

tran.Commit();

}

catch

{

tran.Rollback();

throw;

}

public static IEnumerable<TABLE1> Select(OracleConnection conn)

{

return conn.Query<TABLE1>("SP_TABLE1_SELECT", null, commandType: CommandType.StoredProcedure);

}

public static void Insert(OracleConnection conn, OracleTransaction tran, TABLE1 insertTABLE1)

{

var sqlParam = new

{

id = insertTABLE1.ID,

code = insertTABLE1.CODE,

value_string = insertTABLE1.VALUE_STRING,

value_date = insertTABLE1.VALUE_DATE

};

conn.Execute("SP_TABLE1_INSERT", sqlParam, tran, commandType: CommandType.StoredProcedure);

}

ストアドプロシージャ定義

C#から呼び出しているストアドプロシージャの実装内容は、C#側に定数として持っていた、Select文、Insert文をそのままストアドプロシージャ（PL/SQL）へ移しています。
※SQLをストアドプロシージャ側に纏めると、C#側のソースコードはシンプルになり、SQL実行時にORACLEがSQLの実行プランを毎回解析する必要はなくなり、処理コストを少なくできます。
ストアドプロシージャをの作り方は ORACLEのストアドプロシージャを SQL Developerで作成する手順を参照下さい。


CREATE OR REPLACE PROCEDURE SP_TABLE1_SELECT AS 
  c1 SYS_REFCURSOR; 
BEGIN

  open c1 for
  SELECT
    id,
    code,
    value_string,
    value_date
  FROM
    testuser1.table1;
    
  DBMS_SQL.RETURN_RESULT(c1);

END;

CREATE OR REPLACE PROCEDURE SP_TABLE1_SELECT AS

c1 SYS_REFCURSOR;

BEGIN

open c1 for

SELECT

id,

code,

value_string,

value_date

FROM

testuser1.table1;

DBMS_SQL.RETURN_RESULT(c1);

END;


create or replace PROCEDURE SP_TABLE1_INSERT
(
  ID IN NUMBER,
  CODE IN VARCHAR2,
  VALUE_STRING IN VARCHAR2,
  VALUE_DATE IN DATE 
) AS 
BEGIN

  INSERT INTO testuser1.table1(
    id, 
    value_string, 
    value_date,
    code
  ) VALUES (
    ID, 
    VALUE_STRING, 
    VALUE_DATE,
    CODE
  );

END SP_TABLE1_INSERT;

create or replace PROCEDURE SP_TABLE1_INSERT

(

ID IN NUMBER,

CODE IN VARCHAR2,

VALUE_STRING IN VARCHAR2,

VALUE_DATE IN DATE

) AS

BEGIN

INSERT INTO testuser1.table1(

id,

value_string,

value_date,

code

) VALUES (

ID,

VALUE_STRING,

VALUE_DATE,

CODE

);

END SP_TABLE1_INSERT;