ASP.NET(C#) 使用 SqlBulkCopy 实现批量插入SQL（快捷简单）

业务需要，系统在处理数据时，每暂存一列数据将他插入到右侧的表格中，再执行批量保存，如图所示：

//以前的做法可能是生成一堆 insert into xx values xxx 的sql语句，在程序中去一次执行来实现。这种做法在数据量大的情况下，简直不要太慢！不建议使用

//在.NET里可以使用SqlBulkCopy来一次性插入多条数据，我们只需要使用WriteToServer给它传递一个DataTable类型，他就会自动实现批量插入

代码如下，我的代码需要处理一些数据，如果看起来麻烦请看代码下方的Demo：

        protected void Button5_Click1(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            CJJLModel model = new CJJLModel();
            //前台获取到的table所有数据转为List
            model.CjjlList = Newtonsoft.Json.JsonConvert.DeserializeObject<cjjLists>(this.HidValue.Value);
            Tool.SystemPage UserDal = new Tool.SystemPage();
            string UserData = UserDal.GetPageUserData();
            string[] ArrUser = UserData.Split(',');
            for (int i = 0; i < model.CjjlList.Count; i++)
            {
                model.CjjlList[i].Creater = ArrUser[0];
                model.CjjlList[i].CreateData = DateTime.Now;
                model.CjjlList[i].CreateTime = DateTime.Now;
            }
            //list转DataTable
            dt =  ToDataTable(model.CjjlList);
            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(SqlHelper.SCConnString))
            {
                bulkCopy.DestinationTableName = "SC_CJ_CJJL";   //指定要插入的目标表

                //DataTable列名与数据库列名的映射
                bulkCopy.ColumnMappings.Add("ID", "ID");
                bulkCopy.ColumnMappings.Add("Location", "Location");
                bulkCopy.ColumnMappings.Add("MLDM", "MLDM");
                bulkCopy.ColumnMappings.Add("MLMC", "MLMC");
                bulkCopy.ColumnMappings.Add("GGMC", "GGMC");
                bulkCopy.ColumnMappings.Add("XDL", "XDL");
                bulkCopy.ColumnMappings.Add("SJHL", "SJHL");
                bulkCopy.ColumnMappings.Add("ZPPS", "ZPPS");
                bulkCopy.ColumnMappings.Add("TMLKS", "TMLKS");
                bulkCopy.ColumnMappings.Add("SCDH", "SCDH");
                bulkCopy.ColumnMappings.Add("CHS", "CHS");
                bulkCopy.ColumnMappings.Add("BZ", "BZ");
                bulkCopy.ColumnMappings.Add("QWID", "QWID");
                bulkCopy.ColumnMappings.Add("ZCID", "ZCID");
                bulkCopy.ColumnMappings.Add("MRBBID", "MRBBID");
                bulkCopy.ColumnMappings.Add("KH", "KH");
                bulkCopy.ColumnMappings.Add("FDCS", "FDCS");
                bulkCopy.ColumnMappings.Add("YSDM", "YSDM");
                bulkCopy.ColumnMappings.Add("SYBW", "SYBW");
                bulkCopy.ColumnMappings.Add("CreateData", "CreateData");
                bulkCopy.ColumnMappings.Add("Creater", "Creater");
                bulkCopy.ColumnMappings.Add("CreateTime", "CreateTime");

                bulkCopy.WriteToServer(dt);//写入到数据库中
            }
            Response.Write("<script>alert('保存成功');location.href='CJJLList.aspx';</script>");
        }

Demo示例代码：

protected void Button5_Click(object sender, EventArgs e)
{
    //准备要批量插入的数据
    DataTable table = new DataTable();
    table.Columns.Add("Age");
    table.Columns.Add("Name");

    //添加测试数据
    DataRow row = table.NewRow();
    row["Age"] = 28;
    row["Name"] = "张三";
    table.Rows.Add(row);

    DataRow row1 = table.NewRow();
    row1["Age"] = 29;
    row1["Name"] = "李四";
    table.Rows.Add(row1);


    string connectionStr = "数据连接字符串";
    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionStr))
    {
        bulkCopy.DestinationTableName = "Person";//在插入的目标表
                                                 //DataTable列名与数据库列名的映射
        bulkCopy.ColumnMappings.Add("Age", "Age");
        bulkCopy.ColumnMappings.Add("Name", "Name");
        bulkCopy.WriteToServer(table);//写入到数据库中
    }
    Response.Write("YES");
}

到此结束，谢谢各位~