VB.NET Transaction and SQLDataReader

Question

Please have a look at the code below:

Public Function Orphand() 
        Dim objCon As SqlConnection
        Dim objCommand As SqlCommand
        Dim objCommand2 As SqlCommand
        Dim objCommand3 As SqlCommand
        Dim objDR As SqlDataReader
        Dim transaction As SqlTransaction
        Try
            objCon = New SqlConnection(_ConString)
            objCommand = New SqlCommand
            objCommand2 = New SqlCommand
            objCommand3 = New SqlCommand
            objCommand.Connection = objCon
            objCommand2.Connection = objCon
            objCommand3.Connection = objCon
            objCon.Open()
            transaction = objCon.BeginTransaction
            objCommand.Transaction = transaction
            objCommand2.Transaction = transaction
            objCommand3.Transaction = transaction
            Using objCon
                objCommand.CommandText = "SELECT TOP 3 * FROM dbReviews"
                objDR = objCommand.ExecuteReader
                Do While objDR.Read
                    objCommand2.CommandText = "INSERT INTO dbReviewChangesVALUES (1,1,'1',1,'2012-01-09')"
                    objCommand2.ExecuteNonQuery()
                    objCommand3.CommandText = "INSERT INTO dbReviewChangesVALUES (1,1,'1',1,'2012-01-09')"
                    objCommand3.ExecuteNonQuery()
                Loop
                transaction.Commit()
                objDR.Close()
            End Using
        Catch ex As Exception

        End Try
    End Function
End Class

Is there a way to committ the transaction on every loop rather than waiting until all records are looped over or is this poor practice?

I have spent some time Googling this. I didn't think I would have to post a question, but I have not found an answer.

Answer 1

Yes, open a transaction inside your loop.