DowntownIT
asked on
nested loop datatable or dataview
I have a datatable stored in a viewstate, this datatable has a couple columns called customer# and perf_no. A sample of the result set would be:
Customer# perf_no
1		100
1		200
1		300
2		100
2		300
I need to loop through the datatable getting the first customer#, do something with that customer# then loop through it again grabbing each perf_no for that customer and do something for each perf_no. The logic should look like something like this.
For each customer# in datatable
	Do something(customer#) 'Should execute only once for each customer
		For each perf_no filtered by current customer#
			Do somethingElse(customer#,pe rf_no)
		Next
Next
Thanks for the help!
Customer# perf_no
1		100
1		200
1		300
2		100
2		300
I need to loop through the datatable getting the first customer#, do something with that customer# then loop through it again grabbing each perf_no for that customer and do something for each perf_no. The logic should look like something like this.
For each customer# in datatable
	Do something(customer#) 'Should execute only once for each customer
		For each perf_no filtered by current customer#
			Do somethingElse(customer#,pe
		Next
Next
Thanks for the help!
ASKER
I did say datatable. Essentially what I am looking for is how to loop through that datatable taking each distinct customer# and then and then loop a second time through that datatable for each perf_no for that customer.
Thanks,
Thanks,
Hi, please try this example that I made for you:
The key of what you want to do is on the DataRelation, I hope this help.
' Your original datatable
Dim dttMyDataTable As New DataTable
dttMyDataTable.Columns.Add("CustomerId", GetType(Integer))
dttMyDataTable.Columns.Add("perf_no", GetType(Integer))
' Dummy data (for demo)
dttMyDataTable.Rows.Add(1, 100)
dttMyDataTable.Rows.Add(1, 200)
dttMyDataTable.Rows.Add(1, 300)
dttMyDataTable.Rows.Add(2, 100)
dttMyDataTable.Rows.Add(2, 300)
dttMyDataTable.AcceptChanges()
Dim dttCustomer As New DataTable
dttCustomer.Columns.Add("CustomerId", GetType(Integer))
dttCustomer.PrimaryKey = New DataColumn() {dttCustomer.Columns(0)}
' Gets distincts customer ids
For Each row As DataRow In dttMyDataTable.Rows
Dim customerIdValue As Object = row("CustomerId")
Dim parentRow As DataRow = dttCustomer.Rows.Find(customerIdValue)
If parentRow Is Nothing Then
dttCustomer.Rows.Add(customerIdValue)
End If
Next
dttCustomer.AcceptChanges()
' Required to be able to use DataRelations.
Dim myDataSet As New DataSet
myDataSet.Tables.Add(dttCustomer)
myDataSet.Tables.Add(dttMyDataTable)
Dim relCustomerPerf As DataRelation = myDataSet.Relations.Add("MyRelation", dttCustomer.Columns("CustomerId"), dttMyDataTable.Columns("CustomerId"))
' Parents rows
For Each parentRow As DataRow In dttCustomer.Rows
' Child rows of the current parent
For Each childRow As DataRow In parentRow.GetChildRows(relCustomerPerf)
Console.WriteLine(childRow(1))
Next
Next
The key of what you want to do is on the DataRelation, I hope this help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Above code might have compilation errors
ASKER
Worked for me and easy to follow. Thanks!
Your question is not clear. Explain it in detail.