David Sceppa...if you are listening...Update question

[Greg]

First, the book is awesome. Started reading it Monday, finished it last
night. I've done some writing for (formerly) Wrox, so I knows it is tough
to do.

Now, the gory stuff.

We have a strange case I am wondering how to handle with cascading updates.

We have a parent table, call it names (members), and a child table, call it
addr (addresses). Names can have 0 to many types of addresses (Home,
Office, Special, Temporary). The names table has a field for each address:
names.haddr for home address, names.taddr for temp address, names.faddr for
office address, etc.

These fields store a control number that links to the address table. This
control number is the PK for the address table.

Our select returns the names record and any address records in separate
resultsets. @indexname and @id are input parameter to the sproc (sent from a
search screen on the client):

--names
SELECT <these columns>
FROM dbo.names n
WHERE
n.indexname > @indexname
OR
(n.indexname = @indexname AND n.id >= @names_id)
ORDER BY n.indexname, n.id;

--temp address
SELECT TOP 10
atemp.control, atemp.addr1, atemp.addr2 ,atemp.city, atemp.zip,
atemp.phone, atemp.fax , atemp.pobzip, atemp.fc, atemp.st
FROM dbo.names n LEFT JOIN dbo.addr atemp on n.taddr = atemp.control
WHERE
n.indexname > @indexname
OR
(n.indexname = @indexname AND n.id >= @names_id)
ORDER BY n.indexname, n.id

-- home address
SELECT TOP 10
ah.addr1, ah.addr2 , ah.city , ah.zip, ah.phone, ah.fax , ah.pobzip,
ah.control, ah.fc, ah.st
FROM dbo.names n LEFT JOIN dbo.addr ah on n.haddr = ah.control
WHERE
n.indexname > @indexname
OR
(n.indexname = @indexname AND n.id >= @names_id)
ORDER BY n.indexname, n.id;

etc for the remaining possible address

The client creates a DataRelation between the names address fields and each
address DataTable control field. There are 5 DataRelations in all, meaning
there are 5 address DataTables and one names Datatable.

So, on the client, names is the parent and each addr DataTable is a child (5
DataRelations). This works perfect, DataBinding is happy.

Here is the issue. Control is the PK for the addr table in sql server.
Control links to the individual names.haddr. names.taddr, etc fields in the
names table.

This field gets populated from a 'next available control number table in sql
server on an address insert. Kinda like autoincrement accept we supply the
value ourselves.

After doing the insert into the addr table, we need to update the names
record (haddr, taddr, etc) fields. So, on the update the addr is
kinda\sorta the parent and names is kinda\sort the child, if we want
cascading to work. Problem is, the constraint is set up the other way on
the client (in the schema) so navigation works on the client.

Anyway to make this work?

 

[Greg]

Oh, I forgot to mention all of this needs to happen inside a transaction.
If the user wants to add a new member (names), they enter all the necessary
data, include 0 to 5 addresses, and does a save. The save will insert a
names record (address fields are blank at first) then insert the addr
records. Now, with the addr inserts complete, we have the address fields
for the names table. If any of these fail, we need to roll back all of it.

 

[David Sceppa]

Greg,

I put together a very simple example with a similar
hierarchy - contacts and phone numbers.

The database contains a PhoneNumbers table and a Contacts
table. Each row in the Contacts table has columns for
HomePhoneID, WorkPhoneID, and CellPhoneID. These columns are
each child columns in foreign key constraints.

In the user interface, however, I show contacts information
as the starting point. Now, if I want to use data binding to
show the corresponding phone numbers for each contact, I would
need the columns to be parent columns in a DataRelation. But, as
you noted, retrieving new server-generated ID values for phone
numbers and cascading those values to the corresponding columns
in the contacts DataTable requires that the Contacts.HomePhoneID
(and others) are child columns in a DataRelation.

What to do? There are a couple options. First off, you
could avoid the problem by taking a slightly different approach
such as:

- Use code rather than data binding to handle the user interface.
Data binding is a powerful feature, but writing code to
display data and handle user input can offer more control and/or
functionality. You could have your DataRelations mirror your
database's foreign key constraints and use
ContactsRow.GetParentRow to programmatically access the
corresponding Phone row.

- Use key values that are generated at the client.
I'm not trying to sway you one way or the other. People
feel strongly one way or the other about this, especially on this
newsgroup, but it's worth pointing out that generating key values
at the client would prevent you from having to cascade changes to
key columns after submitting new rows to the database and
simplify the scenario.


Getting back to a possible solution using the ADO.NET object
model. Essentially, you want to use DataRelations to cascade
newly retrieved key values, but you also want to use
DataRelations for data binding and navigation. Thankfully, one
does not preclude the other.

In my sample, I created DataRelations that mirrored my
database's foreign key constraints (named "..._GetNewKey") to
cascade the newly retrieved key values and ones to facilitate
data binding (named "..._DataBind"). Note that the calls to
create the "data binding" relations include False for the
CreateConstraints parameter because you don't want constraints
for these "inverted" relationships.


DataTable tblPhoneNumbers = ds.Tables["PhoneNumbers"];
DataTable tblContacts = ds.Tables["Contacts"];

//Constraint-relationships
ds.Relations.Add("HomePhone_GetNewKey",
tblPhoneNumbers.Columns["PhoneID"],
tblContacts.Columns["HomePhoneID"]);
ds.Relations.Add("WorkPhone_GetNewKey",
tblPhoneNumbers.Columns["PhoneID"],
tblContacts.Columns["WorkPhoneID"]);
ds.Relations.Add("CellPhone_GetNewKey",
tblPhoneNumbers.Columns["PhoneID"],
tblContacts.Columns["CellPhoneID"]);

