程序中批处理增删改的一些建议

      刚进一家公司，还在熟悉他们的代码，看到他们经常在for循环中执行数据的插入，修改以及删除操作，接触到Entity Framework时候，它里面有个SavaChange()方法，EF里面的执行数据库操作只有通过调用该方法才会真正的到数据库中执行。所以从中得到启发：我们也可以将Insert/Update/Delete操作批量进行执行，减少数据库访问次数，提高执行效率。

　　首先附上SqlHelper部分代码

       private readonly static string conStr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;

        public static int ExecuteNonQuery(string sql, CommandType type, params SqlParameter[] pars)
        {
            using (SqlConnection con = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.CommandType = type;
                    if (pars != null && pars.Length > 0)
                    {
                        cmd.Parameters.AddRange(pars);
                    }
                    con.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }

 

像数据库中重复插入十万条数据

 class Program
    {
        static void Main(string[] args)
        {
            Stopwatch watch = new Stopwatch();
            watch.Start();
          
            #region 重复访问数据库插入
            for (int i = 0; i < 100000; i++)
            {
                string sql = "insert into TPerson values(@Name,@Age)";
                SqlParameter[] pams = new SqlParameter[]{
                    new SqlParameter("@Name","sj"+i),
                    new SqlParameter("@Age",i)
                };
                SqlHelper.ExecuteNonQuery(sql, System.Data.CommandType.Text, pams);
            }
            #endregion
          
            watch.Stop();
            Console.WriteLine("所执行时间：" + watch.ElapsedMilliseconds);
            Console.Read();
        }
    }

所执行时间：

 

我们可以通过查看SQL Server Profier

他在数据库中实际执行了四十万条指令，大概算了下大约每秒能插入300到400条数据

 

通过循环拼sql，多次批量插入数据库执行

 class Program
    {
        static void Main(string[] args)
        {
            Stopwatch watch = new Stopwatch();
            watch.Start();
            #region 拼sql语句多次执行
            for (int i = 0; i < 1000; i++)
            {
                StringBuilder sb = new StringBuilder();
                List<SqlParameter> parameterList = new List<SqlParameter>();
                for (int j = 0; j < 100; j++)
                {
                    sb.Append(string.Format(" insert into TPerson values(@Name{0},@Age{0}); ", j));
                    parameterList.Add(new SqlParameter("@Name" + j, "sj" + j));
                    parameterList.Add(new SqlParameter("@Age" + j, j));

                }
                SqlHelper.ExecuteNonQuery(sb.ToString(), System.Data.CommandType.Text, parameterList.ToArray());
            }
            #endregion
            watch.Stop();
            Console.WriteLine("所执行时间：" + watch.ElapsedMilliseconds);
            Console.Read();
        }
    }

执行的效果：

同样我们查看SQL Server Profier

执行了大概4000条指令，算了下大概每秒可以插入1500条数据

当然不是内部循环越多就越好的（对应的就是代码中的j越大），SqlParameter支持的参数最多2100个，大概j=200时候效率相对来说会更高点，当然可能是由于参数制约吧，有兴趣的朋友可以试试。

如果i=500,j=200时候，效果如下：

总结

     通过上述实验，我们可以发现当出现大量操作数据库（insert/update/delete）时候，我们可以不再for循环中直接访问数据库，操作执行。我们可以通过循环一定次数，拼装sql，以及SqlParameter，这样执行效率将大大提高。我有一个想法，我们可以自己开发一个组件，这个组件功能是一个队列，用于存放这些sql语句，我们写的sql语句（Insert/Update/Delete）都放到该队列中，该队列每隔段时间进行数据库访问。不过这样带来的问题是如何将队列中执行后的结果返回到调用的地方。

　　后来听同事提到SqlBulkCopy这个类可以批量进行数据插入，网上搜索下总结下他的功能：单独批量复制操作，可将数据从一个数据源移动到SQL Server表中。也可执行多个批量复制操作。在数据库事务中可执行批量复制操作。所以还是有一些局限性的。希望能够得到大家的看法，谢谢。