SqlBulkCopy简单封装，让批量插入更方便-白红宇

SqlBulkCopy简单封装，让批量插入更方便

阅读量：4628 次

发布时间：2019-06-09

本文共 12112 字，大约阅读时间需要 40 分钟。

关于 SqlServer 批量插入的方式，前段时间也有大神给出了好几种批量插入的方式及对比测试（），估计大家也都明白，最佳的方式就是用 SqlBulkCopy。自从LZ把Chloe.ORM开源以后，有不少园友/群友询问，框架怎么批量插入数据。我的回答是不支持！最后建议他们用 SqlBulkCopy 的方式插入。在我们公司，我对 SqlBulkCopy 封装成了一个 Helper 方法，使得批量插入更加方便，以满足公司内部不少批量插入需求。我也在群里分享了给他们。因为已经有好几位朋友咨询过，所以，我感觉应该还有很多人还没有自己的一个批量插入方法，因此，LZ今儿给大家分享下我封装的这个批量插入方法，希望大家喜欢。

先看看封装后的方法定义：

public static class SqlConnectionExtension{    ///     /// 使用 SqlBulkCopy 向 destinationTableName 表插入数据    /// 
    /// 
     
      必须拥有与目标表所有字段对应属性
         ///     /// 要插入的数据    /// SqlBulkCopy.BatchSize    /// 如果为 null，则使用 TModel 名称作为 destinationTableName    /// SqlBulkCopy.BulkCopyTimeout    /// 要使用的事务    public static void BulkCopy
     
      (this SqlConnection conn, List
      
        modelList, int batchSize, string destinationTableName = null, int? bulkCopyTimeout = null, SqlTransaction externalTransaction = null);}

上面都有详细解释，相信大家一看就会明白，接下来演示下用法及效果：

先创建一个测试的 Users 表：

1   [dbo].[Users](  2 [Id] []  ,  3 [Name] [](100) ,  4 [Gender] [] ,  5 [Age] [] ,  6 [CityId] [] ,  7 [OpTime] [] ,  8   [PK_Users]     9 ( 10 [Id]  11 ) (PAD_INDEX = , STATISTICS_NORECOMPUTE = , IGNORE_DUP_KEY = , ALLOW_ROW_LOCKS = , ALLOW_PAGE_LOCKS = )  [] 12 )  []

然后定义一个与表映射的 Model，记住，由于 SqlBulkCopy 的特性，定义的 Model 必须拥有与表所有的字段对应的属性：

1 public enum Gender  2 {  3     Man = 1,  4     Woman  5 }  6   7 public class User  8 {  9     public Guid Id { get; set; } 10     public string Name { get; set; } 11     public Gender? Gender { get; set; } 12     public int? Age { get; set; } 13     public int? CityId { get; set; } 14     public DateTime? OpTime { get; set; } 15 }

制造些数据，然后就可以直接插入了：

1 List
     
       usersToInsert = new List
      
       ();  2 usersToInsert.Add(new User() { Id = Guid.NewGuid(), Name = "so1", Gender = Gender.Man, Age = 18, CityId = 1, OpTime = DateTime.Now });  3 usersToInsert.Add(new User() { Id = Guid.NewGuid(), Name = "so2", Gender = Gender.Man, Age = 19, CityId = 2, OpTime = DateTime.Now });  4 usersToInsert.Add(new User() { Id = Guid.NewGuid(), Name = "so3", Gender = Gender.Man, Age = 20, CityId = 3, OpTime = DateTime.Now });  5 usersToInsert.Add(new User() { Id = Guid.NewGuid(), Name = "so4", Gender = Gender.Man, Age = 21, CityId = 4, OpTime = DateTime.Now });  6   7 using (SqlConnection conn = new SqlConnection("Data Source = .;Initial Catalog = Chloe;Integrated Security = SSPI;"))  8 {  9     conn.BulkCopy(usersToInsert, 20000, "Users"); 10 }

执行插入后表数据：

很方便吧，定义好 Model，调用 BulkCopy 方法就能插入了。这个方法主要解决了两个问题：1.免去手动构造 DataTable 和向 DataTable 填充数据，要知道，SqlBulkCopy 要求 DataTable 的列必须和表列顺序一致，如果手动构造 DataTable 的话会使代码很难维护；2.不用亲自 new 出 SqlBulkCopy 对象以及手动给 SqlBulkCopy 对象设置各种值，如 DestinationTableName、BulkCopyTimeout、BatchSize 等，用封装的方法，直接传相应的值就好了。接下来贴干货，简单介绍下实现。

先了解 SqlBulkCopy 的定义（部分）：

public sealed class SqlBulkCopy : IDisposable{    public SqlBulkCopy(SqlConnection connection);    public SqlBulkCopy(string connectionString);    public SqlBulkCopy(string connectionString, SqlBulkCopyOptions copyOptions);    public SqlBulkCopy(SqlConnection connection, SqlBulkCopyOptions copyOptions, SqlTransaction externalTransaction);    public int BatchSize { get; set; }    public int BulkCopyTimeout { get; set; }    public SqlBulkCopyColumnMappingCollection ColumnMappings { get; }    public string DestinationTableName { get; set; }    public bool EnableStreaming { get; set; }    public int NotifyAfter { get; set; }    public event SqlRowsCopiedEventHandler SqlRowsCopied;    public void Close();    public void WriteToServer(DataRow[] rows);    public void WriteToServer(DataTable table);    public void WriteToServer(IDataReader reader);    public void WriteToServer(DataTable table, DataRowState rowState);}

