Dumb question #102

[Martin Robins]

Ok, so everybody sings the praises of or dishes the DataSet; I use them, but
not nearly enough as I probably should. So, here is my question:

A DataSet can have more than one table, and those tables can be related; so,
how can I loop through all the records in a table and get related values
from the other tables?

For example, lets say that I create a dataset with 3 tables; Accounts,
Status and Controller:

Accounts: Reference, Name, StatusId, ControllerId
Status: Id, Text
Controller: Id, Name

I set up a relationship between the tables where Accounts.StatusId=Status.Id
and Accounts.ControllerId=Controller.Id

Now, how do I loop through the rows of Accounts, pulling the correct status
and controller from the related tables?

foreach(DataRow row in ds.Tables["Accounts"].Rows) {
Console.WriteLine(row["Reference"]); // Display the account
reference
Console.WriteLine( ???????????? ); // Display the controller
}

I know that this can be done as I have seen similar examples (though
normally working the other way around - Select the controller and run
through the accounts for example), but I have developed a mental block
(instead of software!) on how to do it.

Cheers.

 

[Scott M.]

You extract data the same way you would in any other relational environment.
Your select statement would include what fields you want from what tables.
The power of having dataRelations in a dataset come into play when enforcing
referential integrity.

 

[Martin Robins]

Scott,

Thanks for your suggestion, but I specifically want to have the relations
within the DataSet. I can already perform the relations when extracting
tables from the database (select a1, b2 from a left outer join b ...), I am
looking at an alternative here where I want to be able to report from a
dataset that may or may not contain relationships or could just contain a
single table.

Cheers.

You extract data the same way you would in any other relational environment.
Your select statement would include what fields you want from what tables.
The power of having dataRelations in a dataset come into play when enforcing
referential integrity.

Ok, so everybody sings the praises of or dishes the DataSet; I use them, but
not nearly enough as I probably should. So, here is my question:

A DataSet can have more than one table, and those tables can be related; so,
how can I loop through all the records in a table and get related values
from the other tables?

For example, lets say that I create a dataset with 3 tables; Accounts,
Status and Controller:

Accounts: Reference, Name, StatusId, ControllerId
Status: Id, Text
Controller: Id, Name

I set up a relationship between the tables where Accounts.StatusId=Status.Id
and Accounts.ControllerId=Controller.Id

Now, how do I loop through the rows of Accounts, pulling the correct status
and controller from the related tables?

foreach(DataRow row in ds.Tables["Accounts"].Rows) {
Console.WriteLine(row["Reference"]); // Display the account
reference
Console.WriteLine( ???????????? ); // Display the controller
}

I know that this can be done as I have seen similar examples (though
normally working the other way around - Select the controller and run
through the accounts for example), but I have developed a mental block
(instead of software!) on how to do it.

Cheers.

 

[Cor]

Hi Martin,

Have a look to this, (a loot change in code so watch typos)

dRelA = New DataRelation ("RA", ds.Tables("A").Columns("ident"), _
ds.Tables("B").Columns("ident"))
ds.Relations.Add(DRelA)
dg1.SetDataBinding(ds, "A")
dg2.SetDataBinding(ds, "A.RA")

I hope this helps something?

Cor

 

[Scott M.]

If you have a typed dataset, then the datarelations will be populated within
the dataset. If you don't have a typed dataset you can set the relations
yourself as Cor shows.

What else are you trying to do? Once the dataset has the relations set, you
are good to go.

Scott,

Thanks for your suggestion, but I specifically want to have the relations
within the DataSet. I can already perform the relations when extracting
tables from the database (select a1, b2 from a left outer join b ...), I am
looking at an alternative here where I want to be able to report from a
dataset that may or may not contain relationships or could just contain a
single table.

Cheers.

You extract data the same way you would in any other relational environment.
Your select statement would include what fields you want from what tables.
The power of having dataRelations in a dataset come into play when enforcing
referential integrity.

Ok, so everybody sings the praises of or dishes the DataSet; I use

them,
but

not nearly enough as I probably should. So, here is my question:

A DataSet can have more than one table, and those tables can be

related;
so,

how can I loop through all the records in a table and get related values
from the other tables?

For example, lets say that I create a dataset with 3 tables; Accounts,
Status and Controller:

Accounts: Reference, Name, StatusId, ControllerId
Status: Id, Text
Controller: Id, Name

I set up a relationship between the tables where Accounts.StatusId=Status.Id
and Accounts.ControllerId=Controller.Id

Now, how do I loop through the rows of Accounts, pulling the correct status
and controller from the related tables?

foreach(DataRow row in ds.Tables["Accounts"].Rows) {
Console.WriteLine(row["Reference"]); // Display the account
reference
Console.WriteLine( ???????????? ); // Display the controller
}

I know that this can be done as I have seen similar examples (though
normally working the other way around - Select the controller and run
through the accounts for example), but I have developed a mental block
(instead of software!) on how to do it.

Cheers.

 

[Martin Robins]

Sorry guys, everybody seems to be missing the question.

I have a typed DataSet with multiple, related tables and the relationships
set up. I want to loop through a table, and be able to pull out data from
the related tables. For example (using the example tables I provided
originally)

Accounts MR1, Martin, 1, 2
MR2, David, 2, 3
MR3, Stephen, 1, 3

Status 1, On Hold
2, Trading
3, Legal

Controller 2, Bob
3, Terry

I want to be able to loop through the Accounts table, pulling out:

MR1, Martin, On Hold, Bob
MR2, David, Trading, Terry
MR3, Stephen, On Hold, Terry

I want to do this programatically; how do I loop through the tables
correctly to pull out this data?
I can loop through the Accounts table without problem, [foreach (DataRow row
in ds.Tables["Accounts"].Rows) {}], but how do I pull the related data from
Status and Controller for each row that I process?

Cheers.

 

[Martin Robins]

By the way, just to clarify further, I am not looking to use any datagrids
here. I want to programatically, sequentially process each row in one of the
tables pulling in the data from the others; eqivelent to:

Martin.

Sorry guys, everybody seems to be missing the question.

I have a typed DataSet with multiple, related tables and the relationships
set up. I want to loop through a table, and be able to pull out data from
the related tables. For example (using the example tables I provided
originally)

Accounts MR1, Martin, 1, 2
MR2, David, 2, 3
MR3, Stephen, 1, 3

Status 1, On Hold
2, Trading
3, Legal

Controller 2, Bob
3, Terry

I want to be able to loop through the Accounts table, pulling out:

MR1, Martin, On Hold, Bob
MR2, David, Trading, Terry
MR3, Stephen, On Hold, Terry

I want to do this programatically; how do I loop through the tables
correctly to pull out this data?
I can loop through the Accounts table without problem, [foreach (DataRow row
in ds.Tables["Accounts"].Rows) {}], but how do I pull the related data from
Status and Controller for each row that I process?

Cheers.

Ok, so everybody sings the praises of or dishes the DataSet; I use them, but
not nearly enough as I probably should. So, here is my question:

A DataSet can have more than one table, and those tables can be related; so,
how can I loop through all the records in a table and get related values
from the other tables?

For example, lets say that I create a dataset with 3 tables; Accounts,
Status and Controller:

Accounts: Reference, Name, StatusId, ControllerId
Status: Id, Text
Controller: Id, Name

I set up a relationship between the tables where Accounts.StatusId=Status.Id
and Accounts.ControllerId=Controller.Id

Now, how do I loop through the rows of Accounts, pulling the correct status
and controller from the related tables?

foreach(DataRow row in ds.Tables["Accounts"].Rows) {
Console.WriteLine(row["Reference"]); // Display the account
reference
Console.WriteLine( ???????????? ); // Display the controller
}

I know that this can be done as I have seen similar examples (though
normally working the other way around - Select the controller and run
through the accounts for example), but I have developed a mental block
(instead of software!) on how to do it.

Cheers.

 

