星期五, 4月 25, 2008

(簡單範例)用SqlBulkCopy大量寫入資料庫結合交易

這幾天在案子在寫這東西..發現..一點都不難..一點點程式就寫完了..Hot
大家看圖說故事..這樣比較快..^^..

名字dbo.test資料庫

ghj

.aspx頁面..就放兩個button..有各自的事件

<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" Text="大量寫入" onclick="Button1_Click" />
&nbsp;<asp:Button ID="Button2" runat="server" Text="刪除" onclick="Button2_Click" />
</div>
</form>
</body>

.cs簡單的說就是先產生一個datatable..把你要寫入資料庫的東西..先寫在datatable
然後在一次大量寫入資料庫..原理很簡單..

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);
}
}

這樣就完成了..順便說一下..2.0的交易真的超簡單的..包住..就給可以跑..哈哈..
大家參考一下..有問題說ㄅ..

沒有留言: