sql server 批量插入 bluk 和 表值参数

普通插入:

 public static void CommonInsert()
        {
            var num = 0;
            var watch = new Stopwatch();

            using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ToString()))
            {
                con.Open();
                var cmd = new SqlCommand();
                cmd.Connection = con;
                cmd.CommandText = "insert into t_orders (goods_id,goods_num) values(@goods_id,@goods_num)";

                var pramsList = new List<SqlParameter>();
                pramsList.Add(new SqlParameter("@goods_id", 1));
                pramsList.Add(new SqlParameter("@goods_num", 2));
                cmd.Parameters.AddRange(pramsList.ToArray());

                watch.Start();
                for (int i = 0; i < 10; i++)
                {
                    for (int x = i * 10000; x < (i + 1) * 10000; x++)
                    {
                        num += cmd.ExecuteNonQuery();
                    }
                }
                watch.Stop();
                Console.WriteLine($"当前插入数量:{num}");
            }

            Console.WriteLine(watch.ElapsedMilliseconds / 1000 + "秒");
        } 

bluk 插入:

      public static void BulkInsert()
        {
            var num = 0;
            var watch = new Stopwatch();

            using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ToString()))
            {
                con.Open();
                var bluk = new SqlBulkCopy(con);
                bluk.DestinationTableName = "t_orders";
                watch.Start();
                for (int i = 0; i < 10; i++)
                {
                    var tb = Createtable();

                    for (int x = i * 10000; x < (i + 1) * 10000; x++)
                    {
                        var row = tb.NewRow();
                        row[0] = x;
                        row[1] = 1;
                        row[2] = 2;
                        tb.Rows.Add(row);
                    }
                    bluk.BatchSize = tb.Rows.Count;
                    bluk.WriteToServer(tb);
                    num += bluk.BatchSize;
                }
                watch.Stop();
                Console.WriteLine($" BulkInsert 当前插入数量:{num}");
                con.Close();
            }
            Console.WriteLine("BulkInsert" + watch.ElapsedMilliseconds / 1000 + "秒");
        }

表值参数:  需要到 sql server 先定义好 表参数 类型    

CREATE TYPE t_orders_cache AS TABLE  (id int, goods_id int,  goods_num int) 

---

做法类似于 创建一个实体类

 public static void TableValueInsert()
        {
            var num = 0;
            var watch = new Stopwatch();

            using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ToString()))
            {
                con.Open();
                var cmd = new SqlCommand(" insert into t_orders (goods_id,goods_num ) select goods_id,goods_num  from @t_orders",  con);

                watch.Start();
                for (int i = 0; i < 10; i++)
                {
                    var tb = Createtable();

                    for (int x = i * 10000; x < (i + 1) * 10000; x++)
                    {
                        var row = tb.NewRow();
                        row[0] = x;
                        row[1] = 1;
                        row[2] = 2;
                        tb.Rows.Add(row);
                    }
                    //指定作为参数的表
                    cmd.Parameters.Clear();
                    var tbaleVal = cmd.Parameters.AddWithValue("@t_orders", tb);
                    //tbaleVal.SqlDbType = SqlDbType.Structured;
                    //必须指明
                    // 需要先在 sql serveer 中 定义这个类型 
                    // CREATE TYPE t_orders_cache AS TABLE  (id int, goods_id int,  goods_num int) 
                    // 类似 List<>
                    tbaleVal.TypeName = "t_orders_cache";

                    num += cmd.ExecuteNonQuery();
                }
                watch.Stop();
                Console.WriteLine($" TableValueInsert 当前插入数量:{num}");
                con.Close();
            }
            Console.WriteLine("TableValueInsert" + (decimal )watch.ElapsedMilliseconds /(decimal)1000  + "秒");
        }

按照上面的数据 都是插入10万条数据 效率如下:

再测试 100万数据效率  普通插入 已经没耐性等了,确实等不到了

再测试下 1000万条数据的效率  这个效率确实太屌了