[Cor]

Hi Martin,

If you do not want the relations, you can choose for the datatable.select
and/or you can use the dataview.rowfilter or even a normal loop throught the
tables, it is on you how to use it.

Cor

 

[Martin Robins]

But I do want the relations - I simply cannot work out how to access them
from the related tables whilst looping through the main table.

Here is a simple loop to read the main table; can somebody add in the
missing line to get the related data, making the assumption that I have
already set up the necessary relationships?

foreach (DataRow dataRow in ds.Tables["Accounts"].Rows) {
Console.WriteLine(dataRow["Reference"].ToString());
Console.WriteLine( ??? ); // What goes here to display the Status.Text
related to the Accounts.Reference just displayed?
}

Base on the previously supplied example data; I want to display

MR1
On Hold
MR2
Trading
MR3
On Hold


This is the question I am asking; I can populate the tables, I can set up
the relationships and I can loop through the main table sequentially
displaying the content; I simply cannot display the related table data.

Thanks.

Hi Martin,

If you do not want the relations, you can choose for the datatable.select
and/or you can use the dataview.rowfilter or even a normal loop throught the
tables, it is on you how to use it.

Cor

Sorry guys, everybody seems to be missing the question.

I have a typed DataSet with multiple, related tables and the relationships
set up. I want to loop through a table, and be able to pull out data from
the related tables. For example (using the example tables I provided
originally)

Accounts MR1, Martin, 1, 2
MR2, David, 2, 3
MR3, Stephen, 1, 3

Status 1, On Hold
2, Trading
3, Legal

Controller 2, Bob
3, Terry

I want to be able to loop through the Accounts table, pulling out:

MR1, Martin, On Hold, Bob
MR2, David, Trading, Terry
MR3, Stephen, On Hold, Terry

I want to do this programatically; how do I loop through the tables
correctly to pull out this data?
I can loop through the Accounts table without problem, [foreach (DataRow row
in ds.Tables["Accounts"].Rows) {}], but how do I pull the related data from
Status and Controller for each row that I process?

Cheers.

 

[Scott M.]

Martin, I see what you are getting at and (IMHO), I wouldn't have set up the
data this way to begin with. In my "Accounts" table, I would have a record
like this:

MR1, Martin, OnHold, Bob

Where the field that contains the value "OnHold" is related to a record in
the "Status" table with a primary key of "OnHold", not "1". The field that
contains "Bob" is related to a record in the table "Controller", where there
is a record that has "Bob" as its primary key value, not "2".

The "OnHold" field value in the Accounts table is related to the "OnHold"
record in the Status table and the "Bob" field value in the Accounts table
is related to the "Bob" record in the Controller table. The data you want
is already in the Accounts table and there is no need to worry about how to
access it (the point of your post). What you get for your trouble is that
if someone were to try to delete the "Bob" record from Controllers or the
"OnHold" record from Status, referential integrity would prevent it (the
point of having relationships).

All the data you need would be in the Accounts table to begin with and you
just loop through that.

But I do want the relations - I simply cannot work out how to access them
from the related tables whilst looping through the main table.

Here is a simple loop to read the main table; can somebody add in the
missing line to get the related data, making the assumption that I have
already set up the necessary relationships?

foreach (DataRow dataRow in ds.Tables["Accounts"].Rows) {
Console.WriteLine(dataRow["Reference"].ToString());
Console.WriteLine( ??? ); // What goes here to display the Status.Text
related to the Accounts.Reference just displayed?
}

Base on the previously supplied example data; I want to display

MR1
On Hold
MR2
Trading
MR3
On Hold


This is the question I am asking; I can populate the tables, I can set up
the relationships and I can loop through the main table sequentially
displaying the content; I simply cannot display the related table data.

Thanks.

Hi Martin,

If you do not want the relations, you can choose for the datatable.select
and/or you can use the dataview.rowfilter or even a normal loop throught the
tables, it is on you how to use it.

Cor

Sorry guys, everybody seems to be missing the question.

