Dataset Relation

Imports System.Console

Module Hierarchical1

Sub Main()
Dim daHData As SqlDataAdapter
Dim dsHData As New DataSet()

Dim dtPublishers As DataTable

Dim drPublisher As DataRow
Dim drTitle As DataRow

Dim sSQL As String = "SELECT Pub_Id, Title, Price FROM Titles ;SELECT Pub_ID, Pub_Name FROM Publishers"
Dim cnstring As String = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Pubs;Data Source=MALPRO777\PYRAMID"

daHData = New SqlDataAdapter(sSQL, cnstring)

Try

daHData.Fill(dsHData, "Titles")

With dsHData

.Tables(1).TableName = "Publishers"
dtPublishers = .Tables("Publishers")

.Relations.Add("PubTitles", dtPublishers.Columns("Pub_ID"), _
dsHData.Tables("Titles").Columns("Pub_ID"))
End With

Catch e As SqlException
Console.WriteLine(e.Message)
End Try

For Each drPublisher In dtPublishers.Rows
WriteLine(drPublisher("Pub_Id") & vbTab & drPublisher("Pub_Name"))
WriteLine("=====================")

For Each drTitle In drPublisher.GetChildRows("PubTitles")
WriteLine(drTitle("Title") & vbTab & IIf(CLng(CStr(0 & drTitle("price"))) > 0, drTitle("price"), "n/a"))
Next
Next
Console.ReadLine()
End Sub
End Module






Imports System.Data.SqlClient
Imports System.Console

Module Hierarchical2
Sub Main()
Dim daHData As SqlDataAdapter
Dim dsHData As New DataSet()

Dim dtPublishers As DataTable

Dim drPublisher As DataRowView
Dim drTitle As DataRow

Dim sSQL As String = "SELECT Pub_Id, Title, Price FROM Titles ;SELECT Pub_ID, Pub_Name FROM Publishers"
Dim cnstring As String = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Pubs;Data Source=MALPRO777\PYRAMID"

daHData = New SqlDataAdapter(sSQL, cnstring)

Try

daHData.Fill(dsHData, "Titles")

With dsHData

.Tables(1).TableName = "Publishers"
dtPublishers = .Tables("Publishers")

.Relations.Add("PubTitles", dtPublishers.Columns("Pub_ID"), _
dsHData.Tables("Titles").Columns("Pub_ID"))
End With

Catch e As SqlException
Console.WriteLine(e.Message)
End Try


dtPublishers.DefaultView.RowFilter = "Pub_ID < 2000"
dtPublishers.DefaultView.Sort = "PUB_ID Desc"

For Each drPublisher In dtPublishers.DefaultView
With drPublisher
WriteLine(.row("Pub_Id") & vbTab & .row("Pub_Name"))
WriteLine("=====================")

For Each drTitle In .Row.GetChildRows("PubTitles")
WriteLine(drTitle("Title") & vbTab & IIf(CLng(CStr(0 & drTitle("price"))) > 0, drTitle("price"), "n/a"))
Next
End With
Next
Console.ReadLine()
End Sub

End Module