mysql bulk copy_关于SqlBulkCopy的测试

最近要做.net关于sql大量插入，找到了sqlbulkcopy(自己google下，应该很多说明了)这个好东西，于是测试下性能，用了三个方法对比：

1)直接用ado.net,for循环N次进行单条插入

2)把N条插入语句拼在一个sql，进行插入

3)直接使用sqlbulkcopy进行插入

代码如下：

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();

}

}

}

插入N条数据的测试结果如下：

效率相差还是很夸张的，大家大量数据插入还是有sqlbulkcopy吧，原理还的高手告知~~