Problem Updating Adapters

[Guest]

I have a struck a problem updating adapters when there are database relationships. If I setup two adapters for the Orders, and OrderDetails tables from Northwind, and then I delete an Order with related order details, I get a constraint violation in the database (because I am updating the Order adapter first). If I swap around the order of the updates, then I get a similar exception when I do a insert, because there is no related order for the order details

I have tried wrapping all commands in a transaction. This did not work. I got the same exception raised from the adapter.update line (when I expected the Adapter.Update to be postponed until the Transaction.Commit)

This seems like quite a simple problem that should be able to be solved by transactions. Here is some sample code. Both adapters were configured using the default configuration against the orders and orderdetails table of the Northwind database. The dataset (dsOrders) simply has the Orders and OrderDetails tables on it with one relation on OrderID

Please dont reply if you dont know for sure

Thanks

Private Sub btnGet_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGet.Clic
Dim dsTemp As New dsOrder
Me.sqldaOrder.Fill(dsTemp
Me.sqldaOrderDetail.Fill(dsTemp
Me.DsOrders1.Clear(
Me.DsOrders1.Merge(dsTemp
End Su

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Clic
Dim tran As SqlTransactio
Tr
Me.SqlConnection1.Open(
tran = Me.SqlConnection1.BeginTransaction(
Me.SetupAdapter(Me.sqldaOrder, tran
Me.SetupAdapter(Me.sqldaOrderDetail, tran
Me.sqldaOrder.Update(Me.DsOrders1
Me.sqldaOrderDetail.Update(Me.DsOrders1
tran.Commit(
Catch ex As Exceptio
tran.Rollback(
Finall
Me.SqlConnection1.Close(
End Tr
End Su

Public Sub SetupAdapter(ByVal Adapter As IDbDataAdapter, ByVal Tran As IDbTransaction
Adapter.SelectCommand.Connection = Tran.Connectio
Adapter.InsertCommand.Connection = Tran.Connectio
Adapter.DeleteCommand.Connection = Tran.Connectio
Adapter.UpdateCommand.Connection = Tran.Connectio
Adapter.SelectCommand.Transaction = Tra
Adapter.InsertCommand.Transaction = Tra
Adapter.DeleteCommand.Transaction = Tra
Adapter.UpdateCommand.Transaction = Tra
End Su

 

[Miha Markic [MVP C#]]

Hi Tim,

I have a struck a problem updating adapters when there are database

relationships. If I setup two adapters for the Orders, and OrderDetails
tables from Northwind, and then I delete an Order with related order
details, I get a constraint violation in the database (because I am updating
the Order adapter first). If I swap around the order of the updates, then I
get a similar exception when I do a insert, because there is no related
order for the order details.

I have tried wrapping all commands in a transaction. This did not work.

I got the same exception raised from the adapter.update line (when I
expected the Adapter.Update to be postponed until the Transaction.Commit).

This seems like quite a simple problem that should be able to be solved by

transactions. Here is some sample code. Both adapters were configured
using the default configuration against the orders and orderdetails table of
the Northwind database. The dataset (dsOrders) simply has the Orders and
OrderDetails tables on it with one relation on OrderID.

You should do it in the right order.
First get all deleted rows from OrderDetails and delete them:
Dim deletedDetails as DataTable = DsOrders1.GetChanges(DataRowState.Deleted)
Me.sqldaOrderDetail.Update(deletedDetails)

Then do the same for orders.
After that, do the same for added rows in orders (DataRowState.Added) and
after that for OrderDetails.

For the grand finale, update all Modified rows and this is it.
Of course, don't forget to do the merge if you need to.

Please dont reply if you dont know for sure!

Demanding are you ;-)

 

[Gleb Holodov]

Dear Microsoft,

excuse me, but even respecting you for .Net as a whole, I must admit that
ADO.NET is really not as flexible as it should be, and even not as flexible
as other parts of .Net are (like remoting or XML support).
The bad thing is that some very common problems like the one Tim talks
about, force people to either write bloats of technical and similar-looking
code thus annihilating the whole idea of RAD programming or to write add-in
architectures, potentially causing problems migrating to Whidbey where
ADO.NET and integrating code.

Me, personally, have also encountered this problem and found no way
other than writing CompositeDataAdapter - a DataAdapter-derived component,
to which other data adapters could be bound at design time, and which
executes updates in the right order by calling the aforementioned GetChanges
method.

Gleb

 

[Miha Markic [MVP C#]]

I am not Microsoft, but the stuff you are talking about is called
flexibility and power.
You are free to create more abstract functionality based on provided one.
I don't think the other direction is better.

 

[Gleb Holodov]

Miha,

your thesis is absolutely correct, but it was very frustrating to see that
Microsoft didn't take into account such a common problem.

The problem is not with transactions or anything around them, unlike Tim
said, - ADO.NET seems to presume that the only DRI that exist for the data
are imposed in the DataSet, and thus there's no need to correctly sequence
updates. Treating relational database in this way seems to be common for
certain development groups inside microsoft (for ex., an old version of MSMQ
used a SQL Server database without defining relationships between tables),
but from my perspective, the tiny performance gain which is taken from
skipping FK verification in the database, will never cover up possible
inconsistencies.

Gleb