Compartir a través de

Using DataView.RowFilter

  • Artículo

Often in the forums we see questions about having SELECT-like functionality on local data (that is, on a DataSet, without accessing a database).

There are a number of operations that the ADO.NET classes can perform on the client which are often good enough for many scenarios, and on this post I want to discuss one in particular.

Given a DataTable, how do we select only the rows that meet certain criteria? Enter DataView.RowFilter.

This little gem is mentioned on Sorting and Filter Data Using a DataView on MSDN, but I often talk to developers who are unaware of how to use this property.

RowFilter is an expression string that can be evaluated to true or false, and uses the syntax described in the DataColumn.Expression documentation.

But enough talk and pointers. Here's a super-fast way of trying out some expressions for yourself (save this to a file named dv.cs if you build with the command line shown).

// Build with: %windir%\Microsoft.NET\Framework\v2.0.50727\csc.exe dv.cs

using System;
using System.Data;
using System.Windows.Forms;

namespace NS
{
public class DoIt
{
[STAThread]
public static void Main(string[] args)
{
DataTable table = new DataTable();
table.Columns.Add(new DataColumn("ID", typeof(int)));
table.Columns.Add(new DataColumn("Name", typeof(string)));
table.Columns.Add(new DataColumn("Date", typeof(DateTime)));
table.Rows.Add(new object[] { 1, "Charlie", DateTime.Now });
table.Rows.Add(new object[] { 2, "Candy", DateTime.Now.AddHours(2) });

DataView dv = new DataView(table);
DataGridView view = new DataGridView();
view.DataSource = dv;
view.Dock = DockStyle.Fill;

Form form = new Form();
form.Text = "DataView RowFilter Sample";

Label label = new Label();
label.Dock = DockStyle.Bottom;

            TextBox box = new TextBox();
box.Dock = DockStyle.Top;
box.TextChanged += delegate {
try
{
dv.RowFilter = box.Text;
label.Text = "";
}
catch(Exception exception)
{
label.Text = exception.Message;
}
};

            form.Controls.Add(view);
form.Controls.Add(box);
form.Controls.Add(label);

Application.Run(form);
}
}
}

As you type, the RowFilter property is updated, and if the expression isn't correct, the error message is displayed at the bottom of the window.

To get you started, try some of these filters:

  • ID < 2 (displays row with ID=1)
  • Name like 'ch*' (displays row with Name=Charlie)
  • Name like 'ch%' (displays row with Name=Charlie)
  • Name like 'c%' (displays both rows)
  • ID % 2 = 0 (displays row with ID=2, divisible by 2)

There is more functionality available - I strongly recommend reading through the Expression page on MSDN.

Enjoy!

 

This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm .

Comments

  • Anonymous
    March 10, 2007
    That's very handy - thanks. I'm working on app where I have to use RowFilter extensively but the one thing I'm really having trouble with is testing columns that are nulls as part of the filter.  You are meant to be able to do something like IsNull(EmployeeNickname,'') = '' but I can't get this to work - it just doesn't match at all even when I know that column has many null values.    I've also tried it with a numeric field - eg: IsNull(LastPayRiseAmount,0) = 0 but that doesn't work either. The MSDN documentation on IsNull is awful - there's no proper explanation of testing null columns at all and I can't work out how else it's meant to work other than what all the examples I've seen suggest which all suggest the above examples should work. I've hunted all over for info on how to get IsNull to work - any ideas?  

  • Anonymous
    March 11, 2007
    Forget the above comment - I'd coped the code from an example in which RowStateFilter was set to DataViewRowState.ModifiedCurrent - and that would never return any matches as it is a static dataset I'm looking up. (And yes, the word "idiot" springs to my mind as well!)

  • Anonymous
    March 19, 2007
    After my DataView.RowFilter post , the next obvious step is of course a short post on using the DataView.Sort