使用 Bulk Copy 將大量數(shù)據(jù)復(fù)制到數(shù)據(jù)庫如果一次要向數(shù)據(jù)庫服務(wù)器提交多條記錄 , 通常會(huì)執(zhí)行多次Insert命令 , 這樣就為要插入的每個(gè)記錄執(zhí)行一次與數(shù)據(jù)庫服務(wù)器的往返 , 這就給服務(wù)器增加了壓力 , 效率也大大的降低了...
.Net FrameWork 2.0 新增功能 Bulk Copy 可以很快將大量數(shù)據(jù)加載到數(shù)據(jù)庫中 , 現(xiàn)在利用這一新功能來實(shí)現(xiàn)上述功能.
這里從 MS Sql Server 2000 的 NorthWind 的 Orders 表加載數(shù)據(jù)到 DateTable 模擬要向數(shù)據(jù)庫服務(wù)器提交的多條記錄集 . 用 Tempdb 庫來模擬目標(biāo)數(shù)據(jù)庫服務(wù)器 .
先在 Tempdb 建一個(gè)表 temp_orders
USE TEMPDB
CREATE TABLE TEMP_ORDERS
(
TEMP_ORDERID INT,
TEMP_CUSTOMERID NCHAR(5),
TEMP_ORDERDATE DATETIME,
TEMP_SHIPNAME NVARCHAR(40)
)
下面為模擬程序
protected void Page_Load(object sender, EventArgs e)
{
#region 從NorthWind的Orders表獲取要插入的數(shù)據(jù)
DataTable dtNorthWindOrders = new DataTable();
using ( SqlConnection northWindConnection = new SqlConnection( "Data Source=.;Initial Catalog=NorthWind;Integrated Security=True" ) )
{
using ( SqlDataAdapter northWindAdapter = new SqlDataAdapter( "SELECT ORDERID,CUSTOMERID,ORDERDATE,SHIPNAME FROM ORDERS" , northWindConnection ) )
{
northWindAdapter.Fill( dtNorthWindOrders );
}
}
#endregion
using ( SqlConnection tempdbConnection = new SqlConnection( "Data Source=.;Initial Catalog=Tempdb;Integrated Security=True" ) )
{
tempdbConnection.Open( );
using ( SqlTransaction tran = tempdbConnection.BeginTransaction( ) )
{
SqlBulkCopy bulkCopyOrders = new SqlBulkCopy( tempdbConnection , SqlBulkCopyOptions.Default , tran );
bulkCopyOrders.DestinationTableName = "TEMP_ORDERS";
//將數(shù)據(jù)源表字段和目標(biāo)表的字段做個(gè)映射
bulkCopyOrders.ColumnMappings.Add( "ORDERID" , "TEMP_ORDERID" );
bulkCopyOrders.ColumnMappings.Add( "CUSTOMERID" , "TEMP_CUSTOMERID" );
bulkCopyOrders.ColumnMappings.Add( "ORDERDATE" , "TEMP_ORDERDATE" );
bulkCopyOrders.ColumnMappings.Add( "SHIPNAME" , "TEMP_SHIPNAME" );
bulkCopyOrders.BulkCopyTimeout = 1000;
//每處理10行觸發(fā)一個(gè)事件向頁面上輸出一個(gè)消息
bulkCopyOrders.SqlRowsCopied += new SqlRowsCopiedEventHandler( onRowsCopy );
bulkCopyOrders.NotifyAfter = 10;
try
{
bulkCopyOrders.WriteToServer( dtNorthWindOrders );
tran.Commit( );
}
catch ( Exception ex )
{
Response.Write( ex.ToString( ) );
}
finally
{
dtNorthWindOrders = null;
}
}
}
}
private void onRowsCopy ( object Sender , SqlRowsCopiedEventArgs args )
{
Response.Write("已復(fù)制：<font color=red>"+ args.RowsCopied.ToString( ) + "</font><br />" );
}
通過SQL SERVER 事件探察器發(fā)現(xiàn)執(zhí)行的SQL為:
insert bulk TEMP_ORDERS ([TEMP_ORDERID] Int, [TEMP_CUSTOMERID] NChar(5) COLLATE Chinese_PRC_CI_AS, [TEMP_ORDERDATE] DateTime, [TEMP_SHIPNAME] NVarChar(40) COLLATE Chinese_PRC_CI_AS)
通過運(yùn)行程序可以看出這個(gè)速度是相當(dāng)?shù)目?, 使用這個(gè)方法的最大優(yōu)點(diǎn)是 : 減少對(duì)數(shù)據(jù)庫的訪問次數(shù) .
WriteToServer不僅可以處理 DataTable 對(duì)象 , 還可以處理 DataReader , DataRow 對(duì)象數(shù)組 .
