sqlbulkcopymysql_C#使⽤SqlBulkCopy批量复制数据到数据
表
本⽂实例讲述了C#使⽤SqlBulkCopy批量复制数据到数据表的⽅法。分享给⼤家供⼤家参考。具体实现⽅法如下：
使⽤ SqlBulkCopy 类只能向 SQL Server 表写⼊数据。但是，数据源不限于 SQL Server；可以使⽤任何数据源，只要数据可加载到DataTable 实例或可使⽤ IDataReader 实例读取数据
1.使⽤Datatable作为数据源的⽅式：
下⾯的代码使⽤到了ColumnMappings，因为⽬标表和数据源Datatable的结构不⼀致，需要这么⼀个映射来指定对应关系
public string SaveJHCData(LzShopBasicData[] datas)
{
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");
2011年江苏高考英语JHCOrderItemscopy.ColumnMappings.Add("tradeTime", "tradeTime");
JHCOrderItemscopy.ColumnMappings.Add("uv", "uv");
JHCOrderItemscopy.ColumnMappings.Add("srcId", "srcId");
JHCOrderItemscopy.ColumnMappings.Add("srcName", "srcName");
2012年福建高考数学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.Clo();
return result.ToSerializeObject();
}
2.使⽤IDataReader作为数据源的⽅式，这种⽅式个⼈认为⽤的很少，⾸先⽬标表和来源表两个数据库连接你都需要拿到，如果两个都可以拿到，⼀般直接操作sql就可以解决：
这⾥是直接拷贝的MSDN的代码，
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString = GetConnectionString();
// Open a sourceConnection to the AdventureWorks databa.
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 u SqlBulkCopy to move data from one table to the other // in the same databa. This is for demonstration purpos 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
// map columns.
using (SqlBulkCopy bulkCopy =
new SqlBulkCopy(destinationConnection))
{kindl
bulkCopy.DestinationTableName =
"dbo.BulkCopyDemoMatchingColumns";
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(reader);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
// Clo the SqlDataReader. The SqlBulkCopy怎样练瑜伽
// object is automatically clod at the end
// of the using block.
reader.Clo();
}
}
/
/ Perform a final count on the destination
// table to e how many rows were added.
long countEnd = System.Convert.ToInt32(
commandRowCount.ExecuteScalar());
Console.WriteLine("Ending row count = {0}", countEnd);
Console.WriteLine("{0} rows were added.", countEnd - countStart); Console.WriteLine("Press Enter to finish.");
Console.ReadLine();
}
}
}
private static string GetConnectionString()
// To avoid storing the sourceConnection string in your code,
// you can retrieve it from a configuration file.
{
return "Data Source=(local); " +
" Integrated Security=true;" +
"Initial Catalog=AdventureWorks;";
}
}
实战：借助类型反射动态构建Datatable数据源，通过SqlBulkCopy批量保存⼊库范晓虹
1.获取⼀张空的Datatable：
var dt = bisdal.From(TopBrand._.ID == -1, OrderByClip.Default).ToDataTable();
2.填充DataTable，这⾥是通过遍历外部的集合，把属性属性逐⼀赋值填充到⽬标Datatable
foreach (var item in brandlldataitems)
{
try
{
TopBrand topbrand = new TopBrand
{
BrandIndex = item.mk,
BrandName = item.c58,
Date = date,
WinnerAmt = item.c60,
WinnerPeople = item.c62,
WinnerProNum = item.c61,
HotTaobaoCategoryID = cid
};
CreateDtByItem(topbrand, dt);
}
catch (Exception ex)
{
Logger.Error(ex.ToString());
continue;
}jump是什么意思
}
这⾥借助反射，遍历实体属性集合，动态构建DataTableRow对象
private void CreateDtByItem(T item, DataTable dt)
{
System.Reflection.PropertyInfo[] properties = item.GetType().GetProperties(System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.Public);
var newrow = dt.NewRow();
foreach (System.Reflection.PropertyInfo pitem in properties)
英文字母发音{
string name = pitem.Name;
