使用SqlBulkCopy批量插入，批量更新数据解决方案

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

特记录如下： 

思路：

   1） 根据取过来的数据（DataTable）字段创建临时表 ‘ tmp_表名‘

    2）SqlBulkCopy数据导入临时表 

    3） 根据正式表主键对数据进行处理，删除相同主键数据，批量导入数据

    4） 删除临时表

废话不多说，上源码：

 public class DataBaseUtil 
    {
        public event Action<string> LogHandler;
        private dynamic GetCreateTableSql(DataTable table,string tmptableName, string tableName)
        {
            var colList = new List<string>();
            var fieldNames = new List<string>();
            foreach (DataColumn col in table.Columns)
            {
                
                fieldNames.Add(col.ColumnName);
            }

            var sql = string.Format(@" if object_id('{0}') is not null begin truncate table {0} drop table {0} end 
                select top 0 {1} into {0} from {2};
                    ", tmptableName, string.Join(",", 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, filedObj.Sql);
                #endregion
               
                LogHandler("新建临时表"+ tmpTableName+ "成功....");
              
                #region 2.将his数据使用sqlbulk导入到临时表
                //开始数据保存逻辑

                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();

                    SqlTransaction tran = conn.BeginTransaction();//开启事务

                    //在插入数据的同时检查约束，如果发生错误调用sqlbulkTransaction事务
                    SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, tran);
                    bulkCopy.DestinationTableName = tmpTableName;//代表要插入数据的表名
                    foreach (DataColumn dc in table.Columns)  //传入上述table
                    {
                        bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);//将table中的列与数据库表这的列一一对应
                    }

                    try
                    {
                        bulkCopy.WriteToServer(table);
                        tran.Commit();
                        isSucc = true;
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        LogHandler("histoTmp" + ex.Message);
                    }
                    finally
                    {
                        bulkCopy.Close();
                        conn.Close();
                    }
                    if (!isSucc) throw new Exception($"histoTmp失败{tmpTableName}");
                    LogHandler("histoTmp" + tmpTableName + "成功....");

                }
                #endregion

                #region 3.将临时表数据，批量插入到正式表

                var fieldStr = string.Join(",", filedObj.Fields);
                var sqlList = new List<string>();
                //包含主键设置的话，在正式表里删除重复数据
                if (!string.IsNullOrEmpty(keyField))
                {
                    var delSql= string.Format(@"delete t1 from {0} t1 inner join tmp_{0} t2 on t2.{1}=t1.{1};",
                        tableName,  keyField);
                    sqlList.Add(delSql);
                }

                var insertSql = string.Format(@" insert into {0}({1})select {1} from tmp_{0};", tableName, fieldStr);
                sqlList.Add(insertSql);
                sqlList.Add($"drop table tmp_{tableName}");
               
                isSucc = ExecuteSqlTran(connectionString, sqlList) > 0;
                if (!isSucc) throw new Exception($"TmpToMiddle失败{tableName}");
                #endregion
            }
            catch (Exception e)
            {
                LogHandler(e.Message);
            }
            return isSucc;
        }

       

        #region QueryMult

        public DataTable ReieveTableBySql(string connectionStr,string sql)
        {
            DataSet ds = SQLHelper.Query(sql, connectionStr);

            if (ds == null || ds.Tables.Count == 0) return null;
            var dt = ds.Tables[0];

            return dt;

        }



      
    

        public int ExecuteSqlTran(string connectionStr, List<SqlExeInfo> SQLStringList)
        {
            using (SqlConnection conn = new SqlConnection(connectionStr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;

                var currentSql = "";
                try
                {
                    int count = 0;
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        var info = SQLStringList[n];
                        string strsql = info.ExeSql;
                        if (string.IsNullOrEmpty(strsql)) continue;
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            currentSql = strsql;
                            //cmd.CommandType = CommandType.Text;
                            if (info.SqlParameters != null)
                            {
                                foreach (SqlParameter parm in info.SqlParameters)
                                    cmd.Parameters.Add(parm);
                            }

                            count += cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                    return count;
                }
                catch (Exception ee)
                {
                    var a = currentSql;
                    tx.Rollback();

                    LogHandler("执行trans报错：sql:" + currentSql + Environment.NewLine+
                        "问题："+ ee.Message);
                    return 0;//throw;
                }
            }
        }


        public int ExecuteSqlTran(string connectionStr, List<string> SQLStringList)
        {
            var infos = new List<SqlExeInfo>();
            for (var i = 0; i < SQLStringList.Count; i++)
            {
                infos.Add(new SqlExeInfo(){ExeSql =SQLStringList[i]});
            }
            return ExecuteSqlTran(connectionStr,infos);
        }

        #endregion
	
    }