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

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

2024-07-05 15:38:21   224人阅读

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

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
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;  
       }  
   }

　　


联系
我们