ado.net mysql 批量插入_ADO.Net的批量插入

摘要：做大批量数据插入的时候，如果用Insert into ... values (...)这种方式的话效率极低，这里介绍两种性能比较好的批量插入方法。

在做大批量数据插入的时候，如果用Insert into ... values (...)这种方式的话效率极低，这里介绍两种性能比较好的批量插入方法。

1. 使用SqlBulkCopy

privatestaticlongSqlBulkCopyInsert()

{

Stopwatch stopwatch=newStopwatch();

stopwatch.Start();

DataTable dataTable=GetTableSchema();stringpassportKey;for(inti=0; i

{

passportKey=Guid.NewGuid().ToString();

DataRow dataRow=dataTable.NewRow();

dataRow[0]=passportKey;

dataTable.Rows.Add(dataRow);

}

SqlBulkCopy sqlBulkCopy=newSqlBulkCopy(connectionString);

sqlBulkCopy.DestinationTableName="Passport";

sqlBulkCopy.BatchSize=dataTable.Rows.Count;

SqlConnection sqlConnection=newSqlConnection(connectionString);

sqlConnection.Open();if(dataTable!=null&&dataTable.Rows.Count!=0)

{

sqlBulkCopy.WriteToServer(dataTable);

}

sqlBulkCopy.Close();

sqlConnection.Close();

stopwatch.Stop();returnstopwatch.ElapsedMilliseconds;

}

使用SqlBulkCopy类进行数据插入其原理是采用了SQL Server的BCP协议进行数据的批量复制。这里我们先要建好一个DataTable(最好是通过DataAdapter来灌数据得到，因为这样出来的DataTable就已经有跟数据表相同的列定义，可以免去之后Mapping Column的步骤)，把要插入的数据加进这个DataTable中，然后用SqlBulkCopy的实例来插入到数据库中。经过测试，SqlBulkCopy方法比直接用Sql语句插入数据的效率高出将近25倍。

2. 使用数据库中的Table类型变量实现数据插入

这种方法的前提是数据库中必须支持Table类型的变量。

CreateType PassportTableTypeasTable(PassportKeynvarchar(50))CreatePROCEDURE[dbo].[CreatePassportWithTVP]@TVPPassportTableType readonlyASBEGINSETNOCOUNTON;InsertintoPassport(PassportKey)selectPassportKeyfrom@TVPEND

privatestaticlongTVPInsert()

{

Stopwatch stopwatch=newStopwatch();

stopwatch.Start();

DataTable dataTable=GetTableSchema();stringpassportKey;for(inti=0; i

{

passportKey=Guid.NewGuid().ToString();

DataRow dataRow=dataTable.NewRow();

dataRow[0]=passportKey;

dataTable.Rows.Add(dataRow);

}

SqlParameter[] sqlParameter={newSqlParameter("@TVP", dataTable) };

SqlHelper.ExecuteNonQuery(connectionString,

CommandType.StoredProcedure,"CreatePassportWithTVP",

sqlParameter);

stopwatch.Stop();returnstopwatch.ElapsedMilliseconds;

}

这种方法实现起来有点儿复杂。首先， 在数据库里新建一个自定义类型，继承Table类型的，然后建立Stored Procedure，将刚建立的自定义类型作为参数传入。关键点在这里了：使用insert into ... select ... from ...这个语句来实现批量插入。最后就剩下在ADO.Net中调用这个存储过程，将DataTable作为参数传入就大功告成了。

PS. 可能有人要说，DataAdapter不是提供了一个Update的方法，可以实现批量Insert、Delete、Update 的么？但其实DataAdapter的这种做法跟逐条逐条sql语句输入数据库然后执行没什么区别，甚至更慢。还是上面提供的这两种方法可以从根本上提升批量插入的性能。