Getting certain item from two related tables in a dataset

[Jacobier]

Hi
I am a newbie for vb.net. I am trying to related two tables in a DataSet. I have looked through a lot of threads but could not find the answer I want. Say, I have two tables:

table1 : "ID", "ID in table 2"
1 3
2 4

table2 : "ID", "other inf "
1 a
2 b
3 c
4 d
The second field in table1 connect these two tables together.

I know what to do up to the step for building the relation between them.

My question is:

1.After relation setup, what is the syntax for get a certain item from table2, if "ID" in table 1 is given. For example, given ID= 2 in the table 1 , I want to get the output "other inf "= d.

2. Will it be less effective if I directly code this function without using dataset relation, such as:
search "ID=2" in table 1-> get 4 -> search "ID=4" in table 2 with -> get result d

I have seen an example in the forum, but the operation is on items. So I still need the syntax for items

Thank you very much!

 

[IanRyder]

Hi,

There is nothing wrong with using logic that you have described in your option 2 but I would always recommend using Relationships where it is possible since it nicely links related information for you to work with.

If you add a Relationship to your Dataset then you can look for something in your Parent Table and then use that relationship name to easily access the child objects of that Parent. Have a look at this example:-

Using this block of code to create some data with a relationship:-

Private DS As New DataSet
 
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
  DS.Tables.Add("ParentTable")
  With DS.Tables(0)
    .Columns.Add("IDNo", GetType(Integer))
    .Columns.Add("ChildID", GetType(Integer))
    For Counter As Integer = 1 To 10
      .Rows.Add(Counter, Counter + 10)
    Next
  End With
 
  DS.Tables.Add("ChildTable")
  With DS.Tables(1)
    .Columns.Add("ChildID", GetType(Integer))
    .Columns.Add("ChildValue", GetType(String))
    For Counter As Integer = 1 To 10
      .Rows.Add(Counter + 10, "SomeValue" & Counter.ToString)
    Next
  End With
 
  DS.Relations.Add("ParentToChild", DS.Tables(0).Columns(1), DS.Tables(1).Columns(0))
End Sub

You can then use the following example to get the child rows from a query on the Parent:-

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
  Dim someChildRows() As DataRow = DS.Tables(0).Rows.Cast(Of DataRow).Where(Function(x) CInt(x(0)) = 2).Select(Function(x) x.GetChildRows("ParentToChild").ToArray).FirstOrDefault
 
  For Each DR As DataRow In someChildRows
    MsgBox(DR(1).ToString)
  Next
End Sub

Hope that helps.

Cheers,

Ian

 

[cjard]

If you use strongly typed datasets, then it becomes a simple matter of:

'if t2 is a child of t1, then for any given row in t1 get all the child rows in t2
someTable1Row.Gettable2Rows()

'if t2 is a child of t1, then for any given row in t2, access the parent row
someTable2Row.table1Row



Simple eh?