Need help (Update with transaction)

[Richard]

Hi,
I have 1 dataset with 2 tables (Table1 as parent, Table2 as Child), 1 row in
both the tables.
I am updating it with a transaction. First parent then child.
When child update fails, it raise an exception, and rollback the parent
update. But it also change the rowstate of the parent row from Added to
unchanged
When you try to save again parent row never get created in database. So
child update will fail again.
Please suggest what am I suppose to do to update both parent and child.
This is what I am doing. This is just a SAMPLE code.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim sTransaction As SqlClient.SqlTransaction
SqlConnection1.Open()
sTransaction = SqlConnection1.BeginTransaction
Try
daTable1.InsertCommand.Transaction = sTransaction
daTable1.UpdateCommand.Transaction = sTransaction
daTable1.DeleteCommand.Transaction = sTransaction
daTable1.Update(ds)

daTable2.InsertCommand.Transaction = sTransaction
daTable2.UpdateCommand.Transaction = sTransaction
daTable2.DeleteCommand.Transaction = sTransaction
daTable2.Update(ds)
sTransaction.Commit()
Catch ex As Exception
sTransaction.Rollback()
MsgBox(ex.Message)
Finally
SqlConnection1.Close()
End Try
End Sub

Please advise me.Thanks in advance,
RC

 

[David Browne]

Hi,
I have 1 dataset with 2 tables (Table1 as parent, Table2 as Child), 1 row
in both the tables.
I am updating it with a transaction. First parent then child.
When child update fails, it raise an exception, and rollback the parent
update. But it also change the rowstate of the parent row from Added to
unchanged

The DataSet is not transactional, so you need to preserve a copy of your
DataSet's before state in order to restore it after a rollback.

One way to do this is to use DataSet.GetChanges() to get a new DataSet
containing only the changes to your main DataSet. Use the changes DataSet
for the DataAdapter, and then, only on success, run ds.AcceptChanges.

A caviat here is that if the update procedure generates any changes to the
DataSet, you would have to marshal them back to the original DataSet. In
the case of IDENTITY columns, this can be a pain.

Another option is to make a complete copy of your DataSet, attempt the
update, and if it fails, replace your DataSet with the pre-update copy.

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim sTransaction As SqlClient.SqlTransaction
SqlConnection1.Open()
sTransaction = SqlConnection1.BeginTransaction
Try
daTable1.InsertCommand.Transaction = sTransaction
daTable1.UpdateCommand.Transaction = sTransaction
daTable1.DeleteCommand.Transaction = sTransaction

dim changes as DataSet = ds.GetChanges()

daTable1.Update(changes)

daTable2.InsertCommand.Transaction = sTransaction
daTable2.UpdateCommand.Transaction = sTransaction
daTable2.DeleteCommand.Transaction = sTransaction
daTable2.Update(changes)

'marshal any db-originated updates back to ds
ds.AcceptChanges

sTransaction.Commit()
Catch ex As Exception

'nothing to do to ds since no changes have been applied.

sTransaction.Rollback()
MsgBox(ex.Message)
Finally
SqlConnection1.Close()
End Try
End Sub

David