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

Use scalable bulk operations (Bulk Insert, Update, Delete and Merge) and always get the best performance available for your database provider.

• SQL Server 2008+
• SQL Azure
• SQL Compact
• MySQL
• SQLite
• PostgreSQL (Coming soon)
• Oracle (Coming soon)

Operations	1,000 Rows	10,000 Rows	100,000 Rows	1,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 use flexible features 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();


            }
        }
    }
}