SqlBulkCopy A至B

Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub btnBulkCopy_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnBulkCopy.Click
'程式本機 Database Server Connection String
Dim shcs As New SqlConnectionStringBuilder
shcs.DataSource = ".\SQLEXPRESS"
shcs.InitialCatalog = "Share"
shcs.UserID = "bruce"
shcs.Password = "123456789"
'遠端Database Server Connection String
Dim ntcs As New SqlConnectionStringBuilder
ntcs.DataSource = "192.168.3.12\SQLEXPRESS"
ntcs.InitialCatalog = "Northwind"
ntcs.UserID = "bruce"
ntcs.Password = "987654321"
Dim shcmd As New SqlCommand
Dim shdr As SqlDataReader
Dim shconn As New SqlConnection(shcs.ConnectionString)
shcmd.CommandText = " SELECT ID, First_Name, Last_Name, 'CEO' as Source FROM MailingList"
shcmd.CommandType = CommandType.Text
shcmd.Connection = shconn
shcmd.Connection.Open()
'與遠端建立SqlBulkCopy實體
Dim ntbcp As New SqlBulkCopy(New SqlConnection(ntcs.ConnectionString))
'指定遠端的資料表名稱
ntbcp.DestinationTableName = "Employees"
'本機與遠端的Column對應，如本機與遠端的Schema一樣，可省略ColumnMappings ntbcp.ColumnMappings.Add("ID", "EmployeeID")
ntbcp.ColumnMappings.Add("First_Name", "FirstName")
ntbcp.ColumnMappings.Add("Last_Name", "LastName")
'自定義欄位，把Source對應到Employees的Title欄位，所有欄位都會等於CEO
Dim TitleMapping As New SqlBulkCopyColumnMapping("Source", "Title") ntbcp.ColumnMappings.Add(TitleMapping)
'TimeOut，單位秒
ntbcp.BulkCopyTimeout = 360
'多少筆發一次通知
ntbcp.NotifyAfter = 1000
'委派
AddHandler ntbcp.SqlRowsCopied, AddressOf OnSqlRowsCopied
'AddHandler ntbcp.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf sqlRowsCopied)
shdr = shcmd.ExecuteReader
Try　　　　　
'執行WriteToServer，把本機資料(DataReader)大量複製到遠端資料庫
ntbcp.WriteToServer(shdr)
Catch ex As Exception
lblResult.Text = ex.Message
Finally
shdr.Close()
End Try
End Sub

Private Sub OnSqlRowsCopied(ByVal sender As Object, ByVal args As SqlRowsCopiedEventArgs) 　　　　　
'由於ntbcp.NotifyAfter的關係，每1000筆觸發一次
lblCounter.Text += args.RowsCopied.ToString() + " rows are copied. "
End SubEnd

Class

程式流程不會很難，


建立本機SqlConnection實體
建立遠端SqlBulkCopy實體(使用遠端Sqlconnection實體)
執行本機ExecuteReader方法，取得資料
執行SqlBulkCopy實體的WriteToServer方法，將DataReader寫到遠端Server去