Maintain DataRowState in ADO.NET Dataset - RejectChanges() method does not work?

Question

The problem simplified:

I have a DataSet with some datatables... I have a Winforms DataGrid bound to one of the datatables. User sticks some rows into said datatable, via the DataGrid, let's say 3 rows;

All three rows now have their RowState = DataRowState.Added.

I now begin a sqlserver transaction.

Then call dataAdapter1.Update(dataSet1) to update rows into SqlServer. row 1.. OK row 2.. OK row 3.. error at the sqlserver level (by design i enforced a unique index)

Upon detecting this error, i Rollback the sqlserver transaction.

I also try to "rollback" the datatable / dataset changes, using either of Dataset1.RejectChanges() and / or Datatable1.RejectChanges().

Problem is neither of .RejectChanges() work the way i envisaged. My datatable now has two rows (row1, row2), whose RowState = DataRowState.Unchanged; row3 has disappeared altogether.

What i want to happen is, when i roll back the sqlserver transaction, for all 3 rows in the datatable to remain in the SAME STATE just prior to the call to dataAdapter1.Update() method.

(Reason is so that the user can look at the error in the bound DataGrid, take corrective action, and attempt the Update again).

Any ideas anyone? i.e. i am looking for something equivalent to rolling back the state at the ADO dataTable level.

Answer 1

Ok, so i figured a way to get around this.

Get a clone of the original datatable, and update the clone.

If an error occurs, you still have the original datatable, with its original DataRowState; Furthermore, you can copy any errors that occur in the clone to the original Datatable, thus reflecting the errors in the datagrid for the user to see.

If update is successful, you simply refresh the original datatable with the clone.

VB Code:

Try
    'daMyAdapter.Update(dsDataset, "MyDatatable") <-- replace original with below lines.
    _dtMyDatatableClone = dsDataset.MyDatatable.Copy()

    If _dtMyDatatableClone IsNot Nothing Then
      daMyAdapter.Update(_dtMyDatatableClone)

       'if you get here, update was successul - refresh now!
       dsDataset.MyDatatable.Clear()
       dsDataset.MyDatatable.Merge(_dtMyDatatableClone, False, MissingSchemaAction.Ignore)
    End If
Catch
    'uh oh, put error handler here.
End Try