SqlBulkCopy批量插入数据

注意点：SqlBulkCopy是根据index来插入数据的，所以要实体类字段顺序和数据库字段顺序一致。也可以用ColumnMapping一一映射。

1、表结构

ID    uniqueidentifier    Unchecked
MonthlyInstitutionID    uniqueidentifier    Unchecked
MonthlyEmployeeID    uniqueidentifier    Unchecked
CredentialTypeName    nvarchar(64)    Unchecked
Credential15    nvarchar(32)    Unchecked
InsuranceDate    datetime    Unchecked
ServiceCharge    decimal(18, 4)    Unchecked
ServiceCode    tinyint    Unchecked
CreateBy    uniqueidentifier    Checked
CreateOn    datetime    Unchecked
UpdateBy    uniqueidentifier    Unchecked
UpdateOn    datetime    Checked

2、实体类

    public class EmployeeServiceCharge
    {
        /// <summary>
        /// 员工ID
        /// </summary>
        public Guid ID { get; set; }
        /// <summary>
        /// 证件类型
        /// </summary>
        public string CredentialTypeName { get; set; }
        /// <summary>
        /// 证件号15号
        /// </summary>
        public string Credential15 { get; set; }
        /// <summary>
        /// 员工服务代码
        /// </summary>
        public int ServiceCode { get; set; }
        /// <summary>
        /// 员工服务名
        /// </summary>
        public string ServiceName { get; set; }
        /// <summary>
        /// 参保月
        /// </summary>
        public DateTime InsuranceDate { get; set; }
        /// <summary>
        /// 是否收费
        /// </summary>
        public int IsCharge { get; set; }
        /// <summary>
        /// 服务费
        /// </summary>
        public decimal ServiceCharge { get; set; }
    }

3、获取DataTable

        private DataTable GetDatble(List<EmployeeServiceCharge> employees, Guid monthlyInstitutionID)
        {
            var dt = new DataTable("MonthlyEmployeeServices");
            dt.Columns.Add("ID", typeof(Guid));
            dt.Columns.Add("MonthlyInstitutionID", typeof(Guid));
            dt.Columns.Add("MonthlyEmployeeID", typeof(Guid));
            dt.Columns.Add("CredentialTypeName", typeof(string));
            dt.Columns.Add("Credential15", typeof(string));
            dt.Columns.Add("InsuranceDate", typeof(DateTime));
            dt.Columns.Add("ServiceCharge", typeof(decimal));
            dt.Columns.Add("ServiceCode", typeof(byte));
            dt.Columns.Add("CreateBy", typeof(Guid));
            dt.Columns.Add("CreateOn", typeof(DateTime));
            dt.Columns.Add("UpdateBy", typeof(Guid));
            dt.Columns.Add("UpdateOn", typeof(DateTime));
            var dr = dt.NewRow();
            foreach (var emp in employees)
            {
                dr = dt.NewRow();
                dr["ID"] = Guid.NewGuid();
                dr["MonthlyInstitutionID"] = monthlyInstitutionID;
                dr["MonthlyEmployeeID"] = emp.ID;
                dr["CredentialTypeName"] = emp.CredentialTypeName;
                dr["Credential15"] = emp.Credential15;
                dr["InsuranceDate"] = emp.InsuranceDate;
                dr["ServiceCharge"] = emp.ServiceCharge;
                dr["ServiceCode"] = Convert.ToByte(emp.ServiceCode);
                dr["CreateBy"] = base.CurUserID.Value;
                dr["CreateOn"] = DateTime.Now;
                dr["UpdateBy"] = base.CurUserID.Value;
                dr["UpdateOn"] = DateTime.Now;
                dt.Rows.Add(dr);
            }
            return dt;
        }

4、执行插入

  var connstr = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
                    using (SqlConnection conn = new SqlConnection(connstr))
                    {
                        conn.Open();
                        using (SqlBulkCopy sbc = new SqlBulkCopy(conn))
                        {
                            sbc.DestinationTableName = "Bill_MonthlyEmployeeService";
                            sbc.WriteToServer(GetDatble(employees, monthlyInstitutionID));
                        }
                    }

5、问题：和EF中的事务不能结合，需要单独一个事务，因为是单独一个sql连接。

