bulkcopy.vb

wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重推荐,电子书,电子书下载

Option Explicit On
Option Strict On

Imports System.IO
Imports System.Data
Imports System.Data.SqlClient

Public Class BulkCopy
    Private strPath As String
    Private intCustRows As Integer
    Private intProdRows As Integer
    Private cnnNwind As SqlConnection
    Private strConn As String = "Data Source=localhost;" + _
      "Initial Catalog=Northwind;Integrated Security=SSPI"

    Private cmdCusts As SqlCommand
    Private cmdProds As SqlCommand
    Private htStats As Hashtable
    Private Sub frmBulkCopy_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim srDDL As StreamReader = Nothing
        Try
            'Create the connection and two commands
            cnnNwind = New SqlConnection(strConn)
            cmdCusts = cnnNwind.CreateCommand()
            cmdCusts.CommandType = CommandType.Text
            cmdProds = cnnNwind.CreateCommand
            cmdProds.CommandType = CommandType.Text

            strPath = Application.StartupPath + "\"

            'Open the DDL script files and set the CommandText
            Dim strCustCopyDDL As String
            If File.Exists(strPath + "CustomersCopy.sql") Then
                srDDL = New StreamReader(strPath + "CustomersCopy.sql")
                strCustCopyDDL = srDDL.ReadToEnd
                srDDL.Close()
            Else
                Dim excDDL As New Exception("CustomersCopy.sql script is missing.")
                Throw excDDL
            End If
            cmdCusts.CommandText = strCustCopyDDL

            Dim strProdCopyDDL As String
            If File.Exists(strPath + "ProductsCopy.sql") Then
                srDDL = New StreamReader(strPath + "ProductsCopy.sql")
                strProdCopyDDL = srDDL.ReadToEnd
                srDDL.Close()
            Else
                Dim excDDL As New Exception("ProductsCopy.sql script is missing")
                Throw excDDL
            End If
            cmdProds.CommandText = strProdCopyDDL

            'Open the connection, execute the DDL scripts, and fill list boxes
            cnnNwind.Open()
            cmdCusts.ExecuteNonQuery()
            FillCustsList(False)
            cmdProds.ExecuteNonQuery()
            FillProdsList(False)

        Catch exc As Exception
            MsgBox(exc.Message + exc.StackTrace, , "Exception in Form Load")
        Finally
            If srDDL IsNot Nothing Then
                srDDL.Close()
            End If
            cnnNwind.Close()
        End Try
    End Sub

    Private Sub FillCustsList(ByVal blnIsCopy As Boolean)
        'Fill the Customers list box
        lstCusts.Items.Clear()
        If blnIsCopy Then
            cmdCusts.CommandText = "SELECT CustomerID, CompanyName FROM CustomersCopy"
        Else
            cmdCusts.CommandText = "SELECT CustomerID, CompanyName FROM Customers"
        End If
        Dim sdrCusts As SqlDataReader = cmdCusts.ExecuteReader
        With sdrCusts
            While .Read
                lstCusts.Items.Add(.GetString(0) + " - " + .GetString(1))
            End While
            .Close()
        End With
    End Sub

    Private Sub FillProdsList(ByVal blnIsCopy As Boolean)
        'Fill the Products list box
        lstProds.Items.Clear()
        If blnIsCopy Then
            cmdProds.CommandText = "SELECT ProductID, ProductName FROM ProductsCopy ORDER BY ProductID"
        Else
            cmdProds.CommandText = "SELECT ProductID, ProductName FROM Products ORDER BY ProductID"
        End If
        Dim sdrProds As SqlDataReader = cmdProds.ExecuteReader
        With sdrProds
            While .Read
                lstProds.Items.Add(.GetInt32(0).ToString + " - " + .GetString(1))
            End While
            .Close()
        End With
    End Sub

    Private Sub btnCopyCusts_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCopyCusts.Click
        'Copy Customers table to CustomersCopy
        Dim sdrCusts As SqlDataReader = Nothing
        Dim sbcCusts As SqlBulkCopy = Nothing
        Try
            Dim lngTime As Long = Now.Ticks
            btnCopyCusts.Enabled = False
            cnnNwind.Open()
            cnnNwind.StatisticsEnabled = True
            'Delete records, if present
            cmdCusts.CommandText = "DELETE FROM CustomersCopy"
            Dim intRecs As Integer = cmdCusts.ExecuteNonQuery

            'Create and execute the source reader
            cmdCusts.CommandText = "SELECT * FROM Customers"
            sdrCusts = cmdCusts.ExecuteReader()

            'Create a bulk copy object with an associated connection
            sbcCusts = New SqlBulkCopy(strConn, SqlBulkCopyOptions.UseInternalTransaction)
            AddHandler sbcCusts.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf CustRowAdded)
            With sbcCusts
                'Set up the SqlBulkCopy object
                .DestinationTableName = "CustomersCopy"
                .BatchSize = CInt(nudBatchSize.Value)
                .BulkCopyTimeout = 30
                .NotifyAfter = 1
                .WriteToServer(sdrCusts)
                .Close()
            End With
            sdrCusts.Close()
            lngTime = Now.Ticks - lngTime
            txtTime.Text = Format(lngTime / 10000000, "0.000")
            'Populate the list box
            FillCustsList(True)
        Catch excCopy As Exception
            MsgBox(excCopy.Message + excCopy.StackTrace, , "Customers Bulk Copy Exception")
        Finally
            If Not sbcCusts Is Nothing Then
                sbcCusts.Close()
            End If
            If Not sdrCusts Is Nothing Then
                sdrCusts.Close()
            End If
            If Not cnnNwind Is Nothing Then
                cnnNwind.Close()
            End If
            btnCopyCusts.Enabled = True
        End Try
    End Sub

    Sub CustRowAdded(ByVal oSource As Object, ByVal oArgs As SqlRowsCopiedEventArgs)
        'Display number of rows added
        txtCustRows.Text = oArgs.RowsCopied.ToString
        Application.DoEvents()
    End Sub

    Private Sub btnCopyProds_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCopyProds.Click
        'Copy Products table to ProductsCopy
        Dim sdrProds As SqlDataReader = Nothing
        Dim sbcProds As SqlBulkCopy = Nothing
        Try
            Dim lngTime As Long = Now.Ticks
            btnCopyProds.Enabled = False
            cnnNwind.Open()
            'Delete records, if present
            cmdProds.CommandText = "DELETE FROM ProductsCopy"
            Dim intRecs As Integer = cmdProds.ExecuteNonQuery

            'Create and execute the source reader
            cmdProds.CommandText = "SELECT * FROM Products"
            sdrProds = cmdProds.ExecuteReader()

            'Create a bulk copy object with an associated connection
            If chkKeepIdentity.Checked Then
                'Specify KeepIdentity to retain ProductId values
                sbcProds = New SqlBulkCopy(strConn, _
                 SqlBulkCopyOptions.UseInternalTransaction Or _
                 SqlBulkCopyOptions.KeepIdentity)
            Else
                'Increment ProductID identity values
                sbcProds = New SqlBulkCopy(strConn, _
                 SqlBulkCopyOptions.UseInternalTransaction)
                Dim blnUseCm As Boolean = True
                If blnUseCm Then
                    sbcProds.ColumnMappings.Clear()
                    Dim intCol As Integer
                    For intCol = 1 To 9
                        sbcProds.ColumnMappings.Add(intCol, intCol)
                    Next intCol
                End If
            End If
            'Add a handler
            AddHandler sbcProds.SqlRowsCopied, New SqlRowsCopiedEventHandler(AddressOf ProdRowAdded)
            With sbcProds
                .DestinationTableName = "ProductsCopy"
                .BatchSize = CInt(nudBatchSize.Value)
                .BulkCopyTimeout = 30
                .NotifyAfter = 1
                .WriteToServer(sdrProds)
                .Close()
            End With
            sdrProds.Close()
            lngTime = Now.Ticks - lngTime
            txtTime.Text = Format(lngTime / 10000000, "0.000")
            'Populate the list box
            FillProdsList(True)
        Catch excCopy As Exception
            MsgBox(excCopy.Message + excCopy.StackTrace, , "Products Bulk Copy Exception")
        Finally
            If Not sbcProds Is Nothing Then
                sbcProds.Close()
            End If
            If Not sdrProds Is Nothing Then
                sdrProds.Close()
            End If
            If Not cnnNwind Is Nothing Then
                cnnNwind.Close()
            End If
            btnCopyProds.Enabled = True
        End Try
    End Sub

    Sub ProdRowAdded(ByVal oSource As Object, ByVal oArgs As SqlRowsCopiedEventArgs)
        'Display number of rows added
        txtProdRows.Text = oArgs.RowsCopied.ToString
        Application.DoEvents()
    End Sub

    Private Sub btnShowStats_Click(ByVal sender As System.Object, _
     ByVal e As System.EventArgs) Handles btnShowStats.Click
        'Display connection statistics
        Try
            htStats = CType(cnnNwind.RetrieveStatistics(), Hashtable)
            Dim txtStats As Control = frmConnStats.Controls.Item("txtStats")
            txtStats.Text = ""
            Dim oStat As Object
            Dim strStat As String
            For Each oStat In htStats.Keys
                strStat = oStat.ToString
                If InStr(strStat, "Time") > 0 Then
                    txtStats.Text += strStat + " = " + _
                      Microsoft.VisualBasic.Format(CLng(htStats(strStat)) / 1000, _
                      "#,##0.000") + " secs" + vbCrLf
                Else
                    txtStats.Text += strStat + " = " + htStats(strStat).ToString + vbCrLf
                End If
            Next
            frmConnStats.Show()
            frmConnStats.Controls.Item("btnClose").Focus()
        Catch excStats As Exception
            MsgBox(excStats.Message + excStats.StackTrace, , _
            "Exception Displaying Connection Statistics")
        End Try
    End Sub
End Class