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

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();
}