469,630 Members | 1,200 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,630 developers. It's quick & easy.

Binding a grid to master/detail related datatables

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!
Apr 18 '07 #1
4 2339
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
"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!

Apr 18 '07 #2
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.
----------------------------------
"anonymous" <an*******@discussions.microsoft.comwrote in message
news:42**********************************@microsof t.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.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
"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!


Apr 18 '07 #3
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 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.
----------------------------------
"anonymous" <an*******@discussions.microsoft.comwrote in message
news:42**********************************@microsof t.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.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
"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!



Apr 19 '07 #4
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*******@discussions.microsoft.comwrote in message
news:3C**********************************@microsof t.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 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.
----------------------------------
"anonymous" <an*******@discussions.microsoft.comwrote in message
news:42**********************************@microso ft.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.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
"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!




Apr 20 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Christopher Weaver | last post: by
2 posts views Thread by ruca | last post: by
6 posts views Thread by p.mc | last post: by
3 posts views Thread by no | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.