欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

C#使用SqlBulkCopy批量复制数据到数据表

程序员文章站 2023-12-17 23:57:22
本文实例讲述了c#使用sqlbulkcopy批量复制数据到数据表的方法。分享给大家供大家参考。具体实现方法如下: 使用 sqlbulkcopy 类只能向 sql serv...

本文实例讲述了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");
  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数据库可以直接在网上下载到,下载地址如下:

复制代码 代码如下:
using system.data.sqlclient;

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:

复制代码 代码如下:
var dt = bisdal.from<topbrand>(topbrand._.id == -1, orderbyclip.default).todatatable();

2.填充datatable,这里是通过遍历外部的集合,把属性属性逐一赋值填充到目标datatable

复制代码 代码如下:
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;
 }
}

这里借助反射,遍历实体属性集合,动态构建datatablerow对象

复制代码 代码如下:
private void createdtbyitem<t>(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;
 if (name == "children")
 {
     continue;
 }
 object value = pitem.getvalue(item, null);
 newrow[name] = value == null ? dbnull.value : value;
    }
    dt.rows.add(newrow);
}

3.保存入库:

复制代码 代码如下:
bulkwritetoserver(con, "topbrand", dt);

这里因为目标表和数据源的datatable数据结构一致,所以省去了columnmappings列映射的操作,可以直接writetoserver保存

复制代码 代码如下:
private void bulkwritetoserver(sqlconnection con, string destinationtablename, datatable sourcedt)
{
    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());
    }
}

完整调用代码:

复制代码 代码如下:
private void createtopbranddata(int date, int cid, list<brandselldataitem> brandselldataitems)
{
    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#程序设计有所帮助。

上一篇:

下一篇: