I want to update the records batch wise in SqlTransaction. In every batch i am updating 50 records. If any one record fails to update i want rollback those 50 records. I have written this code in while loop. But i am getting following error randomly. 'The commit transaction request has no corresponding begin transaction'.
Following is my code:
Dim _completed as Integer=0
Dim sqlCon As SqlClient.SqlConnection = Nothing
Dim sqlTransaction As SqlClient.SqlTransaction = Nothing
Dim lstq as List(of SqlCommand) 'It contains list of update queries
sqlCon = New SqlClient.SqlConnection(AIMSCommon.sqlServerConnection)
sqlCon.Open()
While True
Dim _res = lstq.Skip(_completed).Take(40)
If _res.Count = 0 Then Exit While
If sqlCon.State <> ConnectionState.Open Then
sqlCon = New SqlClient.SqlConnection(AIMSCommon.sqlServerConnection)
sqlCon.Open()
End If
Try
sqlTransaction = sqlCon.BeginTransaction()
For Each cls In _res
Try
cls.Connection = sqlCon
cls.Transaction = sqlTransaction
Dim i As Integer = cls.ExecuteNonQuery()
cls.Dispose()
Catch ex As Exception
If cls IsNot Nothing Then
cls.Dispose()
End If
Throw ex
End Try
Next
sqlTransaction.Commit()
sqlTransaction.Dispose()
sqlTransaction = Nothing
_completed += _res.Count
Catch ex As Exception
sqlCon.Close()
sqlCon.Dispose()
If sqlTransaction IsNot Nothing Then
sqlTransaction.Rollback()
sqlTransaction.Dispose()
End If
If sqlCon IsNot Nothing Then
sqlCon.Close()
sqlCon.Dispose()
End If
End Try
End While
If sqlCon IsNot Nothing Then
sqlCon.Close()
sqlCon.Dispose()
End If
