ADO.Net的批量插入

在做大批量資料插入的時候，如果用Insert into ... values (...)這種方式的話效率極低，這裡介紹兩種效能比較好的批量插入方法。

1. 使用SqlBulkCopy

private static long SqlBulkCopyInsert()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
DataTable dataTable = GetTableSchema();
string passportKey;
for (int i = 0; i < count; i++)
{
passportKey = Guid.NewGuid().ToString();
DataRow dataRow = dataTable.NewRow();
dataRow[0] = passportKey;
dataTable.Rows.Add(dataRow);
}
SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionString);
sqlBulkCopy.DestinationTableName = "Passport";
sqlBulkCopy.BatchSize = dataTable.Rows.Count;
SqlConnection sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
if (dataTable!=null && dataTable.Rows.Count!=0)
{
sqlBulkCopy.WriteToServer(dataTable);
}
sqlBulkCopy.Close();
sqlConnection.Close();
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}  

 

使用SqlBulkCopy類進行資料插入其原理是採用了SQL Server的BCP協議進行資料的批量複製。這裡我們先要建好一個DataTable（最好是通過DataAdapter來灌資料得到，因為這樣出來的DataTable就已經有跟資料表相同的列定義，可以免去之後Mapping Column的步驟），把要插入的資料加進這個DataTable中，然後用SqlBulkCopy的例項來插入到資料庫中。經過測試，SqlBulkCopy方法比直接用Sql語句插入資料的效率高出將近25倍。

 

2. 使用資料庫中的Table型別變數實現資料插入

這種方法的前提是資料庫中必須支援Table型別的變數。

Create Type PassportTableType as Table
(
PassportKey nvarchar(50)
)

Create PROCEDURE [dbo].[CreatePassportWithTVP]
@TVP PassportTableType readonly
AS
BEGIN
SET NOCOUNT ON;
Insert into Passport(PassportKey) select PassportKey from @TVP
END

 

private static long TVPInsert()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
DataTable dataTable = GetTableSchema();
string passportKey;
for (int i = 0; i < count; i++)
{
passportKey = Guid.NewGuid().ToString();
DataRow dataRow = dataTable.NewRow();
dataRow[0] = passportKey;
dataTable.Rows.Add(dataRow);
}
SqlParameter[] sqlParameter = { new SqlParameter("@TVP", dataTable) };
SqlHelper.ExecuteNonQuery(connectionString, CommandType.StoredProcedure, "CreatePassportWithTVP", sqlParameter);
stopwatch.Stop();
return stopwatch.ElapsedMilliseconds;
}  

 

這種方法實現起來有點兒複雜。首先， 在資料庫裡新建一個自定義型別，繼承Table型別的，然後建立Stored Procedure，將剛建立的自定義型別作為引數傳入。關鍵點在這裡了：使用insert into ... select ... from ...這個語句來實現批量插入。最後就剩下在ADO.Net中呼叫這個儲存過程，將DataTable作為引數傳入就大功告成了。

 

PS. 可能有人要說，DataAdapter不是提供了一個Update的方法，可以實現批量Insert、Delete、Update 的麼？但其實DataAdapter的這種做法跟逐條逐條sql語句輸入資料庫然後執行沒什麼區別，甚至更慢。還是上面提供的這兩種方法可以從根本上提升批量插入的效能。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12639172/viewspace-663251/，如需轉載，請註明出處，否則將追究法律責任。