By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,844 Members | 836 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,844 IT Pros & Developers. It's quick & easy.

Problem with saving changes of DataSet to database with relations

P: 5
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
Oct 19 '07 #1
Share this Question
Share on Google+
6 Replies


Shashi Sadasivan
Expert 100+
P: 1,435
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.
Oct 19 '07 #2

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

BTW: if you have any suggestions, post it, please.
Oct 19 '07 #3

Shashi Sadasivan
Expert 100+
P: 1,435
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?
Oct 19 '07 #4

P: 5
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.
Oct 19 '07 #5

P: 5
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
Oct 19 '07 #6

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

For my example:

Expand|Select|Wrap|Line Numbers
  1. mydataset.GetChanges(); //returns also rows with DataRowState.Unchanged
  2.  
  3. mytable1TableAdapter.Update(mydataset.mytable1); //also child's rows are modified to DataRowState.Unchanged, so they will not be updated!!!
Oct 20 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.