通过SqlBulkCopy类实现高效批量插入

以下是关于C#中BulkSqlHelper用法的综合说明：

‌基础封装方法‌
        通过SqlBulkCopy类实现高效批量插入
        典型封装包含连接字符串管理、事务支持和错误处理
        需引用System.Data.SqlClient命名空间
‌核心实现步骤

        

public class BulkSqlHelper
    {
        private SqlConnection conn;
        public BulkSqlHelper(SqlConnection con)
        {
            this.conn = con;
        }
        public static void BulkInsert<T>(SqlConnection conn, string tableName, IList<T> list)
        {
            try
            {
                using (var bulkCopy = new SqlBulkCopy(conn))
                {
                    bulkCopy.BatchSize = list.Count;
                    bulkCopy.DestinationTableName = tableName;
                    //bulkCopy.NotifyAfter = 500000;
                    //bulkCopy.BulkCopyTimeout = 600000;
                    var table = new DataTable();
                    var props = TypeDescriptor.GetProperties(typeof(T))
                        .Cast<PropertyDescriptor>()
                        .Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
                        .ToArray();
                    props = props.Where(p => p.Name != "ID").ToArray();

                    foreach (var propertyInfo in props)
                    {
                        if (propertyInfo.Name == "ID") continue;
                        bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                        table.Columns.Add(propertyInfo.Name,
                            Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
                    }

                    var values = new object[props.Length];
                    foreach (var item in list)
                    {
                        for (var i = 0; i < values.Length; i++)
                        {
                            values[i] = props[i].GetValue(item);
                        }
                        table.Rows.Add(values);
                    }
                    bulkCopy.WriteToServer(table);
                }
            }
            catch (Exception e)
            {
            }
        }

        public static void BulkInsertGuid<T>(SqlConnection conn, string tableName, IList<T> list)
        {
            try
            {
                using (var bulkCopy = new SqlBulkCopy(conn))
                {
                    bulkCopy.BatchSize = list.Count;
                    bulkCopy.DestinationTableName = tableName;

                    var table = new DataTable();
                    var props = TypeDescriptor.GetProperties(typeof(T))
                        .Cast<PropertyDescriptor>()
                        .Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
                        .ToArray();
                    //props = props.Where(p => p.Name != "ID").ToArray();

                    foreach (var propertyInfo in props)
                    {
                       // if (propertyInfo.Name == "ID") continue;
                        bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                        table.Columns.Add(propertyInfo.Name,
                            Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
                    }

                    var values = new object[props.Length];
                    foreach (var item in list)
                    {
                        for (var i = 0; i < values.Length; i++)
                        {
                            values[i] = props[i].GetValue(item);
                        }
                        table.Rows.Add(values);
                    }

                    bulkCopy.WriteToServer(table);
                }
            }
            catch (Exception e)
            {
            }
        }

        public static string BulkInsertDataTable(SqlConnection conn, DataTable table, string sqlTableName)
        {
            try
            {
                using (var bulkCopy = new SqlBulkCopy(conn))
                {
                    if (table.Rows.Count < 50000)
                    {
                        bulkCopy.BatchSize = table.Rows.Count;
                    }
                    else
                    {
                        bulkCopy.BatchSize = 50000;
                    }
                   
                    //bulkCopy.BulkCopyTimeout = 3600;
                    bulkCopy.DestinationTableName = sqlTableName;
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        bulkCopy.ColumnMappings.Add(table.Columns[i].ColumnName, i+1);
                    }
                    bulkCopy.WriteToServer(table);
                }
                return string.Empty;
            }
            catch (Exception e)
            {
                return $"sqlbulk写入表【{sqlTableName}】错误" + e;
            }
        }
    }

具体调用：

     if (db.Database.Connection.State != ConnectionState.Open)
           {
                            db.Database.Connection.Open(); //打开Connection连接    
          }

 BulkSqlHelper.BulkInsert((SqlConnection)db.Database.Connection, tableName,list<Model>）;
BulkSqlHelper.BulkInsertGuid<Model>((SqlConnection)db.Database.Connection,tableName,list<Model>);
 BulkSqlHelper.BulkInsertDataTable((SqlConnection)db.Database.Connection, DataTable, tableName);
         if (db.Database.Connection.State != ConnectionState.Closed)
       {
                            db.Database.Connection.Close(); //关闭Connection连接    
       }

//其中BulkSqlHelper.BulkInsertDataTable 中数据组合如下：

DataTable dt= new DataTable();
   AddColumns<model>(dt);

 foreach (var item in list)
   {
      dt.Rows.Add(GetTableInfo(item));
   }

 private void AddColumns<T>(DataTable table)
        {
            var props = TypeDescriptor.GetProperties(typeof(T))
                       .Cast<PropertyDescriptor>()
                       .Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
                       .ToArray();
            props = props.Where(p => p.Name != "ID").ToArray();

            foreach (var propertyInfo in props)
            {
                if (propertyInfo.Name == "ID") continue;
                // bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                table.Columns.Add(propertyInfo.Name,
                    Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
            }
        }
        private  object[] GetTableInfo<T>(T item)
        {
            var props = TypeDescriptor.GetProperties(typeof(T)).Cast<PropertyDescriptor>().Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System")).ToArray();
            props = props.Where(p => p.Name != "ID").ToArray();
            var values = new object[props.Length];

            for (var i = 0; i < values.Length; i++)
            {
                values[i] = props[i].GetValue(item);
            }
            return values;
        }