Using DataView.RowFilter

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 .