在事务中执行批量复制操作SqlBulkCopy,SqlTransaction .
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 }
调用SqlBulkCopy方法得到的结果：
已完成的行数：10
已完成的行数：20
已完成的行数：30
SQLBulkCopy的参数和方法的描述可以参考网站：https://msdn.microsoft.com/zh-cn/library/System.Data.SqlClient.SqlBulkCopy(v=vs.80).aspx
