使用 Bulk Copy 将大量数据复制到数据库

如果一次要向数据库服务器提交多条记录 , 通常会执行多次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)
)

下面为模拟程序

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为:
view plaincopy to clipboardprint?
<div style="BORDER-RIGHT: windowtext 0.5pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: windowtext 0.5pt solid; PADDING-LEFT: 5.4pt; BACKGROUND: #e6e6e6; PADDING-BOTTOM: 4px; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 95%; WORD-BREAK: break-all; PADDING-TOP: 4px; BORDER-BOTTOM: windowtext 0.5pt solid"><div><img align="top" alt="" src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><span style="COLOR: #000000">insert bulk TEMP_ORDERS ([TEMP_ORDERID] Int, [TEMP_CUSTOMERID] NChar(</span><span style="COLOR: #000000">5</span><span style="COLOR: #000000">) COLLATE Chinese_PRC_CI_AS, [TEMP_ORDERDATE] DateTime, [TEMP_SHIPNAME] NVarChar(</span><span style="COLOR: #000000">40</span><span style="COLOR: #000000">) COLLATE Chinese_PRC_CI_AS)</span></div></div> 

通过运行程序可以看出这个速度是相当的快 , 使用这个方法的最大优点是 : 减少对数据库的访问次数 .

WriteToServer不仅可以处理 DataTable 对象 , 还可以处理 DataReader , DataRow 对象数组 .