我们只需关注 WriteToServer 方法。因为我们的数据源不是数据库或excel，所以我们直接不考虑 WriteToServer(IDataReader reader)。WriteToServer(DataRow[] rows) 直接无视，不多解释，所以我们只需考虑用 WriteToServer(DataTable table) 就行了。开干！

一、构造一个结构严谨的 DataTable。

由于 SqlBulkCopy 要求 DataTable 的列必须和表列顺序一致，并且不能多也不能少，所以，我们首先要创建一个和目标表字段顺序一致的 DataTable，先查出目标表的结构：

static List
     
       GetTableColumns(SqlConnection sourceConn, string tableName){    string sql = string.Format("select * from syscolumns inner join sysobjects on syscolumns.id=sysobjects.id where sysobjects.xtype='U' and sysobjects.name='{0}' order by syscolumns.colid asc", tableName);    List
      
        columns = new List
       
        ();    using (SqlConnection conn = (SqlConnection)((ICloneable)sourceConn).Clone())    {        conn.Open();        using (var reader = conn.ExecuteReader(sql))        {            while (reader.Read())            {                SysColumn column = new SysColumn();                column.Name = reader.GetDbValue("name");                column.ColOrder = reader.GetDbValue("colorder");                columns.Add(column);            }        }        conn.Close();    }    return columns;}

得到基本的表结构 List<SysColumn>，再创建“严格”的 DataTable 对象：

DataTable dt = new DataTable();Type modelType = typeof(TModel);List
     
       columns = GetTableColumns(conn, tableName);List
      
        mappingProps = new List
       
        ();var props = modelType.GetProperties();for (int i = 0; i < columns.Count; i++){    var column = columns[i];    PropertyInfo mappingProp = props.Where(a => a.Name == column.Name).FirstOrDefault();    if (mappingProp == null)        throw new Exception(string.Format("model 类型 '{0}'未定义与表 '{1}' 列名为 '{2}' 映射的属性", modelType.FullName, tableName, column.Name));    mappingProps.Add(mappingProp);    Type dataType = GetUnderlyingType(mappingProp.PropertyType);    if (dataType.IsEnum)        dataType = typeof(int);    dt.Columns.Add(new DataColumn(column.Name, dataType));}

注意，构造 DataColumn 时，要给 Column 设置 DataType，及数据类型。因为如果不指定数据类型，默认是 string 类型，那样会导致将数据发送至数据库时会引起数据转换，会有些许无谓的性能损耗，同时，如果不指定数据类型，导入一些数据类型时可能会失败，比如模型属性是 Guid 类型，导入时会出现类型转换失败异常。

二、利用反射，获取属性值，构造一行一行的 DataRow，填充 DataTable：

foreach (var model in modelList){    DataRow dr = dt.NewRow();    for (int i = 0; i < mappingProps.Count; i++)    {        PropertyInfo prop = mappingProps[i];        object value = prop.GetValue(model);        if (GetUnderlyingType(prop.PropertyType).IsEnum)        {            if (value != null)                value = (int)value;        }        dr[i] = value ?? DBNull.Value;    }    dt.Rows.Add(dr);}

三、一个完整包含数据的 DataTable 对象就创建好了，我们就可以使用 SqlBulkCopy 插入数据了：

public static void BulkCopy
     
      (this SqlConnection conn, List
      
        modelList, int batchSize, string destinationTableName = null, int? bulkCopyTimeout = null, SqlTransaction externalTransaction = null){    bool shouldCloseConnection = false;    if (string.IsNullOrEmpty(destinationTableName))        destinationTableName = typeof(TModel).Name;    DataTable dtToWrite = ToSqlBulkCopyDataTable(modelList, conn, destinationTableName);    SqlBulkCopy sbc = null;    try    {        if (externalTransaction != null)            sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, externalTransaction);        else            sbc = new SqlBulkCopy(conn);        using (sbc)        {            sbc.BatchSize = batchSize;            sbc.DestinationTableName = destinationTableName;            if (bulkCopyTimeout != null)                sbc.BulkCopyTimeout = bulkCopyTimeout.Value;            if (conn.State != ConnectionState.Open)            {                shouldCloseConnection = true;                conn.Open();            }            sbc.WriteToServer(dtToWrite);        }    }    finally    {        if (shouldCloseConnection && conn.State == ConnectionState.Open)            conn.Close();    }}

完事，一个批量插入的 Helper 方法就这么产生了，最终的完整实现如下：

