ado.net mysql 批量插入_ADO.NET下Sql Server批量插入数据

在.net开发中，避免不了数据库访问，其访问数据库主要通过ADO.NET技术。下面讲下 ADO.NET 批量插入数据(高性能)的实现，其实比较流行的ORM库EF、Dapper也是通过 ADO.NET访问数据库的，应也有对应的批量插入数据的方法。

利用SqlBulkCopy对象批量插入数据

测试数据库和表

create database BulkTestDB;

go

use BulkTestDB;

go

--Create Table

Create table BulkTestTable(

Id int primary key,

UserName nvarchar(32),

Pwd varchar(16))

go

通过ADO.NET实现

public static void BulkToDB(DataTable dt)

{

SqlConnection sqlConn = new SqlConnection(

ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);

SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);

bulkCopy.DestinationTableName = "BulkTestTable";

bulkCopy.BatchSize = dt.Rows.Count;

try

{

sqlConn.Open();

if (dt != null && dt.Rows.Count != 0)

bulkCopy.WriteToServer(dt);

}

catch (Exception ex)

{

throw ex;

}

finally

{

sqlConn.Close();

if (bulkCopy != null)

bulkCopy.Close();

}

}

public static DataTable GetTableSchema()

{

DataTable dt = new DataTable();

dt.Columns.AddRange(new DataColumn[]{

new DataColumn("Id",typeof(int)),

new DataColumn("UserName",typeof(string)),

new DataColumn("Pwd",typeof(string))});

return dt;

}

static void Main(string[] args)

{

Stopwatch sw = new Stopwatch();

for (int multiply = 0; multiply < 10; multiply++)

{

DataTable dt = Bulk.GetTableSchema();

for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++)

{

DataRow r = dt.NewRow();

r[0] = count;

r[1] = string.Format("User-{0}", count * multiply);

r[2] = string.Format("Pwd-{0}", count * multiply);

dt.Rows.Add(r);

}

sw.Start();

Bulk.BulkToDB(dt);

sw.Stop();

Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));

}

Console.ReadLine();

}

注意：SqlBulkCopy对象

新建 SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);

设置插入的数量bulkCopy.BatchSize = dt.Rows.Count;

插入数据库数据bulkCopy.WriteToServer(dt);

关闭 bulkCopy.Close();