Binding a grid to master/detail related datatables

[Guest]

Picture the cut down scenario...

Say I have three datatables (Person, Address, Street) that are linked with
datarelation objects using the PK/FK associations...

Person
--------
PersonId {PK}
AddrId {FK}
Forename
....


Address
----------
AddrId {PK
StreetId {FK}
HouseName
....


Street
-------
StreetId {PK}
StreeName

How can I BIND a datagrid to the Person datatable to show all person rows
and include data from Parent table (HouseName from Address) and from the
parent table again (StreetName from Street) for each row.

Grid example
---------------

PersonId | Forename | HouseName | StreetName
_________________________________________
1 Bill billshouse York Street
2 Stan stanshouse York Street
3 Tom tomshouse New Street
etc

I know you can do the reverse. So for this scenario you could have a
hierarchical grid binding to the master datatable Street and working down to
the detail person but I don't want that.

Any help appreciated!

 

[Guest]

I would use Joins in your SQL Query that retrieves the data. Typically I
recommend Stored Procedures for accessing your database, but that usually
depends on your work environment. The SQL Query to pull the data you are
looking for goes like this:

SELECT Person.PersonID,
Person.Forename,
Address.HouseName,
Street.StreeName
FROM Person
JOIN Address ON Adress.AddrId = Person.AddrId
JOIN Street ON Sreet.StreetID = Address.StreetId


The above SQL Query should give you the data you need. You can take that
data and bind it to your GridView. I have found the following website to be
very useful in answering any questions I may have about SQL. I would also
point out that performing an operation like this is usually most easily done
in a language like SQL, not an imperative language like C#, but that may
change in C# 3.0. http://www.w3schools.com/sql/default.asp

 

[RobinS]

anonymous,

You know his next post is going to be, "I used the SqlCommandBuilder to
build my command objects, but it won't update the database." ;-)

J.Matthews -- I agree with anonymous that this is the way to go. But if you
want to do updates, you will need to program them manually.

Robin S.
----------------------------------

 

[Guest]

Thanks for the replies. Yes that would have been my reply comment (regarding
updating). Ok, then I will have to program the updating manually

Thanks

 

[RobinS]

That's pretty much the level of progression. I just thought I'd answer it
before you asked.

Good luck!
Robin S.
-----------------