Binding a grid to master/detail related datatables

[=?Utf-8?B?Si5NYXR0aGV3cw==?=]

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!

[=?Utf-8?B?YW5vbnltb3Vz?=]

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.HouseNa me,
Street.StreeNam e
FROM Person
JOIN Address ON Adress.AddrId = Person.AddrId
JOIN Street ON Sreet.StreetID = Address.StreetI d
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
"J.Matthews " wrote:

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!

[RobinS]

anonymous,

You know his next post is going to be, "I used the SqlCommandBuild er 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.
----------------------------------
"anonymous" <an*******@disc ussions.microso ft.comwrote in message
news:42******** *************** ***********@mic rosoft.com...

>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.HouseNa me,
Street.StreeNam e
FROM Person
JOIN Address ON Adress.AddrId = Person.AddrId
JOIN Street ON Sreet.StreetID = Address.StreetI d
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
"J.Matthews " wrote:

>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!

[=?Utf-8?B?Si5NYXR0aGV3cw==?=]

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" wrote:

anonymous,

You know his next post is going to be, "I used the SqlCommandBuild er 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.
----------------------------------
"anonymous" <an*******@disc ussions.microso ft.comwrote in message
news:42******** *************** ***********@mic rosoft.com...

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.HouseNa me,
Street.StreeNam e
FROM Person
JOIN Address ON Adress.AddrId = Person.AddrId
JOIN Street ON Sreet.StreetID = Address.StreetI d
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
"J.Matthews " wrote:

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!

[RobinS]

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

Good luck!
Robin S.
-----------------
"J.Matthews " <JM*******@disc ussions.microso ft.comwrote in message
news:3C******** *************** ***********@mic rosoft.com...

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" wrote:

>anonymous,

You know his next post is going to be, "I used the SqlCommandBuild er 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.
----------------------------------
"anonymous" <an*******@disc ussions.microso ft.comwrote in message
news:42******* *************** ************@mi crosoft.com...

>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.HouseNa me,
Street.StreeNam e
FROM Person
JOIN Address ON Adress.AddrId = Person.AddrId
JOIN Street ON Sreet.StreetID = Address.StreetI d
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
"J.Matthews " wrote:

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!