Transactions with untyped dataset

[belidzs]

I'm trying to post changes using an untyped dataset and a stored procedure in the same transaction using dotConnect Professional. The problem is that if posting dataset succeeds, but the stored procedure afterwards doesn't, rolling back the transaction doesn't roll back the dataset to its original state, the dataset looks like it has been successfully commited. Is there any solution to avoid this problem?

[Pinturiccio]

Could you please send us a small test project with DDL/DML scripts for reproducing the issue.

[belidzs]

I've sent the requested files

[Pinturiccio]

When you execute the Rollback method, you do not make changes to the database. At the same time, by calling the Update method for your table, you changed the state of the added row from RowState.Added to RowState.Unchanged.
The Commit and Rollback functions influence only the changes made to the database. After calling the Rollback function, the situation can occur, when data on the server and on the client in the dataset will desynchronize, in this case you will have to synchronize the row statuses yourself.

[belidzs]

Could you provide an example how to do it?

[Pinturiccio]

In your test project you add one dr row to PgSqlDataTable. I'm posting here an example for this row. However you have to track all rows being added or changed in your table yourself in your code.

For the dr row you have to add the following code in the catch block.

Code: Select all

catch (PgSqlException) 
{
            //rolling back
            pgSqlConnection1.Rollback();
            dr.SetAdded(); // table1Table.Update() will return 1
}

[belidzs]

I was afraid of this. Thank you however!