Database and Dataset setup.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

This should be a fairly simple question. With our next project, we've
decided to make use of the sql server relationships to force concurrency (no
orphaned records). We formerly were doing this on the dataset, but sometimes,
it wasn't necessary to get child data, and we could get orphaned records.

Anyways, we're having trouble on some situations where you delete a record
from the parent table. It automatically deletes the record from the child
table, causing an error when we try to update the child table. But if I set
all of the delete rules to none, it causes an exception, because it orphans
the row. As soon as the update would occur, there'd be no orphaned records.

I suppose I could do:
dataset.GetChanges(System.Data.DataRowState.Deleted).AcceptChanges()

but I wanted to make sure there was no better solution that would make more
sense.

Thanks in advance.
 
Bryce:

When you have referential integrity and cascading deletes setup on your
database, then the order in which you perform deletes does become important.
Although I do not know all of your implementation details regarding
dataadapters, etc., it would seem that you have at least two choices.

Assuming that you have a dataadapter for each table, you could call the
Update method on the parent table and let the cascading deletes remove the
child rows. In this case, you would not want to have a delete command
defined for the dataadapter that updates the child table.

A second approach would be to have a delete command for each dataadapter and
to call the Update method on the child table first. Again, these are some
broad guidelines, as I do not know the specifics of your implementation.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


Hi,

This should be a fairly simple question. With our next project, we've
decided to make use of the sql server relationships to force concurrency (no
orphaned records). We formerly were doing this on the dataset, but
sometimes,
it wasn't necessary to get child data, and we could get orphaned records.

Anyways, we're having trouble on some situations where you delete a record
from the parent table. It automatically deletes the record from the child
table, causing an error when we try to update the child table. But if I set
all of the delete rules to none, it causes an exception, because it orphans
the row. As soon as the update would occur, there'd be no orphaned records.

I suppose I could do:
dataset.GetChanges(System.Data.DataRowState.Deleted).AcceptChanges()

but I wanted to make sure there was no better solution that would make more
sense.

Thanks in advance.
 
David,

The first solution is the better choice, but it won't work like that. If
deleting the rows out of the parent table sets the child rows to the deleted
rowstate, the update routine on the datadapter needs to have a delete
command, or it'll throw an exception.

The second solution won't work, since SQL server is handling the
relationships, and this particular input form allows for adding rows to both
tables, updating the child table first would try to add the child rows before
the parent rows.

Any other ideas?

Thanks again.
 
Back
Top