I have a DataTable that I am updating inside a transaction
After each row is updated the changes on the row are implicitly accepted by
the dataAdapter and the rowstate is set to unchanged.
However If the transaction fails - Say, because a unique constraint is
violated, the transaction rolls back but the rowstate is not rolled back ant
the dataset is nolonger in sync with the database. This means that after the
errors are fixed and the update is called again, the rows which were updated
successfully first time are not updated again.
Can I Avoid this behaviour and have the rowstate rolled back with the
transaction?
'=======================================
'Assocciate comands with Data Adapter
With _DataAdapter
.InsertCommand = _InsertCommand
.UpdateCommand = _UpdateCommand
.DeleteCommand = _DeleteCommand
End With
'Asign a transaction to the commands
Dim _Transaction As SqlClient.SqlTransaction =
_Connection.BeginTransaction
_UpdateCommand.Transaction = _Transaction
_InsertCommand.Transaction = _Transaction
_DeleteCommand.Transaction = _Transaction
Try
_DataAdapter.Update(DocTypes)
_Transaction.Commit()
Catch ex As DBConcurrencyException When
ex.Message.IndexOf("affected 0 records.") > -1
_Transaction.Rollback()
Throw New BR.LookupData.DatabaseUpdateException(ex.Message)
Catch ex As Data.SqlClient.SqlException When
ex.Message.IndexOf("Violation of PRIMARY KEY constraint") > -1
_Transaction.Rollback()
Throw New BR.LookupData.DatabaseUpdateException(ex.Message)
Catch ex As Data.SqlClient.SqlException When
ex.Message.IndexOf("Violation of UNIQUE KEY constraint") > -1
_Transaction.Rollback()
Throw New BR.LookupData.DatabaseUpdateException(ex.Message)
Catch ex As Exception
_Transaction.Rollback()
Throw New BR.LookupData.DatabaseUpdateException(ex.Message)
Finally
_Connection.Close()
End Try
'================================================