I have a typed DataSet with multiple, related tables and the relationships
set up. I want to loop through a table, and be able to pull out data from
the related tables. For example (using the example tables I provided
originally)

Accounts MR1, Martin, 1, 2
MR2, David, 2, 3
MR3, Stephen, 1, 3

Status 1, On Hold
2, Trading
3, Legal

Controller 2, Bob
3, Terry

I want to be able to loop through the Accounts table, pulling out:

MR1, Martin, On Hold, Bob
MR2, David, Trading, Terry
MR3, Stephen, On Hold, Terry

I want to do this programatically; how do I loop through the tables
correctly to pull out this data?
I can loop through the Accounts table without problem, [foreach

(DataRow
row

in ds.Tables["Accounts"].Rows) {}], but how do I pull the related data from
Status and Controller for each row that I process?

Cheers.

 

[Martin Robins]

I do not wish to seem rude Scott, but why can nobody on this group attempt
to answer the question instead of telling me how they would do it
differently in the data.

I have already written 2 production systems in .NET with SQL Server behind.
I know how to use databases and normalise data, I can use
left/right/inner/outer joins to acheive the very same as I am looking for
directly from the database; but I want to do this particular piece this
way - 3 related tables in a DataSet and retrieve data from them via the
relations.

Thanks.

Martin.

Martin, I see what you are getting at and (IMHO), I wouldn't have set up the
data this way to begin with. In my "Accounts" table, I would have a record
like this:

MR1, Martin, OnHold, Bob

Where the field that contains the value "OnHold" is related to a record in
the "Status" table with a primary key of "OnHold", not "1". The field that
contains "Bob" is related to a record in the table "Controller", where there
is a record that has "Bob" as its primary key value, not "2".

The "OnHold" field value in the Accounts table is related to the "OnHold"
record in the Status table and the "Bob" field value in the Accounts table
is related to the "Bob" record in the Controller table. The data you want
is already in the Accounts table and there is no need to worry about how to
access it (the point of your post). What you get for your trouble is that
if someone were to try to delete the "Bob" record from Controllers or the
"OnHold" record from Status, referential integrity would prevent it (the
point of having relationships).

All the data you need would be in the Accounts table to begin with and you
just loop through that.

But I do want the relations - I simply cannot work out how to access them
from the related tables whilst looping through the main table.

Here is a simple loop to read the main table; can somebody add in the
missing line to get the related data, making the assumption that I have
already set up the necessary relationships?

foreach (DataRow dataRow in ds.Tables["Accounts"].Rows) {
Console.WriteLine(dataRow["Reference"].ToString());
Console.WriteLine( ??? ); // What goes here to display the Status.Text
related to the Accounts.Reference just displayed?
}

Base on the previously supplied example data; I want to display

MR1
On Hold
MR2
Trading
MR3
On Hold


This is the question I am asking; I can populate the tables, I can set up
the relationships and I can loop through the main table sequentially
displaying the content; I simply cannot display the related table data.

Thanks.

Hi Martin,

If you do not want the relations, you can choose for the datatable.select
and/or you can use the dataview.rowfilter or even a normal loop

throught
the

tables, it is on you how to use it.

Cor

Sorry guys, everybody seems to be missing the question.

I have a typed DataSet with multiple, related tables and the relationships
set up. I want to loop through a table, and be able to pull out data from
the related tables. For example (using the example tables I provided
originally)

Accounts MR1, Martin, 1, 2
MR2, David, 2, 3
MR3, Stephen, 1, 3

Status 1, On Hold
2, Trading
3, Legal

Controller 2, Bob
3, Terry

I want to be able to loop through the Accounts table, pulling out:

MR1, Martin, On Hold, Bob
MR2, David, Trading, Terry
MR3, Stephen, On Hold, Terry

I want to do this programatically; how do I loop through the tables
correctly to pull out this data?
I can loop through the Accounts table without problem, [foreach (DataRow
row
in ds.Tables["Accounts"].Rows) {}], but how do I pull the related data
from
Status and Controller for each row that I process?

