批量导入/更新 数据的几种方法

在工作中经常遇到要导入数据的场景。

1 导入全新的数据

2 导入的数据中库中可能已经存在，已经存在的数据不能覆盖，不能变更

3 导入的数据中库中可能已经存在，已经存在的数据主键等不能变化，同时需要更新这些数据的一些字段（比如：积分字段）

 

第一种最简单，最坑爹的是第三种了。

新手碰到这种问题往往一筹莫展，能想到的最直接的办法就是 一条条获取 ，然后判断，然后 update。这种方式的效率在少量数据的时候还没太大问题，当达到上千上万条时候问题就会出现了。

这种处理方式的资源开销和效率简直“惨绝人寰”。我也经历过这种方式，并渐渐的了解是应用了更高效的方式。

一、一条条的插入/获取判断和更新

这种方式上文已经说了效率和资源开销都是最大的，没啥好讲的，做程序的都会

二、一次插入多条/一次更新多条

插入多条：即一个connection.open()  后执行多个 command命令 。参考代码如下：

　　

 public static void ExecuteSqlTran(List<string> SQLStringList)//SQLs
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }

这种的方式效率会比  一  高很多，但是资源开销和执行效率还是难以让人忍受，在一次执行语句数量达到万级时候就表现的很明显。

我工作中的一次案例数据库中数据有2300w条记录，要对比的数据有20w条记录时候  纯update语句 1w条数据时候需要等待100s左右，当要对比的数据为40w条时候需要等待时间是600s左右。这种方式在效率上是相当让人无法忍受的。

三、使用DataSet更新记录

参考代码如下：

　　

/// <summary>
        /// 利用dataset批量更新数据
        /// </summary>
        /// <param name="sqlString"></param>

        public void BatchUpDataForDataset(string sqlString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {


                using (SqlDataAdapter da = new SqlDataAdapter(sqlString, connection))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        da.Fill(ds);
                        //交给委托处理
                        if (DataUpWork != null)
                        {
                            DataUpWork(ds);
                        }

                        SqlCommandBuilder scb = new SqlCommandBuilder(da);
                        //执行更新
                        da.InsertCommand = scb.GetUpdateCommand();
                        da.Update(ds);
                        //使DataTable保存更新
                        ds.AcceptChanges();

                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
            }
        }

在代码中我使用了委托DataUpWork来处理dataset中的数据，随后提交。

这种方式写代码可能会轻松很多，但是效率也是不尽人意，好像最终也是生成update语句批量执行的，具体没有细细研究，请知道的大神指出这种方式的工作原理。

这种方式同样不能同时执行插入数据的操作（个人没测试过，不知道对DataSet新增的数据会不会插入到数据库中。(/ □ \)）

四、 使用SqlBulkCopy批量插入全新的数据

这个批量插入效率真的极高，插入10w也是瞬间完成，参考代码

  /// <summary>
        /// 使用原列列表、目标列表和目标表明及数据记录数在指定的时间将数据批量导入到数据库
        /// </summary>
        /// <param name="sourceColumnName">源列name</param>
        /// <param name="dbTableColumnName">目标列name</param>
        /// <param name="tableName">目标表名</param>
        /// <param name="sourceDt">数据源</param>
        /// <param name="timeOut">指定时间</param>
        public static void BatchInput(List<string> sourceColumnName, List<string> dbTableColumnName, string tableName, DataTable sourceDt, int timeOut = 300 ,string  exSql="")
        {
            if (sourceColumnName.Count != dbTableColumnName.Count)
            {
                throw new Exception("传递的量表数据数不匹配");
            }


            SqlConnection sqlConn = new SqlConnection(connectionString);
            SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
            bulkCopy.BulkCopyTimeout = timeOut;

            //目标表
            if (string.IsNullOrEmpty(tableName))
            {
                throw new ArgumentNullException(" 无效的目标表名：" + tableName);
            }

            bulkCopy.DestinationTableName = tableName;



            for (int i = 0; i < sourceColumnName.Count; i++)
            {
                //原列和目标列
                bulkCopy.ColumnMappings.Add(sourceColumnName[i], dbTableColumnName[i]);

            }
            //导入的数据量
            bulkCopy.BatchSize = sourceDt.Rows.Count;

            try
            {
                sqlConn.Open();
                if (!string.IsNullOrEmpty(exSql))
                {
                    SqlCommand cmd = new SqlCommand(exSql, sqlConn);
                    cmd.ExecuteNonQuery();
                }

                if (bulkCopy.BatchSize != 0)
                {
                    bulkCopy.WriteToServer(sourceDt);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {

                //释放资源
              
                if (bulkCopy != null)
                {
                    bulkCopy.Close();
                }

                sqlConn.Close();
            }


        }

但是该方法只能插入全新的记录，对于已经存在的数据无能为力了，不会去除已经存在的记录项，更不能更新已经存在的记录项