概要

C#でOracle DBにデータを格納するためのサンプルコードを、自身の勉強を兼ねて記載してみました。

コード

ユーザーの入力データをもとに、Oracle DBにデータを格納するコードを記載しました。

入力データをDBに格納する大元となるクラス

入力データをもとに、Oracle DBのテーブルを更新するメソッドを呼び出すクラスです。
入力データはUploadTable()の引数inputedDataに入っている想定です。
このクラスじの呼出し方は最下部に記載しています。

DBManager.cs

using System.Threading.Tasks;

namespace Sample
{
    internal static class DBManager
    {
        /// <summary>
        /// ユーザーの入力データを元にテーブルを更新する
        /// </summary>
        /// <param name="inputedData">入力データ</param>
        /// <returns></returns>
        public static async Task<Datas> UploadTable(InputedData inputedData)
        {
            // 入力データをOracle DBに格納するために変換
            var datas = ToOracleData.Convert(inputedData);

            // 更新
            await DbAccess.AddData(datas);

            return datas;
        }
    }
}

入力データの変換クラス

ユーザーが入力したデータをOracle DB格納用のクラスに変換するクラスです。

ToOracleData.cs

namespace Sample
{
    public static class ToOracleData
    {
        public static Datas Convert(InputedData inputedData)
        {
            var datas = new Datas();
            datas.TABLE_A = new TABLE_A
            {
                TYPE = inputedData.Type,
                ID = inputedData.Id,
                SUB_ID = inputedData.SubId,
                RESULT = inputedData.Result,

                // 以下略 (入力データをもとにプロパティに入れていく)
            };

            datas.TABLE_B.Add(new TABLE_B
            {
                TYPE = inputedData.Type,
                ID = inputedData.Id,
                SUB_ID = inputedData.SubId,
                CONTENT = inputedData.Content,

                // 以下略 (入力データをもとにプロパティに入れていく)
            });

            return datas;
        }
    }
}

データクラス

Datasクラス

Oracle DBに格納するためのデータクラスをまとめたクラスです。

Datas.cs

using System.Collections.Generic;

namespace Sample
{
    public class Datas
    {
        public TABLE_A TABLE_A { get; set; }
        public List<TABLE_B> TABLE_B { get; set; } = new List<TABLE_B>();
    }
}

TABLE_A、TABLE_Bクラス

TABLE.csというファイルにOracle DBに格納するためのTABLE_AクラスとTABLE_Bクラスを定義しています。

TABLE.cs

using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;

namespace Sample
{
    public class TABLE_A : IPKOwner<TABLE_A>
    {
        public int TYPE { get; set; }

        [Column(TypeName = "VARCHAR2(18 CHAR)")]
        public string ID { get; set; }

        [Column(TypeName = "VARCHAR2(18 CHAR)")]
        public string SUB_ID { get; set; }

        [Column(TypeName = "NVARCHAR2(2000)")]
        public string RESULT { get; set; }

        public object[] GetKeys() => new object[] { TYPE, ID, SUB_ID };

        public IQueryable<TABLE_A> Find(IQueryable<TABLE_A> source)
            => source.Where(e => e.TYPE == TYPE && e.ID == ID && e.SUB_ID == SUB_ID);
    }

    public class TABLE_B : IPKOwner<TABLE_B>
    {
        public int TYPE { get; set; }

        [Column(TypeName = "VARCHAR2(18 CHAR)")]
        public string ID { get; set; }

        [Column(TypeName = "VARCHAR2(18 CHAR)")]
        public string SUB_ID { get; set; }

        [Column(TypeName = "TIMESTAMP")]
        public DateTime CHECK_DATETIME { get; set; }

        [Column(TypeName = "NVARCHAR2(2000)")]
        public string CONTENT { get; set; }

        public object[] GetKeys() => new object[] { TYPE, ID, SUB_ID};

        public IQueryable<TABLE_B> Find(IQueryable<TABLE_B> source) 
            => source.Where(e => e.TYPE == TYPE && e.ID == ID && e.SUB_ID == SUB_ID);
    }
}

説明

どちらのクラスもIPKOwnerというインターフェースを実装しています。

IPKOwnerインターフェース

TABLE_AとTABLE_Bに実装しているインターフェースです。

IPKOwner.cs