1 public static class SqlConnectionExtension  2 {  3     ///   4     /// 使用 SqlBulkCopy 向 destinationTableName 表插入数据  5     /// 
  6     /// 
      
       必须拥有与目标表所有字段对应属性
        7     ///   8     /// 要插入的数据  9     /// SqlBulkCopy.BatchSize 10     /// 如果为 null，则使用 TModel 名称作为 destinationTableName 11     /// SqlBulkCopy.BulkCopyTimeout 12     /// 要使用的事务 13     public static void BulkCopy
      
       (this SqlConnection conn, List
       
         modelList, int batchSize, string destinationTableName = null, int? bulkCopyTimeout = null, SqlTransaction externalTransaction = null) 14     { 15         bool shouldCloseConnection = false; 16  17         if (string.IsNullOrEmpty(destinationTableName)) 18             destinationTableName = typeof(TModel).Name; 19  20         DataTable dtToWrite = ToSqlBulkCopyDataTable(modelList, conn, destinationTableName); 21  22         SqlBulkCopy sbc = null; 23  24         try 25         { 26             if (externalTransaction != null) 27                 sbc = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, externalTransaction); 28             else 29                 sbc = new SqlBulkCopy(conn); 30  31             using (sbc) 32             { 33                 sbc.BatchSize = batchSize; 34                 sbc.DestinationTableName = destinationTableName; 35  36                 if (bulkCopyTimeout != null) 37                     sbc.BulkCopyTimeout = bulkCopyTimeout.Value; 38  39                 if (conn.State != ConnectionState.Open) 40                 { 41                     shouldCloseConnection = true; 42                     conn.Open(); 43                 } 44  45                 sbc.WriteToServer(dtToWrite); 46             } 47         } 48         finally 49         { 50             if (shouldCloseConnection && conn.State == ConnectionState.Open) 51                 conn.Close(); 52         } 53     } 54  55     public static DataTable ToSqlBulkCopyDataTable
        
         (List
         
           modelList, SqlConnection conn, string tableName) 56     { 57         DataTable dt = new DataTable(); 58  59         Type modelType = typeof(TModel); 60  61         List
          
            columns = GetTableColumns(conn, tableName); 62         List
           
             mappingProps = new List
            
             (); 63 64 var props = modelType.GetProperties(); 65 for (int i = 0; i < columns.Count; i++) 66 { 67 var column = columns[i]; 68 PropertyInfo mappingProp = props.Where(a => a.Name == column.Name).FirstOrDefault(); 69 if (mappingProp == null) 70 throw new Exception(string.Format("model 类型 '{0}'未定义与表 '{1}' 列名为 '{2}' 映射的属性", modelType.FullName, tableName, column.Name)); 71 72 mappingProps.Add(mappingProp); 73 Type dataType = GetUnderlyingType(mappingProp.PropertyType); 74 if (dataType.IsEnum) 75 dataType = typeof(int); 76 dt.Columns.Add(new DataColumn(column.Name, dataType)); 77 } 78 79 foreach (var model in modelList) 80 { 81 DataRow dr = dt.NewRow(); 82 for (int i = 0; i < mappingProps.Count; i++) 83 { 84 PropertyInfo prop = mappingProps[i]; 85 object value = prop.GetValue(model); 86 87 if (GetUnderlyingType(prop.PropertyType).IsEnum) 88 { 89 if (value != null) 90 value = (int)value; 91 } 92 93 dr[i] = value ?? DBNull.Value; 94 } 95 96 dt.Rows.Add(dr); 97 } 98 99 return dt;100 }101 static List
             
               GetTableColumns(SqlConnection sourceConn, string tableName)102 {103 string sql = string.Format("select * from syscolumns inner join sysobjects on syscolumns.id=sysobjects.id where sysobjects.xtype='U' and sysobjects.name='{0}' order by syscolumns.colid asc", tableName);104 105 List
              
                columns = new List
               
                ();106 using (SqlConnection conn = (SqlConnection)((ICloneable)sourceConn).Clone())107 {108 conn.Open();109 using (var reader = conn.ExecuteReader(sql))110 {111 while (reader.Read())112 {113 SysColumn column = new SysColumn();114 column.Name = reader.GetDbValue("name");115 column.ColOrder = reader.GetDbValue("colorder");116 117 columns.Add(column);118 }119 }120 conn.Close();121 }122 123 return columns;124 }125 126 static Type GetUnderlyingType(Type type)127 {128 Type unType = Nullable.GetUnderlyingType(type); ;129 if (unType == null)130 unType = type;131 132 return unType;133 }134 135 class SysColumn136 {137 public string Name { get; set; }138 public int ColOrder { get; set; }139 }140 }141

View Code

代码不多，仅仅150行，大家可以直接拷走拿去用。其中用了反射，估计吃瓜群众可能不淡定了~哈哈，如果你真有大数据插入需求，这点反射消耗相对大数据插入简直九牛一毛，微乎其微，放心好了。

最后，感谢大家阅读至此。如果本文对您有用，还望给个爱心推荐，您的赞赏是我持续分享的动力。也欢迎广大C#同胞入群交流(群号在顶部)，畅谈.NET复兴大计。

转载于:https://www.cnblogs.com/so9527/p/6193154.html

你可能感兴趣的文章