craigmac

I have a number of tableadapters which must update under one transaction.

After a transaction failure I notice that the data in the (SQL Server 2005) database has rolled back but the data in the datatables on the middle tier has not.

This creates problems as some of the tables contain data on which other data tables are dependent. In particular, identity fields on these datatables are left populated with the identity from the failed insert.

Is there a way to accomplish a rollback of data in these datatables without resorting to datatable.clear() followed by datatable.Add[tablename]row

Regards.



Re: Transactions Programming tableadapter update inside failed transactionscope doesn't rollback the datatable

craigmac

Nevermind.

I've found a workaround.

  1. Copy the datatable before the save
  2. After the save clear the main datatable
  3. Merge the copy back in

Works a treat.





Re: Transactions Programming tableadapter update inside failed transactionscope doesn't rollback the datatable

Jesse - MSFT

While this may work now, it's still a dangerous and non-scalable way of doing rollback.

Can you describe how you write to the tables in the middle tier If you are using API's that understand System.Transactions to connect to them, for instance, those data tables should have no problem rolling back when the transaction aborts.

If you are using such API's, note that the connection to the database must be done inside the TransactionScope in order for data read/written over the connection to be transacted.





Re: Transactions Programming tableadapter update inside failed transactionscope doesn't rollback the datatable

craigmac

All the work that gets done against the DataTables (inside DataSets) is accessed through methods and properties exposed in partial classes attached to the xsd files.

Generally I have a public property that exposes the New[tablename]Row method of the DataSet. I call this from outwith the DataSet and fill its properties. I then pass the row back into the partial class through an Insert method of my own devising that uses the DataTable.Rows.Add(DataRow) method to add it to the DataTable.

Is there a better way Perhaps using the Add[tablename]Row method directly on the DataTable

Craig





Re: Transactions Programming tableadapter update inside failed transactionscope doesn't rollback the datatable

Jesse - MSFT

Ideally, if the DataTable and DataSet participated in the transaction like SQL does, you would have to do nothing -- it would just be automatic.

You may have better luck asking this specific question on the System.Data forums to see if they have any answers or ideas how to make this work reliably.





Re: Transactions Programming tableadapter update inside failed transactionscope doesn't rollback the datatable

Dumitru Sbenghe

DataTables/DataSets are not TransactionScope aware and will not rollback when the transaction is failing.

An ¡°elegant¡± way to solve this problem is to implement a volatile resource manager which will manage the rollback of your datatables/datasets.





Re: Transactions Programming tableadapter update inside failed transactionscope doesn't rollback the datatable

Erick Thompson - MSFT

If you set the Transaction Property on the Update/Delete/Insert SqlCommand used by the TableAdapter, you can have the operations that the DataAdapter performs inside of a Transaction. This will help make sure that your updates occur all together or not at all.

However, this does not address the issue of data being out of date in your middle tier. However, all is not lost. While DataSet does not have the idea of a transaction, is does have the AcceptChanges/RejectChanges idea, which is somewhat similar. After you call a Update, AcceptChanges is automatically called, unless an exception occurs or you set AcceptChangesDuringUpdate property to false. If you have a way for the SqlCommands to be nofitied of a transaction failure (for example, from a T-SQL exception), you can then call RejectChanges, which will roll back all your middle tier DataSet changes since the last AcceptChanges call. As long as you only call AcceptChanges after a successful database operation, the effect is the same as rolling back to the state after the last successful database operation.

Let me know if you have any questions.

Erick