SqlTransaction Rollback not undoing changes to DataTable

[Colin Svingen]

Hello,

I have a DataTable with an autoincrement primary key that is retrieved
from the database after insert. The Primary key is returned with the
following code:
this.DataAdapter.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;

When I execute my InsertCommand within an SqlTransaction the rows in the
DataTable are updated with the autogenerated key from SQL Server.
However, when one of the inserts fail and I rollback the entire
transaction, all my DataRows are left with the invalid keys from the
database and a RowState of DataRowState.Unchanged instead of
DataRowState.Added.

So, when I rerun this code:
this.DataAdapter.Update(this.Table);

nothing happens. No inserts are performed. This kind of defeats the
purpose of having an SqlTransaction. No changes are made to the
database, but permanent changes are made to the DataTable. Is this a
bug? A feature? Can anyone help me with a workaround?


Colin Svingen

 

[David Sceppa]

Colin,

This behavior is by design. The DataTable has no knowledge of the
SqlTransaction. When you submit the pending inserts, the DataAdapter calls
the AcceptChanges on each DataRow so the rows are no longer marked as
pending changes.

If you need to revert the DataTable back to its state at the start of
the transaction, you'll need to store that data somewhere. One option is
to call DataTable.Copy() and save the return value in another variable.
You could also store the contents of your DataSet to a file or in memory.

While this probably was not the response you were hoping for, I hope
this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.

 

[Miha Markic [MVP C#]]

Hi Colin,

Use DataTable.GetChanges() and pass the changes to update method.
After you've done updating and if successful, use Merge method to merge the
changes with original data.