2023年6月21日发(作者：)

使⽤SqlBulkCopy批量插⼊，批量更新数据解决⽅案最近重构公司的⼀个⽼项⽬，需要从指定库中批量获取更新数据，⼀条条插⼊实在太慢，搜寻了⼀下解决⽅案，有说使⽤sqlbulkcopy实现的，果然快很多。但是SqlBulkCopy有其局限性，字段顺序及名称必须完全对应，遂想了⼀个解决⽅案特记录如下：

思路： 1） 根据取过来的数据（DataTable）字段创建临时表 ‘ tmp_表名‘ 2）SqlBulkCopy数据导⼊临时表

3） 根据正式表主键对数据进⾏处理，删除相同主键数据，批量导⼊数据 4） 删除临时表废话不多说，上源码： public class DataBaseUtil

{ public event Action LogHandler; private dynamic GetCreateTableSql(DataTable table,string tmptableName, string tableName) { var colList = new List(); var fieldNames = new List(); foreach (DataColumn col in s) {

(Name); } var sql = (@" if object_id('{0}') is not null begin truncate table {0} drop table {0} end

select top 0 {1} into {0} from {2}; ", tmptableName, (",", fieldNames),tableName);

return new { Sql = sql ,Fields= fieldNames }; } public bool SqlBulkCopyUpgrade(string connectionString, string tableName,string keyField, DataTable table) { bool isSucc = false; var tmpTableName = "tmp_" + tableName; var msg = ""; try { #region 1.创建临时表 var filedObj = GetCreateTableSql(table, tmpTableName,tableName); ExecuteNoQuerySql(connectionString, ); #endregion

LogHandler("新建临时表"+ tmpTableName+ "成功....");

#region 2.将his数据使⽤sqlbulk导⼊到临时表 //开始数据保存逻辑 using (SqlConnection conn = new SqlConnection(connectionString)) { (); SqlTransaction tran = ransaction();//开启事务 //在插⼊数据的同时检查约束，如果发⽣错误调⽤sqlbulkTransaction事务 SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, onstraints, tran); ationTableName = tmpTableName;//代表要插⼊数据的表名 ationTableName = tmpTableName;//代表要插⼊数据的表名 foreach (DataColumn dc in s) //传⼊上述table { (Name, Name);//将table中的列与数据库表这的列⼀⼀对应 } try { oServer(table); (); isSucc = true; } catch (Exception ex) { ck(); LogHandler("histoTmp" + e); } finally { (); (); } if (!isSucc) throw new Exception($"histoTmp失败{tmpTableName}"); LogHandler("histoTmp" + tmpTableName + "成功...."); } #endregion #region 3.将临时表数据，批量插⼊到正式表 var fieldStr = (",", ); var sqlList = new List(); //包含主键设置的话，在正式表⾥删除重复数据 if (!OrEmpty(keyField)) { var delSql= (@"delete t1 from {0} t1 inner join tmp_{0} t2 on t2.{1}=t1.{1};", tableName, keyField); (delSql); } var insertSql = (@" insert into {0}({1})select {1} from tmp_{0};", tableName, fieldStr); (insertSql); ($"drop table tmp_{tableName}");

isSucc = ExecuteSqlTran(connectionString, sqlList) > 0; if (!isSucc) throw new Exception($"TmpToMiddle失败{tableName}"); #endregion } catch (Exception e) { LogHandler(e); } return isSucc; }

#region QueryMult public DataTable ReieveTableBySql(string connectionStr,string sql) { DataSet ds = (sql, connectionStr); if (ds == null || == 0) return null; var dt = [0]; return dt; }

public int ExecuteSqlTran(string connectionStr, List SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionStr)) { (); SqlCommand cmd = new SqlCommand(); tion = conn; SqlTransaction tx = ransaction(); ction = tx; var currentSql = ""; try { int count = 0; for (int n = 0; n < ; n++) { var info = SQLStringList[n]; string strsql = ; if (OrEmpty(strsql)) continue; if (().Length > 1) { dText = strsql; currentSql = strsql; //dType = ; if (ameters != null) { foreach (SqlParameter parm in ameters) (parm); } count += eNonQuery(); } } (); return count; } catch (Exception ee) { var a = currentSql; ck(); LogHandler("执⾏trans报错：sql:" + currentSql + e+ "问题："+ e); return 0;//throw; } } } public int ExecuteSqlTran(string connectionStr, List SQLStringList) { var infos = new List(); for (var i = 0; i < ; i++) { (new SqlExeInfo(){ExeSql =SQLStringList[i]}); } } return ExecuteSqlTran(connectionStr,infos); } #endregion

}

2023年6月21日发(作者：)

使⽤SqlBulkCopy批量插⼊，批量更新数据解决⽅案最近重构公司的⼀个⽼项⽬，需要从指定库中批量获取更新数据，⼀条条插⼊实在太慢，搜寻了⼀下解决⽅案，有说使⽤sqlbulkcopy实现的，果然快很多。但是SqlBulkCopy有其局限性，字段顺序及名称必须完全对应，遂想了⼀个解决⽅案特记录如下：

思路： 1） 根据取过来的数据（DataTable）字段创建临时表 ‘ tmp_表名‘ 2）SqlBulkCopy数据导⼊临时表

3） 根据正式表主键对数据进⾏处理，删除相同主键数据，批量导⼊数据 4） 删除临时表废话不多说，上源码： public class DataBaseUtil

{ public event Action LogHandler; private dynamic GetCreateTableSql(DataTable table,string tmptableName, string tableName) { var colList = new List(); var fieldNames = new List(); foreach (DataColumn col in s) {

(Name); } var sql = (@" if object_id('{0}') is not null begin truncate table {0} drop table {0} end

select top 0 {1} into {0} from {2}; ", tmptableName, (",", fieldNames),tableName);

return new { Sql = sql ,Fields= fieldNames }; } public bool SqlBulkCopyUpgrade(string connectionString, string tableName,string keyField, DataTable table) { bool isSucc = false; var tmpTableName = "tmp_" + tableName; var msg = ""; try { #region 1.创建临时表 var filedObj = GetCreateTableSql(table, tmpTableName,tableName); ExecuteNoQuerySql(connectionString, ); #endregion

LogHandler("新建临时表"+ tmpTableName+ "成功....");

#region 2.将his数据使⽤sqlbulk导⼊到临时表 //开始数据保存逻辑 using (SqlConnection conn = new SqlConnection(connectionString)) { (); SqlTransaction tran = ransaction();//开启事务 //在插⼊数据的同时检查约束，如果发⽣错误调⽤sqlbulkTransaction事务 SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, onstraints, tran); ationTableName = tmpTableName;//代表要插⼊数据的表名 ationTableName = tmpTableName;//代表要插⼊数据的表名 foreach (DataColumn dc in s) //传⼊上述table { (Name, Name);//将table中的列与数据库表这的列⼀⼀对应 } try { oServer(table); (); isSucc = true; } catch (Exception ex) { ck(); LogHandler("histoTmp" + e); } finally { (); (); } if (!isSucc) throw new Exception($"histoTmp失败{tmpTableName}"); LogHandler("histoTmp" + tmpTableName + "成功...."); } #endregion #region 3.将临时表数据，批量插⼊到正式表 var fieldStr = (",", ); var sqlList = new List(); //包含主键设置的话，在正式表⾥删除重复数据 if (!OrEmpty(keyField)) { var delSql= (@"delete t1 from {0} t1 inner join tmp_{0} t2 on t2.{1}=t1.{1};", tableName, keyField); (delSql); } var insertSql = (@" insert into {0}({1})select {1} from tmp_{0};", tableName, fieldStr); (insertSql); ($"drop table tmp_{tableName}");

isSucc = ExecuteSqlTran(connectionString, sqlList) > 0; if (!isSucc) throw new Exception($"TmpToMiddle失败{tableName}"); #endregion } catch (Exception e) { LogHandler(e); } return isSucc; }

#region QueryMult public DataTable ReieveTableBySql(string connectionStr,string sql) { DataSet ds = (sql, connectionStr); if (ds == null || == 0) return null; var dt = [0]; return dt; }

public int ExecuteSqlTran(string connectionStr, List SQLStringList) { using (SqlConnection conn = new SqlConnection(connectionStr)) { (); SqlCommand cmd = new SqlCommand(); tion = conn; SqlTransaction tx = ransaction(); ction = tx; var currentSql = ""; try { int count = 0; for (int n = 0; n < ; n++) { var info = SQLStringList[n]; string strsql = ; if (OrEmpty(strsql)) continue; if (().Length > 1) { dText = strsql; currentSql = strsql; //dType = ; if (ameters != null) { foreach (SqlParameter parm in ameters) (parm); } count += eNonQuery(); } } (); return count; } catch (Exception ee) { var a = currentSql; ck(); LogHandler("执⾏trans报错：sql:" + currentSql + e+ "问题："+ e); return 0;//throw; } } } public int ExecuteSqlTran(string connectionStr, List SQLStringList) { var infos = new List(); for (var i = 0; i < ; i++) { (new SqlExeInfo(){ExeSql =SQLStringList[i]}); } } return ExecuteSqlTran(connectionStr,infos); } #endregion

}