transactions.vb

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

        Else
            System.Windows.Forms.MessageBox.Show(Me, "Validation errors occurred.", "System.Transactions Demo", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Warning)
        End If
    End Sub

    Public Sub OnComplete(ByVal sender As Object, ByVal e As TransactionEventArgs)
        'TransactionStatus always is Active (0), never Committed or Aborted when this event fires
        Dim tiStatus As TransactionStatus = e.Transaction.TransactionInformation.Status
    End Sub

    Private Sub DataAdapterTransactions(ByVal blnReset As Boolean)
        'Typical code to implement ADO.NET 1.x SqlTransactions for DataAdapters
        Me.Cursor = Cursors.WaitCursor
        Dim trnUpdate As SqlTransaction = Nothing
        Dim cnNwind As New SqlConnection(My.Settings.NorthwindConnectionString)
        Dim dsNwind As New DataSet("dsNwind")
        Try
            'Create DataAdapter, CommandBuilder, and generate/assign Commands
            Dim daOrders As New SqlDataAdapter("SELECT * FROM Orders WHERE OrderID > 11077;", cnNwind)
            Dim cbOrders As SqlCommandBuilder = New SqlCommandBuilder(daOrders)
            daOrders.UpdateCommand = cbOrders.GetUpdateCommand
            daOrders.InsertCommand = cbOrders.GetInsertCommand
            daOrders.DeleteCommand = cbOrders.GetDeleteCommand

            Dim daDetails As New SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID > 11077;", cnNwind)
            Dim cbDetails As New SqlCommandBuilder(daDetails)
            daDetails.UpdateCommand = cbDetails.GetUpdateCommand
            daDetails.InsertCommand = cbDetails.GetInsertCommand
            daDetails.DeleteCommand = cbDetails.GetDeleteCommand

            'Fill the DataTables with DataAdapters
            cnNwind.Open()
            daOrders.Fill(dsNwind, "Orders")
            daDetails.Fill(dsNwind, "OrderDetails")

            'Disconnect the DataAdapters
            cnNwind.Close()

            'Update the DataSet's Orders and OrderDetails DataTables
            Dim dtOrders As DataTable = dsNwind.Tables("Orders")
            Dim intRow As Integer
            For intRow = 0 To dtOrders.Rows.Count - 1
                If blnReset Then
                    dtOrders.Rows(intRow).Item("ShippedDate") = DBNull.Value
                Else
                    dtOrders.Rows(intRow).Item("ShippedDate") = Today.ToShortDateString
                End If
            Next intRow

            Dim dtDetails As DataTable = dsNwind.Tables("OrderDetails")
            For intRow = 0 To dtDetails.Rows.Count - 1
                If blnReset Then
                    dtDetails.Rows(intRow).Item("Quantity") = dtDetails.Rows(intRow).Item("Quantity") - 1
                Else
                    dtDetails.Rows(intRow).Item("Quantity") = dtDetails.Rows(intRow).Item("Quantity") + 1
                End If
            Next intRow

            If chkViolateConstraint.Checked Then
                'Create a foreign-key constraint to force a rollback
                dtDetails.Rows(intRow - 1).Item("OrderID") = 100
            End If

            lngTicks = Now.Ticks

            Dim blnSysTran As Boolean = True
            If blnSysTran Then
                'cnNwind.Open() 'Opening here disables enlistment (no transaction)
                Dim tsExplicit As New TransactionScope
                Using tsExplicit
                    Try
                        'cnNwind.Open() 'Opening here uses one connection for transaction
                        daOrders.Update(dsNwind, "Orders")
                        daDetails.Update(dsNwind, "OrderDetails")
                        tsExplicit.Complete()
                    Catch exc As Exception
                        MsgBox(exc.Message)
                    Finally
                        cnNwind.Close()
                    End Try
                End Using
            Else
                'Open the connection to update the database
                cnNwind.Open()

                'Start a new Transaction and assign it to each Command
                trnUpdate = cnNwind.BeginTransaction
                daOrders.UpdateCommand.Transaction = trnUpdate
                daOrders.InsertCommand.Transaction = trnUpdate
                daOrders.DeleteCommand.Transaction = trnUpdate
                daOrders.Update(dsNwind, "Orders")

                daDetails.UpdateCommand.Transaction = trnUpdate
                daDetails.InsertCommand.Transaction = trnUpdate
                daDetails.DeleteCommand.Transaction = trnUpdate
                daDetails.Update(dsNwind, "OrderDetails")

                trnUpdate.Commit()
            End If
            txtTime.Text = Format((Now.Ticks - lngTicks) / 10000000, "0.000")
        Catch exc As Exception
            If trnUpdate IsNot Nothing Then
                trnUpdate.Rollback()
            End If
            txtTime.Text = Format((Now.Ticks - lngTicks) / 10000000, "0.000")
            Me.Cursor = Cursors.Default
            MsgBox(exc.Message + " The SqlTransaction has been rolled back.")
        Finally
            cnNwind.Close()
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Private Sub Transactions_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Set below true to open a second connection
        Dim blnNewConn As Boolean
        If blnNewConn Then
            Me.Order_DetailsTableAdapter.Connection.ConnectionString = strConn2
        End If
        'Wizard-generated code
        'TODO: This line of code loads data into the 'NorthwindDataSet.Order_Details' table. You can move, or remove it, as needed.
        Me.Order_DetailsTableAdapter.Fill(Me.NorthwindDataSet.Order_Details)
        'TODO: This line of code loads data into the 'NorthwindDataSet.Orders' table. You can move, or remove it, as needed.
        Me.OrdersTableAdapter.Fill(Me.NorthwindDataSet.Orders)
        Me.Order_DetailsTableAdapter.Connection.Close()
        Me.OrdersTableAdapter.Connection.Close()
    End Sub

    Private Sub btnAdapterUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdapterUpdate.Click
        'Add ShippedDate (Today) and 1 to Order Details quantity
        DataAdapterTransactions(False)
        If chkUpdatesInGrid.Checked Then
            Transactions_Load(Nothing, Nothing)
        End If
    End Sub

    Private Sub btnAdapterReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdapterReset.Click
        'Revert the changes to the DataTables
        DataAdapterTransactions(True)
        If chkUpdatesInGrid.Checked Then
            Transactions_Load(Nothing, Nothing)
        End If
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        bindingNavigatorSaveItem.PerformClick()
    End Sub

    Private Sub rbImplicit_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbImplicit.CheckedChanged
        txtTime.Text = ""
    End Sub

    Private Sub chkTranOptions_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkTranOptions.CheckedChanged
        txtTime.Text = ""
    End Sub

    Private Sub chkUpdatesInGrid_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkUpdatesInGrid.CheckedChanged
        txtTime.Text = ""
    End Sub

    Private Sub chkViolateConstraint_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkViolateConstraint.CheckedChanged
        txtTime.Text = ""
    End Sub
End Class