TIPS - 使用 Dapper 將 JObject 陣列匯入資料表

今天遇到的需很類似先前展示過的正式台測試台 JSON + Dapper 資料搬移術，差別在於資料表匯出的 JSON 個數很多，逐一宣告成強型別類別再反序列化太費工，且日後新增匯入對象就得改程式碼也麻煩。心想 Json.NET 可以 DeserializeObject<List>，直接用 List 當成 .Execute(insertSql, batchInsertObjectArray) 的第二個參數，應是最簡單的方法。

var tableName = "Boo";
List<JObject> list =
	JsonConvert.DeserializeObject<List<JObject>>("SomeTable"
		File.ReadAllText(Path.Combine(importFolder, tableName + ".json")));
var props = list.First().Properties().Select(o => o.Name).ToArray();
var insertScript = GenerateInserScript(props, tableName);
using (var cn = new OracleConnection(cnStr))
{
	try
	{
		logger.Debug($"Insert {n} ({list.Count:n0} rows)...");
		cn.Execute(insertScript, list);
		logger.Debug($"{n} inserted");
	}
	catch (Exception ex)
	{
		logger.Error(ex.ToString());
		throw;
	}
}

代誌沒有憨人想得哈尼甘單，一實測馬上撞壁。Dapper.SqlMapper 的屬性映對機制遇上內藏玄機的 JObject，出現難以預期的古怪錯誤：

ERROR System.InvalidOperationException: An enumerable sequence of parameters (arrays, lists, etc) is not allowed in this context
   於 Dapper.SqlMapper.GetCacheInfo(Identity identity, Object exampleParameters, Boolean addToCache)
   於 Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command)
   於 Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType)

直接映對 JObject 屬性的計劃失敗，我想到先將 JObject 轉成 DynamicParameters 肯定可行。這裡有個訣竅：JProperty.Value 型別為 JToken，取值時必須以 JToken.Value() 明確轉成 int、DateTime、string 等型別，若直接傳入 JToken，執行 INSERT INTO () VALUES () 時會噴出 ORA-00923 : FROM keyword not found where expected 的詭異錯誤，讓人一頭霧水。沒想到更美妙的解法，參考之前的文章：自動產生對應Javascript物件的.NET類別，乖乖用 switch ... case 刻：(註：以下範例未涵蓋所有轉換情境，如遇問題請自行修補)

private static object GetJTokenValue(JToken token)
{
    var t = token.Type;
    switch (t)
    {
        case JTokenType.Boolean:
            return token.Value<bool>();
        case JTokenType.Bytes:
            return token.Value<byte[]>();
        case JTokenType.Date:
            return token.Value<DateTime>();
        case JTokenType.Float:
            return token.Value<decimal>();
        case JTokenType.Integer:
            return token.Value<int>();
        case JTokenType.String:
            return token.Value<string>();
        case JTokenType.Null:
            return null;
        default:
            throw new ApplicationException(
                t.ToString() + " is not supported");
    }
}   

調整後可執行範例如下：

//將 List<JObject> 轉為 List<DynamicParameters>
var paramList = list.Select(o =>
{
    var dp = new DynamicParameters();
    props.ToList().ForEach(p => { dp.Add(p, GetJTokenValue(o[p])); });
    return dp;
}).ToList();
var insertScript = GenerateInserScript(props, tableName);
using (var cn = new OracleConnection(cnStr))
{
    try
    {
        cn.Execute($"TRUNCATE TABLE {tableName}");
        cn.Open();
        //啟用 Transaction 提升效能
        using (var tran = cn.BeginTransaction())
        {
            cn.Execute(insertScript, paramList);
            tran.Commit();
        }
        logger.Debug($"{tableName} inserted");
    }
    catch (Exception ex)
    {
        logger.Error(ex.ToString());
        throw;
    }
}

最後補充用 Dapper 批次 INSERT 的效能技巧，加不加 Transaction 有差別。依本案例實測，四個資料表約六萬筆資料，未加 Transaction 耗時 161 秒，加 Transaction 後耗時 100 秒，加快了 30% 以上。關於 Dapper 批次新增技巧，推薦這篇：Dapper 練習題 - 新增多筆或大量資料 by mrkt

Tips of using Dapper to batch-insert JObject array without strong-typed class mapping. JToken.Value<T>() is required to convert JToken Value to primitive types and IDbTransaction can significantly improve performance.