Support filtering and searching on multiple columns with RowFilterBuilder

Thomas Haller

2.35/5 (13 votes)

Jul 2, 2006

CPOL

48474

1512

A class that builds a RowFilter for DataViews to support filtering on multiple columns.

Sample Image

Introduction

If working with a DataGridView or a DataView, you may have been confronted with the problem that you want to supply an easy to use filter (or search) mechanism. In my opinion, "easy to use", in this case, means that the user doesn't have to think about what columns he wants to filter - he just needs to type in the words he is looking for. In the screenshot above, it means that the user is looking for an "Owner" in "Mexico".

Coding this scenario can be very bizarre - that's the reason why I wrote my own class that handles the logic. All you need is to pass the result of a function to the RowFilter property of a DataView.

//example
string filter = Codeproject.RowFilterBuilder.BuildMultiColumnFilter(
                this.textBox1.Text, dataView1);
dataView1.RowFilter = filter;

In the scenario above, dataView1.RowFilter would now be something like this:

"( (CONVERT( [CustomerID], 'System.String') like '%Mexico%' ) OR  
  (CONVERT( [CompanyName], 'System.String') like '%Mexico%' ) OR  
  (CONVERT( [ContactName], 'System.String') like '%Mexico%' ) OR 
  (CONVERT( [ContactTitle], 'System.String') like '%Mexico%' ) OR 
  (CONVERT( [Address], 'System.String') like '%Mexico%' ) OR 
  (CONVERT( [City], 'System.String') like '%Mexico%' ) OR 
  (CONVERT( [Region], 'System.String') like '%Mexico%' ) OR 
  (CONVERT( [PostalCode], 'System.String') like '%Mexico%' ) OR 
  (CONVERT( [Country], 'System.String') like '%Mexico%' ) OR 
  (CONVERT( [Phone], 'System.String') like '%Mexico%' ) OR 
  (CONVERT( [Fax], 'System.String') like '%Mexico%' )) AND ( 
  (CONVERT( [CustomerID], 'System.String') like '%Owner%' ) OR 
  (CONVERT( [CompanyName], 'System.String') like '%Owner%' ) OR 
  (CONVERT( [ContactName], 'System.String') like '%Owner%' ) OR 
  (CONVERT( [ContactTitle], 'System.String') like '%Owner%' ) OR 
  (CONVERT( [Address], 'System.String') like '%Owner%' ) OR 
  (CONVERT( [City], 'System.String') like '%Owner%' ) OR 
  (CONVERT( [Region], 'System.String') like '%Owner%' ) OR 
  (CONVERT( [PostalCode], 'System.String') like '%Owner%' ) OR 
  (CONVERT( [Country], 'System.String') like '%Owner%' ) OR 
  (CONVERT( [Phone], 'System.String') like '%Owner%' ) OR 
  (CONVERT( [Fax], 'System.String') like '%Owner%' ))"

Et voila - that's it!

History

07/02/2006: Initial release.