Cheers.

 

[Cor]

Hi Martin,

I do not wish to seem rude Scott, but why can nobody on this group attempt
to answer the question instead of telling me how they would do it
differently in the data.

I think because you want to do it on your own way, which is for nobody
intresting to investigate because they have their own way, which they think
is better.

If you want to do it on your special way, maybe someone sees it who does it
the same or else you have to investigage it yourself I think.

Just my thought about it.

Cor

 

[David D. McCrory]

Martin,

I found the following information in the online help that is similar to what
you are trying to do......It was under the topic of DataRelations
(Navigating a Relationship between Tables)

Dim custOrderRel As DataRelation = custDS.Relations.Add("CustOrders", _
custDS.Tables("Customers").Columns("CustomerID"), _
custDS.Tables("Orders").Columns("CustomerID"))

Dim orderDetailRel As DataRelation = custDS.Relations.Add("OrderDetail", _
custDS.Tables("Orders").Columns("OrderID"), _
custDS.Tables("OrderDetails").Columns("OrderID"),
false)

Dim orderProductRel As DataRelation = custDS.Relations.Add("OrderProducts",
_
custDS.Tables("Products").Columns("ProductID"), _
custDS.Tables("OrderDetails").Columns("ProductID"))

Dim custRow, orderRow, detailRow As DataRow

For Each custRow In custDS.Tables("Customers").Rows
Console.WriteLine("Customer ID:" & custRow("CustomerID").ToString())

For Each orderRow In custRow.GetChildRows(custOrderRel)
Console.WriteLine(" Order ID: " & orderRow("OrderID").ToString())
Console.WriteLine(vbTab & "Order Date: " &
orderRow("OrderDate").ToString())

For Each detailRow In orderRow.GetChildRows(orderDetailRel)
Console.WriteLine(vbTab & " Product: " &
detailRow.GetParentRow(orderProductRel)("ProductName").ToString())
Console.WriteLine(vbTab & " Quantity: " &
detailRow("Quantity").ToString())
Next
Next
Next

Sorry guys, everybody seems to be missing the question.

I have a typed DataSet with multiple, related tables and the relationships
set up. I want to loop through a table, and be able to pull out data from
the related tables. For example (using the example tables I provided
originally)

Accounts MR1, Martin, 1, 2
MR2, David, 2, 3
MR3, Stephen, 1, 3

Status 1, On Hold
2, Trading
3, Legal

Controller 2, Bob
3, Terry

I want to be able to loop through the Accounts table, pulling out:

MR1, Martin, On Hold, Bob
MR2, David, Trading, Terry
MR3, Stephen, On Hold, Terry

I want to do this programatically; how do I loop through the tables
correctly to pull out this data?
I can loop through the Accounts table without problem, [foreach (DataRow row
in ds.Tables["Accounts"].Rows) {}], but how do I pull the related data from
Status and Controller for each row that I process?

Cheers.

Ok, so everybody sings the praises of or dishes the DataSet; I use them, but
not nearly enough as I probably should. So, here is my question:

A DataSet can have more than one table, and those tables can be related; so,
how can I loop through all the records in a table and get related values
from the other tables?

For example, lets say that I create a dataset with 3 tables; Accounts,
Status and Controller:

Accounts: Reference, Name, StatusId, ControllerId
Status: Id, Text
Controller: Id, Name

I set up a relationship between the tables where Accounts.StatusId=Status.Id
and Accounts.ControllerId=Controller.Id

Now, how do I loop through the rows of Accounts, pulling the correct status
and controller from the related tables?

foreach(DataRow row in ds.Tables["Accounts"].Rows) {
Console.WriteLine(row["Reference"]); // Display the account
reference
Console.WriteLine( ???????????? ); // Display the controller
}

I know that this can be done as I have seen similar examples (though
normally working the other way around - Select the controller and run
through the accounts for example), but I have developed a mental block
(instead of software!) on how to do it.

Cheers.

 

[Scott M.]

