'____________________________________________________

'                  S Q L   T R A N S A C T I O N S

'____________________________________________________


'Updated 11/4/08
'This shows how to use a transaction. This means you need to process two or more operations on the DB
'and if any of them fail, you need to halt the whole process (roll back the transaction). Otherwise, you commit the transaction.

'This demo adds a couple records...one to each of two tables. 
'When the program starts, it removes these records so you can start fresh each time. See DeleteExtraRecords()


Imports System.Data
Imports System.Data.SqlClient



Public Class Form1

   
    Private NorthwindConnection As New SqlConnection("Data Source=.\sqlexpress2005;Initial Catalog=Northwind;Integrated Security=True")

    Private Sub PerformTransactionButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PerformTransactionButton.Click
        PerformTransaction()
    End Sub


    Private Sub PerformTransaction()
        Try
            ' Remove the records from this example
            ' so it works each time the button is clicked!
            DeleteExtraRecords()

            ' Create the transaction
            Dim transaction As SqlTransaction

            ' Create 2 commands to execute in the transaction
            Dim myRegionCommand As New SqlCommand("INSERT INTO Region" & _
                                           "(RegionID, RegionDescription)" & _
                                            "VALUES (10,'Northwest')", NorthwindConnection)
            Dim myTerritoryCommand As New SqlCommand("INSERT INTO Territories" & _
                                                   "(TerritoryID, TerritoryDescription, RegionID)" & _
                                                    "VALUES (98012, 'Bothell', 10)", NorthwindConnection)


            ' Open the connection and begin the transaction
            NorthwindConnection.Open()
            transaction = NorthwindConnection.BeginTransaction()


            ' Set the commands to execute within the transaction
            myRegionCommand.Transaction = transaction
            myTerritoryCommand.Transaction = transaction

            ' Execute the commands
            myRegionCommand.ExecuteNonQuery()
            myTerritoryCommand.ExecuteNonQuery()


            ' After executing the commands display a dialog
            ' that allows the user to complete or abort the 
            ' transaction.
            Dim response As DialogResult = MessageBox.Show("Commands have already been executed." _
                & Environment.NewLine & "Proceed with transaction?", "Performing Transaction", MessageBoxButtons.YesNo)

            ' Process the response and either 
            ' Commit or Rollback the transaction.
            Select Case response
                Case Windows.Forms.DialogResult.Yes
                    transaction.Commit()
                Case Windows.Forms.DialogResult.No
                    transaction.Rollback()
            End Select


        Catch ex As Exception
            MessageBox.Show("An error occurred during this process..." & ex.Message)
        Finally
            NorthwindConnection.Close()

        End Try
    End Sub



    Private Sub DeleteExtraRecords()
        ' Clear these records if they exist.
        Dim Command1 As New SqlCommand("DELETE FROM Territories WHERE TerritoryID = 98012; DELETE FROM Region WHERE RegionID = 10", NorthwindConnection)
        NorthwindConnection.Open()
        Command1.ExecuteNonQuery()
        NorthwindConnection.Close()
    End Sub



    
End Class