Creating a dataview from a dataset preserving relationships.

[mac4_life]

Hello,
I am using vb.net. I have a dataset with 2 tables, joined by a relationship. I need to create a dataview that shows all of the child records, but with one field from the corresponding parent record.

The parent table is named quantity (PK of part_no) and the child table is detail (FK of JobName11). Here is what I have now, that only shows all of the children:

Dim ModelDV As DataView
ModelDV = New DataView(ds1.Tables("detail"))

If somebody could tell me how to create this view preserving the relationship and including the "count" field of the quantity table, I would greatly appreciate it. Thanks a ton.

 

[Schenz]

Since you have already loaded the DataSet with all the values I would use the GetChildRows Method:

Dim drModel() As DataRow

' Use GetChildRows to get the related Records of the row in the parent table
' Must specify RelationShipName
drModel = ds1.Tables("Quantity").Rows(Whatever Row you want child records for).GetChildRows(Name of your Relationship)

Now drModel is an array of DataRows that are children of the specified parent row

 

[Schenz]

I may have just found a beter way on MSDN:

Dim catTable As DataTable = catDS.Tables("Categories")
Dim prodTable As DataTable = catDS.Tables("Products")

' Create a relation between the Categories and Products tables.
Dim catProdRel As DataRelation = catDS.Relations.Add("CatProdRel", _
                                                     catTable.Columns("CategoryID"), _
                                                     prodTable.Columns("CategoryID"))

' Create DataViews for the Categories and Products tables.
Dim catView As DataView = New DataView(catTable, "", "CategoryName", DataViewRowState.CurrentRows)
Dim prodView As DataView

' Iterate through the Categories table.
Dim catDRV, prodDRV As DataRowView

For Each catDRV In catView
  Console.WriteLine(catDRV("CategoryName"))

  ' Create a DataView of the child product records.
  prodView = catDRV.CreateChildView(catProdRel)
  prodView.Sort = "ProductName"

  For Each prodDRV In prodView
    Console.WriteLine(vbTab & prodDRV("ProductName"))
  Next

Next

 

[Paszt]

Perhaps this article can help: HOW TO: Implement a DataSet JOIN Helper Class in Visual Basic .NET