章立民研究室

我們曾經在「Visual Basic 2005 檔案 IO 與資料存取秘訣」一書的第 10 章說明如何使用 SqlBulkCopy 物件來執行大量複製作業。有讀者詢問，是否可以使用 SqlBulkCopy 物件將文字檔的內容大量複製到資料庫。答案當然是肯定的？事實上，只要您能夠將資料載入至一個 DataTable 物件或是利用 IDataReader 物件來加以讀取，就可以使用 SqlBulkCopy 將其複製到 SQL Server 資料庫內的資料表（Table）或檢視表（View）當中。 

不過我們親愛的讀者在實際測試時遇到了一個問題，那就是他的文字檔內含 200 萬筆資料列，因此在將文字檔複製到一個中介用的 DataTable 物件時便發生記憶體不足的情況？他詢問我，該如何解決。 

首先我要聲明，要將一個內含 200 萬筆資料列的文字檔大量複製到 SQL Server 資料庫，想必不是一個經常性的作業，像這樣的工作，其實可以考慮使用 SQL Server 2005 本身的公用程式與介面工具來完成，不一定非得在前端程式使用 SqlBulkCopy 物件不可。不過既然讀者問了，我就試作了一次，結果令人滿意。 

我的作法是，先剖析文字檔的內容以便正確地將資料列一筆接著一筆寫入一個中介用的 DataTable 物件中，等到寫滿 50000 筆資料記錄之後，就使用 SqlBulkCopy 物件將 DataTable 中的 50000 筆資料記錄大量複製到 SQL Server 資料庫，接著將 DataTable 物件中的所有資料記錄清除，然後再繼續從文字檔將後續的資料列讀入DataTable物件中。反覆依此進行，直到已大量複製完所有的資料記錄為止。這樣一批接著一批讀取並大量複製的方式，可以避免發生記憶體不足的情況。 

圖表1 

圖表 1 所示者是我所撰寫之大量複製程式的介面。我的來源文字檔共內含 1,485,953 筆資料列（將近一百五十萬筆），文字檔的檔案大小達到 260MB，希望藉由這樣的大量資料實作，來驗證程式寫法的正確性。至於程式碼完整列示如下： 

Option Strict On

' 匯入命名空間。
Imports Microsoft.VisualBasic.FileIO
Imports System.Data.SqlTypes
Imports System.Data.SqlClient

