几种批量插入数据方式的比较

1 直接循环insert ：这样效率是最慢的 经过测试测试程序 插入10w条数据需要 5分钟多

2 拼接sql 语句 再一次执行 ：这样效率有明显提升 只需要40多s

3 使用 sqlbullcopy ：先把要插入的数据 组装成 datatable 再copy 到数据库 效率最佳 只要5s

以下 测试 代码

主程序：

class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("start");
            Fun1();
            Console.WriteLine("end");
            Console.WriteLine("SHEEP ...");
            Thread.Sleep(3000);
            Fun2();
            Console.WriteLine("end");
            Console.WriteLine("SHEEP ...");
            Fun3();
            Console.ReadKey();
        }
        /// <summary>
        /// recyle insert 
        /// </summary>
        static void Fun1()
        {
            Console.WriteLine("recyle start:"+ DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            for (int i = 0; i < 100000; i++)
            {
               
                string commandText = "insert into GoodsTB(goodsid,goodsname,goodsprice,goodinventory) values('{0}','{1}','{2}','{3}')";
                commandText = string.Format(commandText, Guid.NewGuid().ToString(), "shop recyle " +i, new Random().Next(20, 200), new Random().Next(20, 200));
                SQLHelper.ExcuteSqlNoReturn(commandText);
            }
            Console.WriteLine("recyle end:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        }

        static void Fun2()
        {
            Console.WriteLine("stringBuilder start:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < 100000; i++)
            {
                
                string commandText = "insert into GoodsTB(goodsid,goodsname,goodsprice,goodinventory) values('{0}','{1}','{2}','{3}')";
                commandText = string.Format(commandText, Guid.NewGuid().ToString(), "shop stringBuilder " + i, new Random().Next(20, 200), new Random().Next(20, 200));
                sb.Append(commandText);
                sb.Append("; ");
            }
            SQLHelper.ExcuteSqlNoReturn(sb.ToString());
            Console.WriteLine("stringBuilder end:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        }

        static void Fun3()
        {
            Console.WriteLine("MutiInsertData start:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("GoodsId",typeof(string)));
            dt.Columns.Add(new DataColumn("GoodsName", typeof(string)));
            dt.Columns.Add(new DataColumn("GoodsPrice",typeof(Decimal)));
            dt.Columns.Add(new DataColumn("GoodsInventory", typeof(Int16)));

            for (int i = 0; i < 100000; i++)
            {
                dt.Rows.Add(Guid.NewGuid().ToString(), "shop MutiInsertData " + i, new Random().Next(20, 200), new Random().Next(20, 200)); 
            }
            SQLHelper.MutiInsertData(dt);
            Console.WriteLine("MutiInsertData end:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
        }
    }

辅助数据库操作类：

 public class SQLHelper
    {
        public static string connectionstring = "Data Source=blueker;Initial Catalog=EasyShop;User ID=sa;Password=killers8Y;";

        /// <summary>
        /// return -1 :error else affected rows
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static int ExcuteSqlNoReturn(string commandText)
        {
            int rows = 0;
            SqlConnection sql = new SqlConnection(connectionstring);
            SqlCommand cmd = new SqlCommand();
            try
            {
                sql.Open();
                cmd.Connection = sql;
                cmd.CommandText = commandText;
                cmd.CommandType = CommandType.Text;
                rows = cmd.ExecuteNonQuery();

            }
            catch (Exception e)
            { 
                rows = -1;
                Console.WriteLine(e.Message + e.StackTrace);
            }
            finally {
                sql.Close();
                sql.Dispose();
                cmd.Dispose();
            }
            return rows;
        }

        public static void MutiInsertData(DataTable DT)
        {
            SqlConnection conn = new SqlConnection(connectionstring);
            
            try
            {
                conn.Open();
                using (SqlBulkCopy sbc = new SqlBulkCopy(conn))
                {
                    sbc.BatchSize = DT.Rows.Count;
                    sbc.BulkCopyTimeout = 100;
                    sbc.DestinationTableName = "goodstb";
                    sbc.WriteToServer(DT);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            finally { conn.Close(); conn.Dispose(); }
        }
    }
}