TIPS - 使用 Dapper 將 JObject 陣列匯入資料表
3 |
今天遇到的需很類似先前展示過的正式台測試台 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.
Comments
# by 阿梅
請問本案例的DB效能開多大呢? 使用Azure DB開到20dtu,單一table新增3000多筆也要30s以上。
# by Jeffrey
to 阿梅,我的測試對象都是地端資料庫,沒法提供相關參考數據。
# by 路人
Execute 那邊要寫成 cn.Execute(insertScript, paramList, tran); 不然會出錯喔