用sqlbulkcopy大量寫入結合交易檔

聲明:本篇是別人文章
相關請參考原出處
續引一篇進來,主要寫法不大一樣;多了(丟出事件)和(交易)這兩個動作
(1)
//處理完後丟出一個事件,或是說處理幾筆後就丟出事件
mySbc.NotifyAfter = sourceDt.Rows.Count;
mySbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(mySbc_SqlRowsCopied);
(2)
//完成交易
myScope.Complete();
-------------------------------------------------------
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Transactions;

public partial class test_sqlBulkCopy : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}

protected void Button1_Click(object sender, EventArgs e)
{
//產生source table
DataTable sourceDt = new DataTable();
sourceDt.Columns.Add("id", typeof(int));
sourceDt.Columns.Add("a", typeof(string));
sourceDt.Columns.Add("b", typeof(string));
sourceDt.Columns.Add("c", typeof(string));

//看你要產生幾筆row先寫在datatable
for (int i = 0; i < 10; i++)
{
DataRow dr = sourceDt.NewRow();
dr["a"] = "a" + i.ToString();
dr["b"] = "b" + i.ToString();
dr["c"] = "c" + i.ToString();
sourceDt.Rows.Add(dr);
}

//加入2.0以後的交易,記得匯入System.Transactions.dll
using (TransactionScope myScope = new TransactionScope())
{
//大量寫入
using (SqlConnection myConn = new SqlConnection(ConfigurationManager.
ConnectionStrings["NORTHWNDConnectionString"].ConnectionString))
{
myConn.Open();

using (SqlBulkCopy mySbc = new SqlBulkCopy(myConn))
{
//設定
mySbc.BatchSize = 1000;
mySbc.BulkCopyTimeout = 60;

//處理完後丟出一個事件,或是說處理幾筆後就丟出事件
mySbc.NotifyAfter = sourceDt.Rows.Count;
mySbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(mySbc_SqlRowsCopied);

//更新哪個資料庫
mySbc.DestinationTableName = "dbo.test";

//column對應
mySbc.ColumnMappings.Add("id", "id");
mySbc.ColumnMappings.Add("a", "a");
mySbc.ColumnMappings.Add("b", "b");
mySbc.ColumnMappings.Add("c", "c");

//寫入
mySbc.WriteToServer(sourceDt);
//throw new Exception("error");

//完成交易
myScope.Complete();
}
}
}
}

void mySbc_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
ClientScript.RegisterStartupScript(GetType(), "test", "alert('完成');", true);
}

protected void Button2_Click(object sender, EventArgs e)
{
//刪除測試資料
linqToSqlDataContext db = new linqToSqlDataContext();
db.ExecuteCommand("DELETE FROM test");
ClientScript.RegisterStartupScript(GetType(), "test", "alert('完成');", true);
}
}