Saturday, March 20, 2010

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:


while not ClientDataSet1.EOF do


//Do something here with the current record



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.

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

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.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:

DataView1, DataView2: DataView;
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';

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


  1. Thanks Cary,

    I sometimes forget how powerful clientdatasets are and its good to see a comparison with other frameworks and how they do things.

    Looking forward to seeing your talks at ADUG in Melbourne next week.


  2. Hi Cary

    I have a question:
    Do we have to do something to preserve sorting after applying a filter, because I'm loosing my client dataset's sort after filtering.

    Thanks in advance

  3. Setting a filter using the Filter property and setting Filtered to True should have no affect on the current index set either using IndexName or IndexFieldNames.

    I did a quick test, setting IndexFieldNames and then setting Filter and Filtered. The index was not affected.

    What specifically did you do to set the index and the filter?

  4. Hi Cary

    Thank you for your quick response.

    You are absolutely right.
    This morning I found that actualy the sortation is OK over the records which are written in English, but not for the others written in my native language. Over them the sortation is messed. Yesterday I did test only with second one.

    That's my case:

    These properties are set during design time.
    Fields-> ReqFirm
    CaseInsFields-> ReqFirm
    Name-> cdsFirmIndex

    FilterOtions-> [foCaseInsensitive]
    IndexName-> cdsFirmIndex

    And these at run time:
    DM.cdsFirm.Filter := 'reqfirm = '''+ SearchString+ '*''';
    DM.cdsFirm.Filtered := True;
    hooked on every dbGridKeyUp and all works just as expected - fine.

    Thank you and have a nice day.