SqlBulkCopy批量插入数据

需求：读取传入的DataSet，将DataSet里的DataTable插入数据库

分析：

1.数据量大，不推荐循环sql insert

2.多表操作，事务

  /// <summary>
        /// 添加数据
        /// </summary>
        /// <returns></returns>
        public static void AddTaxInfo(DataSet infoSet)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                SqlTransaction sqlbulkTransaction = conn.BeginTransaction();

                try
                {
                    //插入主表
                    using (var bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction))
                    {
                        var table_Main = infoSet.Tables["Core_TaxMain"];
                        foreach (DataColumn dc in table_Main.Columns)
                        {
                            bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
                        }
                        bulkCopy.DestinationTableName = "Core_TaxMain";
                        //复制Table
                        bulkCopy.WriteToServer(table_Main);
                    }
                    //插入系表
                    using (var bulkCopy_ = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction))
                    {
                        var table_Detail = infoSet.Tables["Core_TaxDetail"];
                        foreach (DataColumn dc in table_Detail.Columns)
                        {
                            bulkCopy_.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
                        }
                        bulkCopy_.DestinationTableName = "Core_TaxDetail";

                        bulkCopy_.WriteToServer(table_Detail);
                    }
                    //一个事务
                    sqlbulkTransaction.Commit();
                }
                catch (Exception)
                {
                    sqlbulkTransaction.Rollback();
                    throw;
                }
            }
        }

改进：上面的方法完全能够满足需求，只是创建bulkcopy对象显得重复，于是把数据库连接对象封装了一下。

封装好的类将写在另一篇博客里自己写的数据库工具类，这里直接调用：

public static void AddTaxInfo(DataSet infoSet)
        {
            var table_Main = infoSet.Tables["Core_TaxMain"];//主表
            var table_Detail = infoSet.Tables["Core_TaxDetail"];//系表
            using (SqlAccess client = new SqlAccess(_connectionString))//SqlAcess是封装类，类似SqlHelper
            {
                client.BeginTransaction();

                foreach (DataColumn dc in table_Main.Columns)
                {
                    client.BulkCopyColumnMapping.Add(dc.ColumnName, dc.ColumnName);
                }

                client.SetBulkCopy("a").WriteToServer(table_Main);

                foreach (DataColumn dc in table_Detail.Columns)
                {
                    client.BulkCopyColumnMapping.Add(dc.ColumnName, dc.ColumnName);
                }

                client.SetBulkCopy("b").WriteToServer(table_Detail);

                client.Commit();
            }