I understand what you are saying Martin and the reason you are having
trouble is that you are looking for a way to do something that isn't how
most people would do it in the first place.

In Classic ADO, you could pull data from several tables and wind up with it
all in one recordset, so it was easy to iterate through the one recordset
and pull your data out.

In .NET, you now have the option to pull data in from different tables and
store them in different tables and it makes things complicated to write 1
loop that will iterate through one table and pull records from another.

As I see it you have 3 possibilities:

1. Use the classic ADO Recordset in .NET (I wouldn't do it this way, but it
would give you what you want).

2. Within one loop that iterates through the main data, stop at each record
and loop again through the other 2 tables (individually) to extract the
correct data there. (Total of 3 loops and not very efficient).

3. Take my last suggestion and store the data in a more accessible way in
the first place so that you still get relational data, but don't have to
hunt for it.

Good luck,

-Scott

I do not wish to seem rude Scott, but why can nobody on this group attempt
to answer the question instead of telling me how they would do it
differently in the data.

I have already written 2 production systems in .NET with SQL Server behind.
I know how to use databases and normalise data, I can use
left/right/inner/outer joins to acheive the very same as I am looking for
directly from the database; but I want to do this particular piece this
way - 3 related tables in a DataSet and retrieve data from them via the
relations.

Thanks.

Martin.

Martin, I see what you are getting at and (IMHO), I wouldn't have set up the
data this way to begin with. In my "Accounts" table, I would have a record
like this:

MR1, Martin, OnHold, Bob

Where the field that contains the value "OnHold" is related to a record in
the "Status" table with a primary key of "OnHold", not "1". The field that
contains "Bob" is related to a record in the table "Controller", where there
is a record that has "Bob" as its primary key value, not "2".

The "OnHold" field value in the Accounts table is related to the "OnHold"
record in the Status table and the "Bob" field value in the Accounts table
is related to the "Bob" record in the Controller table. The data you want
is already in the Accounts table and there is no need to worry about how to
access it (the point of your post). What you get for your trouble is that
if someone were to try to delete the "Bob" record from Controllers or the
"OnHold" record from Status, referential integrity would prevent it (the
point of having relationships).

All the data you need would be in the Accounts table to begin with and you
just loop through that.

But I do want the relations - I simply cannot work out how to access them
from the related tables whilst looping through the main table.

Here is a simple loop to read the main table; can somebody add in the
missing line to get the related data, making the assumption that I have
already set up the necessary relationships?

foreach (DataRow dataRow in ds.Tables["Accounts"].Rows) {
Console.WriteLine(dataRow["Reference"].ToString());
Console.WriteLine( ??? ); // What goes here to display the Status.Text
related to the Accounts.Reference just displayed?
}

Base on the previously supplied example data; I want to display

MR1
On Hold
MR2
Trading
MR3
On Hold


This is the question I am asking; I can populate the tables, I can set up
the relationships and I can loop through the main table sequentially
displaying the content; I simply cannot display the related table data.

Thanks.



Hi Martin,

If you do not want the relations, you can choose for the datatable.select
and/or you can use the dataview.rowfilter or even a normal loop throught
the
tables, it is on you how to use it.

Cor

Sorry guys, everybody seems to be missing the question.

I have a typed DataSet with multiple, related tables and the
relationships
set up. I want to loop through a table, and be able to pull out data
from
the related tables. For example (using the example tables I provided
originally)

Accounts MR1, Martin, 1, 2
MR2, David, 2, 3
MR3, Stephen, 1, 3

Status 1, On Hold
2, Trading
3, Legal

Controller 2, Bob
3, Terry

I want to be able to loop through the Accounts table, pulling out:

MR1, Martin, On Hold, Bob
MR2, David, Trading, Terry
MR3, Stephen, On Hold, Terry

I want to do this programatically; how do I loop through the tables
correctly to pull out this data?
I can loop through the Accounts table without problem, [foreach (DataRow
row
in ds.Tables["Accounts"].Rows) {}], but how do I pull the related data
from
Status and Controller for each row that I process?

