Test for BULK Insert SqlBulkCopy

About the SqlBulkCopy test

Recently to do. NET about SQL large insert, found SqlBulkCopy (own Google, should be a lot of instructions) this good thing, so test performance, with three methods comparison:

1) directly with Ado.net,for Loop n times for single insertion

2) Insert the N INSERT statement in a SQL

3) Insert directly using the SqlBulkCopy

The code is as follows:

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465 66676869707172737475767778798081828384858687888990919293949596979899

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SqlClient;using System.Diagnostics;namespace SQLTEST{    class Program    {        static void Main(string[] args)        {            //int time = 200;            test(20);            test(200);            test(2000);            test(10000);            test(50000);            Console.ReadLine();        }        public static void test(int time){            Stopwatch sp = new Stopwatch();            Console.WriteLine(time + "条数据插入测试");             //测试方法1            {                using (SqlConnection sqlcon = new SqlConnection("Data Source=.;Initial Catalog=Test;User ID=sa;Password=??"))                {                    sqlcon.Open();                    string singesql = "INSERT INTO [student] ([name],[age])VALUES(‘abc‘,3);";                    SqlCommand sqlcommand = new SqlCommand(singesql, sqlcon);                    //计时开始                    sp.Restart();                    for (int i = 0; i < time; i++)                    {                        sqlcommand.ExecuteNonQuery();                    }                    sp.Stop();                }            }            Console.WriteLine("方法1：" + sp.ElapsedMilliseconds + "毫秒");             //测试方法2            {                using (SqlConnection sqlcon = new SqlConnection("Data Source=.;Initial Catalog=Test;User ID=sa;Password=??"))                {                    sqlcon.Open();                    string singesql = "INSERT INTO [student] ([name],[age])VALUES(‘abc‘,3);";                    string execsql = "";                    for (int i = 0; i < time; i++)                    {                        execsql = execsql + singesql;                    }                    SqlCommand sqlcommand = new SqlCommand(execsql, sqlcon);                     //计时开始                    sp.Restart();                    sqlcommand.ExecuteNonQuery();                    sp.Stop();                }            }            Console.WriteLine("方法2：" + sp.ElapsedMilliseconds + "毫秒");            //测试方法3            {                using (SqlConnection sqlcon = new SqlConnection("Data Source=.;Initial Catalog=Test;User ID=sa;Password=??"))                {                    sqlcon.Open();                    SqlBulkCopy sqlc = new SqlBulkCopy(sqlcon);                    DataTable dt = new DataTable();                    dt.Columns.Add("id");                    dt.Columns.Add("name");                    dt.Columns.Add("age");                     for (int i = 0; i < time; i++)                    {                        dt.Rows.Add(38009, "nemw", 123);                    }                    sqlc.DestinationTableName = "student";                    //计时开始                    sp.Restart();                    sqlc.WriteToServer(dt);                    sp.Stop();                }            }            Console.WriteLine("方法3：" + sp.ElapsedMilliseconds + "毫秒");            Console.WriteLine();                }    }}

　　

The test results for inserting n data are as follows:

Efficiency difference or very exaggerated, we have a large number of data insertion or sqlbulkcopy bar, the principle of the master told ~ ~

Test for BULK Insert SqlBulkCopy