解决方案，利用EF的事务创建SqlBulkCopy对象，

        #region Conn
        private DbConnection conn = null;
        /// <summary>
        /// 当前连接
        /// </summary>
        public EntityConnection CurConn
        {
            get { return conn as EntityConnection; }
        }


        /// <summary>
        /// 连接数据库连接
        /// </summary>
        public void Connect()
        {
            if (conn == null)
            {
                conn = ((System.Data.Entity.Infrastructure.IObjectContextAdapter)this).ObjectContext.Connection;
            }
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            else if (conn.State == ConnectionState.Broken)
            {
                conn.Close();
                conn.Open();
            }
        }
        /// <summary>
        /// 断开数据库连接
        /// </summary>
        public void Disconnect()
        {
            if (conn.State != ConnectionState.Closed)
            {
                conn.Dispose();
            }
        }
        #endregion


        #region Tran
        /// <summary>
        /// 当前事务
        /// </summary>
        public EntityTransaction CurTran { get; private set; }

        /// <summary>
        /// 获取 EntityTransaction 相关联的SqlTransaction
        /// </summary>
        public SqlTransaction CurRelSqlTran
        {
            get
            {
                if (CurTran == null) return null;
                return (SqlTransaction)CurTran.GetType()
                    .InvokeMember("StoreTransaction",
                        BindingFlags.FlattenHierarchy | BindingFlags.NonPublic | BindingFlags.InvokeMethod
                        | BindingFlags.Instance | BindingFlags.GetProperty | BindingFlags.NonPublic, null, CurTran, new object[0]);
            }
        }

        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTran(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            Connect();
            if (CurTran == null)
            {
                CurTran = conn.BeginTransaction(isolationLevel) as EntityTransaction;
            }
        }
        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTran()
        {
            if (CurTran != null)
            {
                CurTran.Commit();
                CurTran.Dispose();
                CurTran = null;
            }
        }
        /// <summary>
        /// 回退事务
        /// </summary>
        public virtual void RollBackTran()
        {
            if (CurTran != null)
            {
                CurTran.Rollback();
                CurTran.Dispose();
                CurTran = null;
            }
        }
        #endregion

                    using (SqlBulkCopy sbc = new SqlBulkCopy(base.DBContext.CurRelSqlTran.Connection, SqlBulkCopyOptions.KeepIdentity, base.DBContext.CurRelSqlTran))
                    {
                        sbc.DestinationTableName = "Bill_MonthlyEmployeeService";
                        sbc.WriteToServer(GetDatble(employees, monthlyInstitutionID));
                    }

关于表变量和sqlbulkcopy比较

摘抄自http://blog.csdn.net/htl258/article/details/5696862

下表说明根据插入操作的速度应使用哪种技术。

数据源	服务器逻辑	行数	最佳技术
服务器上带格式的数据文件	直接插入	< 1000	BULK INSERT
服务器上带格式的数据文件	直接插入	> 1000	BULK INSERT
服务器上带格式的数据文件	复杂	< 1000	表值参数
服务器上带格式的数据文件	复杂	> 1000	BULK INSERT
远程客户端进程	直接插入	< 1000	表值参数
远程客户端进程	直接插入	> 1000	BULK INSERT
远程客户端进程	复杂	< 1000	表值参数
远程客户端进程	复杂	> 1000	表值参数

6、听说EF6.0有直接EntityFramework.BulkInsert

var ctx = (this.UnitOfWork as UnitOfWorkContextBase).DbContext;  
    using (var transactionScope = new TransactionScope())  
    {  
        // some stuff in dbcontext  
  
        ctx.BulkInsert(entities2);  
  
        ctx.SaveChanges();  
        transactionScope.Complete();  
    }  

可惜我用的是5.0

7、使用sqlbulkcopy注意点：

①、用EF插入，自动增长键会带回数据库中的值，但是BulkInsert 不会；

②、外键关联表的数据，比如：日志、详情，用EF时，如果有数据会自动插入；用BulkInsert 不会插入，只会插入主表的数据，还要自己根据业务，查询出主表的主键然后赋值给 日志、详情的外键字段，然后我们才可以将这些数据再次插入数据库；

③、考虑事务。

8、公共类

