如果一次要向数据库服务器提交多条记录 , 通常会执行多次Insert命令 , 这样就为要插入的每个记录执行一次与数据库服务器的往返 , 这就给服务器增加了压力 , 效率也大大的降低了...
.Net FrameWork 2.0 新增功能 Bulk Copy 可以很快将大量数据加载到数据库中 , 现在利用这一新功能来实现上述功能.
这里从 MS Sql Server 2000 的 NorthWind 的 Orders 表加载数据到 DateTable 模拟要向数据库服务器提交的多条记录集 . 用 Tempdb 库来模拟目标数据库服务器 .
先在 Tempdb 建一个表 temp_orders
USE
TEMPDB
CREATE TABLE TEMP_ORDERS
(
TEMP_ORDERID INT ,
TEMP_CUSTOMERID NCHAR ( 5 ),
TEMP_ORDERDATE DATETIME ,
TEMP_SHIPNAME NVARCHAR ( 40 )
)
下面为模拟程序
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表获取要插入的数据
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 " ;
// 将数据源表字段和目标表的字段做个映射
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行触发一个事件向页面上输出一个消息
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( " 已复制:<font color=red> " + args.RowsCopied.ToString( ) + " </font><br /> " );
}
通过SQL SERVER 事件探察器发现执行的SQL为:
{
#region 从NorthWind的Orders表获取要插入的数据
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 " ;
// 将数据源表字段和目标表的字段做个映射
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行触发一个事件向页面上输出一个消息
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( " 已复制:<font color=red> " + args.RowsCopied.ToString( ) + " </font><br /> " );
}
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)
通过运行程序可以看出这个速度是相当的快 , 使用这个方法的最大优点是 : 减少对数据库的访问次数 .
WriteToServer不仅可以处理 DataTable 对象 , 还可以处理 DataReader , DataRow 对象数组 .