本文实例讲述了C#使用SqlBulkCopy批量复制数据到数据表的方法。分享给大家供大家参考。具体实现方法如下：
使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 DataTable 实例或可使用 IDataReader 实例读取数据
1.使用Datatable作为数据源的方式：
下面的代码使用到了ColumnMappings，因为目标表和数据源Datatable的结构不一致，需要这么一个映射来指定对应关系
{
var result = new AResult();
SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["**"].ConnectionString);
con.Open();
foreach (var item in datas)
{
Logger.Info("数据更新处理，店铺名称：" + item.ShopName + "数据日期" + item.SellDate);
try
{
using (TransactionScope scope = new TransactionScope())
{
DataTable JHCOrderItemsdt = SaveJHCOrderItemsData(item);
SqlBulkCopy JHCOrderItemscopy = new SqlBulkCopy(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 (Exception ex)
{
Logger.Error(ex.ToString());
continue;
}
}
con.Close();
return result.ToSerializeObject();
}</div>
2.使用IDataReader作为数据源的方式，这种方式个人认为用的很少，首先目标表和来源表两个数据库连接你都需要拿到，如果两个都可以拿到，一般直接操作sql就可以解决：
这里是直接拷贝的MSDN的代码，
用到的AdventureWorks数据库可以直接在网上下载到，下载地址如下：http://msftdbprodsamples.codeplex.com/releases
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks database.
using (SqlConnection sourceConnection =
new SqlConnection(connectionString))
{
sourceConnection.Open();
// Perform an initial count on the destination table.
SqlCommand commandRowCount = new SqlCommand(
"SELECT COUNT(*) FROM " +
"dbo.BulkCopyDemoMatchingColumns;",
sourceConnection);
long countStart = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Starting row count = {0}", countStart);
// Get data from the source table as a SqlDataReader.
SqlCommand commandSourceData = new SqlCommand(
"SELECT ProductID, Name, " +
"ProductNumber " +
"FROM Production.Product;", sourceConnection);
SqlDataReader reader =
commandSourceData.ExecuteReader();
// Open the destination connection. In the real world you would
// not use SqlBulkCopy to move data from one table to the other
// in the same database. This is for demonstration purposes only.
using (SqlConnection destinationConnection =
new SqlConnection(connectionString))
{
destinationConnection.Open();
// Set up the bulk copy object.
// Note that the column positions in the source
// data reader match the column positions in
// the destination table so there is no need to