transactions.vb

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

Imports System.Data
Imports System.Data.SqlClient
Imports System.Transactions

Public Class Transactions
    Private lngTicks As Long
    Private strConn1 As String = "Data Source=localhost;Initial Catalog=Northwind;Integrated Security = True"
    Private strConn2 As String = "Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security = True"

    Private Sub bindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bindingNavigatorSaveItem.Click
        If Me.Validate Then
            'Modifications to Wizard-generate code start here
            Me.Order_DetailsBindingSource.EndEdit()
            Me.OrdersBindingSource.EndEdit()
            If NorthwindDataSet.HasChanges Then
                Me.Cursor = Cursors.WaitCursor
                'For implicit transactions
                Dim tsImplicit As TransactionScope = Nothing
                'For explicit transactions
                Dim tsExplicit As CommittableTransaction = Nothing
                'For implicit or explict transactions
                Dim tsOptions As New TransactionScopeOption
                'For implicit transaction options
                Dim toOptions As New TransactionOptions

                If chkTranOptions.Checked Then
                    'Transaction (RequiresNew) and TransactionScope (ReadCommitted and 15 seconds) options
                    toOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted
                    Dim tsTimeout As New TimeSpan(0, 0, 15)
                    toOptions.Timeout = tsTimeout
                    tsOptions = TransactionScopeOption.RequiresNew
                Else
                    'Defaults (Requires, Serializable, 1 minute timeout)
                    toOptions.IsolationLevel = System.Transactions.IsolationLevel.Serializable
                    Dim tsTimeout As New TimeSpan(0, 1, 0)
                    toOptions.Timeout = tsTimeout
                    tsOptions = TransactionScopeOption.Required
                End If

                If rbExplicit.Checked Then
                    If chkTranOptions.Checked Then
                        tsExplicit = New CommittableTransaction(toOptions)
                    Else
                        tsExplicit = New CommittableTransaction
                    End If
                Else
                    If chkTranOptions.Checked Then
                        tsImplicit = New TransactionScope(tsOptions, toOptions)
                    Else
                        tsImplicit = New TransactionScope
                    End If
                End If

                lngTicks = Now.Ticks
                If rbExplicit.Checked Then
                    'Open and enlist connection(s) in an explicit transaction
                    'Dim tsExplicit As New CommittableTransaction
                    Dim cnNwind As SqlConnection = Nothing
                    Try
                        Dim blnOneConn As Boolean ' = True
                        If blnOneConn Then
                            'Attempting to assign a single connection throws an exception
                            cnNwind = New SqlConnection(My.Settings.NorthwindConnectionString)
                            cnNwind.Open()
                            Me.Order_DetailsTableAdapter.Connection = cnNwind
                            Me.OrdersTableAdapter.Connection = cnNwind
                        Else
                            'Open the predefined connections
                            Me.Order_DetailsTableAdapter.Connection.Open()
                            Me.OrdersTableAdapter.Connection.Open()
                        End If
                        Me.Order_DetailsTableAdapter.Connection.EnlistTransaction(tsExplicit)
                        Me.OrdersTableAdapter.Connection.EnlistTransaction(tsExplicit)
                        Me.Order_DetailsTableAdapter.Update(Me.NorthwindDataSet.Order_Details)
                        Me.OrdersTableAdapter.Update(Me.NorthwindDataSet.Orders)
                        tsExplicit.Commit()
                        txtTime.Text = Format((Now.Ticks - lngTicks) / 10000000, "0.000")
                    Catch exc As Exception
                        tsExplicit.Rollback()
                        txtTime.Text = Format((Now.Ticks - lngTicks) / 10000000, "0.000")
                        MsgBox(exc.Message)
                    Finally
                        If cnNwind IsNot Nothing AndAlso cnNwind.State = ConnectionState.Open Then
                            cnNwind.Close()
                        End If
                        If Me.OrdersTableAdapter.Connection.State <> ConnectionState.Closed Then
                            Me.OrdersTableAdapter.Connection.Close()
                        End If
                        If Me.Order_DetailsTableAdapter.Connection.State <> ConnectionState.Closed Then
                            Me.Order_DetailsTableAdapter.Connection.Close()
                        End If
                    End Try
                    Dim strStatus As String = Nothing
                    Select Case tsExplicit.TransactionInformation.Status
                        Case TransactionStatus.Aborted
                            strStatus = "Aborted (transaction rolled back)"
                        Case TransactionStatus.Active
                            strStatus = "Active"
                        Case TransactionStatus.Committed
                            strStatus = "Committed"
                        Case TransactionStatus.InDoubt
                            strStatus = "InDoubt"
                        Case Else
                            strStatus = "Unknown"
                    End Select
                    Dim strLocalID As String = tsExplicit.TransactionInformation.LocalIdentifier
                    Dim strDistributedID As String = tsExplicit.TransactionInformation.DistributedIdentifier.ToString

                    Dim strMsg As String = "Explicit transaction status is '" + strStatus + "'." + vbCrLf + vbCrLf
                    strMsg += "LocalIdentifier: " + strLocalID + vbCrLf
                    If strDistributedID IsNot Nothing Then
                        strMsg += "DistributedIdentifier: " + strLocalID + vbCrLf
                    End If
                    MsgBox(strMsg, , "Explicit Transaction Specified")
                Else
                    'Dim tsImplicit As New TransactionScope
                    Dim trnCurrent As Transaction  '"Ambient" transaction
                    Using tsImplicit
                        trnCurrent = Transaction.Current
                        AddHandler trnCurrent.TransactionCompleted, AddressOf OnComplete
                        Dim strLocalID As String = trnCurrent.TransactionInformation.LocalIdentifier
                        'Following returns a null GUID because transaction isn't promoted
                        Dim strDistributedID As String = trnCurrent.TransactionInformation.DistributedIdentifier.ToString
                        'TransactionScope disposes when exiting Using block, which commits or rolls back the changes
                        'The Complete method sets a completed bit
                        Try
                            Dim cnNwind As SqlConnection = Nothing
                            Dim blnOpenConn As Boolean = True
                            If blnOpenConn Then
                                'Open a single connection explictly
                                cnNwind = New SqlConnection(My.Settings.NorthwindConnectionString)
                                cnNwind.Open()
                                Me.Order_DetailsTableAdapter.Connection = cnNwind
                                Me.OrdersTableAdapter.Connection = cnNwind
                            Else
                                'Adapter opens connections 
                            End If
                            Me.Order_DetailsTableAdapter.Update(Me.NorthwindDataSet.Order_Details)
                            Me.OrdersTableAdapter.Update(Me.NorthwindDataSet.Orders)
                            Dim tsStatus As TransactionStatus = trnCurrent.TransactionInformation.Status
                            txtTime.Text = Format((Now.Ticks - lngTicks) / 10000000, "0.000")
                            tsImplicit.Complete()
                        Catch exc As Exception
                            txtTime.Text = Format((Now.Ticks - lngTicks) / 10000000, "0.000")
                            Dim tsStatus As TransactionStatus = trnCurrent.TransactionInformation.Status
                            Me.Cursor = Cursors.Default
                            MsgBox(exc.Message + " (Implicit transaction has been rolled back).", , "Implicit Transaction Specified")
                        Finally
                            'Adapter closes connections automatically
                            If Me.OrdersTableAdapter.Connection.State <> ConnectionState.Closed Then
                                Me.OrdersTableAdapter.Connection.Close()
                            End If
                            If Me.Order_DetailsTableAdapter.Connection.State <> ConnectionState.Closed Then
                                Me.Order_DetailsTableAdapter.Connection.Close()
                            End If
                        End Try
                    End Using
                End If

                'Refresh DataSet to display changes persisted, if any
                Transactions_Load(Nothing, Nothing)
                Me.Cursor = Cursors.Default
            Else
                MsgBox("Dataset has no changes.", , "System.Transactions Demo")
            End If