C#使用SqlBulkCopy批量复制数据到数据表
本文实例讲述了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();
}
2.使用idatareader作为数据源的方式，这种方式个人认为用的很少，首先目标表和来源表两个数据库连接你都需要拿到，如果两个都可以拿到，一般直接操作sql就可以解决：
这里是直接拷贝的msdn的代码，
用到的adventureworks数据库可以直接在网上下载到，下载地址如下：
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
// map columns.
using (sqlbulkcopy bulkcopy =
new sqlbulkcopy(destinationconnection))
{
bulkcopy.destinationtablename =
"dbo.bulkcopydemomatchingcolumns";
try
{
// write from the source to the destination.
bulkcopy.writetoserver(reader);
}
catch (exception ex)
{
console.writeline(ex.message);
}
finally
{
// close the sqldatareader. the sqlbulkcopy
// object is automatically closed at the end
// of the using block.
reader.close();
}
}
// perform a final count on the destination
// table to see 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：
2.填充datatable，这里是通过遍历外部的集合，把属性属性逐一赋值填充到目标datatable
{
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>(topbrand, dt);
}
catch (exception ex)
{
logger.error(ex.tostring());
continue;
}
}
这里借助反射，遍历实体属性集合，动态构建datatablerow对象
{
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;
if (name == "children")
{
continue;
}
object value = pitem.getvalue(item, null);
newrow[name] = value == null ? dbnull.value : value;
}
dt.rows.add(newrow);
}
3.保存入库：
这里因为目标表和数据源的datatable数据结构一致，所以省去了columnmappings列映射的操作，可以直接writetoserver保存
{
try
{
if (con.state == connectionstate.closed)
{
con.open();
}
sqlbulkcopy topbranddtcopy = new sqlbulkcopy(con);
topbranddtcopy.destinationtablename = destinationtablename;
topbranddtcopy.writetoserver(sourcedt);
con.close();
}
catch (exception ex)
{
logger.error("批量新增数据:" + destinationtablename + "," + ex.tostring());
}
}
完整调用代码：
{
try
{
var dt = bisdal.from<topbrand>(topbrand._.id == -1, orderbyclip.default).todatatable();
foreach (var item in brandselldataitems)
{
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>(topbrand, dt);
}
catch (exception ex)
{
logger.error(ex.tostring());
continue;
}
}
bulkwritetoserver(con, "topbrand", dt);
}
catch (exception ex)
{
throw new exception("createtopbranddata:" + ex.tostring());
}
}
希望本文所述对大家的c#程序设计有所帮助。