Problem with saving changes of DataSet to database with relations

[x5Rb]

Database: 2 parent tables + 1 child table (child of both parent tables), enforcing foreign key constraints

App: winformsapp, stuff (dataset, datatables, tableadatpers) generated by designer

Thanks constraints (server's database) I must save changes in this order:
1. If I'm adding rows:
a) add parents rows
b) then add child rows
2. If I'm removing rows:
a) add child rows
b) then add parent rows
3. If I'm changing/updating --> doesn't matter

So I cannot use tableadapter's Update method, because I need to isolate updating of added rows (DataRowState.Added) and removed rows (DataRowState.Deleted).

My datatables use datacolumns with Expression property which uses Parent/Child relationship. So datable's GetChanges throws an exception (getchanges method creates a clone with no parents/children).

DataSet's GetChanges doesn't work with added rows, because during update operation is primary key updated from database (you know, something like: insert into .... ; select ... from ... where primarykey = SCOPE_IDENTITY()) and after merging I have 2 copies of each of added row.

I'm tired. I was googling for long time (and also studing help, trying own solutions) but I really didn't find answer anywhere.


Any HELP GREATLY APPRECIATED.


NOTE: please don't tell me anything like this:
don't use FK constraints in database, don't use datacolumn's expressions, have you defined keys on your tables?, etc.

I would be also happy to use deigner's generated code because datatables are quiet complex.



Technical details:
MS SQL Server Express Edition
.NET Framework 2.0
Windows Application
.Net SqlClient Data Provider
Microsoft Visual Studio 2005 Express Edition

[Shashi Sadasivan]

1. If I'm adding rows:
a) add parents rows
b) then add child rows
2. If I'm removing rows:
a) add child rows
b) then add parent rows
3. If I'm changing/updating --> doesn't matter

Please chage that to
1. If I'm adding rows:
a) add parents rows
b) then add child rows
2. If I'm removing rows:
a) remove child rows
b) then remove parent rows
3. If I'm editing --> doesn't matter

I understand that it was a typo, but should work.

[x5Rb]

Thanks, you're right, but I can't edit it.

BTW: if you have any suggestions, post it, please.

[Shashi Sadasivan]

Thanks, you're right, but I can't edit it.

BTW: if you have any suggestions, post it, please.

Does it throw any sort of error/exception or it just plainly cpmpletes the operation, but does no change to the DB?

[x5Rb]

Does it throw any sort of error/exception or it just plainly cpmpletes the operation, but does no change to the DB?

DB (on the server) is correctly updated.

Maybe I didn't write it clearly, but:
1. I can't simply use dataadapter method to not violate (server-side) FK constraint.
2. If I use dataset's getchanges+dataadpater's update+dataset's merge it doesn't merge added rows correctly (client-side has 2 copies of the same row with different primary key values).

I could simply reload all data after update but that's what I don't want to do.

[x5Rb]

OK, let it be, I solved it QAD (quick and dirty):

1. PKs of all tables in client-side: autoincrement step=-1, autoincrement seed=-1
2. PKs of all tables in DB: autoincrement step=1, autoincrement seed=1
3. after dataset's Merge I look up and delete all rows with negative primary keys

[x5Rb]

I am here again, my QAD doesn't work. ADO.NET is crap.

For my example:

mydataset.GetChanges(); //returns also rows with DataRowState.Unchanged
 
mytable1TableAdapter.Update(mydataset.mytable1); //also child's rows are modified to DataRowState.Unchanged, so they will not be updated!!!