C# Bulk Operations(转)


转自http://blog.csdn.net/winnyrain/article/details/51240684

Overcome SqlBulkCopy Limitations with C# Bulk Insw3school.com.cnert, Update, Delete and Merge

// Support all type of operations
var bulk = new BulkOperation(connection);
bulk.BulkInsert(dt);
bulk.BulkUpdate(dt);
bulk.BulkDelete(dt);
bulk.BulkMerge(dt);

// Support List<T> and Lambda Mapping
var bulk = new BulkOperation<Customer>(connection);
bulk.ColumnInputExpression = c => new { c.Name,  c.FirstName };
bulk.ColumnOutputExpression = c => c.CustomerID;
bulk.ColumnPrimaryKeyExpression = c => c.Code;
bulk.BulkMerge(customers);

High Performance Operations

Operations1,000 Rows10,000 Rows100,000 Rows1,000,000 Rows
Insert 6 ms 25 ms 200 ms 2,000 ms
Update 50 ms 80 ms 575 ms 6,500 ms
Delete 45 ms 70 ms 625 ms 6,800 ms
Merge 65 ms 160 ms 1,200 ms 12,000 ms

* Benchmark for SQL Server

Output Identity Value

Overcome SqlBulkCopy limitations and useflexiblefeatures to output inserted identity and concurrency column values.

// Output newly inserted identity value after an insert
bulk.ColumnMappings.Add("CustomerID", ColumnMappingDirectionType.Output);

bulk.BulkInsert(dt);
// Support all type of operations
var bulk = new BulkOperation(connection);
bulk.BulkInsert(dt);
bulk.BulkUpdate(dt);
bulk.BulkDelete(dt);
bulk.BulkMerge(dt);
bulk.BulkSaveChanges(ds);
bulk.BulkSynchronize(dt);
Reference:
http://bulk-operations.net/
http://www.zzzprojects.com/

Examples:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Diagnostics;
using Z.BulkOperations;
using Z.Data.SqlClient;


namespace BulkTest
{
  class Program
  {
    static void Main(string[] args)
    {
      DataTable dt = new DataTable();
      dt.TableName = "OBDData";


      DataColumn column = new DataColumn("serviceid", typeof(long));
      column.AutoIncrement = true;
      dt.Columns.Add(column);
      dt.Columns.Add(new DataColumn("gpstime", typeof(DateTime)));
      dt.Columns.Add(new DataColumn("lat", typeof(Decimal)));
      dt.Columns.Add(new DataColumn("lng", typeof(Decimal)));
      dt.Columns.Add(new DataColumn("speed", typeof(Decimal)));
      dt.Columns.Add(new DataColumn("altitude", typeof(int)));


      for (int d = 0; d < 100000; d++)
      {
       dt.Rows.Add(new object[] { null, DateTime.Now, d % 100, (d + 2) / (d + 1), (d + 3) / (d + 1), (d + 4) % 8 });
      }
      string ConnectionString = @"server=192.168.20.115\MSSQLSERVER2008;database=GPSTest;uid=test;pwd=test";


      Stopwatch sw = new Stopwatch();
      using (DbConnection connection = new SqlConnection(ConnectionString))
      {


        connection.Open();
        sw.Start();
        var bulk = new BulkOperation(connection);
        //bulk.BulkInsert(dt);
        bulk.BulkUpdate(dt);
        //bulk.BulkDelete(dt);
        //bulk.BulkMerge(dt);
        //bulk.BulkSaveChanges(ds);
        //bulk.BulkSynchronize(dt);
        sw.Stop();
        Console.WriteLine("用时:" + sw.ElapsedMilliseconds.ToString());
        Console.Read();


      }
    }
  }
}
优质内容筛选与推荐>>
1、matlab练习程序(弧形、圆柱投影的复原)
2、模拟105 题解
3、.net core中使用GB2312编码的问题
4、十二、Angular 路由
5、随心所欲SOA


长按二维码向我转账

受苹果公司新规定影响,微信 iOS 版的赞赏功能被关闭,可通过二维码转账支持公众号。

    阅读
    好看
    已推荐到看一看
    你的朋友可以在“发现”-“看一看”看到你认为好看的文章。
    已取消,“好看”想法已同步删除
    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字,当前共

    发送中

    网络异常,请稍后重试

    微信扫一扫
    关注该公众号





    联系我们

    欢迎来到TinyMind。

    关于TinyMind的内容或商务合作、网站建议,举报不良信息等均可联系我们。

    TinyMind客服邮箱:support@tinymind.net.cn