locked
RollBack Transaction ... RRS feed

  • Question

  • I have a Sub Routine as below:

    Private Sub SaveRecord()
        Call InsertIntoTable1
        Call InsertIntoTable2
        Call InsertIntoTable3
        Call InsertIntoTable4
    End Sub

    I want to use the transaction object in such a way that it starts just before the first Insert Call and ends after the last Insert Call. The situation is that I have to save records in each table using different procedures.

    Presently, if suppose records are successfully inserted in First and Second Tables and if insertion fails in Third Table then the entries done in First and Second Table must  rollback.

    How it can be done?

    Friday, July 20, 2007 6:59 PM

Answers

  • K.Rohit,

     

    In the following code, I will do several key things to effect the transaction process.

     

    1. Create and open a Connection Object to the database.

    2. Add a Transaction object to the Connection.  The Transaction object is tied to the Connection Object until the transaction is Committed or Rolled Back.

    3. Create a Command Object and add the Connection Object to it.

    4. Create an SQL statement for each update.  For this article I am using Dynamic SQL, but in real life I would want to use a Stored Procedure.  For that, I would change the CommandText type to StoredProcedure.

    5. For each database action, I will use the same Command Object; just change the SQL statement for each new update.

    6. Call the ExecuteNonQuery method of the Command Object to do the update.

    7. If I do not get at least one row updated or inserted by each call to ExecuteNonQuery, I will throw an exception and terminate the processing, which will cause all of the database activity to be Rolled Back.

     

    Code Snippet

        Private Function CheckOut(ByVal EquipID As String, _

       ByVal CustomerID As Integer) As Integer

            ' wrap all database activity in a transaction

            ' so that all records are updated or none are.

            Dim conn As New SqlConnection("RealConnectString")

            Dim cmd As New SqlCommand

            Dim transact As SqlTransaction

            Dim Sql As String

            Dim rows As Integer

     

            Try

                conn.Open()

            Catch ex As Exception

                Return -1 ' can't open connection

            End Try

     

            transact = conn.BeginTransaction()

            cmd.Connection = conn

            Try

                ' now update the equipment table to indicate on rent

                Sql = "Update Equipment set Status = 'On Rent', "

                Sql &= "Renting_CustomerID = " & CustomerID.ToString & " "

                Sql &= "where EquipID = '" & EquipID & "'"

     

                cmd.CommandText = Sql

                rows = cmd.ExecuteNonQuery()

                If rows <> 1 Then

                    Throw New Exception( _

                     "Update of equipment record failed in checkout")

                End If

     

                ' Insert Invoice header record, use same command object

                Sql = "insert into Invoices(....) values(....)"

                cmd.CommandText = Sql

                rows = cmd.ExecuteNonQuery()

                If rows <> 1 Then

                    Throw New Exception( _

                     "insert of invoice record failed in checkout")

                End If

     

                ' insert invoice details here

                ' use same type of code as shown above

     

                ' Update customer record balance due here

                ' use same type of code as shown above

     

     

                ' if we make it here, all updates will be good

                ' commit them to the database

                transact.Commit()

                Return 1 ' check out was successful

            Catch ex As Exception

                ' if we get here some database action failed

                ' we don't want any of them to be record

                transact.Rollback()

                Return -2 ' database failed

            End Try

        End Function

     

    Wednesday, July 25, 2007 5:57 AM
  • Hi,

    I have written the following code snippet for your solution. For this, I have defined a SQL Connection and a SQL Transaction object at Global level.

    The code is as follows

     

    'Objects at Global Level

    Dim connectObj As SqlConnection

    Dim transactObj As SqlTransaction

     

    Public Sub SaveRecord()

    connectObj = New SqlConnection(yourConnectionString)

    connectObj.Open()

    transactObj = connectObj.BeginTransaction()

    Try

     

    fnUpdate()

    fnUpdate1()

    transactObj.Commit()

    MsgBox("Commit")

    Catch sqlEx As SqlException

    MsgBox("SQL Exception")

    transactObj.Rollback()

    Catch ex As Exception

    MsgBox("Exception")

    transactObj.Rollback()

    Finally

    connectObj.Close()

    End Try

    End Sub

     

    //your functions for updation

    Sub fnUpdate()

    Dim commandObj As New SqlCommand

    commandObj.Connection = connectObj

    commandObj.CommandType = CommandType.Text

    commandObj.CommandText = yourquery

    commandObj.Transaction = transactObj

    commandObj.ExecuteNonQuery()

    End Sub

     

    Sub fnUpdate1()

    Dim commandObj As New SqlCommand

    commandObj.Connection = connectObj

    commandObj.CommandType = CommandType.Text

    commandObj.CommandText = yourquery

    commandObj.Transaction = transactObj

    commandObj.ExecuteNonQuery()

    End Sub

     

     I hope this post helps you. Also try to use Stored Procedures instead of SQL Queries

     

    Regards

     

    Vineed

    Wednesday, July 25, 2007 11:46 AM

