/// <summary>
/// 利用Net SqlBulkCopy 批量导入数据库,速度超快
/// </summary>
/// <param name="dt">源内存数据表（先通过SELECT TOP 0获取空白DataTable）</param>
/// <param name="destinationTableName">目标表名称</param>
/// <param name="bulkCopyTimeout">超时限制（毫秒）</param>
/// <param name="batchSize">批大小（默认0，即一次性导入）</param>
public override bool SqlBulkCopyData(DataTable dt, string destinationTableName, int bulkCopyTimeout = 1000, int batchSize = 0)
{
var result = false;
var stopwatch = new Stopwatch();
stopwatch.Start();
if (string.IsNullOrEmpty(destinationTableName))
{
destinationTableName = dt.TableName;
}
// SQL 数据连接
SqlConnection sqlConnection = null;
// 打开数据库
Open();
// 获取连接
sqlConnection = (SqlConnection)GetDbConnection();
using (var tran = sqlConnection.BeginTransaction())
{
// 批量保存数据，只能用于Sql
var sqlBulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, tran);
sqlBulkCopy.BatchSize = batchSize;
// 设置目标表名称
sqlBulkCopy.DestinationTableName = destinationTableName;
// 设置超时限制
sqlBulkCopy.BulkCopyTimeout = bulkCopyTimeout;
foreach (DataColumn dtColumn in dt.Columns)
{
sqlBulkCopy.ColumnMappings.Add(dtColumn.ColumnName, dtColumn.ColumnName);
}
try
{
// 写入
sqlBulkCopy.WriteToServer(dt);
// 提交事务
tran.Commit();
result = true;
}
catch (Exception ex)
{
tran.Rollback();
sqlBulkCopy.Close();
LogUtil.WriteException(ex);
}
finally
{
sqlBulkCopy.Close();
Close();
}
}
stopwatch.Stop();
var statisticsText = $"Elapsed time: {stopwatch.Elapsed.TotalMilliseconds}ms";
SqlUtil.WriteLog(destinationTableName, "SqlBulkCopy", null, statisticsText);
if (stopwatch.Elapsed.TotalMilliseconds >= BaseSystemInfo.SlowQueryMilliseconds)
{
var sb = Pool.StringBuilder.Get();
sb.Append("SqlBulkCopy to Table " + destinationTableName + " , " + statisticsText);
LogUtil.WriteLog(sb.Put(), "Slow.DbHelper.SqlBulkCopy");
}
return(result);
}
