Filtering and Sorting in ADO .NET

There are many ways to filter data. One way is to filter data by using a WHERE clause on your database query. In ADO .NET, there is some additional functionality that you can use to filter data in a dataset. Two fundamental approaches of it:

(i) DataTable Select Method
(ii) DataView Object

Filtering with Select Method
Imagine that a dataset contains data about Students and Grades tables. To filter on those data with the Grade of A, you can use Select method, which will return an array of rows.

VB
Dim ds_Student As New DataSet
Dim drows() As DataRow
drows = ds_Student.Tables(0).Select("Grade = 'A'")

C#
DataSet ds_Student = new DataSet();
DataRow[] drows;
drows = ds_Student.Tables(0).Select("Grade = 'A'");

Select method does not return filtered tables object as expected, it returns an array of DataRow objects instead. Namely, the returned object cannot be bind directly to a datagrid or other data bound controls.

You can iterate through the array using the foreach statement or for loop.

VB
For i = 0 To drows.Length – 1
     // your process here
Next

C#
for (i = 0; i <= drows.Length - 1; i++) {
     // your process here
}

Filtering and Sorting with DataViews
The DataSet.Table[0].DefaultView property is the DataView associated with a dataset.

A filter can be setup by using RowFilter property. A sort can be setup by using Sort property. See examples as below:

ds_Student.Table(0).DefaultView.Sort = "StudentID";
ds_Student.Table(0).DefaultView.RowFilter = "Grade = 'A'";

DataGrid1.DataSource = ds_Student.Table(0).DefaultView;

Note that a DataView can be bound to datagrid more easily, as shown above.