Transactions / DataTable AcceptChanges

[StevenK]

I'm working on a small test application to better understand the workings of C# transactions against a SQL Server 2000 database.
I have 2 DataTables dt1 + dt2 along with 2 accompanying SqlDataAdapters sqlDA1 + sqlDA2 (each with an appropriate working UpdateCommand)
The DataTables show data from two separate tables and are displayed in two dataGrids on a simple form.
I allow editing of the data in the grids and have a button (to perform the update) with the following syntax :

SqlTransaction myTrans1 = sqConn.BeginTransaction();
sqlDA1.UpdateCommand.Transaction = myTrans1;
sqlDA2.UpdateCommand.Transaction = myTrans1;
try
{
// Perform update against first DataTable
sqlDA1.Update(dt1);
Application.DoEvents();
dt1.AcceptChanges();
// Perform update against second DataTable
sqlDA2.Update(dt2);
Application.DoEvents();
dt2.AcceptChanges();
// Attempt to commit the transaction
myTrans1.Commit();
MessageBox.Show("Transaction Committed"
}
catch (Exception ex)
{
myTrans1.Rollback();
MessageBox.Show("Transaction failed.\n\n" + ex.ToString());
}

My problem is this :
If the update against the first DataTable is OK but the second fails (on attempting to update the underlying SQL Server database) then the display in my grids still shows the attempted edits made in both of the grids (with a red exclamation mark against the failed edit in the second).
It should be noted that due to the transaction neither of the updates were performed to the respective tables (as intended)
How can I reset the data to how it was without the need of re-SQLing the database ?


Furthermore if I try to run the attempted updates against the DataTables outside of my 'try' I get an SqlException on the second (as I've fed in data that should fail) and the code does not get as far as my RollBack() catch code - however the first edit is not committed against the database so no damage is done but I do not get the rollback message as I would have hoped.

The code for this attempt was this :

SqlTransaction myTrans1 = sqConn.BeginTransaction();
sqlDA1.UpdateCommand.Transaction = myTrans1;
sqlDA2.UpdateCommand.Transaction = myTrans1;
// Perform update against first DataTable
sqlDA1.Update(dt1);
Application.DoEvents();
dt1.AcceptChanges();
// Perform update against second DataTable
sqlDA2.Update(dt2);
Application.DoEvents();
dt2.AcceptChanges();
try
{
// Attempt to commit the transaction
myTrans1.Commit();
MessageBox.Show("Transaction Committed"
}
catch (Exception ex)
{
myTrans1.Rollback();
MessageBox.Show("Transaction failed.\n\n" + ex.ToString());
}

Can anyone enlighten me as to the correct way I should be working with transactions when making data changes to more than one table (all examples seem to be for a single table update / insert) ?
Similarly can anyone tell me how I can return the data as displayed in the DataTable / dataGrid to its state prior to the failed transaction posting ?

Any help would be appreciated.

Thanks in advance.

 

[RiverGuy]

One thing you could do for the second question would be to clone the tables before you accept the changes, and if it fails, then clone them back.

Or you could do two separate try/catch blocks, and on the catch, reject the changes if you catch an exception.

 

[StevenK]

Isn't there anything I could do without having to do some clever coding ?
Steve