HOW TO: Perform a Complex Filter in ADO.NET (321896)

SUMMARY

In ADO.NET, you can use the DataView.RowFilter property and the DataTable.Select method to filter a subset of records. However, the expressions that you can use have limitations. This article demonstrates three techniques to work around the limitations of the filter expressions.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:

• Microsoft Visual Studio .NET installed on a computer that is running a compatible Microsoft Windows operating system

This article assumes that you are familiar with the following topics:

• Microsoft Visual Basic .NET Windows applications
• ADO.NET coding

back to the top

Techniques

To work around the limitations of the filter expressions, you can use one of the following three techniques:

• If you do not have to bind the rows to anything, you can add the rows that you want to an ArrayList class. This returns similar results as the DataTable.Select method, which returns an array of DataRow objects.

  Dim foundRows As New ArrayList()
  ' DataTable1 is an existing DataTable object. You want to select the second and fifth rows from this object.
  foundRows.Add(DataTable1.Rows(1))
  foundRows.Add(DataTable1.Rows(4))
  					

• Add a boolean column with a Hidden attribute to the DataTable so that this column does not appear in the DataGrid. Loop through the records, set the flag where appropriate, and then filter on the boolean column. This method works best when you have tables that are related, when you must have hierarchical navigation, and when you must maintain referential integrity.

  'DataTable1 is an existing DataTable object.
  DataTable1.Columns.Add("Flag", GetType(Boolean))
  DataTable1.Columns("Flag").ColumnMapping = MappingType.Hidden
  Dim dr As DataRow
  For Each dr In DataTable1.Rows
    ' If the criteria are satisfied Then
        dr("Flag") = True
    ' End If
  Next
  Dim dv As New DataView(DataTable1, "Flag = True", "", DataViewRowState.CurrentRows)
  DataGrid1.DataSource = dv
  					

• Create a new DataSet object, and then add a clone of the DataTable to the DataSet object. Use the DataTable.ImportRow method to copy the rows you want. If you have to update capability, use the DataSet.Merge method to merge the DataTable back into the main DataSet object. This method is best only if you have to update a single table that has no referential integrity or hierarchical navigation issues.

  Dim dsFiltered As New DataSet()
  ' dsMain is the existing DataSet object. You want to select the second and fifth rows from the "MyTable" table.
  dsFiltered.Tables.Add(dsMain.Tables("MyTable").Clone)
  dsFiltered.Tables(0).ImportRow(dsMain.Tables("MyTable").Rows(1))
  dsFiltered.Tables(0).ImportRow(dsMain.Tables("MyTable").Rows(4))
  
  dsMain.AcceptChanges()   ' This is not required if you never edit dsMain.
  dsMain.Merge(dsFiltered, False)
  MyDataAdapter.Update(dsMain, "MyTable")
  					

back to the top