Cheers.

 

[Martin Robins]

Now this is very close to what I am looking for.

Thanks David.

Martin,

I found the following information in the online help that is similar to what
you are trying to do......It was under the topic of DataRelations
(Navigating a Relationship between Tables)

Dim custOrderRel As DataRelation = custDS.Relations.Add("CustOrders", _
custDS.Tables("Customers").Columns("CustomerID"), _
custDS.Tables("Orders").Columns("CustomerID"))

Dim orderDetailRel As DataRelation = custDS.Relations.Add("OrderDetail", _
custDS.Tables("Orders").Columns("OrderID"), _
custDS.Tables("OrderDetails").Columns("OrderID"),
false)

Dim orderProductRel As DataRelation = custDS.Relations.Add("OrderProducts",
_
custDS.Tables("Products").Columns("ProductID"), _
custDS.Tables("OrderDetails").Columns("ProductID"))

Dim custRow, orderRow, detailRow As DataRow

For Each custRow In custDS.Tables("Customers").Rows
Console.WriteLine("Customer ID:" & custRow("CustomerID").ToString())

For Each orderRow In custRow.GetChildRows(custOrderRel)
Console.WriteLine(" Order ID: " & orderRow("OrderID").ToString())
Console.WriteLine(vbTab & "Order Date: " &
orderRow("OrderDate").ToString())

For Each detailRow In orderRow.GetChildRows(orderDetailRel)
Console.WriteLine(vbTab & " Product: " &
detailRow.GetParentRow(orderProductRel)("ProductName").ToString())
Console.WriteLine(vbTab & " Quantity: " &
detailRow("Quantity").ToString())
Next
Next
Next

Sorry guys, everybody seems to be missing the question.

I have a typed DataSet with multiple, related tables and the relationships
set up. I want to loop through a table, and be able to pull out data from
the related tables. For example (using the example tables I provided
originally)

Accounts MR1, Martin, 1, 2
MR2, David, 2, 3
MR3, Stephen, 1, 3

Status 1, On Hold
2, Trading
3, Legal

Controller 2, Bob
3, Terry

I want to be able to loop through the Accounts table, pulling out:

MR1, Martin, On Hold, Bob
MR2, David, Trading, Terry
MR3, Stephen, On Hold, Terry

I want to do this programatically; how do I loop through the tables
correctly to pull out this data?
I can loop through the Accounts table without problem, [foreach (DataRow row
in ds.Tables["Accounts"].Rows) {}], but how do I pull the related data from
Status and Controller for each row that I process?

Cheers.

Ok, so everybody sings the praises of or dishes the DataSet; I use

them,
but

not nearly enough as I probably should. So, here is my question:

A DataSet can have more than one table, and those tables can be

related;
so,

how can I loop through all the records in a table and get related values
from the other tables?

For example, lets say that I create a dataset with 3 tables; Accounts,
Status and Controller:

Accounts: Reference, Name, StatusId, ControllerId
Status: Id, Text
Controller: Id, Name

I set up a relationship between the tables where Accounts.StatusId=Status.Id
and Accounts.ControllerId=Controller.Id

Now, how do I loop through the rows of Accounts, pulling the correct status
and controller from the related tables?

foreach(DataRow row in ds.Tables["Accounts"].Rows) {
Console.WriteLine(row["Reference"]); // Display the account
reference
Console.WriteLine( ???????????? ); // Display the controller
}

I know that this can be done as I have seen similar examples (though
normally working the other way around - Select the controller and run
through the accounts for example), but I have developed a mental block
(instead of software!) on how to do it.

Cheers.

 

[Martin Robins]

Fair point. No offense intended.

Hi Martin,


I think because you want to do it on your own way, which is for nobody
intresting to investigate because they have their own way, which they think
is better.

If you want to do it on your special way, maybe someone sees it who does it
the same or else you have to investigage it yourself I think.

Just my thought about it.

Cor

 

[Martin Robins]