using HRS.Lib.Comm;
using HRS.Lib.Comm.Exceptions;
using HRS.Lib.EFDataModelContext.TEntity;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HRS.Lib.EFDataModelContext
{
    /// <summary>
    /// 快速插入数据
    /// 不考虑自增长字段，外键关联表和日志
    /// 使用需注意事务和延迟加载
    /// </summary>
    public static class SqlBulkCopyUtil
    {
        private static string _connectionString;
        static SqlBulkCopyUtil()
        {
            _connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        }

        public static void ResetConnStr(string conn)
        {
            _connectionString = ConfigurationManager.ConnectionStrings[conn].ConnectionString;
        }

        /// <summary>
        /// 单独开事务
        /// </summary>
        /// <param name="table"></param>
        /// <param name="destinationTableName"></param>
        public static void WriteToServer(DataTable table, string destinationTableName)
        {
            if (table == null || table.Rows.Count == 0) return;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                using (var sqlbulkcopy = new SqlBulkCopy(conn))
                {
                    try
                    {
                        sqlbulkcopy.DestinationTableName = destinationTableName;
                        for (int i = 0; i < table.Columns.Count; i++)
                        {
                            sqlbulkcopy.ColumnMappings.Add(table.Columns[i].ColumnName, table.Columns[i].ColumnName);
                        }
                        sqlbulkcopy.WriteToServer(table);
                    }
                    catch (Exception ex)
                    {
                        throw new ShowInfoException(ex.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 单独开事务
        /// </summary>
        /// <param name="table"></param>
        /// <param name="destinationTableName"></param>
        public static void WriteToServer<T>(IEnumerable<T> list, string destinationTableName) where T : BaseEntity<Guid>, new()
        {
            if (list == null || list.Count() == 0) return;
            T t = new T();
            if (t is ICreateUpdateLogEntity<Guid>) throw new ShowInfoException("缺少创建人相关ID");
            foreach (var item in list)
            {
                t.ID = Guid.NewGuid();
            }
            WriteToServer(list.ToDataTable(), destinationTableName);
        }

        /// <summary>
        /// 单独开事务
        /// </summary>
        /// <param name="table"></param>
        /// <param name="destinationTableName"></param>
        public static void WriteToServer<T>(IEnumerable<T> list, string destinationTableName, Guid createBy) where T : BaseCreateUpdateLogEntity<Guid>
        {
            if (list == null || list.Count() == 0) return;
            foreach (var item in list)
            {
                item.ID = new Guid();
                item.CreateBy = createBy;
                item.CreateOn = DateTime.Now;
                item.UpdateBy = createBy;
                item.UpdateOn = DateTime.Now;
            }
            WriteToServer(list.ToDataTable(), destinationTableName);
        }

        /// <summary>
        /// 包含事务
        /// </summary>
        /// <param name="table"></param>
        /// <param name="destinationTableName"></param>
        public static void WriteToServer(DataTable table, string destinationTableName, SqlTransaction curSqlTran)
        {
            using (var sqlbulkcopy = new SqlBulkCopy(curSqlTran.Connection, SqlBulkCopyOptions.KeepIdentity, curSqlTran))
            {
                try
                {
                    sqlbulkcopy.DestinationTableName = destinationTableName;
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        sqlbulkcopy.ColumnMappings.Add(table.Columns[i].ColumnName, table.Columns[i].ColumnName);
                    }
                    sqlbulkcopy.WriteToServer(table);
                }
                catch (Exception ex)
                {
                    throw new ShowInfoException(ex.Message);
                }
            }
        }

        /// <summary>
        /// 包含事务
        /// </summary>
        /// <param name="table"></param>
        /// <param name="destinationTableName"></param>
        public static void WriteToServer<T>(IEnumerable<T> list, string destinationTableName, SqlTransaction curSqlTran) where T : BaseEntity<Guid>, new()
        {
            if (list == null || list.Count() == 0) return;
            T t = new T();
            if (t is ICreateUpdateLogEntity<Guid>) throw new ShowInfoException("缺少创建人相关ID");
            foreach (var item in list)
            {
                t.ID = Guid.NewGuid();
            }
            WriteToServer(list.ToDataTable(), destinationTableName, curSqlTran);
        }

        /// <summary>
        /// 包含事务
        /// </summary>
        /// <param name="table"></param>
        /// <param name="destinationTableName"></param>
        public static void WriteToServer<T>(IEnumerable<T> list, string destinationTableName, SqlTransaction curSqlTran, Guid createBy) where T : BaseCreateUpdateLogEntity<Guid>
        {
            if (list == null || list.Count() == 0) return;
            foreach (var item in list)
            {
                item.ID = Guid.NewGuid();
                item.CreateBy = createBy;
                item.CreateOn = DateTime.Now;
                item.UpdateBy = createBy;
                item.UpdateOn = DateTime.Now;
            }
            WriteToServer(list.ToDataTable(), destinationTableName, curSqlTran);
        }
    }
}

使用方式

                    var list = employees.Select(p => new MonthlyEmployeeServiceEntity
                    {
                        MonthlyInstitutionID = monthlyInstitutionID,
                        MonthlyEmployeeID = p.ID,
                        CredentialTypeName = p.CredentialTypeName,
                        Credential15 = p.Credential15,
                        InsuranceDate = p.InsuranceDate,
                        ServiceCode = Convert.ToByte(p.ServiceCode),
                        ServiceCharge = p.ServiceCharge
                    }).ToArray();
                    SqlBulkCopyUtil.WriteToServer<MonthlyEmployeeServiceEntity>(list, "Bill_MonthlyEmployeeService", base.DBContext.CurRelSqlTran, base.CurUserID.Value);