SqlBulkCopy excel 字段映射解决办法二使用数字索引代替字段名称做映射避免字段中有中文大小写等情况

时间：2022-03-10 17:17

多线程的SqlBulkCopy批量导入、事务和SqlBulkCopy使用的数据集中自定义映射字段的注意事项

  
    
    class 
Program   
   {   
       static 
volatile bool result;   
       static 
void Main(string[] args)   
       {   
          DataSet ds = ExportDataSet();   
 
          //使用2个线程模拟并发操作   
          Thread t = new 
Thread(delegate() { result = Insert(ds); Console.WriteLine(result ? "导入成功" 
: "导入失败"); });   
          t.Start();   
          Thread t1 = new 
Thread(delegate() { result = Insert(ds); Console.WriteLine(result ? "导入成功" 
: "导入失败"); });   
          t1.Start();   
          Console.ReadLine();   
       }   
 
       /// <summary>   
       /// 获取数据DataSet   
       /// </summary>   
       /// <returns></returns>   
 
       static 
private DataSet ExportDataSet()   
       {   
 
           //局域网的某服务器模拟数据库远程连接   
           SqlConnection RemoteConn = new 
SqlConnection("Data Source=192.168.0.183;Initial Catalog=Northwind;User ID=sa;Password=sa");   
 
 
 
           using 
(   
 
            /*目标表与源表结构并不相同，目标表只包含OrderID、CustomerID、EmployeeID、ShipCountry这四个字段。注意这里字段是区分大小写的，不然SqlBulkCopy的WriteToServer方法会报运行时异常：“给定的 ColumnMapping 与源或目标中的任意列均不匹配”的处理方法。这个地方浪费了我1个小时才发现*/
            SqlDataAdapter oda = new 
SqlDataAdapter("SELECT [OrderID], [CustomerID], [EmployeeID], [ShipCountry] FROM [Northwind].[dbo].[Orders]", RemoteConn))   
 
           //如果目标表与源表结构完全一致，则用下面语句即可，msdn的例子也只是这样   
 
            //SqlDataAdapter oda = new SqlDataAdapter("SELECT * FROM [Ednoland].[dbo].[Score]", RemoteConn))   
           {   
               DataSet ds = new 
DataSet();   
 
               oda.Fill(ds, "Orders");//给定表名   
 
                 return 
ds;   
 
           }   
 
       }   
 
       /// <summary>   
       /// 将DataSet导入远程数据库(未来放在WebService中)   
       /// </summary>   
       /// <param name="ds"></param>   
       /// <returns></returns>   
       public 
static bool Insert(DataSet ds)   
       {   
 
 
           using 
(SqlConnection sqlconn = new 
SqlConnection("Data Source=.;Initial Catalog=Northwind;User ID=sa;Password=sa"))   
           {   
               sqlconn.Open();   
               SqlTransaction sqlbulkTransaction = sqlconn.BeginTransaction(IsolationLevel.ReadCommitted);   
 
 
 
               using 
(SqlBulkCopy sbc = new 
SqlBulkCopy(sqlconn, SqlBulkCopyOptions.KeepIdentity, sqlbulkTransaction))   
               {   
                   sbc.BatchSize = 20000;//20000行每连接   
                   sbc.BulkCopyTimeout = 50;//50秒超时   
                   if 
(ds.Tables == null 
|| ds.Tables.Count == 0)   
                       return 
false;   
 
                   if 
(ds.Tables.Count == 1)   
                   {   
 
 
                       return 
BulkInsert(sbc, ds.Tables[0], sqlbulkTransaction); ;   
 
                   }   
                   else
                   {   
                       bool 
res = true;   
                       foreach 
(DataTable dt in 
ds.Tables)   
                       {   
 
                           res = BulkInsert(sbc, dt, sqlbulkTransaction);   
 
                       }   
                       return 
res;   
                   }   
 
               }   
           }   
 
       }   
 
       private 
static bool BulkInsert(SqlBulkCopy sbc, DataTable dt, SqlTransaction sqlbulkTransaction)   
       {   
            
           bool 
res = true;   
                      try
           {   
 
             //将DataTable表名作为待导入库中的目标表名   
               sbc.DestinationTableName = dt.TableName;   
 
 
 
              //将数据集合和目标服务器库表中的字段对应   
               for 
(int 
i = 0; i < dt.Columns.Count; i++)   
               {   
                   //sbc.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);   
                     sbc.ColumnMappings.Add(i,i);//可以避免字段大小写不一致造成无法映射的问题   
               }   
 
               sbc.WriteToServer(dt);   
 
               //提交事务   
               sqlbulkTransaction.Commit();   
               res = true;   
           }   
           catch 
(SqlException ex)   
           {   
               res = false;   
               sqlbulkTransaction.Rollback();   
           }   
 
           return 
res;   
       }   
   }