relationship tables and datarelations

  • Thread starter Thread starter niv
  • Start date Start date
N

niv

Hi,

I am unsure on how to retrieve data from tables 2 tables
that are related through a relationship table..

TableOne
--------
TableOneID
TableOneDesc

TableTwo
--------
TableTwoID
TableTwoDesc


TableOne_TableTwo_Relationship
------------------------------
TableOne_TableTwo_RelID
TableOneID
TableTwoID

I have related TableOne to TableOne_TableTwo_Relationship.
Then I have related TableTwo to
TableOne_TableTwo_Relationship.

I am unsure on how to traverse though the set
relationships to retrieve data. ie) I want to print out
all TableTwoDesc that have been related to a specific
TableOneID.

Thanks for your help,

niv
 
I have added the DataRelation objects that I have created
below.


DataRelation TableOneToRelTable = dsTest.Relations.Add
("T1ToRel", TableOne.Columns["TableOneID"],
TableOne_TableTwo_Relationship.Columns["TableOneID"]);

DataRelation TableTwoToRelTable = dsTest.Relations.Add
("T2ToRel",TableTwo.Columns["TableTwoID"],
TableOne_TableTwo_Relationship.Columns["TableTwoID"]);
 
I have to do it this way because I am working with 3
DataTables.

What do you mean by use a SQL statement?
how? When retrieving the DataSet?
Thanks,
niv
-----Original Message-----
I am not sure what you mean by "traversing through the set relationships to
retrieve data". Why can't you use just a SQL select statement?

SELECT t2.TableTwoDesc
FROM TableOne t1 INNER JOIN
Click to expand...
TableOne_TableTwo_Relationship t12
 
I meant making SQL requests against tables in the database using appropriate
DB access components, like DataAdapter, DbCommand (Ole or Sql) for
populating the dataset.

As I see now, you mean something else. You already have your dataset with 3
tables inside and you want to run your requests against the tables in the
dataset as opposed to the ones in the database. If you insist on this model,
I can't help you much, I don't have any practical experience in selecting
required data from the related tables in the dataset. But, it looks to me,
you could charge your database with running selects, with sql it's easy and
straightforward. Just one data access operation with the select will
replaces many lines on code you might have to write otherwise. Also, you
wouldn't need your DataRelation object.

OK, I understand that you might still have good reasons to follow your way.

Eliyahu
 
I have recently found myself in the same boat because I am populating a
DataSet via an XML file, not from a database. I am currently looking at a
number of methods to filter and sort the data.

One is the Select method of the DataTable object. This seems to provide an
SQL type selection syntax. Another is the GetChildRows method of the
DataRow object which doesn't appear to offer any type of sort capabilities,
if that is important to you. And lastly I'm looking at manually iterating
the rows of the tables with a foreach loop in search of matching child rows.

As of yet I don't have any clue as to the performance differences of any of
these methods. Fortunately for me my DataSet simply has two tables with one
relationship and there will be very few rows in either table. I doubt that
performance will be an issue for me so I'll most likely choose the method
that produces the cleanest code unless I can find direction otherwise.

HTH
 
Back
Top