All replies

  • K.Rohit,

     

    In the following code, I will do several key things to effect the transaction process.

     

    1. Create and open a Connection Object to the database.

    2. Add a Transaction object to the Connection.  The Transaction object is tied to the Connection Object until the transaction is Committed or Rolled Back.

    3. Create a Command Object and add the Connection Object to it.

    4. Create an SQL statement for each update.  For this article I am using Dynamic SQL, but in real life I would want to use a Stored Procedure.  For that, I would change the CommandText type to StoredProcedure.

    5. For each database action, I will use the same Command Object; just change the SQL statement for each new update.

    6. Call the ExecuteNonQuery method of the Command Object to do the update.

    7. If I do not get at least one row updated or inserted by each call to ExecuteNonQuery, I will throw an exception and terminate the processing, which will cause all of the database activity to be Rolled Back.

     

    Code Snippet

        Private Function CheckOut(ByVal EquipID As String, _

       ByVal CustomerID As Integer) As Integer

            ' wrap all database activity in a transaction

            ' so that all records are updated or none are.

            Dim conn As New SqlConnection("RealConnectString")

            Dim cmd As New SqlCommand

            Dim transact As SqlTransaction

            Dim Sql As String

            Dim rows As Integer

     

            Try

                conn.Open()

            Catch ex As Exception

                Return -1 ' can't open connection

            End Try

     

            transact = conn.BeginTransaction()

            cmd.Connection = conn

            Try

                ' now update the equipment table to indicate on rent

                Sql = "Update Equipment set Status = 'On Rent', "

                Sql &= "Renting_CustomerID = " & CustomerID.ToString & " "

                Sql &= "where EquipID = '" & EquipID & "'"

     

                cmd.CommandText = Sql

                rows = cmd.ExecuteNonQuery()

                If rows <> 1 Then

                    Throw New Exception( _

                     "Update of equipment record failed in checkout")

                End If

     

                ' Insert Invoice header record, use same command object

                Sql = "insert into Invoices(....) values(....)"

                cmd.CommandText = Sql

                rows = cmd.ExecuteNonQuery()

                If rows <> 1 Then

                    Throw New Exception( _

                     "insert of invoice record failed in checkout")

                End If

     

                ' insert invoice details here

                ' use same type of code as shown above

     

                ' Update customer record balance due here

                ' use same type of code as shown above

     

     

                ' if we make it here, all updates will be good

                ' commit them to the database

                transact.Commit()

                Return 1 ' check out was successful

            Catch ex As Exception

                ' if we get here some database action failed

                ' we don't want any of them to be record

                transact.Rollback()

                Return -2 ' database failed

            End Try

        End Function

     

    Wednesday, July 25, 2007 5:57 AM
  • Hi,

    I have written the following code snippet for your solution. For this, I have defined a SQL Connection and a SQL Transaction object at Global level.

    The code is as follows

     

    'Objects at Global Level

    Dim connectObj As SqlConnection

    Dim transactObj As SqlTransaction

     

    Public Sub SaveRecord()

    connectObj = New SqlConnection(yourConnectionString)

    connectObj.Open()

    transactObj = connectObj.BeginTransaction()

    Try

     

    fnUpdate()

    fnUpdate1()

    transactObj.Commit()

    MsgBox("Commit")

    Catch sqlEx As SqlException

    MsgBox("SQL Exception")

    transactObj.Rollback()

    Catch ex As Exception

    MsgBox("Exception")

    transactObj.Rollback()

    Finally

    connectObj.Close()

    End Try

    End Sub

     

    //your functions for updation

    Sub fnUpdate()

    Dim commandObj As New SqlCommand

    commandObj.Connection = connectObj

    commandObj.CommandType = CommandType.Text

    commandObj.CommandText = yourquery

    commandObj.Transaction = transactObj

    commandObj.ExecuteNonQuery()

    End Sub

     

    Sub fnUpdate1()

    Dim commandObj As New SqlCommand

    commandObj.Connection = connectObj

    commandObj.CommandType = CommandType.Text

    commandObj.CommandText = yourquery

    commandObj.Transaction = transactObj

    commandObj.ExecuteNonQuery()

    End Sub

     

     I hope this post helps you. Also try to use Stored Procedures instead of SQL Queries

     

    Regards

     

    Vineed

    Wednesday, July 25, 2007 11:46 AM