使用SqlBulkCopy批量插入或遷移資料(轉)

在.Net1.1中無論是對於批量插入整個DataTable中的所有資料到資料庫中，還是進行不同資料來源之間的遷移，都不是很方便。而在.Net2.0中，SQLClient名稱空間下增加了幾個新類幫助我們通過DataTable或DataReader批量遷移資料。資料來源可以來自關聯式資料庫或者XML檔案，甚至WebService返回結果。其中最重要的一個類就是SqlBulkCopy類，使用它可以很方便的幫助我們把資料來源的資料遷移到目標資料庫中。

//一開始我們先產生一個DataTable來裝我們要寫入的資料 
DataTable dt = new DataTable(); 
dt.Columns.Add("id", typeof(int)); 
dt.Columns.Add("name", typeof(string)); 
 
//因為SqlBulkCopy的猛就是大量的一次寫入，所以我們也來跑10萬筆吧 
int i; 
for (i = 0; i < 100000; i++) 
{ 
    DataRow dr = dt.NewRow(); 
    dr["name"] = i.ToString(); 
    dt.Rows.Add(dr); 
} 
 
//宣告連結字串 
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ASPNETDBConnectionString1"].ConnectionString); 
 
conn.Open(); 
//宣告SqlBulkCopy 
using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn)) 
        { 
            //設定一個批次量寫入多少筆資料 
            sqlBC.BatchSize = 1000; 
            //設定逾時的秒數 
            sqlBC.BulkCopyTimeout = 60; 
 
            //設定 NotifyAfter 屬性，以便在每複製 10000 個資料列至資料表後，呼叫事件處理常式。 
            sqlBC.NotifyAfter = 10000; 
            sqlBC.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied); 
 
            //設定要寫入的資料庫 
            sqlBC.DestinationTableName = "dbo.Table1"; 
             
            //對應資料行 
            sqlBC.ColumnMappings.Add("id", "id"); 
            sqlBC.ColumnMappings.Add("name", "name"); 
 
            //開始寫入 
            sqlBC.WriteToServer(dt); 
        } 
        conn.Dispose(); 
} 
void OnSqlRowsCopied(object sender, SqlRowsCopiedEventArgs e) 
{ 
    Response.Write("---
 

"); 
} 
 

下面我們先通過另一個簡單的例子說明這個類的使用：

DateTime startTime;
    protected void Button1_Click(object sender, EventArgs e)
    {
        startTime = DateTime.Now;
        string SrcConString;
        string DesConString;
        SqlConnection SrcCon = new SqlConnection();
        SqlConnection DesCon = new SqlConnection();
        SqlCommand SrcCom = new SqlCommand();
        SqlDataAdapter SrcAdapter = new SqlDataAdapter();
        DataTable dt = new DataTable();
        SrcConString =
        ConfigurationManager.ConnectionStrings["SrcDBConnectionString"].ConnectionString;
        DesConString =
        ConfigurationManager.ConnectionStrings["DesDBConnectionString"].ConnectionString;
        SrcCon.ConnectionString = SrcConString;
        SrcCom.Connection = SrcCon;
        SrcCom.CommandText = " SELECT * From [SrcTable]";
        SrcCom.CommandType = CommandType.Text;
        SrcCom.Connection.Open();
        SrcAdapter.SelectCommand = SrcCom;
        SrcAdapter.Fill(dt);
        SqlBulkCopy DesBulkOp;
        DesBulkOp = new SqlBulkCopy(DesConString,
        SqlBulkCopyOptions.UseInternalTransaction);
        DesBulkOp.BulkCopyTimeout = 500000000;
        DesBulkOp.SqlRowsCopied +=
        new SqlRowsCopiedEventHandler(OnRowsCopied);
        DesBulkOp.NotifyAfter = dt.Rows.Count;
        try
        {
            DesBulkOp.DestinationTableName = "SrcTable";
            DesBulkOp.WriteToServer(dt);
        }
        catch (Exception ex)
        {
            lblResult.Text = ex.Message;
        }
        finally
        {
            SrcCon.Close();
            DesCon.Close();
        }
    }

    private void OnRowsCopied(object sender, SqlRowsCopiedEventArgs args)
    {
        lblCounter.Text += args.RowsCopied.ToString() + " rows are copied
";
        TimeSpan copyTime = DateTime.Now - startTime;
        lblCounter.Text += "Copy Time:" + copyTime.Seconds.ToString() + "." + copyTime.Milliseconds.ToString() + " seconds";
    }
接著具體分析這幾行程式碼：
SqlBulkCopy DesBulkOp;
DesBulkOp = new SqlBulkCopy(DesConString, SqlBulkCopyOptions.UseInternalTransaction);先生成SqlBulkCopy 例項，建構函式指定了目標資料庫，使用SqlBulkCopyOptions.UseInternalTransaction是指遷移動作指定在一個Transaction當中，如果資料遷移中產生錯誤或異常將發生回滾。其他選項請參考MSDN。

DesBulkOp.BulkCopyTimeout = 500000000;
指定操作完成的Timeout時間

        DesBulkOp.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnRowsCopied);
        DesBulkOp.NotifyAfter = dt.Rows.Count;
        try
        {
            DesBulkOp.DestinationTableName = "SrcTable";
            DesBulkOp.WriteToServer(dt);
        }
NotifyAfter屬性指定通知通知事件前處理的資料行數，在這裡指定為表的行數，並新增SqlRowsCopied事件輸出整個遷移過程的時間。WriteToServer方法就是將資料來源拷備到目標資料庫。在使用WriteToServer方法之前必須先指定DestinationTableName屬性，也就是目標資料庫的表名，

我們還可以自己定義一個Transaction,例如：

SqlTransaction Transaction;
Transaction =
SrcCom.Connection.BeginTransaction();
SqlBulkCopy DesBulkOp;
DesBulkOp = new SqlBulkCopy(new SqlConnection(DesConString),
SqlBulkCopyOptions.Default,
Transaction);

try
{   
//..
}
catch{}   
finally
{
Transaction.Commit();
}
另外還有一個SqlBulkCopyColumnMapping類，可以讓資料來源欄位對映到目標資料中命名不同的欄位上。也就是說如果目標資料和源資料的列名不同時，可以用這個類進行對映：
SqlBulkCopyColumnMapping ColMap = new SqlBulkCopyColumnMapping("SrcCol", "DesCol");
DesBulkOp.ColumnMappings.Add(ColMap);
或者可以直接新增對映：
DesBulkOp.ColumnMappings.Add("SrcCol", "DesCol");
效能問題：
我使用上面的例子測試，遷移了2萬條左右的記錄，花的時間不到一秒，應改說效能還是不錯的。另外，使用SQL Profile監視遷移事件，可以看見請求記錄非常少，只有幾條而已。據說使用SqlBulkCopy可以大大減少資料遷移的時間。

 

 

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