C# 使用 SqlBulkCopy 类批量复制数据到数据库

最近公司需要优化导入的问题，由于之前使用的方式是生成 Insert 语句插入数据库，数据量小的时候还行，但是随着发展数据量渐渐大了，之前的方法性能就跟不上了，于是发现了 SqlBulkCopy 这个类。
使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 DataTable 实例或可使用 IDataReader 实例读取数据。

public class Conn
{
    private static string StrConn
    {
        get
        {
            return ConfigurationManager.ConnectionStrings["StrConn"].ToString();
            //return ConfigurationManager.AppSettings["StrConn"].ToString();
        }
    }
    public static SqlConnection SqlConn
    {
        get
        {
            return new SqlConnection(StrConn);
        }
    }
}
public class SqlHelper
{
    public DataTable GetDataTable(string sql)
    {
        DataTable dt = new DataTable();
        SqlConnection conn = null;
        SqlDataAdapter sda = null;
        try
        {
            conn = Conn.SqlConn;
            sda = new SqlDataAdapter(sql, conn);
            conn.Open();
            sda.Fill(dt);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
            if (sda != null)
            {
                sda.Dispose();
            }
        }
        return dt;
    }
    public DataSet GetDataSet(string sql)
    {
        DataSet ds = new DataSet();
        SqlConnection conn = null;
        SqlDataAdapter sda = null;
        try
        {
            conn = Conn.SqlConn;
            sda = new SqlDataAdapter(sql, conn);
            conn.Open();
            sda.Fill(ds);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
            }
            if (sda != null)
            {
                sda.Dispose();
            }
        }
        return ds;
    }
    /// <summary>
    /// 使用事务插入方法
    /// </summary>
    /// <param name="dt">源数据</param>
    /// <param name="tableName">目标表名</param>
    public void InsertO(DataTable dt, string tableName)
    {
        using (SqlConnection conn = Conn.SqlConn)
        {
            using (SqlBulkCopy sqlBuleCopy = new SqlBulkCopy(conn.ConnectionString, 
                                                             SqlBulkCopyOptions.CheckConstraints 
                                                             | SqlBulkCopyOptions.Default 
                                                             | SqlBulkCopyOptions.UseInternalTransaction))
            {
                try
                {
                    //设置目标表名，即数据库表名
                    sqlBuleCopy.DestinationTableName = tableName;
                    //设置每一批次的行数，即达到指定的行数就插入一次数据库
                    sqlBuleCopy.BatchSize = 100000;
                    //设置超时之前完成的时间（秒）
                    sqlBuleCopy.BulkCopyTimeout = 3600;
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        //设置源数据列与目标表的列的映射关系，第一个参数为源数据列，第二个参数为目标表列
                        sqlBuleCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                    }
                    sqlBuleCopy.WriteToServer(dt);
                }
                catch (Exception)
                {
                }
            }
        }
    }
    /// <summary>
    /// 未使用事务插入方法
    /// </summary>
    /// <param name="dt">源数据</param>
    /// <param name="tableName">目标表名</param>
    public void InsertT(DataTable dt, string tableName)
    {
        using (SqlConnection conn = Conn.SqlConn)
        {
            using (SqlBulkCopy sqlBuleCopy = new SqlBulkCopy(conn))
            {
                try
                {
                    conn.Open();
                    //设置目标表名，即数据库表名
                    sqlBuleCopy.DestinationTableName = tableName;
                    //设置每一批次的行数，即达到指定的行数就插入一次数据库
                    sqlBuleCopy.BatchSize = 100000;
                    //设置超时之前完成的时间（秒）
                    sqlBuleCopy.BulkCopyTimeout = 3600;
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        //设置源数据列与目标表的列的映射关系，第一个参数为源数据列，第二个参数为目标表列
                        sqlBuleCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
                    }
                    sqlBuleCopy.WriteToServer(dt);
                }
                catch (Exception)
                {
                    conn.Close();
                    conn.Dispose();
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
    }
}

我的源数据是使用 Excel 导入的数据，导入的方法就不说了，不是这里的重点，之后我会专门总结一下 Excel 导入的方法。然后查询目标表需要插入数据的字段，修改源数据表的字段名和类型，然后调用批量插入的方法。

protected void btnImport_Click(object sender, EventArgs e)
{
    try
    {
        //获取导入的数据
        DataSet ds = BI.ExecleDs(savePath, "");
        if (ds != null && ds.Tables.Count > 0)
        {
            DataTable dt = ds.Tables[0];
            //查询目标表需要插入的字段
            string sql = " select U_No,U_Name,U_Pwd,P_Id from UserInfo ";
            DataTable dt1 = sqlhelper.GetDataTable(sql);
            if (dt1 != null)
            {
                for (int i = 0; i < dt1.Columns.Count; i++)
                {
                    //修改源数据表的字段类型和字段名称
                    dt.Columns[i].DataType = dt1.Columns[i].DataType;
                    dt.Columns[i].ColumnMapping = dt1.Columns[i].ColumnMapping;
                    dt.Columns[i].ColumnName = dt1.Columns[i].ColumnName;
                }
                sqlhelper.InsertO(dt, "UserInfo");
            }
        }
    }
    catch (Exception ex)
    {
        throw;
    }
}

以上这种修改数据类型的方法，如果碰到数据类型不一致并且 DataTable 有数据的时候，会报错，不能修改有数据的列的数据类型。（好像是废话，嘿嘿，没有数据和有数据时不能修改数据类型，这完全是没用的。）
所以就有了下面的方法，先实例化一个新的 DataTable，然后复制目标表的架构，然后再把数据保存到新的 DataTable 中。

protected void btnImport_Click(object sender, EventArgs e)
{
    try
    {
        //获取导入的数据
        DataSet ds = BI.ExecleDs(savePath, "");
        if (ds != null && ds.Tables.Count > 0)
        {
            DataTable dt = ds.Tables[0];
            //查询目标表需要插入的字段
            string sql = " select U_No,U_Name,U_Pwd,P_Id from UserInfo ";
            DataTable dt1 = sqlhelper.GetDataTable(sql);
            DataTable dt2 = new DataTable();
            if (dt1 != null)
            {
                //复制目标表的架构
                dt2 = dt1.Clone();
                for (int i = 0; i < dt1.Rows.Count; i++)
                {
                    DataRow dr = dt2.NewRow();
                    dr = dt1.Rows[i];
                    dt2.Rows.Add(dr.ItemArray);
                }
                sqlhelper.InsertO(dt2, "UserInfo");
            }
        }
    }
    catch (Exception ex)
    {
        throw;
    }
}

如果源数据表的列和目标表的列的顺序或列名不相同，那就必须使用 ColumnMappings.Add() 方法设置映射关系。