Limiting records with ranges

You can temporarily view and edit a subset of data for any dataset by using filters (see "Displaying and editing a subset of data using filters" on page 18-12). Some table-type datasets support an additional way to access a subset of available records, called ranges.

Ranges only apply to TTable and to client datasets. Despite their similarities, ranges and filters have different uses. The following topics discuss the differences between ranges and filters and how to use ranges.

Understanding the differences between ranges and filters

Both ranges and filters restrict visible records to a subset of all available records, but the way they do so differs. A range is a set of contiguously indexed records that fall between specified boundary values. For example, in an employee database indexed on last name, you might apply a range to display all employees whose last names are greater than "Jones" and less than "Smith". Because ranges depend on indexes, you must set the current index to one that can be used to define the range. As with specifying an index to sort records, you can assign the index on which to define a range using either the IndexName or the IndexFieldNames property.

A filter, on the other hand, is any set of records that share specified data points, regardless of indexing. For example, you might filter an employee database to display all employees who live in California and who have worked for the company for five or more years. While filters can make use of indexes if they apply, filters are not dependent on them. Filters are applied record-by-record as an application scrolls through a dataset.

In general, filters are more flexible than ranges. Ranges, however, can be more efficient when datasets are large and the records of interest to an application are already blocked in contiguously indexed groups. For very large datasets, it may be still more efficient to use the WHERE clause of a query-type dataset to select data. For details on specifying a query, see "Using query-type datasets" on page 18-41.

Specifying Ranges

There are two mutually exclusive ways to specify a range:

  • Specify the beginning and ending separately using SetRangeStart and SetRangeEnd.
  • Specify both endpoints at once using SetRange.

Setting the beginning of a range

Call the SetRangeStart procedure to put the dataset into dsSetKey state and begin creating a list of starting values for the range. Once you call SetRangeStart, subsequent assignments to the Fields property are treated as starting index values to use when applying the range. Fields specified must apply to the current index.

For example, suppose your application uses a TSQLClientDataSet component named Customers, linked to the CUSTOMER table, and that you have created persistent field components for each field in the Customers dataset. CUSTOMER is indexed on its first column (CustNo). A form in the application has two edit components named StartVal and EndVal, used to specify start and ending values for a range. The following code can be used to create and apply a range:

with Customers do begin

SetRangeStart;

FieldByName('CustNo').AsString := StartVal.Text; SetRangeEnd;

FieldByName('CustNo').AsString := EndVal.Text; ApplyRange; end;

This code checks that the text entered in EndVal is not null before assigning any values to Fields. If the text entered for StartVal is null, then all records from the beginning of the dataset are included, since all values are greater than null. However, if the text entered for EndVal is null, then no records are included, since none are less than null.

For a multi-column index, you can specify a starting value for all or some fields in the index. If you do not supply a value for a field used in the index, a null value is assumed when you apply the range. If you try to set a value for a field that is not in the index, the dataset raises an exception.

Tip To start at the beginning of the dataset, omit the call to SetRangeStart.

To finish specifying the start of a range, call SetRangeEnd or apply or cancel the range. For information about applying and canceling ranges, see "Applying or canceling a range" on page 18-33.

Setting the end of a range

Call the SetRangeEnd procedure to put the dataset into dsSetKey state and start creating a list of ending values for the range. Once you call SetRangeEnd, subsequent assignments to the Fields property are treated as ending index values to use when applying the range. Fields specified must apply to the current index.

Warning Always specify the ending values for a range, even if you want a range to end on the last record in the dataset. If you do not provide ending values, Delphi assumes the ending value of the range is a null value. A range with null ending values is always empty.

The easiest way to assign ending values is to call the FieldByName method. For example, with Contacts do begin

SetRangeStart;

FieldByName('LastName').AsString SetRangeEnd;

FieldByName('LastName').AsString ApplyRange; end;

As with specifying start of range values, if you try to set a value for a field that is not in the index, the dataset raises an exception.

To finish specifying the end of a range, apply or cancel the range. For information about applying and canceling ranges, see "Applying or canceling a range" on page 18-33.

Setting start- and end-range values

