none
Filtering parent/child rows when there are multiple parent rows that match criteria RRS feed

  • Question

  •  

    I have a dataset with three tables: Person table with basic name and ID information, Phone table with the set of phone numbers, and Address table with the set of addresses.

     

    If I filter to one person, I can easily use GetChildRows to get the related data.

     

    But my application also allows filtering by lots of different fields, like last name starting with "M" or title containing "Manager". I can filter the Person table just fine ... but cannot figure out how to filter the related tables.

     

    I cannot use GetChildRows unless I process each individual filtered parent row ... which is just way to slow.

     

    I cannot use the DataViewManager because it does not seem to give me anything to work with. It only seems to know how to bind to a grid. (Wish it had a .ToTable method!)

     

    Anyone know if it is possible to get a filtered dataset containing filtered parent rows and their related child rows without processing the parent rows one at a time?

    Thursday, September 27, 2007 3:42 PM

Answers

  • OK, I figured this out. This is *way* cool:
     
                        Dim dt As DataTable
                        Dim vm As DataViewManager = ContactsDataset.DefaultViewManager
                        vm.DataViewSettings(TN_Contacts).RowFilter = sWhere
                        dt = vm.CreateDataView(ContactsDataset.Tables(TN_Contacts)).ToTable
     
                        ' Add the filtered table to the new dataset
                        FilteredContactsDataset = New DataSet
                        FilteredContactsDataset.Tables.Add(dt)
     
                        For i As Integer = 1 To ContactsDataset.Tables.Count - 1
                            vm.DataViewSettings(ContactsDataset.Tables(i).TableName).RowFilter = "Parent." & sWhere
                            dt = vm.CreateDataView(ContactsDataset.Tables(i)).ToTable
                            FilteredContactsDataset.Tables.Add(dt)
                        Next i
     
    The sWhere clause is something like this: "LastName Like '%R'"
     
    This code then filters the child based on the parent using a RowFilter like this: "Parent.LastName Like %R"
     
    Thursday, September 27, 2007 4:54 PM