//Navigation-only relationships
ds.Relations.Add("HomePhone_DataBind",
tblPhoneNumbers.Columns["HomePhoneID"],
tblContacts.Columns["PhoneID"], false);
ds.Relations.Add("WorkPhone_DataBind",
tblPhoneNumbers.Columns["WorkPhoneID"],
tblContacts.Columns["PhoneID"], false);
ds.Relations.Add("CellPhone_DataBind",
tblPhoneNumbers.Columns["CellPhoneID"],
tblContacts.Columns["PhoneID"], false);


//Data binding code
gridContacts.DataSource = ds;
gridContacts.DataMember = "Contacts";

gridHomePhone.DataSource = ds;
gridHomePhone.DataMember = "Contacts.HomePhone_DataBind";

gridWorkPhone.DataSource = ds;
gridWorkPhone.DataMember = "Contacts.WorkPhone_DataBind";

gridCellPhone.DataSource = ds;
gridCellPhone.DataMember = "Contacts.CellPhone_DataBind";


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.

 

[Greg Robinson]

David, thanks for the great input. We thought about 86ing DataBinding
for this one case. However, time does not allow a redesign at this
point.

What I am doing for now is this:

1) Open a connection, begin a SqlTransaction
2) Insert the 4 address records (these are the child records on the
client per the DataRelation) using the 4 address DataTables. Use output
params to retrieve the keys to these records.
3) Update the names (this is the parent datatable in the DataRelation)
datatable with the address tables output params(pks).
4) Insert the names record with the names DataTable
5) Commit the transaction
6) Back on the client, removed all Added DataRows
7) Merge the two Datasets

So far, so good. Not using cascading, doing it manually, but it works.

 

[Greg]

Actually, I tried something like this prior. I tried creating inverted
Constraints to force the cascading. Problem is, in this 'direction' our
rows are not unique as we store a blank record in the address table that
represents a blank address. The names table has a link to this blank
record when the user does not enter an address. It is crazy, not my design,
done for reporting purposes.

Greg,

I put together a very simple example with a similar
hierarchy - contacts and phone numbers.

The database contains a PhoneNumbers table and a Contacts
table. Each row in the Contacts table has columns for
HomePhoneID, WorkPhoneID, and CellPhoneID. These columns are
each child columns in foreign key constraints.

In the user interface, however, I show contacts information
as the starting point. Now, if I want to use data binding to
show the corresponding phone numbers for each contact, I would
need the columns to be parent columns in a DataRelation. But, as
you noted, retrieving new server-generated ID values for phone
numbers and cascading those values to the corresponding columns
in the contacts DataTable requires that the Contacts.HomePhoneID
(and others) are child columns in a DataRelation.

What to do? There are a couple options. First off, you
could avoid the problem by taking a slightly different approach
such as:

- Use code rather than data binding to handle the user interface.
Data binding is a powerful feature, but writing code to
display data and handle user input can offer more control and/or
functionality. You could have your DataRelations mirror your
database's foreign key constraints and use
ContactsRow.GetParentRow to programmatically access the
corresponding Phone row.

- Use key values that are generated at the client.
I'm not trying to sway you one way or the other. People
feel strongly one way or the other about this, especially on this
newsgroup, but it's worth pointing out that generating key values
at the client would prevent you from having to cascade changes to
key columns after submitting new rows to the database and
simplify the scenario.


Getting back to a possible solution using the ADO.NET object
model. Essentially, you want to use DataRelations to cascade
newly retrieved key values, but you also want to use
DataRelations for data binding and navigation. Thankfully, one
does not preclude the other.

In my sample, I created DataRelations that mirrored my
database's foreign key constraints (named "..._GetNewKey") to
cascade the newly retrieved key values and ones to facilitate
data binding (named "..._DataBind"). Note that the calls to
create the "data binding" relations include False for the
CreateConstraints parameter because you don't want constraints
for these "inverted" relationships.


DataTable tblPhoneNumbers = ds.Tables["PhoneNumbers"];
DataTable tblContacts = ds.Tables["Contacts"];

//Constraint-relationships
ds.Relations.Add("HomePhone_GetNewKey",
tblPhoneNumbers.Columns["PhoneID"],
tblContacts.Columns["HomePhoneID"]);
ds.Relations.Add("WorkPhone_GetNewKey",
tblPhoneNumbers.Columns["PhoneID"],
tblContacts.Columns["WorkPhoneID"]);
ds.Relations.Add("CellPhone_GetNewKey",
tblPhoneNumbers.Columns["PhoneID"],
tblContacts.Columns["CellPhoneID"]);

//Navigation-only relationships
ds.Relations.Add("HomePhone_DataBind",
tblPhoneNumbers.Columns["HomePhoneID"],
tblContacts.Columns["PhoneID"], false);
ds.Relations.Add("WorkPhone_DataBind",
tblPhoneNumbers.Columns["WorkPhoneID"],
tblContacts.Columns["PhoneID"], false);
ds.Relations.Add("CellPhone_DataBind",
tblPhoneNumbers.Columns["CellPhoneID"],
tblContacts.Columns["PhoneID"], false);


//Data binding code
gridContacts.DataSource = ds;
gridContacts.DataMember = "Contacts";

gridHomePhone.DataSource = ds;
gridHomePhone.DataMember = "Contacts.HomePhone_DataBind";

gridWorkPhone.DataSource = ds;
gridWorkPhone.DataMember = "Contacts.WorkPhone_DataBind";

gridCellPhone.DataSource = ds;
gridCellPhone.DataMember = "Contacts.CellPhone_DataBind";


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.