Instead of using separate calls to SetRangeStart and SetRangeEnd to specify range boundaries, you can call the SetRange procedure to put the dataset into dsSetKey state and set the starting and ending values for a range with a single call.

SetRange takes two constant array parameters: a set of starting values, and a set of ending values. For example, the following statement establishes a range based on a two-column index:

SetRange([Edit1.Text, Edit2.Text], [Edit3.Text, Edit4.Text]);

For a multi-column index, you can specify starting and ending values for all or some fields in the index. If you do not supply a value for a field used in the index, a null value is assumed when you apply the range. To omit a value for the first field in an index, and specify values for successive fields, pass a null value for the omitted field.

Always specify the ending values for a range, even if you want a range to end on the last record in the dataset. If you do not provide ending values, the dataset assumes the ending value of the range is a null value. A range with null ending values is always empty because the starting range is greater than or equal to the ending range.

Specifying a range based on partial keys

If a key is composed of one or more string fields, the SetRange methods support partial keys. For example, if an index is based on the LastName and FirstName columns, the following range specifications are valid:

Contacts.SetRangeStart; Contacts['LastName'] := 'Smith'; Contacts.SetRangeEnd; Contacts['LastName'] := 'Zzzzzz'; Contacts.ApplyRange;

This code includes all records in a range where LastName is greater than or equal to "Smith." The value specification could also be:

Contacts['LastName'] := 'Sm'; This statement includes records that have LastName greater than or equal to "Sm."

Including or excluding records that match boundary values

By default, a range includes all records that are greater than or equal to the specified starting range, and less than or equal to the specified ending range. This behavior is controlled by the KeyExclusive property. KeyExclusive is False by default.

If you prefer, you can set the KeyExclusive property for a dataset to True to exclude records equal to ending range. For example,

Contacts.KeyExclusive := True; Contacts.SetRangeStart; Contacts['LastName'] := 'Smith'; Contacts.SetRangeEnd; Contacts['LastName'] := 'Tyler'; Contacts.ApplyRange;

This code includes all records in a range where LastName is greater than or equal to "Smith" and less than "Tyler".

Modifying a range

Two functions enable you to modify the existing boundary conditions for a range: EditRangeStart, for changing the starting values for a range; and EditRangeEnd, for changing the ending values for the range.

The process for editing and applying a range involves these general steps:

1 Putting the dataset into dsSetKey state and modifying the starting index value for the range.

2 Modifying the ending index value for the range.

3 Applying the range to the dataset.

You can modify either the starting or ending values of the range, or you can modify both boundary conditions. If you modify the boundary conditions for a range that is currently applied to the dataset, the changes you make are not applied until you call ApplyRange again.

Editing the start of a range

Call the EditRangeStart procedure to put the dataset into dsSetKey state and begin modifying the current list of starting values for the range. Once you call EditRangeStart, subsequent assignments to the Fields property overwrite the current index values to use when applying the range.

Tip If you initially created a start range based on a partial key, you can use EditRangeStart to extend the starting value for a range. For more information about ranges based on partial keys, see "Specifying a range based on partial keys" on page 18-32.

Editing the end of a range

Call the EditRangeEnd procedure to put the dataset into dsSetKey state and start creating a list of ending values for the range. Once you call EditRangeEnd, subsequent assignments to the Fields property are treated as ending index values to use when applying the range.

Applying or canceling a range

When you call SetRangeStart or EditRangeStart to specify the start of a range, or SetRangeEnd or EditRangeEnd to specify the end of a range, the dataset enters the dsSetKey state. It stays in that state until you apply or cancel the range.

Applying a range

When you specify a range, the boundary conditions you define are not put into effect until you apply the range. To make a range take effect, call the ApplyRange method. ApplyRange immediately restricts a user's view of and access to data in the specified subset of the dataset.

Canceling a range

The CancelRange method ends application of a range and restores access to the full dataset. Even though canceling a range restores access to all records in the dataset, the boundary conditions for that range are still available so that you can reapply the range at a later time. Range boundaries are preserved until you provide new range boundaries or modify the existing boundaries. For example, the following code is valid:

MyTable.CancelRange;

{later on, use the same range again. No need to call SetRangeStart, etc.} MyTable.ApplyRange;

Was this article helpful?

+1 0

Post a comment