Introduction to Filtering and Sorting in Datasets
After a dataset has been populated, your application might need to view its data in various ways. For example, you might want to view the records in a specific order or see only a subset of the records (that is, filter them). Because the dataset is disconnected from the data source, it is often impractical and resource-intensive to re-execute SQL commands to perform these actions.
Instead, you can use built-in dataset features to filter and sort. You have two options:
- Data tables support a Select method that you can call to filter and sort. The method does not change the contents or order of records in a table; instead, it presents you with a list of records (an array) representing the criteria you specify.
- You can use a data view (DataView object). A data view is an object that acts as a layer on top of the data table, providing a filtered and sorted view of the table's contents. (You can also use a data view manager, which acts like a collection of data views.) A data view is similar to a view in a database, in that it is not a copy of the data. Instead, it is simply a different way of seeing the data in a table.
Both methods provide the same filtering and sorting capabilities. The primary difference is that you can call a data table's Select method only in code at run time. Using a data view, on the other hand, provides these advantages:
- You can create and configure the data view at design time, with the option to set its properties at run time as well.
- You can use it in data binding; that is, you can bind controls to it in a designer.
- You can create multiple data views to see the data in a table in different ways. For example, one data view might display data in an Orders table in date order, and another data view might show it in customer order.
Note In some instances, you might not be using a dataset at all. Using a dataset (and filtering and sorting data in it) is an appropriate choice when you want to work with the same set of records while disconnected from the data source. For more information, see Recommendations for Data Access Strategies.
Default Data Views
If you choose to use data views, you can add one to a form or component in much the same way you do other data elements, namely, by dragging it onto a designer and setting its properties. However, even if you do not explicitly add a data view to your form or component, a data view — the default data view — is already available for each table in the dataset. You can access this default data view using a table's DefaultView property, which returns a DataView object. You can set the default data view's properties at run time.
For the most part, you can use the default data view as you would any data view. But adding custom data views to your form or component has these advantages:
- You can use multiple data views on a single set of data.
- You can set data view properties at design time. (The default data view is available only at run time.)
- You can assign names to your data views.
Filtering in a Data Table
Using the table's Select method or the RowFilter property of a data view, you can filter records in a data table to make available only records you want to work with. This is useful when you want to work with different subsets of the records in a dataset table. To specify filter criteria, you use the same expression syntax used to create column expressions. The filter expression is evaluated as a Boolean expression; if the expression returns true, the record is included. A filter expression might look like the following:
Price > 10.00
In the expression you can reference column values by name (as in the preceding example), property values, and literal values.
Filtering on Row State and Version
A dataset can maintain different versions of records in tables. When records are first filled in, the dataset contains the original version of the record. If you change a record, the dataset maintains a different version — the current version — that reflects the changes. A property on the record indicates whether the record is unmodified, updated, new, or deleted. For more information about row versions and row state, see Introduction to Dataset Updates.
A common use for filters is to specify only the current versions of records in the data table. If records have been changed, there are two versions of a record: The current version reflecting the changes, and the original version representing the record before any changes were made. Records are also flagged according to their status: new, unchanged, deleted, or modified. A deleted record, for example, still exists in the data table, but its row-state flag has been set to deleted.
If you get all the records in a data table by simply looping through the table's Rows collection, you will include records that have been modified, new records, and deleted records. A common filter, therefore, is one that returns only specific versions of a record or records in a certain state.
If you are calling the Select method and passing it a filter expression, you can add a parameter indicating what row state or version you want to filter on. If you are using a data view, you can set the RowStateFilter to indicate what version or state of a row you want.
Sorting is similar to filtering, in that you specify a sort expression. A typical sort expression is simply the name of the column to sort by. For example, to sort by the OrderDate column, you specify the sort expression
OrderDate. However, you can sort by the value of any expression, including calculated values. If you call a table's Select method, you pass the sort expression as a parameter. If you are using data views, you specify the sort expression as the value of the view's Sort property.
Data View Manager
You can define individual data views that sort or filter the data in each dataset table. If the dataset contains multiple tables, an alternative is to create a data view manager (a DataViewManager object). The data view manager works something like a dataset-wide data view: It provides a single object that manages a collection of data views, each of which is associated with a specific table in a dataset.
A data view manager is particularly useful when you are working with related tables and want to sort or filter child records from a master table. As an example, imagine a dataset containing a Customers table, an Orders table, and a DataRelation object that links the two tables. You can use individual data views to sort the Customers table by CompanyName and the Orders table by OrderDate. However, when you use individual data views to sort this way — for example, when you use the relation object's GetChildRecords method to get Orders records — the records won't be sorted. In contrast, if you use a data view manager, they will be.
To use a data view manager, you must create and configure it in code; there is no design-time object that you can add to a form or component. This means that if you want to bind controls to the data view manager, you must also do so in code, as there is no design-time component to bind them to. For more information, see Creating and Working with Data View Managers.