Fair point. Most people would not do this if they were pulling the data from
a traditional relational database where they could join the tables, but what
if they cannot.

Anyway, sorry you got the brunt of it; no offense intended.

I understand what you are saying Martin and the reason you are having
trouble is that you are looking for a way to do something that isn't how
most people would do it in the first place.

In Classic ADO, you could pull data from several tables and wind up with it
all in one recordset, so it was easy to iterate through the one recordset
and pull your data out.

In .NET, you now have the option to pull data in from different tables and
store them in different tables and it makes things complicated to write 1
loop that will iterate through one table and pull records from another.

As I see it you have 3 possibilities:

1. Use the classic ADO Recordset in .NET (I wouldn't do it this way, but it
would give you what you want).

2. Within one loop that iterates through the main data, stop at each record
and loop again through the other 2 tables (individually) to extract the
correct data there. (Total of 3 loops and not very efficient).

3. Take my last suggestion and store the data in a more accessible way in
the first place so that you still get relational data, but don't have to
hunt for it.

Good luck,

-Scott

I do not wish to seem rude Scott, but why can nobody on this group attempt
to answer the question instead of telling me how they would do it
differently in the data.

I have already written 2 production systems in .NET with SQL Server behind.
I know how to use databases and normalise data, I can use
left/right/inner/outer joins to acheive the very same as I am looking for
directly from the database; but I want to do this particular piece this
way - 3 related tables in a DataSet and retrieve data from them via the
relations.

Thanks.

Martin.



up
the

record

in

the "Status" table with a primary key of "OnHold", not "1". The field that
contains "Bob" is related to a record in the table "Controller", where there
is a record that has "Bob" as its primary key value, not "2".

The "OnHold" field value in the Accounts table is related to the "OnHold"
record in the Status table and the "Bob" field value in the Accounts table
is related to the "Bob" record in the Controller table. The data you want
is already in the Accounts table and there is no need to worry about

how
to

access it (the point of your post). What you get for your trouble is that
if someone were to try to delete the "Bob" record from Controllers or the
"OnHold" record from Status, referential integrity would prevent it (the
point of having relationships).

All the data you need would be in the Accounts table to begin with and you
just loop through that.

But I do want the relations - I simply cannot work out how to access them
from the related tables whilst looping through the main table.

Here is a simple loop to read the main table; can somebody add in the
missing line to get the related data, making the assumption that I have
already set up the necessary relationships?

foreach (DataRow dataRow in ds.Tables["Accounts"].Rows) {
Console.WriteLine(dataRow["Reference"].ToString());
Console.WriteLine( ??? ); // What goes here to display the Status.Text
related to the Accounts.Reference just displayed?
}

Base on the previously supplied example data; I want to display

MR1
On Hold
MR2
Trading
MR3
On Hold


This is the question I am asking; I can populate the tables, I can

set
up

the relationships and I can loop through the main table sequentially
displaying the content; I simply cannot display the related table data.

Thanks.



Hi Martin,

If you do not want the relations, you can choose for the
datatable.select
and/or you can use the dataview.rowfilter or even a normal loop throught
the
tables, it is on you how to use it.

Cor

Sorry guys, everybody seems to be missing the question.

I have a typed DataSet with multiple, related tables and the
relationships
set up. I want to loop through a table, and be able to pull out data
from
the related tables. For example (using the example tables I provided
originally)

Accounts MR1, Martin, 1, 2
MR2, David, 2, 3
MR3, Stephen, 1, 3

Status 1, On Hold
2, Trading
3, Legal

Controller 2, Bob
3, Terry

I want to be able to loop through the Accounts table, pulling out:

MR1, Martin, On Hold, Bob
MR2, David, Trading, Terry
MR3, Stephen, On Hold, Terry

I want to do this programatically; how do I loop through the tables
correctly to pull out this data?
I can loop through the Accounts table without problem, [foreach
(DataRow
row
in ds.Tables["Accounts"].Rows) {}], but how do I pull the

related
data

from
Status and Controller for each row that I process?

Cheers.