Q: Parent Child Update Problem

  • Thread starter Thread starter G .Net
  • Start date Start date
G

G .Net

Hi Everybody

I was wondering if anybody could help me with something that has been
bothering me for some time. Basically, it is a problem I encountered some
time ago with DataSets. I found a solution (which I will refer to later) but
I have a gut feeling that there must be an easier way to do it.

In order I can explain the problem, consider the following: Suppose I have a
DataSet which has two tables "Customers" and "Orders". I set up a
relationship between both tables so that a customer can have one or many
orders. This means that "Orders" has a foreign key e.g. FK_Customer which is
copy of the primary key of the corresponding customer row. So, when I add
new orders for a customer, the new rows in the "Orders" table must have the
correct FK_Customer.

This is the problem:

When the "Customer" table is updated to the database, the primary keys of
newly added rows to this table WILL NOT in general be the same when in the
DataSet after being updated. For example, microsoft recommendes that the
primary keys in a disconnected DataSet are negative. This means that when we
come to update the "Orders" table, the values of FK_Customer will not be
correct!

As I mentioned before, I did find a solution to the problem i.e.

http://www.codeproject.com/cs/datab...asp?df=100&forumid=14883&exp=0&select=1587944

but this involves telling the child row which is its parent row (via
SetParentRow) and handling the OnRowUpdated event.

Have I been missing something? It occurs to me that this parent-child
scenario is so frequent that there must be an easier way to deal with the
situation. Has VS2005 added any new funcitonality to deal with it?

Thanks in advance

G
 
G .Net wrote:
Suppose I have a
DataSet which has two tables "Customers" and "Orders". I set up a
relationship between both tables so that a customer can have one or many
orders. This means that "Orders" has a foreign key e.g. FK_Customer which is
copy of the primary key of the corresponding customer row. So, when I add
new orders for a customer, the new rows in the "Orders" table must have the
correct FK_Customer.

This is the problem:

When the "Customer" table is updated to the database, the primary keys of
newly added rows to this table WILL NOT in general be the same when in the
DataSet after being updated. For example, microsoft recommendes that the
primary keys in a disconnected DataSet are negative. This means that when we
come to update the "Orders" table, the values of FK_Customer will not be
correct!
Click to expand...
<snip>

Maybe you could perform an update of the newly created Customer
*before* adding any orders... This would ensure that the Customer
already had the correct PK in place, I guess.

A kludge, I know.

Regards,

Branco.
 
Hi,

G .Net said:
Hi Everybody

I was wondering if anybody could help me with something that has been
bothering me for some time. Basically, it is a problem I encountered some
time ago with DataSets. I found a solution (which I will refer to later)
but I have a gut feeling that there must be an easier way to do it.

In order I can explain the problem, consider the following: Suppose I have
a DataSet which has two tables "Customers" and "Orders". I set up a
relationship between both tables so that a customer can have one or many
orders. This means that "Orders" has a foreign key e.g. FK_Customer which
is copy of the primary key of the corresponding customer row. So, when I
add new orders for a customer, the new rows in the "Orders" table must
have the correct FK_Customer.

This is the problem:

When the "Customer" table is updated to the database, the primary keys of
newly added rows to this table WILL NOT in general be the same when in the
DataSet after being updated. For example, microsoft recommendes that the
primary keys in a disconnected DataSet are negative. This means that when
we come to update the "Orders" table, the values of FK_Customer will not
be correct!

As I mentioned before, I did find a solution to the problem i.e.

http://www.codeproject.com/cs/datab...asp?df=100&forumid=14883&exp=0&select=1587944

but this involves telling the child row which is its parent row (via
SetParentRow) and
Click to expand...

All what SetParentRow does is set the FK to the PK of the parent row, you
could quite easily do this yourself, even though these PK/FK may be
temporary ones (negative) at least the rows are associated which is
important.

When the parent DataRow gets updated it will retrieve the new PK, if there
is a ForeignKeyConstraint between the PK and FK and UpdateRule is set to
Cascade, then the new PK's are propgated to the relevant FK's. Note that
this only works because the rows were associated using the temporary keys.
handling the OnRowUpdated event.
Click to expand...

You have to handle OnRowUpdated event to retrieve the DB generated keys.

In VC2005 you have to do this for Access (not sure if it's true for all
OleDB or Jet), but it's even harder then in VC2003, because the TableAdapter
doesn't expose the DataAdapter so you can't attach to the RowUpdating event.
Foreach TableAdapter, you need to add another partial TableAdapter class and
expose the private DataAdapter by adding a property.

In VC2005 you don't need to do this for SQL server, the TableAdapter will
automatically retrieve them if the right checkbox is checked while
configurating the TableAdapter.
Have I been missing something? It occurs to me that this parent-child
scenario is so frequent that there must be an easier way to deal with the
Click to expand...

It gets more complicated if you consider that updating involves deleted,
modified and added rows, and to avoid integrity problems _during_ update, a
certain update sequence is required, which is explained in the following
article:
http://msdn2.microsoft.com/en-us/library/ms971502.aspx
 
Thanks guys. Most helpful.

G

Bart Mermuys said:
Hi,



All what SetParentRow does is set the FK to the PK of the parent row, you
could quite easily do this yourself, even though these PK/FK may be
temporary ones (negative) at least the rows are associated which is
important.

When the parent DataRow gets updated it will retrieve the new PK, if there
is a ForeignKeyConstraint between the PK and FK and UpdateRule is set to
Cascade, then the new PK's are propgated to the relevant FK's. Note that
this only works because the rows were associated using the temporary keys.


You have to handle OnRowUpdated event to retrieve the DB generated keys.

In VC2005 you have to do this for Access (not sure if it's true for all
OleDB or Jet), but it's even harder then in VC2003, because the
TableAdapter doesn't expose the DataAdapter so you can't attach to the
RowUpdating event. Foreach TableAdapter, you need to add another partial
TableAdapter class and expose the private DataAdapter by adding a
property.

In VC2005 you don't need to do this for SQL server, the TableAdapter will
automatically retrieve them if the right checkbox is checked while
configurating the TableAdapter.


It gets more complicated if you consider that updating involves deleted,
modified and added rows, and to avoid integrity problems _during_ update,
a certain update sequence is required, which is explained in the following
article:
http://msdn2.microsoft.com/en-us/library/ms971502.aspx
Click to expand...
 
Back
Top