.Net1.1中無論是對于批量插入整個DataTable中的所有資料到資料庫中,還是進行不同資料源之間的遷移,都不是很友善。而 在.Net2.0中,SQLClient命名空間下增加了幾個新類幫助我們通過DataTable或DataReader批量遷移資料。資料源可以來自關 系資料庫或者XML檔案,甚至WebService傳回結果。其中最重要的一個類就是SqlBulkCopy類,使用它可以很友善的幫助我們把資料源的數 據遷移到目标資料庫中。
下面我們先通過一個簡單的例子說明這個類的使用:
首先:web.config
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
< connectionStrings >
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
< add name ="srcDBConnection" connectionString ="server=.;database=pubs;uid=sa;pwd=" />
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
< add name ="desDBConnection" connectionString ="server=.;database=NorthWind;uid=sa;pwd=" />
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
</ connectionStrings >
C#檔案: 前台不Copy了,就一個按鈕,一個Label
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
using System;
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
using System.Data;
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
using System.Configuration;
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
using System.Collections;
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
using System.Web;
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
using System.Web.Security;
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
using System.Web.UI;
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
using System.Web.UI.WebControls;
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
using System.Web.UI.WebControls.WebParts;
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
using System.Web.UI.HtmlControls;
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
using System.Data.SqlClient;
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
public partial class ASP_NET : System.Web.UI.Page
{
private DateTime startTime;
protected void Button1_Click(object sender, EventArgs e)
{
startTime = DateTime.Now;
string srcConnString = "";
string desConnString = "";
SqlConnection srcConnection = new SqlConnection();
SqlConnection desConnection = new SqlConnection();
SqlCommand sqlcmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
//srcConnString = ConfigurationManager.ConnectionStrings["srcDBConnection"].ConnectionString;
desConnString = ConfigurationManager.ConnectionStrings["desDBConnection"].ToString();
//srcConnection.ConnectionString = srcConnString;
srcConnection.ConnectionString = desConnString;
sqlcmd.Connection = srcConnection;
//sqlcmd.CommandText = "select * from jobs";
sqlcmd.CommandText = "select * from abc";
sqlcmd.CommandType = CommandType.Text;
sqlcmd.Connection.Open();
da.SelectCommand = sqlcmd;
da.Fill(dt);
SqlBulkCopy sbc = new SqlBulkCopy(desConnString,SqlBulkCopyOptions.UseInternalTransaction);
sbc.BulkCopyTimeout = 5000;
sbc.SqlRowsCopied +=new SqlRowsCopiedEventHandler(OnRowsCopied);
sbc.NotifyAfter = dt.Rows.Count;
try
{
// sbc.DestinationTableName = "jobs";
sbc.DestinationTableName = "bcd";
sbc.WriteToServer(dt);
}
catch (Exception ex)
{
lblCounter.Text = ex.Message.ToString();
}
finally
{
sqlcmd.Clone();
srcConnection.Close();
desConnection.Close();
}
}
private void OnRowsCopied(object sender, SqlRowsCopiedEventArgs args)
{
lblCounter.Text += args.RowsCopied.ToString() + " rows are copied<Br>";
TimeSpan copyTime = DateTime.Now - startTime;
lblCounter.Text += "Copy Time:" + copyTime.Seconds.ToString() + "." + copyTime.Milliseconds.ToString() + " seconds";
}
}
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
代碼分析:
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
SqlBulkCopy sbc = new SqlBulkCopy(desConnString,SqlBulkCopyOptions.UseInternalTransaction);
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
先生成SqlBulkCopy 執行個體,構造函數指定了目标資料庫,使用SqlBulkCopyOptions.UseInternalTransaction是指遷移動作指定在一個Transaction當中,如果資料遷移中産生錯誤或異常将發生復原。
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
sbc.BulkCopyTimeout = 5000000; //指定操作完成的Timeout時間
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
sbc.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnRowsCopied);
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
sbc.NotifyAfter = dt.Rows.Count;
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
try
{
// sbc.DestinationTableName = "jobs";
sbc.DestinationTableName = "bcd";
sbc.WriteToServer(dt);
}
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
NotifyAfter 屬性指定通知通知事件前處理的資料行數,在這裡指定為表的行數,并添加SqlRowsCopied事件輸出整個遷移過程的時間。 WriteToServer方法就是将資料源拷備到目标資料庫。在使用WriteToServer方法之前必須先指定 DestinationTableName屬性,也就是目标資料庫的表名,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
性能方面:我在Sql中用proc插入68萬條資料花了近8分鐘,用SqlBulkCopy花了53.234秒~,效率高了7倍耶!不過現在也不做這方面的底層了,呵呵,把自己寫的一個測試存儲過程也貼上吧,友善自己學習
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
create table abc
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
(
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
aid int identity ( 1 , 1 ) primary key ,
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
adesc varchar ( 50 ) not null
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
)
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
go
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
create proc addData
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
as
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
declare @i int
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
set @i = 1
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
while @i < 1000000
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
begin
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
insert into abc values ( ' testDescription ' )
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
set @i = @i + 1
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
end
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
go
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
![](https://img.laitimes.com/img/_0nNw4CM6IyYiwiM6ICdiwiIml2ZuUmbv50LcNncvRXYjlGZul0ZulmbpxGd190LcdmbpRHanlGbodWaohXY05Wez9CX0Vmbu4GZzNmLzV2Zh1Wavw1LcpDc0RHaiojIsJye.gif)
select * into titles from pubs.dbo.titles where 1 > 3 複制跨資料庫的表結構