Dapper Plus / BulkInsert 使用紀錄

為工作紀錄。Dapper, .NET6。

引用函式庫

Z.Dapper.Plus 9.2.1nuget

Bulk Insert | The Fastest Way in Dapper to Insert Multiple Rowsdapper-plus.net

注意：Dapper Plus 非免費，只能試用１個月。

關鍵原始碼

using Dapper;
using Z.Dapper.Plus;

public void InvokeJob()
{
  using var connSRC = DBHelper.SourceDB.Open();
  using var connTGT = DBHelper.TargetDB.Open();
  
  Stopwatch stopWatch = new Stopwatch();
  stopWatch.Start();

  DoBulkCopyTable<YourTableA>(connSRC , connTGT );
  DoBulkCopyTable<YourTableB>(connSRC , connTGT );
  DoBulkCopyTable<YourTableC>(connSRC , connTGT );
  
  stopWatch.Stop();
  TimeSpan spentTime = stopWatch.Elapsed;
  _logger.LogInformation($"匯入資料完成，共花費時間{spentTime:c}。");
}

///※ Truncate table & BulkInsert
void DoBulkCopyTable<TTable>(SqlConnection connODS, SqlConnection connSLP)
{
  Type type = typeof(TTable);
  string tableName = type.Name;
  connSLP.Execute($"TRUNCATE TABLE {tableName};");
  var srcList = connODS.Query<TTable>($"SELECT * FROM {tableName} (NOLOCK);");
  connSLP.BulkInsert<TTable>(srcList);
}