using System.Linq;

namespace Sample
{
    public interface IPKOwner<TSource>
    {
        object[] GetKeys();
        
        IQueryable<TSource> Find(IQueryable<TSource> source);
    }
}

説明

プライマリキーを取得するGetKeys()と特定のレコードを見つけるFind()を定義しています。

DBに入力データを追加するクラス

このクラスのAddData()で実際にDBにデータを挿入しています。
DBManagerクラスから呼び出されます。

DbAccess.cs

using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations.Schema;
using System.Diagnostics;
using System.Reflection;

namespace Sample
{
    public static class DbAccess
    {
        /// <summary>
        /// DBにデータを追加する
        /// </summary>
        /// <param name="datas"></param>
        /// <returns></returns>
        public static async Task AddData(Datas datas)
        {
            await Task.Factory.StartNew(() =>
            {
                using (var db = new ApplicationDbContext())
                using (var tx = db.Lock情報())
                {
                    UpdateTable(db.TABLE_A, new List<TABLE_A> { datas.TABLE_A });
                    UpdateTable(db.TABLE_B, datas.TABLE_B);
                    db.SaveChanges();
                    tx.Commit();
                }
            });
        }

        /// <summary>
        /// 対象のテーブルにデータを追加する
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="table"></param>
        /// <param name="data"></param>
        static void UpdateTable<T>(DbSet<T> table, List<T> data) where T : class, IPKOwner<T>
        {
            if (data == null) return;

            // プライマリキーで一意に識別できるデータを格納するDictionary
            var addData = new Dictionary<string, T>();
            foreach (var x in data)
            {
                AdjustOracleData(x);

                // プライマリキーに基づき、既存のデータを削除
                table.RemoveRange(x.Find(table));

                // プライマリキーの列に格納するデータにnullや空文字があればスキップ
                var nullKeys = x.GetKeys()
                    .Select((obj, index) => new { obj, index })
                    .Where(e => e.obj == null || e.obj.ToString() == "")
                    .Select(e => e.index)
                    .ToList();
                nullKeys.ForEach(e => Debug.WriteLine($"Key - {x.GetType().Name} : {e}"));
                if (nullKeys.Any()) continue;

                // プライマリキーを組み合わせて一意のキーを作成
                // キーを"|"で区切る
                var pk = string.Join("|", x.GetKeys().Select(e => e.ToString()));
                addData[pk] = x;
            }

            // 追加するデータが存在する場合のみ処理を実行
            if (!addData.Any()) return;
            table.AddRange(addData.Values);
        }

        /// <summary>
        /// Oracle DBに合わせてデータを調整する
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        static void AdjustOracleData<T>(T data)
        {
            foreach (var e in typeof(T).GetProperties())
            {
                if (e.GetSetMethod() == null) continue;

                // 文字列型のプロパティの調整
                if (e.PropertyType == typeof(string))
                {
                    var text = e.GetValue(data) as string;

                    // 空文字をnullに変換（Oracleでは比較の精度向上のため）
                    // Oracleは空文字をnull扱いするため
                    if (text == string.Empty)
                    {
                        e.SetValue(data, null);
                    }

                    // NVARCHAR2(2000)の場合は閾値を超えたら文字をカットする
                    var col = e.GetCustomAttribute<ColumnAttribute>();
                    if (col != null && col.TypeName?.Replace(" ", string.Empty) == "NVARCHAR2(2000)")
                    {
                        e.SetValue(data, AdjustNVARCHAR2_2000((string?)e.GetValue(data)));
                    }
                }

                // DateTime型のプロパティの調整
                if (e.PropertyType == typeof(DateTime))
                {
                    var date = (DateTime)e.GetValue(data)!;

                    // ミリ秒以下を切り捨てて保存
                    e.SetValue(data, new DateTime(date.Year, date.Month, date.Day, date.Hour, date.Minute, date.Second));
                }

                // nullable DateTime型のプロパティの調整
                if (e.PropertyType == typeof(DateTime?))
                {
                    var date = (DateTime?)e.GetValue(data);
                    if (date != null)
                    {
                        // ミリ秒以下を切り捨てて保存
                        e.SetValue(data, new DateTime(date.Value.Year, date.Value.Month, date.Value.Day, date.Value.Hour, date.Value.Minute, date.Value.Second));
                    }
                }
            }
        }

