SqlServer批量插入数据方法--SqlBulkCopy

最近项目需要插入大量数据，想了一些方法，但是都是低效率、很危险的方法，又在网上找了一些解决方案，自己总结一下，于是有了此文。
1.一条Insert多次请求：sqlcmd.ExecuteNoQuery封装的方法多次调用。
短时间内请求多次，创建多个数据库连接，造成应用连接池堵塞、数据重复等问题。一旦数据量大，对服务器和数据库造成不可想象来说，简直就是灾难，首先排除。

2.多条Insert一次请求：insert into table1 values();insert into table1 values()。
虽然只有一次请求，只有一个数据库连接，但是分条插入会严重拖慢速度，性能也会受到返回计数的影响。对服务器要求很高，要不断的拼接insert语句，也不可取。

3.一条Insert一次请求：insert into table1 values(),(),(),()。SqlServer 2008开始支持
虽然只有一次请求，只有一个数据库连接，也只有一个返回计数，但是因为插入原理和方法2一样，所以效率也会很慢。

4.SqlBulkCopy：.net 2.0时代开始支持，所以SqlServer 2005 完全支持此方法。
推荐此方法，因为它的效率较之前的3个方法提高了5倍左右，且资源占用的不多，原理是先将数据放入内存中的虚拟表DataTable中，再将整张表放入数据库的数据表。使用方法在下文说明。

5.表值参数：SqlServer 2008开始支持表值参数，技术难度较高，因为时间紧迫，没有做过多了解。待日后了解并实现、测试后再做更新。

这里我只用了SqlBulkCopy的一种用法，还有其他用法待日后用到时再做更新，例如：新、旧数据库数据迁移。
这里我给SqlBulkCopy的数据源是DataTable，然而因为数据源的数据不是从别的数据表中读出的，而是json传递的，所以在插入数据之前要先做一个操作，就是生成DataTable。

下面是代码

1.生成DataTable

下面是生成DataTable的公用代码，虽然不是最高效的，但是是我能想到的写法：

/// <summary>/// 根据不同的字段名、数据类型、字段值生成DataTable/// </summary>/// <param name="tblName">要插入的数据库中的表名</param>/// <param name="identity">第一列是否为自增长(一般设计表时都有一个自增长的ID标识列)</param>/// <param name="seed">自增长起始值</param>/// <param name="step">增长长度</param>/// <param name="cellNameType">每一列的名称和数据类型</param>/// <param name="valueList">每一列的名称和值</param>/// <returns></returns>public static DataTable GetDataTable(string tblName, bool identity, int seed, int step, Dictionary<string, string> cellNameType, List<Dictionary<string, string>> valueList){    //这个数据表的名字要和数据库中一致    DataTable dt = new DataTable(tblName);    DataColumn dc = null;    foreach (var item in cellNameType)    {        //根据字典添加列        dc = dt.Columns.Add(item.Key, Type.GetType(cellNameType[item.Key]));    }    //如果第一列是自增长标识列    if (identity)    {        dt.Columns[0].AutoIncrement = identity;//设置为自动增加        dt.Columns[0].AutoIncrementSeed = seed;//设置起始为1        dt.Columns[0].AutoIncrementStep = step;//设置步长为1        dt.Columns[0].AllowDBNull = false;     //设置不允许为空    }    DataRow dr;    foreach (Dictionary<string, string> dic in valueList)    {        dr = dt.NewRow();//新增一行        foreach (var item in dic)        {            //对应列名给列赋值            dr[item.Key] = dic[item.Key];        }        dt.Rows.Add(dr);    }    return dt;}

此方法需要传入的两个特殊的参数，一个是key和value都是string类型的Dictionary字典，key表示列名，value表示数据类型；另一个是字典类型的泛型集合List，这个集合中的字典的key和value也是string类型，key表示列名，value表示列值。在使用此方法时要根据具体数据对这两个参数进行赋值。

2.根据json数据串组织所需GetDataTable()所需参数。

public bool AddList(PayrollModel model, string orderNum){    DataTable dt = new DataTable();    //构造 列名-数据类型 字典    Dictionary<string, string> columns = new Dictionary<string, string>();    columns.Add("salaryId", "System.Int32");    columns.Add("applyId", "System.Int32");    columns.Add("jobId", "System.Int32");    columns.Add("c_userId", "System.Int32");    columns.Add("s_userId", "System.Int32");    columns.Add("orderNum", "System.String");    columns.Add("salaryMoney", "System.Decimal");    columns.Add("giveTime", "System.DateTime");    columns.Add("jobDate", "System.DateTime");    columns.Add("giveType", "System.Int32");    columns.Add("orderState", "System.Int32");    List<Dictionary<string, string>> valueList = new List<Dictionary<string, string>>();    Dictionary<string, string> cellValue;    //以下是我根据业务需要构造 列名-列值 字典的泛型集合，这里的列名、顺序要和上文中的列名一致    foreach (SalaryStudentsModel stu in model.students)    {        foreach (SalaryJobDetailsModel job in stu.jobDetails)        {            cellValue = new Dictionary<string, string>();            cellValue.Add("applyId", job.applyId.ToString());            cellValue.Add("jobId", model.jobId.ToString());            cellValue.Add("c_userId", model.c_userId.ToString());            cellValue.Add("s_userId", stu.s_userId.ToString());            cellValue.Add("orderNum", orderNum);            cellValue.Add("salaryMoney", job.salaryMoney.ToString());            cellValue.Add("giveTime", System.DateTime.Now.ToString());            cellValue.Add("jobDate", job.jobDate.ToString());            cellValue.Add("giveType", "0");            cellValue.Add("orderState", "0");            valueList.Add(cellValue);        }    }    dt = DataTableHandler.GetDataTable("dt_salary", true, 1, 1, columns, valueList);    bool flag = false;    flag = DataTableToServer(dt, "dt_salary");    return flag;}

完成上文两部分代码，可以生成一个有表头、有数据的虚拟表DataTable，如果数据量巨大，要考虑优化这两部分代码和优化服务器硬件。

3.将DataTable的数据插入数据库

public bool DataTableToServer(DataTable dt, string tblName){    using (SqlConnection con = new SqlConnection(DbHelperSQL.connectionString))    {        con.Open();        SqlBulkCopy bulkCopy = new SqlBulkCopy(con);        bulkCopy.DestinationTableName = tblName;//要插入的表名，要和数据库中一致        bulkCopy.BatchSize = dt.Rows.Count;//设置提交一次数据要多少行，数据量巨大时可分批提交        bool flag = false;        try        {            if (dt != null && dt.Rows.Count != 0)            {                bulkCopy.WriteToServer(dt);//写入数据库                flag = true;            }        }        catch (Exception ex)        {            flag = false;            throw ex;        }        finally        {            dt = null;            con.Close();            if (bulkCopy != null)                bulkCopy.Close();        }        return flag;    }}

此时已将大量数据一次性的放入数据库中，之后就是业务处理，完事儿。