How to: Filter and Sort Directly in Data Tables

Article
12/13/2006

You can filter and sort the contents of a data table directly by calling a table's Select method. This strategy allows you to filter and sort only at run time. If you want to set up criteria for filtering and sorting at design time, and especially if you want to bind controls to the results of filtering or sorting, use a BindingSource. For more information, see How to: Filter and Sort Data.

When you sort directly in the data table, you do not reorder the contents of the table. Instead, you work with a result set representing the sorted records.

To filter and sort directly in a data table

Call the data table's Select method, passing it up to three parameters:

            datatable.Select(filterExp, sortExp, rowstatevalue)

The following table lists the parameter values.

Parameter	Description
filterExp	A filter expression that should evaluate to true or false.
sortExp	A sort expression. This is typically the name of a table column, but can be any calculated value. Note For more information about the syntax of filter and sort expressions, see DataColumn.Expression Property.
rowstatevalue	A value indicating what version or state you want to filter on. The acceptable values for this parameter are members of the DataViewRowState enumeration, such as DataViewRowState.CurrentRows. For more information about row state and row version, see Introduction to Dataset Updates.

You can call the method with only the first or only the first and second parameters. To skip a parameter, pass an empty string as a placeholder. The **Select** method returns an array of data rows.

The following example shows how to filter and sort the Customers data table in a dataset called dataSet1. The filter expression selects customers whose status is active. The sort expression causes the results to be sorted by the City column, and the final parameter specifies that you want to see only the current (that is, post-change) versions of the records. The sorted list is displayed in a list box that is assumed to exist on the form.

Dim filterExp As String = "Status = 'Active'"
Dim sortExp As String = "City"
Dim i As Integer
Dim drarray() As DataRow
drarray = dataSet1.Customers.Select(filterExp, sortExp, DataViewRowState.CurrentRows)

For i = 0 To (drarray.Length - 1)
    listBox1.Items.Add(drarray(i)("City").ToString)
Next

string filterExp = "Status = 'Active'";
string sortExp = "City";
System.Data.DataRow[] drarray = dataSet1.Customers.
    Select(filterExp, sortExp, System.Data.DataViewRowState.CurrentRows);

for (int i=0; i < drarray.Length; i++)
{
    listBox1.Items.Add(drarray[i]["City"].ToString());
}

System.String filterExp = "Status = 'Active'";
System.String sortExp = "City";
System.Data.DataRow drarray[] = dataSet1.get_Customers().
    Select(filterExp, sortExp, System.Data.DataViewRowState.CurrentRows);

for (int i = 0; i < drarray.length; i++)
{
    listBox1.get_Items().Add(drarray[i].get_Item("City").ToString());
}

The following example shows how to call the Select method to filter only on row state (skipping the filter and sort expressions), returning only deleted records:

drarray = dataSet1.Customers. _
    Select(Nothing, Nothing, DataViewRowState.Deleted)

drarray = dataSet1.Customers.
    Select(null, null, System.Data.DataViewRowState.CurrentRows);

drarray = dataSet1.get_Customers().
    Select(filterExp, sortExp, System.Data.DataViewRowState.CurrentRows);

How to: Filter and Sort Directly in Data Tables

To filter and sort directly in a data table

See Also

Concepts

Other Resources

Additional resources