在事务中执行批量复制操作SqlBulkCopy,SqlTransaction .

浏览数：39 / 时间：2015年06月12日

Microsoft SQL Server 提供一个称为 bcp 的流行的命令提示符实用工具，用于将数据从一个表移动到另一个表（表既可以在同一个服务器上，也可以在不同服务器上）。SqlBulkCopy 类允许编写提供类似功能的托管代码解决方案。还有其他将数据加载到 SQL Server 表的方法（例如 INSERT 语句），但相比之下 SqlBulkCopy 提供明显的性能优势。

使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 DataTable 实例或可使用 IDataReader 实例读取数据。

 1         public void SqlBulkCopy()
 2         {
 3             SqlConnectionStringBuilder sqlsb = new SqlConnectionStringBuilder();
 4             sqlsb.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
 5 
 6             using (SqlConnection conn=new SqlConnection(sqlsb.ConnectionString))
 7             {
 8                 using (SqlCommand cmd = new SqlCommand("select corpID,corpNo, corpName, corpPhone, RegisteredAddress, registerTime, certifyGrade, certifyNo, corporateName, corporatePhone, licensseNo, regisMoney, response, responsePhone, post, email, zip from corp", conn))
 9                 {
10                     conn.Open();
11                    SqlDataReader sqlreader= cmd.ExecuteReader();//获取源数据
12                    string ConnStr = ConfigurationManager.ConnectionStrings["connectionstringHouseonLine"].ConnectionString;
13                    using (SqlConnection onehouse = new SqlConnection(ConnStr))
14                    {
15                        onehouse.Open();
16                        using (SqlTransaction trans=onehouse.BeginTransaction())
17                        {
18                          
19                            using (SqlBulkCopy bulk=new SqlBulkCopy(onehouse,SqlBulkCopyOptions.KeepIdentity,trans))
20                            {
21                                bulk.BatchSize = 10;//设置每一批次执行的行数
22                                bulk.BulkCopyTimeout = 500000000;//在操作超时之前，允许程序操作的时间单位秒数
23                                bulk.SqlRowsCopied += new SqlRowsCopiedEventHandler(bulk_SqlRowsCopied);//定义事件，完成NotifyAfter属性指定的行数之后发生
24                                bulk.NotifyAfter = 10;//定义在出发通知事件之前要处理的行数
25                                bulk.DestinationTableName = "corp"; //复制数据至目标对象
26 
27                                bulk.ColumnMappings.Clear();//清空一下集合
28 
29                                //设置源对象与目标对象的字段对应关系
30                               SqlBulkCopyColumnMapping column17 = new SqlBulkCopyColumnMapping("corpID", "corpID");
31                                bulk.ColumnMappings.Add(column17);
32                                SqlBulkCopyColumnMapping column1 = new SqlBulkCopyColumnMapping("corpNo", "corpNo");
33                                bulk.ColumnMappings.Add(column1);
34                                SqlBulkCopyColumnMapping column2 = new SqlBulkCopyColumnMapping("corpName", "corpName");
35                                bulk.ColumnMappings.Add(column2);
36                                SqlBulkCopyColumnMapping column3 = new SqlBulkCopyColumnMapping("corpPhone", "corpPhone");
37                                bulk.ColumnMappings.Add(column3);
38                                SqlBulkCopyColumnMapping column4 = new SqlBulkCopyColumnMapping("RegisteredAddress", "RegisteredAddress");
39                                bulk.ColumnMappings.Add(column4);
40                                SqlBulkCopyColumnMapping column5 = new SqlBulkCopyColumnMapping("registerTime", "registerTime");
41                                bulk.ColumnMappings.Add(column5);
42                                SqlBulkCopyColumnMapping column6 = new SqlBulkCopyColumnMapping("certifyGrade", "certifyGrade");
43                                bulk.ColumnMappings.Add(column6);
44                                SqlBulkCopyColumnMapping column7 = new SqlBulkCopyColumnMapping("certifyNo", "certifyNo");
45                                bulk.ColumnMappings.Add(column7);
46                                SqlBulkCopyColumnMapping column8 = new SqlBulkCopyColumnMapping("corporateName", "corporateName");
47                                bulk.ColumnMappings.Add(column8);
48                                SqlBulkCopyColumnMapping column9 = new SqlBulkCopyColumnMapping("corporatePhone", "corporatePhone");
49                                bulk.ColumnMappings.Add(column9);
50                                SqlBulkCopyColumnMapping column10 = new SqlBulkCopyColumnMapping("licensseNo", "licensseNo");
51                                bulk.ColumnMappings.Add(column10);
52                                SqlBulkCopyColumnMapping column11 = new SqlBulkCopyColumnMapping("regisMoney", "regisMoney");
53                                bulk.ColumnMappings.Add(column11);
54                                SqlBulkCopyColumnMapping column12 = new SqlBulkCopyColumnMapping("response", "response");
55                                bulk.ColumnMappings.Add(column12);
56                                SqlBulkCopyColumnMapping column13 = new SqlBulkCopyColumnMapping("responsePhone", "responsePhone");
57                                bulk.ColumnMappings.Add(column13);
58                                SqlBulkCopyColumnMapping column14 = new SqlBulkCopyColumnMapping("post", "post");
59                                bulk.ColumnMappings.Add(column14);
60                                SqlBulkCopyColumnMapping column15 = new SqlBulkCopyColumnMapping("email", "email");
61                                bulk.ColumnMappings.Add(column15);
62                                SqlBulkCopyColumnMapping column16 = new SqlBulkCopyColumnMapping("zip", "zip");
63                                bulk.ColumnMappings.Add(column16);
64                              
65                               
66 
67                                
68 
69                                try
70                                {
71                                    bulk.WriteToServer(sqlreader);
72                                    trans.Commit();
73 
74                                }
75                                catch (Exception err)
76                                {
77                                    trans.Rollback();
78 
79                                    throw new Exception(err.ToString());
80 
81                                }
82                                  
83                            }
84                        }
85                    }
86                 }
87             }
88           
89 
90            
91         }
92 
93         void bulk_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
94         {
95             Response.Write("已完成的行数："+e.RowsCopied.ToString());
96         }