        /// <summary>
        /// NVARCHAR2(2000)の閾値を超えた部分の文字をカットする
        /// </summary>
        /// <param name="text"></param>
        /// <returns></returns>
        static string? AdjustNVARCHAR2_2000(string? text)
        {
            if (text == null) return null;

            int maxLength = 2000;
            if (text.Length <= maxLength) return text;

            // サロゲートペア対応：最後の文字がサロゲートの場合は1文字短く切り取る
            if (Char.IsSurrogate(text[maxLength])) maxLength--;

            // 指定された長さまで切り取り
            return text.Substring(0, maxLength);
        }

        /// <summary>
        /// TableAから特定のレコードを取得する
        /// ※本稿では使用していないメソッド
        /// </summary>
        /// <param name="type"></param>
        /// <param name="id"></param>
        /// <param name="subId"></param>
        /// <returns></returns>
        public static async Task<TABLE_A?> GetTableATargetRecord(int type, string id, string subId)
        {
            return await Task.Factory.StartNew(() =>
            {
                using var db = new ApplicationDbContext();
                return db.TABLE_A
                    .Where(e => e.TYPE == type && e.ID == id && e.SUB_ID == subId)
                    .ToArray()
                    .FirstOrDefault();
            });
        }
    }
}

Oracle DBに接続するクラス

Oracle DBに接続するためのクラスです。

ApplicationDbContext.cs

using LambdicSql.feat.Dapper;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Logging.Debug;

namespace Sample;

public partial class ApplicationDbContext : DbContext
{
    static readonly ILoggerFactory loggerFactory = new LoggerFactory(new[] { new DebugLoggerProvider() });

    protected override void OnConfiguring(DbContextOptionsBuilder options)
    {
        DapperAdapter.Assembly = typeof(Dapper.SqlMapper).Assembly;

        var connectionString = "User Id=hoge; Password=hoge_test; Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))(CONNECT_DATA =(SERVICE_NAME = xe)))";

#if DEBUG
        options.UseOracle(connectionString).UseLoggerFactory(loggerFactory).EnableSensitiveDataLogging();
#else
        options.UseOracle(connectionString);
#endif
    }
}

説明

`ApplicationDbContext`クラス

DbContextクラスを継承し、Entity Framework Coreを使用してOracleデータベースと通信するためのクラスになっています。

EntityFrameworkCore

EntityFrameworkCoreとは、C#などの.NETで使用されるDB操作に関するライブラリです。このライブラリを使用すると、C#からDBに読み書きしたい際は、SQL文を記述せずにデータクラスを介して操作を行うことができます。

DBを操作する為にはSQLが必ず必要なので、正確にはEntityFrameworkCoreがSQLを発行してくれているイメージです。

`DbContext`クラス

DbContextクラスはDBとの接続管理やクエリの実行、変更されたデータの保存等、DBに関する主要な機能を提供するクラスです。

`DbContextOptionsBuilder`クラス

DbContextOptionsBuilderクラスは、データベースプロバイダー (OracleやSQL Serverなど) を選択出来たり、接続文字列の設定やセキュリティ設定等、DB接続に関する設定を提供するクラスです。

`connectionString`の文字列

connectionStringの文字列は、Oracle DBに接続するために必要な情報を含んだ文字列です。セミコロン;で各パラメータを区切ります。User Id、Password、Data Sourceの3つのパラメータを指定しています。また、()で括られているのはOracleに接続するための文字列の形式 (ルール) のためです。この文字列内の大文字や小文字は区別されます。
- ログイン関連
  - User Id：データベースへのログインに使用するユーザーIDです
  - Password：ユーザーのパスワードです
- Data Source
  - DESCRIPTION
    - PROTOCOL：通信プロトコルを設定します。ここではTCP/IPを使用しています
    - HOST：DBが動作しているサーバーのホスト名を設定します。ここではlocalhostを設定しています。
    - PORT：ポート番号を設定します。ここで設定している1521はOracleのデフォルトのポート番号です。
  - CONNECT_DATA
    - SERVICE_NAME：接続先となるOracleサービス名を指定しています。ここで設定しているxeはOracle Express Editionのデフォルトのサービス名です。

LambdicSqlについて

