propagating updated database PK values to the related FK fields in other tables?

[Michael Lang]

Ok, I've looked into using ints as primary key fields in my 3 layered
application. I've been able to get single tables to update correctly, and
have the database assigned key assigned to each DataRow. This is fine and
good for tables that don't have child records in other tables.

What do you do if in this situation:
table : "Order" with columns:
OrderID (int PK)
CustomerID (int FK)
Descriptor (nNarChar)

table: "OrderDetails" with columns:
OrderDetailsID (int PK)
OrderID (int FK)
ProductID (int FK)
Quantity (int)

also tables "Product" and "Customer" with PK's matching FK's above.

The business layer has been populated by querying the datalayer. In the
presentation layer the user adds 5 new customers (sales had a good day ,
10 new orders for those 5 customers, and 2-5 orderDetails for each new
order. Each new record in the business layer has been assigned a temporary
negative decrementing (-1, -2 ,etc..) PK value. Each FK is given the same
temporary value as the PK in the related table.

Now what should the data layer do as it receives all this updated data? I
know how to update the PK's properly, but how does each FK get the same
value as the related PK? Is it as easy as defining the Relationships
between the dataTables, do they all get updated as the PK in the related
table is updated?

My code currently works great for tables with GUID (uniqueidentifier) PK's,
since the PK's don't get updated by the DB. But I'm trying to get an option
that also works for int PK tables.

I'm just trying to determine at this point if I have to roll my own FK
update functionality. My data layer currently has separate classes for each
table. Each currently has their own internal DataSet, and DataAdapter. So
if relations are the cure, then I have some major restructuring to do. Or I
need to roll my own UpdateFKs() method to update the other objects in the
data layer.

IE.
class CustomerDB{
private DataSet _ds;
private DataAdapter _da;
//methods to select/update/insert/delete records in this table
// to see details look at one of the templates (01 to 05) on
// code generator project page (link below)
}

Any input is appreciated.

 

[Carl Prothman [MVP]]

What do you do if in this situation:
table : "Order" with columns:
OrderID (int PK)
CustomerID (int FK)
Descriptor (nNarChar)

table: "OrderDetails" with columns:
OrderDetailsID (int PK)
OrderID (int FK)
ProductID (int FK)
Quantity (int)

Michael,
For this type of advance update scenario (submitting hierarchical changes),
I recommend you purchase David Sceppa's "Microsoft ADO.NET" book.
http://www.microsoft.com/mspress/books/5354.asp

In Chapter 11 (pages 485-503), he goes into great detail on how to do this.
You can download the book's sample code from
http://www.microsoft.com/mspress/books/companion/5354.asp#Companion Content
- See the "ComplexHierarchy" example (C:\ADO.NET\Chapter 11\ComplexHierarchy)

Note the trick to submitting hierarchical changes is to submit the changes in the proper
order to comply with the referential integrity constraints in your database.

--

Thanks,
Carl Prothman
Microsoft ASP.NET MVP
http://www.able-consulting.com

 

[David Sceppa]

Thanks for the plug, Carl.

DataRelations are the simplest way to handle the scenario.
As Carl noted, I cover the general single-DataSet scenario in
depth in "Microsoft ADO.NET". Here's a quick break-down of the
steps involved:

1.) Have ADO.NET generate placeholder values for the parent ID's.
2.) Use a DataRelation in the DataSet and use those parent
placeholder values to associate pending parent rows with the
pending child rows.
3.) Configure your DataAdapter(s) to fetch the
database-generated auto-increment values.
4.) Submit the pending parent rows.
The DataRelation will cascade the newly fetched parent ID's
to the corresponding child rows automatically.
5.) Submit the pending child rows.

My data layer currently has separate classes for each
table. Each currently has their own internal DataSet,
and DataAdapter. So if relations are the cure, then I
have some major restructuring to do. Or I need to roll
my own UpdateFKs() method to update the other objects in
the data layer.

Are the classes able to access their related objects? If
so, you could write code to essentially do what the DataRelation
does for you. You could trap for the parent DataAdapter's
RowUpdated event, access the parent row after performing the
insert, locate the corresponding child rows and assign the
newly-fetched auto-increment value to the rows.


As you noted, using client-generated key values such as
Guids simplifies the process since you don't have to fetch
server-generated values for the new parent rows before submitting
the new child rows.


I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.