C# SqlBulkCopy实现SQLSRVER批量插入

使用Entity Framework连接SQLSERVER数据库,在实现数据导入功能时,一开始使用AddRange()然后Save()操作,发现数据量一大,效率下降的很厉害。

测试发现,一个只有几列的表,插入上万条数据时,可能需要20s左右,甚至出现卡死。以前只知道EF效率慢,不如第三方的ORM框架,如Drapper,不用不知道,一用吓一跳。

于是,自然地要改用批量插入,随手一查你能够发现EF也有扩展能够较好的解决问题,但是收费的,因此只好尝试微软推荐的SQLSERVER批量插入的方式:SQLBulkCopy

下面就来介绍下如何使用:

先在SQLSERVER中建表:

CREATE TABLE paydetail(
    id int IDENTITY(1,1) primary key,
    pid int NULL,
    test varchar(50) NULL,
    amount decimal(10, 2) NULL,
    paytype int NULL,
    remark varchar(32) NULL,
)

其中id时自增主键,其他的就都比较简单,用来测试即可。

MSDN上使用SqlBulkCopy的方式很简单,建立连接,指定表名,写入数据集合即可。但为了通用性,我们实现一个泛型方法BulkInser<T>(List<T> modelList)。

static void BulkInsert<T>(string connStr, List<T> modelList)
{
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connStr))
    {
        var type = typeof(T);       
        bulkCopy.DestinationTableName = type.Name;
        bulkCopy.WriteToServer(ToDataTable<T>(modelList));
    }
}

WriteToServer()的参数是DataTable,因此我们这里还实现了一个辅助方法,用来实现List<T>到DataTable的转换。

static DataTable ToDataTable<T>(IList<T> data)
{
    PropertyDescriptorCollection props =
        TypeDescriptor.GetProperties(typeof(T));
    DataTable table = new DataTable();
    for (int i = 0; i < props.Count; i++)
    {
        PropertyDescriptor prop = props[i];

        Type colType = prop.PropertyType;
        if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
        {
            colType = colType.GetGenericArguments()[0];
        }

        table.Columns.Add(prop.Name, colType);

    }
    object[] values = new object[props.Count];
    foreach (T item in data)
    {
        for (int i = 0; i < values.Length; i++)
        {
            values[i] = props[i].GetValue(item);
        }
        table.Rows.Add(values);
    }
    return table;
}

以上,如果顺利的话,写入便能成功了。当然如果有时候,数据库表的字段有更新,与实体DataTable的顺序不一致时,会提示类型转换错误,就需要为BulkCopy添加一个ColumnMapping

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connStr))
{
    var type = typeof(T);

    PropertyDescriptorCollection props = TypeDescriptor.GetProperties(type);
    foreach (PropertyDescriptor prop in props)
    {
        bulkCopy.ColumnMappings.Add(prop.Name, prop.Name);
    }

    bulkCopy.DestinationTableName = type.Name;
    bulkCopy.WriteToServer(ToDataTable<T>(modelList));
}

下面就是完整的代码文件:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using EntityFramework.BulkInsert;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
using System.ComponentModel;

namespace batchImportTest
{
    public class Program
    {
        static void Main(string[] args)
        {
            List<paydetail> modelList = new List<paydetail>();
            int count = 1;
            for (int i = 0; i < count; i++)
            {
                paydetail de = new paydetail();
                de.remark = "remark";
                de.pid = 1;
                modelList.Add(de);
            }

            BulkInsert<paydetail>(GetConnectionString(), modelList);

            Console.ReadKey();

        }

        static string GetConnectionString()
        {
            using (var context = new testEntities())
            {
                return context.Database.Connection.ConnectionString;
            }
        }

        static void BulkInsert<T>(string connStr, List<T> modelList)
        {
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connStr))
            {
                var type = typeof(T);

                PropertyDescriptorCollection props = TypeDescriptor.GetProperties(type);
                foreach (PropertyDescriptor prop in props)
                {
                    bulkCopy.ColumnMappings.Add(prop.Name, prop.Name);
                }

                bulkCopy.DestinationTableName = type.Name;
                var sw = System.Diagnostics.Stopwatch.StartNew();
                bulkCopy.WriteToServer(ToDataTable<T>(modelList));
                Console.WriteLine(string.Format("BulkCopy: {0}ms", sw.ElapsedMilliseconds));
            }
        }

        static DataTable ToDataTable<T>(IList<T> data)
        {
            PropertyDescriptorCollection props =
                TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();
            for (int i = 0; i < props.Count; i++)
            {
                PropertyDescriptor prop = props[i];

                Type colType = prop.PropertyType;
                if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                {
                    colType = colType.GetGenericArguments()[0];
                }

                table.Columns.Add(prop.Name, colType);

            }
            object[] values = new object[props.Count];
            foreach (T item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(item);
                }
                table.Rows.Add(values);
            }
            return table;
        }
    }
}

改变Count的值进行测试,发现10W条数据,插入基本在1s左右,比EF Save的性能简直天壤之别。

参考:

SqlBulkCopy

List<T> to DataTable

标签: SQLSERVER, 批量插入, SQLBulkcopy

添加新评论