C#使用SqlBulkCopy批量复制数据到数据表
本文实例讲述了C#使用SqlBulkCopy批量复制数据到数据表的方法。分享给大家供大家参考。具体实现方法如下：
使用SqlBulkCopy类只能向SQLServer表写入数据。但是，数据源不限于SQLServer；可以使用任何数据源，只要数据可加载到DataTable实例或可使用IDataReader实例读取数据
1.使用Datatable作为数据源的方式：
下面的代码使用到了ColumnMappings，因为目标表和数据源Datatable的结构不一致，需要这么一个映射来指定对应关系
publicstringSaveJHCData(LzShopBasicData[]datas)
{
varresult=newAResult();
SqlConnectioncon=newSqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["**"].ConnectionString);
con.Open();
foreach(varitemindatas)
{
Logger.Info("数据更新处理，店铺名称："+item.ShopName+"数据日期"+item.SellDate); try { using(TransactionScopescope=newTransactionScope()) {
DataTableJHCOrderItemsdt=SaveJHCOrderItemsData(item); SqlBulkCopyJHCOrderItemscopy=newSqlBulkCopy(con); JHCOrderItemscopy.ColumnMappings.Add("orderId","orderId"); JHCOrderItemscopy.ColumnMappings.Add("auctionId","auctionId"); JHCOrderItemscopy.ColumnMappings.Add("itemTitle","itemTitle"); JHCOrderItemscopy.ColumnMappings.Add("tradeAmt","tradeAmt"); JHCOrderItemscopy.ColumnMappings.Add("alipayNum","alipayNum"); JHCOrderItemscopy.ColumnMappings.Add("tradeTime","tradeTime"); JHCOrderItemscopy.ColumnMappings.Add("uv","uv"); JHCOrderItemscopy.ColumnMappings.Add("srcId","srcId"); JHCOrderItemscopy.ColumnMappings.Add("srcName","srcName"); JHCOrderItemscopy.ColumnMappings.Add("DataType","DataType"); JHCOrderItemscopy.ColumnMappings.Add("DataDate","DataDate"); JHCOrderItemscopy.ColumnMappings.Add("OrderSourceID","OrderSourceID"); JHCOrderItemscopy.ColumnMappings.Add("ShopName","ShopName"); JHCOrderItemscopy.DestinationTableName="JHCOrderItems"; JHCOrderItemscopy.WriteToServer(JHCOrderItemsdt); result.Updatedata+=1; result.UpdatedataText+=item.SellDate+","; scope.Complete(); Logger.Info(item.SellDate+"事务提交"); } } catch(Exceptionex) { Logger.Error(ex.ToString()); continue; } } con.Close(); returnresult.ToSerializeObject(); }