timestamp.vb

来自「wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重」· VB 代码 · 共 170 行

Option Explicit On
Option Strict On
Imports System.Data
Imports System.Data.SqlClient

Public Class TimeStamp
    'Note: Running this project requires adding a timestamp column 
    '(named 'timestamp') to the Orders table with the AddTimeStamp.sql script
    Private strConn As String = "Server=localhost;Database=Northwind;Integrated Security=True"
    Private cnNwind As SqlConnection
    Private dsNwind As New DataSet
    Dim daOrdersTS As New SqlDataAdapter

    Private Sub TimeStamp_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            cnNwind = New SqlConnection(strConn)
            Dim strSQL As String = "SELECT * FROM Orders"
            daOrdersTS.SelectCommand = New SqlCommand(strSQL, cnNwind)

            'Create the command builder and specify timestamp (a.k.a. rowversion)
            Dim cbOrdersTS As New SqlCommandBuilder(daOrdersTS)
            cbOrdersTS.ConflictOption = ConflictOption.CompareRowVersion

            'Make the commands available for reading
            Dim strUpdate As String = cbOrdersTS.GetUpdateCommand.CommandText
            Dim strDelete As String = cbOrdersTS.GetDeleteCommand.CommandText

            'Use DataAdapter to open connection, fill the DataSet, and close the connection
            daOrdersTS.Fill(dsNwind, "OrdersTS")

            'Delegate the RowUpdated event handler
            AddHandler daOrdersTS.RowUpdated, AddressOf BatchUpdated

            'Connect the DataGridView
            With dgvOrdersTS
                .DataSource = dsNwind
                .DataMember = "OrdersTS"
                'DataGridView interprets timestamp (Byte()) column as image (exception)
                .Columns.Remove("timestamp")
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
            End With
        Catch exc As Exception
            MsgBox(exc.Message)
        Finally
            If cnNwind.State = ConnectionState.Open Then
                cnNwind.Close()
            End If
        End Try
    End Sub

    Private Sub BatchUpdated(ByVal sender As Object, ByVal e As SqlRowUpdatedEventArgs)
        'Event fires for each row or batch completed or failed
        Dim intRowsPerBatch As Integer = e.RowCount
        Dim drRow As DataRow = e.Row
        Dim usStatus As UpdateStatus = e.Status
        'Count rows or batches
        txtBatches.Text = (CInt(txtBatches.Text) + 1).ToString
    End Sub

    Private Sub btnIncrDate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnIncrDate.Click
        ChangeOrderDate(False)
        btnDecrDate.Enabled = True
        btnUpdateTable.Enabled = True
    End Sub

    Private Sub btnDecrDate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDecrDate.Click
        ChangeOrderDate(True)
        btnUpdateTable.Enabled = True
    End Sub

    Private Sub ChangeOrderDate(ByVal blnDecrement As Boolean)
        'Use the DataGridView to change the OrderDate
        Me.Cursor = Cursors.WaitCursor
        Dim intRow As Integer
        Dim datOrder As DateTime
        Dim datNewDate As DateTime
        With dgvOrdersTS
            .EditMode = DataGridViewEditMode.EditOnEnter
            .SuspendLayout()
            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
            For intRow = 0 To .Rows.Count - 2
                .CurrentCell = .Rows(intRow).Cells(3)
                datOrder = CType(.CurrentCell.Value, DateTime)
                'Add or subtract 1 day from the OrderDate
                If blnDecrement Then
                    datNewDate = datOrder.AddDays(-1)
                Else
                    datNewDate = datOrder.AddDays(1)
                End If
                .CurrentCell.Value = datNewDate
                '.EndEdit(DataGridViewDataErrorContexts.Commit)
            Next
            .ResumeLayout()
            'Applying EndEdit here is faster
            .EndEdit(DataGridViewDataErrorContexts.Commit)
            .CurrentCell = .Rows(0).Cells(3)
            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
        End With
        Me.Cursor = Cursors.Default
    End Sub

    Private Sub btnUpdateTable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateTable.Click
        Try
            If dsNwind.HasChanges Then
                txtBatches.Text = "0"
                Me.Cursor = Cursors.WaitCursor
                'Update the OrdersTS table
                daOrdersTS.UpdateBatchSize = CType(nudBatchSize.Value, Integer)

                Dim lngTime As Long = Now.Ticks
                daOrdersTS.Update(dsNwind, "OrdersTS")
                lngTime = Now.Ticks - lngTime
                txtTime.Text = Format(lngTime / 10000000, "0.000")

                btnUpdateDataSet.Enabled = True
                Me.Cursor = Cursors.Default
            Else
                MsgBox("DataSet has no changes.")
                txtTime.Text = "0.000"
            End If
        Catch exc As Exception
            'Concurrency exception; paint error icons
            dgvOrdersTS.Refresh()
            Application.DoEvents()
            MsgBox(exc.Message)
        Finally
            If cnNwind.State = ConnectionState.Open Then
                cnNwind.Close()
            End If
            Me.Cursor = Cursors.Default
            btnUpdateTable.Enabled = False
        End Try
    End Sub

    Private Sub btnUpdateDataSet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateDataSet.Click
        'Resynchronize the DataSet and DataGridView with the base table
        Me.Cursor = Cursors.WaitCursor
        With dgvOrdersTS
            .DataMember = Nothing
            .DataSource = Nothing
            .Rows.Clear()
            'Prevent error on filling before removing the timestamp column
            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.None
            dsNwind = New DataSet
            daOrdersTS.Fill(dsNwind, "OrdersTS")
            .DataSource = dsNwind
            .DataMember = "OrdersTS"
            .Columns.Remove("timestamp")
            .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells
        End With
        Me.Cursor = Cursors.Default
        txtTime.Text = "0.000"
        btnUpdateDataSet.Enabled = False
    End Sub

    Private Sub chkBatchMode_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkBatchMode.CheckedChanged
        'Set batchsize control
        If chkBatchMode.Checked Then
            nudBatchSize.Enabled = True
            nudBatchSize.Value = 20
            lblBatches.Text = "Batches"
        Else
            nudBatchSize.Enabled = False
            nudBatchSize.Value = 1
            lblBatches.Text = "Rows"
        End If
        txtBatches.Text = "0"
    End Sub
End Class