使用事务和SqlBulkCopy批量插入数据

SqlBulkCopy是.NET Framework 2.0新增的类，位于命名空间System.Data.SqlClient下，主要提供把其他数据源的数据有效批量的加载到SQL Server表中的功能。类似与 Microsoft SQL Server 包中名为 bcp 的命令行应用程序。但是使用 SqlBulkCopy 类可以编写托管代码解决方案，性能上优于bcp命令行应用程序，更优于如Insert方式向SQL Server表加载大量数据。SqlBulkCopy可以应用到大批量数据的转移上，而不管数据源是什么。

之前在做winform开发的时候，发现当datagridview数据量比较大的时候，用for循环非常耗时间与性能，通过查阅资料，了解到了SqlBulkCopy这种批量的数据转移工具。

下述代码实现了datagridview的批量数据插入。

如果datagridview的列与数据库中的表结构不能完全对应的话，我们需要先将datagridview数据存放到一个DataTable 中，注意DataTable 中的列需要与即将插入的表的列类型兼容，名称与列顺序完全一样。自增列随便填写一个int类型的值即可，也可不写，如果没给自增列指定值的话，在后续的表映射关系中需要明确指出对应关系。因为我这里是用循环来指定表对应关系的，所以对table的字段有严格的要求，其实也可以与数据库表中的字段名不一样，但这样就需要具体指出表之间的对应关系。

1 DataTable table = new DataTable(); 2 table.Columns.AddRange(new DataColumn[]{  3 new DataColumn("flow_id",typeof(int)),  4 new DataColumn("sheet_no",typeof(string)),  5 new DataColumn("item_no",typeof(string)), 6 new DataColumn("unit_no",typeof(string)), 7 new DataColumn("unit_factor",typeof(string)), 8 new DataColumn("in_price",typeof(string)), 9 new DataColumn("order_qnty",typeof(string)),10 new DataColumn("sub_amount",typeof(string)),11 new DataColumn("real_qty",typeof(string)),12 new DataColumn("tax_rate",typeof(string)),13 new DataColumn("pay_percent",typeof(string)),14 new DataColumn("out_qty",typeof(string))});15 for (int i = 0; i < dt.Rows.Count; i++)16 {17 DataRow r = dt.Rows[i];18 table.Rows.Add(i, sheet_no, r["item_no"], r["unit_no"], r["unit_factor"], r["in_price"], r["order_qnty"], r["sub_amount"], r["real_qty"], r["tax_rate"], r["pay_percent"], r["out_qty"]);19 20 }21 22  23 24 //开始数据保存逻辑25 26 using (SqlConnection conn = new SqlConnection(connectionString))27 {28   conn.Open();29 30   SqlTransaction tran = conn.BeginTransaction();//开启事务31 32  33 34   //在插入数据的同时检查约束，如果发生错误调用sqlbulkTransaction事务35 36   SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, tran);37   bulkCopy.DestinationTableName = "***";//***代表要插入数据的表名38   foreach (DataColumn dc in table.Columns)  //传入上述table39   {40     bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);//将table中的列与数据库表这的列一一对应41   }42 43     try44     {45         bulkCopy.WriteToServer(table);46         tran.Commit();47     }48     catch (Exception ex)49     {50         tran.Rollback();51     }52     finally53     {54         bulkCopy.Close();55         conn.Close();56     }

在将datable bulkcpy到数据库的时候，若没有指定第一列自增ID的话就会将下一列当成自增ID

其实用不着加bulk.ColumnMappings.Add("Name", "LastName");这样映射，只要把自增ID的column加到datatable即可

1 DataTable _DT = new DataTable();2 _DT.Columns.Add("ID", Type.GetType("System.Int32"));3 _DT.Columns.Add("Name", Type.GetType("System.String"));4 5 DataRow dataRow = _DT.NewRow();6 dataRow[0] = 0;7 dataRow[1] = "姓名";8 9 _DT.Rows.Add(dataRow);

 这样自增ID就会自增了

SqlBulkCopy导入数据（自增列保留原值）

1 //自增列重新生成：SqlBulkCopy bc = new SqlBulkCopy(conn) 2 //自增列保留原值：SqlBulkCopy bc = new SqlBulkCopy(conn,SqlBulkCopyOptions.KeepIdentity) 3 using(SqlBulkCopy sbc = new SqlBulkCopy(conn,SqlBulkCopyOptions.KeepIdentity)) 4 { 5     sbc.DestinationTableName = tableName; 6     foreach (string col in colList) 7     { 8         sbc.ColumnMappings.Add(col, col); 9     }10     sbc.BulkCopyTimeout = 0;11     sbc.WriteToServer(dt);12 }