/// <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); }