【ADO.NET】SqlBulkCopy批量添加DataTable

使用事务和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的字段有严格的要求，其实也可以与数据库表中的字段名不一样，但这样就需要具体指出表之间的对应关系。

DataTable table = new DataTable();
table.Columns.AddRange(new DataColumn[]{ 
new DataColumn("flow_id",typeof(int)), 
new DataColumn("sheet_no",typeof(string)), 
new DataColumn("item_no",typeof(string)),
new DataColumn("unit_no",typeof(string)),
new DataColumn("unit_factor",typeof(string)),
new DataColumn("in_price",typeof(string)),
new DataColumn("order_qnty",typeof(string)),
new DataColumn("sub_amount",typeof(string)),
new DataColumn("real_qty",typeof(string)),
new DataColumn("tax_rate",typeof(string)),
new DataColumn("pay_percent",typeof(string)),
new DataColumn("out_qty",typeof(string))});
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow r = dt.Rows[i];
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"]);

}

 

//开始数据保存逻辑

using (SqlConnection conn = new SqlConnection(connectionString))
{
  conn.Open();

  SqlTransaction tran = conn.BeginTransaction();//开启事务

 

  //在插入数据的同时检查约束，如果发生错误调用sqlbulkTransaction事务

  SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, tran);
  bulkCopy.DestinationTableName = "***";//***代表要插入数据的表名
  foreach (DataColumn dc in table.Columns)  //传入上述table
  {
    bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);//将table中的列与数据库表这的列一一对应
  }

    try
    {
        bulkCopy.WriteToServer(table);
        tran.Commit();
    }
    catch (Exception ex)
    {
        tran.Rollback();
    }
    finally
    {
        bulkCopy.Close();
        conn.Close();
    }

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

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

DataTable _DT = new DataTable();
_DT.Columns.Add("ID", Type.GetType("System.Int32"));
_DT.Columns.Add("Name", Type.GetType("System.String"));

DataRow dataRow = _DT.NewRow();
dataRow[0] = 0;
dataRow[1] = "姓名";

_DT.Rows.Add(dataRow);

 这样自增ID就会自增了

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

//自增列重新生成：SqlBulkCopy bc = new SqlBulkCopy(conn)
//自增列保留原值：SqlBulkCopy bc = new SqlBulkCopy(conn,SqlBulkCopyOptions.KeepIdentity)
using(SqlBulkCopy sbc = new SqlBulkCopy(conn,SqlBulkCopyOptions.KeepIdentity))
{
    sbc.DestinationTableName = tableName;
    foreach (string col in colList)
    {
        sbc.ColumnMappings.Add(col, col);
    }
    sbc.BulkCopyTimeout = 0;
    sbc.WriteToServer(dt);
}