Microsoft KB Archive/321896

= HOW TO: Perform a Complex Filter in ADO.NET =

Article ID: 321896

Article Last Modified on 5/23/2005

-

APPLIES TO


 * Microsoft ADO.NET 1.1
 * Microsoft ADO.NET 1.0
 * Microsoft Visual Studio .NET 2003 Enterprise Architect
 * Microsoft Visual Studio .NET 2003 Enterprise Developer
 * Microsoft Visual Studio .NET 2003 Academic Edition
 * Microsoft Visual Studio .NET 2002 Professional Edition
 * Microsoft Visual Studio .NET 2002 Enterprise Architect
 * Microsoft Visual Studio .NET 2002 Enterprise Developer
 * Microsoft Visual Studio .NET 2002 Academic Edition

-



This article was previously published under Q321896



IN THIS TASK
SUMMARY
 * Requirements
 * Techniques



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(&quot;Flag&quot;, GetType(Boolean)) DataTable1.Columns(&quot;Flag&quot;).ColumnMapping = MappingType.Hidden Dim dr As DataRow For Each dr In DataTable1.Rows ' If the criteria are satisfied Then dr(&quot;Flag&quot;) = True ' End If Next Dim dv As New DataView(DataTable1, &quot;Flag = True&quot;, &quot;&quot;, 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 &quot;MyTable&quot; table. dsFiltered.Tables.Add(dsMain.Tables(&quot;MyTable&quot;).Clone) dsFiltered.Tables(0).ImportRow(dsMain.Tables(&quot;MyTable&quot;).Rows(1)) dsFiltered.Tables(0).ImportRow(dsMain.Tables(&quot;MyTable&quot;).Rows(4))

dsMain.AcceptChanges  ' This is not required if you never edit dsMain. dsMain.Merge(dsFiltered, False) MyDataAdapter.Update(dsMain, &quot;MyTable&quot;) 

back to the top

Keywords: kbhowtomaster kbsystemdata kbdataadapter KB321896

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.