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;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

此方法需要传入的两个特殊的参数，一个是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;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

完成上文两部分代码，可以生成一个有表头、有数据的虚拟表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;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

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