In-Memory DataSets: ClientDataSets and .NET DataTables Part 4: Navigating, Sorting, and Filtering

In the preceding article in this series I discussed how to populate in-memory datasets. In this continuation, I will look at some of the ways that you can programmatically work with that data, including navigating, sorting, and filtering the data in memory.

Active ClientDataSets are essentially a cursor pointer that refers to the current record in the dataset. To navigate which record your ClientDataSet refers to, you use methods of the ClientDataSet to change the current record. These methods include First, Next, Prior, Last, and MoveBy, to name a few.

The following line of code demonstrates how to navigate to each record of (scan) a ClientDataSet:

ClientDataSet1.Open;
while not ClientDataSet1.EOF do
begin
  //Do something here with the current record
  ClientDataSet1.Next;
end;

DataTables in the .NET framework do not support the concept of a current record. Instead, you use the Rows property to indicate which DataRow of the DataTable you want to work with.

The following code segment demonstrates how to programmatically navigate the DataRows of a DataTable.

var
i: Integer;
DataRow1: DataRow;
begin
  for i := 0 to DataTable1.Rows.Count – 1 do
  begin
    DataRow1 := DataTable1.Rows[i];
    //Do something with the DataRow
  end;

While the DataTable lacks the concept of a current record, Windows Forms applications have a concept that is very similar to current record. Anytime one or more properties are bound to a multivalue binding context (such as a DataTable) in a Windows Forms application, a CurrencyManager is created that can be used to determine to which DataRow the form's controls are synchronized. If you bind data using a BindingSource instance, CurrencyManager property points to the underlying CurrencyManager. Similarly, there are classes, such as BindingSource, that provide features similar to a CurrencyManager.

Note that in .NET Web Forms applications, most multi-value controls automatically iterate through the records of a DataTable or DbDataReader implementing instance when you call the DataBind method. As a result, Web forms do not have currency managers.

Sorting In-Memory Data

ClientDataSets are sorted using ClientDataSet indexes. There are two types of indexes in ClientDataSets, temporary indexes and persistent indexes.

You create a temporary index using the IndexFieldNames property of the ClientDataSet. When you assign a field name (or a comma-separated list of field names) to the IndexFieldNames property, an index is create for that field (or fields) at that moment the property is assigned, and the records of the ClientDataSet are then sorted based on that ascending index. This is demonstrated in the following code segment:

ClientDataSet1.IndexFieldNames := 'State;City';

Persistent indexes, on the other hand, are defined using the IndexDefs property of the ClientDataSet. After defining one or more IndexDef instances, you then set the IndexName property of the ClientDataSet to the name of one of those IndexDef instances.

Indexes created using IndexDefs are more flexible than temporary indexes. While temporary indexes can only be ascending, case-sensitive indexes, your IndexDef definitions can define ascending or descending indexes, as well as case insensitive indexes.

The following line of code demonstrates the creation and use of a persistent index using an IndexDef definition:

ClientDataSet1.IndexDefs.Add('IDIndex', 'State;City',[ixCaseInsensitive]);
ClientDataSet1.IndexName := 'IDIndex';

DataTables cannot be sorted directly. If you want to sort the records in a DataTable, you use a DataView. You sort a DataView using its Sort property, which can hold either the name of a DataColumn to sort the DataView DataRows, or a comma-separated list of DataColumn names.

The following code demonstrates creating a DataView to display a sorted list of a DataTable's DataRows.

var DataView1: DataView;
DataView1.Create(DataTable1);
DataView1.Sort := 'Customer ID';

Rather than creating a new DataView to display sorted DataRows, you can use the DefaultView of a DataTable to refer to the DataView that each DataTable automatically supports. This is shown in the following code segment:

DataTable1.DefaultView.Sort := 'Customer ID';

The alternative is to create a new DataView instance for each sort order you want to use. The following example creates two DataViews for a single DataTable, each with its own sort order:

var
DataView1, DataView2: DataView;
begin
  DataView1 := DataView.Create(DataTable1);
  DataView1.Sort := 'Customer ID';
  DataView2 := DataView.Create(DataTable1);
DataView2.Sort := 'Country;City';

Filtering In-Memory Data

When you filter a ClientDataSet, it behaves as though it contains only those records that meet the filter criteria. For example, if you filter a ClientDataSet for all records where the City field contains the name 'London', that ClientDataSet will act as though it only contains records where the City field value is London.

Note: There is one exception to the behavior of filtered ClientDataSets. Specifically, if you read the XMLData property of a ClientDataSet or call SaveToFile or SaveToStream, the results are as if the ClientDataSet is not filtered. In other words, all records are involved, not just the filtered records. To save only the filtered records, you must point to the filtered ClientDataSet with the DataSet property of a DataSetProvider that is referenced by another ClientDataSet. After opening that other ClientDataSet, it will contain only the filtered records (a copy), which you can then save to a file or stream. These records, however, do not include a change log.

There are two ways to filter a ClientDataSet. One is to use the Filter and Filtered properties. Filter takes a Boolean expression. Records that are available in the filtered view are those for whom the expression evaluates to True. The following code segment demonstrates how to filter a ClientDataSet using the Filter and Filtered properties.

ClientDataSet1.Filter := '[City] = ' + QuotedStr('London');
ClientDataSet1.Filtered := True;

The second way to filter a ClientDataSet is to use the SetRange method. Unlike Filter and Filtered, SetRange requires that you first set an index. Since an index is being used, SetRange is a much faster filter.

The following code demonstrates the use of SetRange.

ClientDataSet1.IndexFieldNames := 'City';
ClientDataSet1.SetRange(['London'],['London']);

You cancel filtering with Filter and Filtered by either setting Filtered to False or setting Filter to an empty string. You cancel filtering using SetRange by calling CancelRange. (For an interesting use of ClientDataSet where Filtered is False but a non-empty value appears in the Filter property, see the FindFirst, FindLast, FindNext, and FindPrior methods).

As is the case with sorting, you do not actually filter a DataTable. Instead, you use a DataView, which supports filtering through its RowFilter property. Filters are more complicated for Delphi developers, since single quotes are used to delimit string literals in the Filter expression. For example, consider the following filter:

DataView1.RowFilter := '[City] = ''London''';

With Delphi Prism, you can use the double quote character, which makes creating filter strings much easier, as shown in the following code.

DataView1.RowFilter := "[City] = 'London'";

You remove a previously set filter on a DataView by setting its RowFilter property to an empty string.

While it’s true that filtering a DataTable requires the use of a DataView, it is not necessary to actually create a new DataView when you want to set a filter. Each DataTable has a default DataView, which controls how the records in the DataTable appear. This DataView is accessed through the DefaultView property of the DataTable. As a result, assuming that the preceding code sample used a DataView created for a DataTable named DataTable1, the following code segment produces the same result:

DataTable1.DefaultView.RowFilter := "[City] = 'London'";

Copyright © 2009-2010 Cary Jensen. All Rights Reserved