Datagrid, data relations and joins revisited

[Earl]

I have to believe this is a fairly common scenario. I have several form
classes where I provide a grid to display historical information. My
database tables are normalized so calling for, say, the supplier table gives
me some foreign keys instead of the actual text that I would like to display
in the grid. Now I can (and have) easily done this with joins or subqueries,
but I'm not satisfied, mainly because this either screws up my
update/insert/delete logic or forces me to make a second trip to the server
*just* for the datagrid display. What's the BEST way to associate the text
from the lookup table with its foreign key in the main table?

In the following example, I'm pulling an ID for the state name but wish to
display the NAME in the datagrid without doing a join:

Supplier table
SupplierID SupplierName SupplierCityStateZipID
.....

CityStateZip table
CityStateZipID CityName StateName Zip
....

Datagrid columns to be displayed
Supplier name Address City State Zip
....

 

[Kevin Yu [MSFT]]

Hi Earl,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to display the city name and
state name in the Supplier table without pulling additional data from
database. If there is any misunderstanding, please feel free to let me know.

Based on my experience, this can be achieved by adding a column in the
child table (Supplier table) and set Expression property for that column.
Here are the steps:

1. Create a DataRelation between Supplier table and CityStateZip table.
Supplier is the child table, while CityStateZip is Parent. The name for the
relation is "CityStateZipSupplier".
2. Add a column named City to the Supplier table in the DataSet schema.
3. Set the Expression attribute of that column to
Parent(CityStateZipSupplier).CityName.

When the table is displayed in DataGrid, the City column in Supplier table
will be filled with corresponding CityName automatically. We can do the
same with StateName column.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Earl]

Thanks for your prompt reply Kevin. If I understand correctly, you mean to
add a column in the datatable rather than the actual table. I'm also still
unclear how we can pull *just* that ID out of the 40,000+ records in the zip
table before we associate it with the data relation?

 

[Earl]

I've done some more research on this issue and from what I understand, I
still need to run a join query to get the child table. Is that the correct
method?

 

[Earl]

I've read a ton of material tonight on data relations - very little of it
relates to how to put the child columns in the datagrid. Based upon what
I've read in the MSDN, I'm not convinced very many developers know how to do
this and further, it seems like an incredibly Rube Goldberg method of
creating what is essentially a join operation. Certainly I still haven't
figured it out. I can see how to create an expression but not how to do this
from another datatable. The syntax is obviously wrong on the expression.

Dim rel As DataRelation
rel = New DataRelation("CityStateZipSupplier", _
ds.Tables("dtSuppliers").Columns("SupplierZipCityID"), _
ds.Tables("dtSupplierZips").Columns("ZipCityStateID"))
ds.Relations.Add(rel)
ds.Tables("dtSuppliers").Columns.Add("City", GetType(String))
'exception on the expression
ds.Tables("dtSuppliers").Columns("City").Expression =
"Child(CityStateZipSupplier).City"

 

[Earl]

With some more digging in Sceppa's book, I have resolved the expression
syntax. I am now curious how this will affect the CurrencyManager
functioning, particularly as it relates to deleting records.

 

[Kevin Yu [MSFT]]

Hi Earl,

We have to use Parent(CityStateZipSupplier).City instead of
Child(CityStateZipSupplier).City, since CityStateZip is the parent table
while Supplier is the child table.

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Earl]

Even if I changed the parent-child order, it would not allow me to use the
Child syntax for the expression, which I found quite odd. I've still not
resolved the constraint issue, but tomorrow is another day.

 

[Kevin Yu [MSFT]]

Hi Earl,

It seems that we cannot use Child in this way. Since it is a 1:n
relationship, we can only find 1 parent from the child table row, but find
serveral child rows from the parent table row. Child can only be used to do
some statistics like: Sum(Child.Price).

HTH.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Earl]

Thanks Kevin, good to know.