CODEWITHSUNDEEP
vb.nettransactions
w0051977
w0051977

Reputation: 15817

TransactionScope committing on each loop

Please see the code below:

Private Sub TransactionExample3()
        Dim objDR As SqlDataReader
        Dim objCommand As SqlCommand, objCommand2 As SqlCommand
        Dim objCon As SqlConnection
        Dim objCon2 As SqlConnection
        Dim id As Integer
        Dim list As List(Of Integer) = New List(Of Integer)
        Try
            _ConString = "Data Source=databaseserver;Initial Catalog=Person;User ID=username;Password=password;MultipleActiveResultSets=True"
            list.Add(1)
            list.Add(2)
            list.Add(3)

            For Each i As Integer In list
                Using trans = New TransactionScope()
                    objCon2 = New SqlConnection(_ConString)
                    objCon2.Open()
                    objCommand2 = New SqlCommand()
                    objCommand2.Connection = objCon2
                    Using objCon2
                        objCommand2.CommandText = "UPDATE Person SET forenames = @forenames WHERE " & _
                            " Reference = @Reference "
                        objCommand2.Parameters.AddWithValue("@forenames", i + 1)
                        objCommand2.Parameters.AddWithValue("@Reference", i)
                        objCommand2.ExecuteNonQuery()
                        objCommand2.Parameters.Clear()
                    End Using
                    trans.Complete()
                End Using
            Next

        Catch ex As Exception
            Throw
        Finally

        End Try

    End Sub

This code works i.e. on each loop the changes are committed to the database.

Now please see the code below:

Private Sub TransactionExample3()
        Dim objDR As SqlDataReader
        Dim objCommand As SqlCommand, objCommand2 As SqlCommand
        Dim objCon As SqlConnection
        Dim objCon2 As SqlConnection
        Dim id As Integer
        Try
            _ConString = "Data Source=server;Initial Catalog=Person;User ID=Username;Password=Password;MultipleActiveResultSets=True"
            objCon = New SqlConnection(_ConString)
            objCommand = New SqlCommand("SELECT top 10 * from Person")
            objCommand.Connection = objCon
            objCon.Open()
            objDR = objCommand.ExecuteReader()
            Do While objDR.Read
                objCon2 = New SqlConnection(_ConString)
                objCon2.Open()
                Using trans = New TransactionScope()
                    objCommand2 = New SqlCommand()
                    objCommand2.Connection = objCon
                    Using objCon2
                        objCommand2.CommandText = "UPDATE Person SET forenames = @forenames WHERE " & _
                            " Reference = @Reference "
                        objCommand2.Parameters.AddWithValue("@forenames", objDR("Reference") + 10)
                        objCommand2.Parameters.AddWithValue("@Reference", objDR("Reference"))
                        objCommand2.ExecuteNonQuery()
                        objCommand2.Parameters.Clear()
                    End Using
                End Using
            Loop
            objDR.Close() 'line 16
        Catch ex As Exception
            Throw
        Finally

        End Try

    End Sub

In the second code exherpt, the scope is not complete (scope.complete), however the results are still committed to the database on each iteration of the while loop. Why is this?

Upvotes: 1

Views: 1474

Answers (1)

Steve
Steve

Reputation: 216313

In the first loop the opening of the TransactionScope is before the opening of the connection. In the second one is after. The connection is not enlisted in the Transaction and thus every command executes without being held by a transaction.

Try to switch these lines

Do While objDR.Read
    Using trans = New TransactionScope()
       objCon2 = New SqlConnection(_ConString)
       objCon2.Open()
       .....

Now you need the call to trans.Complete()

Private Sub TransactionExample3()
    Dim objDR As SqlDataReader
    Dim objCommand As SqlCommand, objCommand2 As SqlCommand
    Dim objCon As SqlConnection
    Dim objCon2 As SqlConnection
    Dim id As Integer
    _ConString = "Data Source=server;Initial Catalog=Person;User ID=Username;Password=Password;MultipleActiveResultSets=True"
    Using objCon = New SqlConnection(_ConString)
        objCommand = New SqlCommand("SELECT top 10 * from Person")
        objCommand.Connection = objCon
        objCon.Open()
        objDR = objCommand.ExecuteReader()
        Using trans = New TransactionScope()
        Using objCon2 = New SqlConnection(_ConString)
            objCon2.Open()
            Do While objDR.Read
                objCommand2 = New SqlCommand()
                objCommand2.Connection = objCon
                Using objCon2
                    objCommand2.CommandText = "UPDATE Person SET forenames = @forenames WHERE " & _
                        " Reference = @Reference "
                    objCommand2.Parameters.AddWithValue("@forenames", objDR("Reference") + 10)
                    objCommand2.Parameters.AddWithValue("@Reference", objDR("Reference"))
                    objCommand2.ExecuteNonQuery()
                    objCommand2.Parameters.Clear()
                End Using
            Loop
            objDR.Close() 'line 16
        End Using
        trans.Complete()
        End Using    
    End Using
End Sub

I suggest to move the Transaction and Connection opening outside the loop and call the Complete and destroy the connection after the foreach loop, if I understand your code correctly you update one record at each loop and so the Transaction makes sense only if you want to update all of your records or none. Another minor optimization could be to move the creation of the SqlCommand and the Parameters outside the loop. You update just the parameters value inside the loop without destroying and rebuilding the parameter collection at each loop

Upvotes: 2

Related Questions