Filtering and Sorting in ADO.NET

Overview

ADO.NET supports many ways to manage memory-resident data returned in a DataSet. In this article you will see that ADO.NET exposes addidional functionality to the ways you can sort and filter data.

Because of the very nature of the disconnected DataSet architecture used in ADO.NET, it's often impractical or impossible to requery a remote data source to reorder or further filter data. Of course, this assumes that you are not implementing a traditional client/server application, which can execute further server-side sorting and filtering.

ADO.NET supports two fundamental approaches for performing these operations:

* The DataTable Select Method - This method is overloaded to accept arguments to filter and sort data rows returning an array of DataRow objects.

* The DataView object sort, filter and find methods - This object uses the same filter arguments supported by the Select method, but the DataView extrudes structures that can be bound to data-aware controls.

Filtering and Sorting with the DataTable Select Method

The DataTable Select returns a DataRow array from a table per a particular filter and/or sort order. The content reflects changes to the underlying table, but the membership and ordering remain static.

The DataTable Select method accepts a filter and sort argument to return an arry of DataRow objects that conform to the criteria in a FilterExpression.

public DataRow[] Select();
Gets an array of all DataRow objects.

public DataRow[] Select(string);
Gets an array of all DataRow objects that match the filter criteria

public DataRow[] Select(
string, string);
Gets an array of all DataRow objects that match the filter criteria, in the specified sort order

public DataRow[] Select(
string, string, DataViewRowState);
Gets an array of all DataRow objects that match the filter in the order of the sort, that match the specified state

For example, a Filter Expression might look like this:

"OrderDate >= '01.03.1998' AND OrderDate <= '31.03.1998'"

A typical Sort Expression is imply the name of the column to sort following by an optional ASC or DESC.

"OrderDate DESC"

Problem with the DataTable Select

The fundamental problem with the Select method is that it does not return a flitered table object as expected - it returns an arry of DataRow objects. This means you can't directly bind this array to a DataGrid or other data bound controls. To accomplish this, use a DataView as shown later in this article.


Filtering and Sorting with the DataView Object

A DataView enables you to create different views of the data stored in a DataTable, a capability that is often used in data-binding applications. Using a DataView, you can expose the data in a table with different sort orders, and you can filter the data by row state or based on a filter expression.

A DataView provides a dynamic view of data whose content, ordering, and membership reflect changes to the underlying DataTable as they occur. This is different from the Select method of the DataTable, which returns a DataRow array from a table per a particular filter and/or sort order and whose content reflects changes to the underlying table, but whose membership and ordering remain static. The dynamic capabilities of the DataView make it ideal for data-binding applications.

A DataView provides you with a dynamic view of a single set of data to which you can apply different sorting and filtering criteria, similar to the view provided by a database. However, a DataView differs significantly from a database view in that the DataView cannot be treated as a table and cannot provide a view of joined tables. You also cannot exclude columns that exist in the source table, nor can you append columns, such as computational columns, that do not exist in the source table.

Default DataView

The DataTable.DefaultView Property is the DataView associated with a DataTable, it can be used to sort, filter, and search a DataTable.

DataView.RowFilter

The DataView.RowFilter Property gets or sets the expression used to filter which rows are viewed in the DataView. To form a RowFilter value, specify the name of a column followed by an operator and a value to filter on. The value must be in quotes. For example:

"LastName = 'Smith'"

To return only those columns with null values, use the following expression:

"Isnull(Col1,'Null Column') = 'Null Column'"
After you set the RowFilter Property, ADO.NET hides (but does not eliminate) all rows in the associated DataTable object's Rows collection that don't match the filter expression. The DataView.Count property returns the number of rows remaining unhidden in the view.
Sorting the DataView
To sort a DataView, construct a sort expression string, note that the Sort property can accept any number of columns on which to sort the Rows collection. For example use the the following sort expression string:

"Price DESC, Title ASC"

This topic taken from this website
http://www.akadia.com/services/dotnet_filter_sort.html