C#利用SqlBulkCopy大批量导入excel文件进入SQL

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› Node.js›Node.js教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

   public void BuckCopy(DataTable dt, string destinationTableName)
        {
            using (SqlConnection sqlConn = (SqlConnection)this.CurrentDataBase.CreateConnection())
            {
                if (sqlConn.State != ConnectionState.Open)
                    sqlConn.Open();
                using (SqlBulkCopy buckCopy = new SqlBulkCopy(sqlConn))
                {
                    buckCopy.DestinationTableName = destinationTableName;
                    buckCopy.ColumnMappings.Add("ID", "ID");                    
                    buckCopy.WriteToServer(dt);
                }
            }
        }

升级，动态mapping

  /// <summary>
        /// 将datatable数据bulkcopy进SQL table
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tablename"></param>
        /// <param name="_constring"></param>
        public static void SqlBulkCopy(DataTable dt, string tablename, string _constring)
        {
            try
            {
                using (SqlConnection sqlConn = new SqlConnection(_constring))
                {
                    if (sqlConn.State != ConnectionState.Open)
                        sqlConn.Open();
                    /*查询数据 tablename对应的字段*/
                    string[] restrictions = new string[4];
                    restrictions[1] = "dbo";
                    restrictions[2] = tablename;
                    /*获取table的列*/
                    DataTable tableinfo = sqlConn.GetSchema("Columns", restrictions);
                    if (tableinfo.Rows.Count > 0)
                    {
                        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn))
                        {
                            bulkCopy.BulkCopyTimeout = _timeout;
                            bulkCopy.DestinationTableName = tablename;
                            bulkCopy.BatchSize = 1000;//每1000条提交一次
                            foreach (DataColumn dc in dt.Columns)
                            {
                                foreach (DataRow dr in tableinfo.Rows)
                                {
                                    //动态mapping字段，大小写可以忽略
                                    if (dc.ColumnName.ToLower() == dr["COLUMN_NAME"].ToString().ToLower())
                                    {
                                        bulkCopy.ColumnMappings.Add(dc.ColumnName, dr["COLUMN_NAME"].ToString());
                                        break;//只要匹配到就跳出该次循环，进行下一次
                                    }
                                }
                            }
                            bulkCopy.WriteToServer(dt);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error("Error:Connercthelper SqlBulkCopy", ex);
                throw;
            }



        }

测试6W条数据，大概9-10秒。如果mapping是写死的，应该要快一些。

又重新重载了一下方法：支持调用的时候先mapping好。

        /// <summary>
        ///  将datatable数据bulkcopy进SQL table
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="tablename"></param>
        /// <param name="_constring"></param>
        /// <param name="columnmapping">自定义mapping column</param>
        public static void SqlBulkCopy(DataTable dt, string tablename, string _constring, SqlBulkCopyColumnMapping[] columnmapping)
        {
            try
            {
                  using (SqlConnection sqlConn = new SqlConnection(_constring))
                  {
                      if (sqlConn.State != ConnectionState.Open)
                          sqlConn.Open();
                       using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn))
                       {
                           bulkCopy.BulkCopyTimeout = _timeout;
                           bulkCopy.DestinationTableName = tablename;
                           bulkCopy.BatchSize = 1000;//每1000条提交一次
                           //循环mapping
                           foreach(SqlBulkCopyColumnMapping map in columnmapping)
                           {
                               bulkCopy.ColumnMappings.Add(map);
                           }
                           bulkCopy.WriteToServer(dt);
                       }
                  }
            }
            catch (Exception ex)
            {
                LogHelper.Error("Error:Connercthelper SqlBulkCopy", ex);
                throw;
            }
        }