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

以下是关于C#中BulkSqlHelper用法的综合说明‌基础封装方法‌通过SqlBulkCopy类实现高效批量插入典型封装包含连接字符串管理、事务支持和错误处理需引用System.Data.SqlClient命名空间‌核心实现步骤public class BulkSqlHelper { private SqlConnection conn; public BulkSqlHelper(SqlConnection con) { this.conn con; } public static void BulkInsertT(SqlConnection conn, string tableName, IListT 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)) .CastPropertyDescriptor() .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 BulkInsertGuidT(SqlConnection conn, string tableName, IListT 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)) .CastPropertyDescriptor() .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, i1); } 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,listModel; BulkSqlHelper.BulkInsertGuidModel((SqlConnection)db.Database.Connection,tableName,listModel); 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(); AddColumnsmodel(dt); foreach (var item in list) { dt.Rows.Add(GetTableInfo(item)); } private void AddColumnsT(DataTable table) { var props TypeDescriptor.GetProperties(typeof(T)) .CastPropertyDescriptor() .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[] GetTableInfoT(T item) { var props TypeDescriptor.GetProperties(typeof(T)).CastPropertyDescriptor().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; }