LambdicSqlはLambdaでSQLを表現することを目的にしたライブラリで、Nugetから入手することが出来ます。
参考：

LoggerFactoryクラス

ログ出力を行うためのインスタンスを生成する為のクラスです。

DebugLoggerProviderクラス

実際のログ出力を担当しているクラスです。Visual Studioの出力ウィンドウにログ出力を行います。

このコードでは以下のようにデバッグモードとリリースモードでコードを分けています。
デバッグモードでは出力ウィンドウに、EntityFrameworkCoreが実際にDBへ投げているSQLを出力してくれるように設定しています。

#if DEBUG
        options.UseOracle(connectionString).UseLoggerFactory(loggerFactory).EnableSensitiveDataLogging();
#else
        options.UseOracle(connectionString);
#endif

トランザクションを管理するクラス

指定されたテーブルの排他ロックを取得し、トランザクション内でロックを管理、またエラー発生時にリソース開放を行っているクラスです。

LockExtensions.cs

using LambdicSql;
using LambdicSql.feat.Dapper;
using Microsoft.EntityFrameworkCore;
using static LambdicSql.Db<Sample.ApplicationDbContext>;

namespace Sample
{
    public static class LockExtensions
    {
        public static TransactionEx<TABLE_A> Lock情報(this ApplicationDbContext db)
            => Lock<TABLE_A>(db, nameof(db.TABLE_A));

        static TransactionEx<T> Lock<T>(this ApplicationDbContext db, string table)
        {
            // トランザクションの開始
            var transaction = db.Database.BeginTransaction();
            try
            {
                // テーブルをロック
                db.Database.GetDbConnection().Execute(Sql(db => $"LOCK TABLE \"{table}\" IN EXCLUSIVE MODE".ToSqlObject()));

                return new TransactionEx<T>(transaction);
            }
            catch
            {
                transaction.Rollback();
                transaction.Dispose();
                throw;
            }
        }
    }
}

説明

拡張メソッドとして定義

拡張メソッドとして実装されていて、ApplicationDbContextクラスに対してLock情報()というメソッドを追加しています。

LOCK TABLE "LOG" IN EXCLUSIVE MODE;

このSQL文により、LOGテーブルの完全な排他制御が確立され、データの一貫性が保証されます。

ToSqlObject()

SQL文字列を内部的なSQLオブジェクト表現に変換するメソッドです。

TransactionExクラス

以下のトランザクション関連の処理を使いやすい形にしたラッパークラスで説明しています。

トランザクション関連の処理を使いやすい形にしたラッパークラス

EntityFrameworkCoreでのトランザクション管理を強化し、より安全で使いやすい形で提供するためのラッパークラスです。

TransactionEx.cs

using Microsoft.EntityFrameworkCore.Storage;

namespace Sample
{
    public class TransactionEx<T> : IDisposable
    {
        IDbContextTransaction _core;
        bool _commited;

        public TransactionEx(IDbContextTransaction core) => _core = core;

        public void Commit()
        {
            _commited = true;
            _core.Commit();
        }

        public void Dispose()
        {
            if (!_commited) _core.Rollback();
            _core.Dispose();
        }
    }
}

説明

IDisposableインターフェースを実装し、using文での使用をサポートしています。
ジェネリック型パラメータTにより、色々なエンティティタイプに対応可能にしています。
Dispose()した際にコミットがされていない場合はロールバックするようにしています。
フィールド_coreにEntityFrameworkCoreのトランザクションオブジェクトを保持しています。

DBにデータを格納するためのメソッドの呼出し方

DBManagerクラスを呼び出しているクラスです。

UploadToOracleDB.cs

using System.Threading.Tasks;

namespace Sample
{
    internal class UploadToOracleDB
    {
        private InputedData InputedData { get; }

        public UploadToOracleDB(InputedData inputedData)
        {
            InputedData = inputedData;
        }

        public async Task<object> Execute()
        {
            return await DBManager.UploadTable(InputedData);
        }
    }
}

説明

入力データinputedDataを引数に、DBManagerクラスのUploadTable()を呼び出しています。
awaitで非同期処理とすることで、データベースとの通信中にメインスレッドがブロックされないようにしています。

終わりに

データベース周りは当たり前ですが専門の知識が必要なので、勉強になります。

参考

EntityFramework Core とは