SqlBulkCopy類別,大量複製資料從A到B

昨天已經說了一個豬頭的故事,那今天就來寫個不豬頭的程式碼。

Default.aspx
<form id="form1" runat="server">
<div>
<asp:button id="btnBulkCopy" runat="server" text="Start Bulk Copy"></asp:button>
<asp:label id="lblResult" runat="server">
<asp:label id="lblCounter" runat="server">
</div>
</form>

Default.aspx.vb

程式目的:將程式本機資料庫中Share的資料大量複製到遠端Northwind資料庫中。

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 OnSqlRowsCopied)

        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 Sub
End Class

程式流程不會很難,

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

委派那一段可寫可不寫,在複製過程會觸發OnSqlRowsCopied事件,主要目的是顯示訊息讓使用者了解目前的進度。

如果兩端網路連線速度沒問題的話,十萬筆資料,都能在幾秒之內「秒殺~秒殺~秒殺~秒殺~秒殺~秒殺~秒殺~」,寫十萬筆Insert,有SqlBulkCopy不用,發瘋嗎。

沒有留言:

張貼留言

感謝您的留言,如果我的文章你喜歡或對你有幫助,按個「讚」或「分享」它,我會很高興的。