【C#】Oracle DBにデータを格納するコードを記載してみた

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

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

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

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

```C#: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格納用のクラスに変換するクラスです。

```C#: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に格納するためのデータクラスをまとめたクラスです。

```C#: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`クラスを定義しています。

```C#: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インターフェース)というインターフェースを実装しています。

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


```C#: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](#入力データをdbに格納する大元となるクラス)クラスから呼び出されます。

```C#: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に接続するためのクラスです。

```C#: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文を記述せずにデータクラスを介して操作を行うことができます。

:::note
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から入手することが出来ます。
参考：
- [GitHub - LambdicSql.Oracle](https://github.com/Codeer-Software/LambdicSql.Oracle)
- [LambdicSqlがβ版になりました！](https://vectographic64.rssing.com/chan-55763919/all_p4.html)
- [最近のLambdicSql - この世の全てのSQLをC#で表現する](https://ishikawa-tatsuya.hatenablog.com/entry/2017/01/26/144401)

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

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

:::note
このコードでは以下のようにデバッグモードとリリースモードでコードを分けています。
デバッグモードでは出力ウィンドウに、`EntityFrameworkCore`が実際にDBへ投げているSQLを出力してくれるように設定しています。
```C#
#if DEBUG
        options.UseOracle(connectionString).UseLoggerFactory(loggerFactory).EnableSensitiveDataLogging();
#else
        options.UseOracle(connectionString);
#endif
:::

### トランザクションを管理するクラス
指定されたテーブルの排他ロックを取得し、トランザクション内でロックを管理、またエラー発生時にリソース開放を行っているクラスです。
```C#: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`でのトランザクション管理を強化し、より安全で使いやすい形で提供するためのラッパークラスです。

```C#: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](#入力データをdbに格納する大元となるクラス)クラスを呼び出しているクラスです。

```C#: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 とは](https://devadjust.exblog.jp/27824075/)