Public Class Form1

    Private currentRow As String()
    Private myRowCount As Integer = 1
    Private myBatchCount As Integer = 1
    Private myCopiedRows As Long = 0
    Private countStart As Long

    ' 建立「章立民研究室」資料表，此處是當作一個中介資料表來使用。
    Private myTable As New DataTable("章立民工作室")

    Private Sub btnParseTextFiles_Click(ByVal sender As System.Object, _
      ByVal e As System.EventArgs) Handles btnGoBulkCopy.Click

        Me.btnGoBulkCopy.Enabled = False

        ' 建立「員工編號」欄位。
        Dim colEmployeeId As DataColumn = _
            myTable.Columns.Add("員工編號", Type.GetType("System.Int32"))

        ' 建立「身份證字號」欄位。
        myTable.Columns.Add("身份證字號", Type.GetType("System.String"))
        myTable.Columns("身份證字號").MaxLength = 10
        myTable.Columns("身份證字號").AllowDBNull = False

        ' 建立「姓名」欄位。
        myTable.Columns.Add("姓名", Type.GetType("System.String"))
        myTable.Columns("姓名").MaxLength = 12

        ' 建立「性別」欄位。
        myTable.Columns.Add("性別", Type.GetType("System.String"))
        'myTable.Columns("性別").MaxLength = 1

        ' 建立「地址」欄位。
        myTable.Columns.Add("地址", Type.GetType("System.String"))
        myTable.Columns("地址").MaxLength = 41

        ' 建立「郵遞區號」欄位。
        myTable.Columns.Add("郵遞區號", Type.GetType("System.String"))
        myTable.Columns("郵遞區號").MaxLength = 5

        ' 建立「出生日期」欄位。
        myTable.Columns.Add("出生日期", Type.GetType("System.DateTime"))

        ' 建立「婚姻狀況」欄位。
        myTable.Columns.Add("婚姻狀況", Type.GetType("System.String"))

        ' 建立「僱用日期」欄位。
        myTable.Columns.Add("僱用日期", Type.GetType("System.DateTime"))

        ' 建立「起薪」欄位。
        myTable.Columns.Add("起薪", Type.GetType("System.Double"))

        ' 建立「目前薪資」欄位。
        myTable.Columns.Add("目前薪資", Type.GetType("System.Double"))

        ' 建立「加薪日期」欄位。
        myTable.Columns.Add("加薪日期", Type.GetType("System.DateTime"))

        ' 建立「部門」欄位。
        myTable.Columns.Add("部門", Type.GetType("System.String"))
        myTable.Columns("部門").MaxLength = 10

        Using myReader As New TextFieldParser("Text章立民工作室.txt")

            ' 表示檔案內容是字元分隔。
            myReader.TextFieldType = FieldType.Delimited

            ' 定義文字檔的字元分隔符號。
            myReader.Delimiters = New String() {","}

            ' 循環處理文字檔中所有資料列的所有欄位。
            While Not myReader.EndOfData

                Try
                    currentRow = myReader.ReadFields()

                    ' 略過標題列
                    If myRowCount > 1 Then
                        myTable.Rows.Add(currentRow)
                    End If
                Catch ex As MalformedLineException
                    MessageBox.Show(ex.Message)
                    Exit Sub
                End Try

                myRowCount += 1
                Me.lblBeingCopyedTextRows.Text = myTable.Rows.Count.ToString
                Me.lblBeingCopyedTextRows.Refresh()

                If myTable.Rows.Count = 50000 Then
                    Try
                        GoBulkCopy()
                    Catch ex As Exception
                        MessageBox.Show(ex.Message)
                        Exit Sub
                    End Try
                    ' 清空資料表。
                    myTable.Rows.Clear()
                    myBatchCount += 1
                End If
            End While

            ' 複製最後一批不足50000 筆的資料記錄。
            If myTable.Rows.Count > 0 Then
                GoBulkCopy()
            End If
        End Using

        Me.lblBeingCopyedTextRows.Text = myTable.Rows.Count.ToString
        Me.lblTextFileRowCount.Text = _
          "來源文字檔的資料筆數：" & (myRowCount - 2).ToString
        Me.btnGoBulkCopy.Enabled = True
    End Sub

    Private Sub GoBulkCopy()
        ' 利用SqlConnectionStringBuilder 物件來構建連接字串。
        ' 由於本範例是在同一個SQL Server 資料庫的不同資料表之間進行大量複製作業，
        ' 因此連接至來源資料庫與連接至目標伺服器的連接字串是相同的。
        Dim sqlconStringBuilder As New SqlConnectionStringBuilder()
        sqlconStringBuilder.DataSource = "(local)SQLExpress"
        sqlconStringBuilder.InitialCatalog = "北風貿易"
        sqlconStringBuilder.IntegratedSecurity = True

        ' 建立連結至目標SQL Server 資料庫的連接。
        Using con_bulkcopy As New _

            SqlConnection(sqlconStringBuilder.ConnectionString)

            ' 開啟連接至目標SQL Server 的連接。
            con_bulkcopy.Open()

            Dim cmdRowCount As New SqlCommand( _
              "SELECT COUNT(*) FROM dbo.Bulk_Target_章立民工作室;", _
              con_bulkcopy)

            If myBatchCount = 1 Then
                ' 計算出目標資料表在執行大量複製作業前有多少筆資料記錄。
                countStart = System.Convert.ToInt32(cmdRowCount.ExecuteScalar())
                Me.lblRowsCountBeforeBulkCopy.Text = _
                  "目標資料表在大量複製前擁有的資料筆數= " & countStart.ToString
                Me.lblRowsCountBeforeBulkCopy.Refresh()
            End If

            ' 建立一個SqlBulkCopy 物件以便執行大量複製作業。
            Using bcp As SqlBulkCopy = New SqlBulkCopy(con_bulkcopy)

                ' 指定目標資料表的名稱。
                bcp.DestinationTableName = "dbo.Bulk_Target_章立民工作室"

                ' 如果來源資料表與目標資料表的各個欄位順序沒有完全對應，
                ' 必須在此設定來源欄位與目標欄位的對應關係。

                ' 將來源資料寫入目標資料表。
                bcp.WriteToServer(myTable)

            End Using

            ' 最後再計算出大量複製了多少筆資料記錄。
            Dim countEnd As Long = _
              System.Convert.ToInt32(cmdRowCount.ExecuteScalar())

            ' 計算出累計複製筆數。
            myCopiedRows = countEnd – countStart

            ' 顯示出批次與大量複製累計筆數。
            Me.DataGridView1.Rows.Add( _
              New String() {CStr(myBatchCount), CStr(myCopiedRows)})
            Me.DataGridView1.Refresh()

        End Using
    End Sub
End Class 

後記：
說真的，這個程式花不到我半小時，但是製作模擬的文字檔並等待程式執行，倒是花了我不少時間。不過，能解決使用者的問題，心中的大石頭總算落了地。最近又要開始趕下一本書，全體成員有得忙了唷。