Searching a ClientDataSet

Abstract: ClientDataSets provide a number of different mechanisms for searching for and location data in its columns. These techniques are covered in this continuation of the discussion of basic ClientDataSet manipulation.

In this article I am continuing the coverage of basic ClientDataSet usage. In
the last installment of this series I discussed how to navigate and edit a
ClientDataSet. In this article I show you how to find a record based on the data
it contains.

In the context of this article, searching means to either move the
current record pointer to a particular record based on the data held in the
record, or to read data from a record based on its data. Filtering,
which shares some similarities with searching, involves restricting the
accessible records in a ClientDataSet to those that contain certain data. This
article does not demonstrate how to filter a ClientDataSet. That topic is be
discussed in the next article in this series.

Scanning for Data

The simplest, and typically slowest mechanism for searching is performed by
scanning. As you learned in the preceding article in this series
(click here to
read
it), you can scan a table by moving to either the first or last record in the
current index order, and then navigating record-by-record until every record in
the view has been visited. If used for searching, your code reads each record's data
as you scan. When a record containing the desired data is found, the scanning
process can be terminated.

The following code
segment provides a simple example of how a search operation like this might
look.

As you learned from the preceding article in this series, scanning involves
first moving to one end of the dataset (the first record in this example), and
then navigating sequentially to each record in the view. When searching using
this technique, upon arriving at a record you read one or more fields to
determine whether or not the current record is the one for which you are
looking. If the record contains the data you need, do something, such as
terminate the search and display the located record to the user. In this
particular case, the code is searching for a value entered into an Edit named
ScanForEdit. The field being searched is the field name currently selected in
the IndexOnComboBox combobox.

This code is taken from the CDSSearch project, available for download
from Code Central (click
here to download). The main form of this project is
shown in the following figure.

Note that the data used in this example is found in the
items.cds example
file that ships with Delphi.

The only method calls within this
code that are not part of the runtime library (RTL) or visual component library
(VCL), are the Start and Done methods. These methods are designed to
initiate and complete a performance monitor, which is used by all
search-initiating event handlers in this project to provide a relative measure
of performance. The performance information is displayed in the StatusBar of
this project, as can be seen in the preceding figure. The implementation of Start
and Done
is shown in the following
code segment.

Both Start and Done make use of the TimeGetTime function, which is imported
in the MMSystem unit. This function returns a tick count, which represents the
number of milliseconds that have past since Windows was started. TimeGetTime is
significantly more accurate than GetTickCount, a commonly-used timing function.
Normally, TimeGetTime is accurate within five milliseconds under NT, and within
one millisecond under Windows 98.

Finding Data

One of the oldest mechanisms for searching a dataset was introduced in Delphi
1. This method, FindKey, permits you to search one or more fields of the current
index for a particular value. FindKey, and its close associate, FindNearest,
both make use of the current index to perform the search. As a result, the
search is always index-based, and always very fast.

Both FindKey and FindNearest take a single constant array parameter. You include
in this array the values for which you want to search on the fields on the
index, with the first element in the array being searched for in the first field
of the index, the second field in the array (if provided) searched for in the
second field of the index, and so forth. Since the search is indexed-based, the
number of fields searched obviously cannot exceed the number of fields in the
index (though there is no problem if you want to search on fewer fields than are
contained in the index).

In the CDSSearch project, the only indexes available are temporary
indexes associated with single fields in the dataset. (The current temporary
index is based on the field listed in the IndexOnComboBox, shown in the
preceding figure.) Consequently, the
demonstrations of the FindKey and FindNearest methods in this project are
limited to single fields, specifically the value entered into the ScanForEdit
Edit component. The following are the event handlers associated with the
FindKey and FindNearest buttons in this project, respectively.

The following figure shows the result of a search performed on an index based on
the
OrderNo field. In this case, as in the preceding figure, the value being
searched for is OrderNo 1278. Notice that in the StatusBar this FindKey search
took significantly less time than the search using scanning.

While FindKey and FindNearest are identical in syntax, there is a subtle
difference in how they operate. FindKey is a Boolean function method that
returns True if a matching record is located. In that case, the cursor is
repositioned in the ClientDataSet to the found record. If FindKey fails it
return False, and the current record pointer does not change.

Unlike FindKey, which is a function, FindNearest is a procedure method.
Technically speaking, FindNearest always succeeds, moving the cursor to the
record that most closely matches the search criteria. For example, in following
figure FindNearest is used to locate the record whose OrderNo most closely
matches the value 99999. As you can see in this figure, the located record
contains OrderNo 1860, the highest OrderNo in the table, and the last record in
the current index order.

Going to Data

GotoKey and GotoNearest provide the same searching features as FindKey and
FindNearest, respectively. The only difference between these two sets of methods
is how you define your search criteria. As you have already learned, FindKey and
FindNearest are passed a constant array as a parameter, and the search criteria
are contained in this array.

Both GotoKey and GotoNearest take no parameters. Instead, their search
criteria is defined using the search key buffer. The search key buffer contains
one field for each field in the current index. For example, if the current index
is based on the field OrderNo, the search key buffer contains one field: OrderNo.
By comparison, if the current index contains three fields the search key buffer
also contains three fields.

Fields in the search key buffer can only be modified when the ClientDataSet
is in a special state called the dsSetKey state. To clear the search key buffer
and enter the dsSetKey state, call the ClientDataSet's SetKey method. If you have
previously assigned one or more values to the search key buffer, you can enter
the dsSetKey state without clearing the search key buffer's contents by calling
the ClientDataSet's EditKey method. From within the dsSetKey state, you assign
data to fields in the search key buffer as if you were assigning data to the
ClientDataSet's fields. For example, assuming that the current index is based on
the OrderNo field, the following lines of code assign the value 1278 to the
OrderNo field of the search key buffer:

As should be apparent, using GotoKey or GotoNearest requires more lines of
code than FindKey and FindNearest. For example, once again assuming that the current
index is based on the OrderNo
field, consider
the following statement:

ClientDataSet1.FindKey([ScanForEdit.Text]);

Achieving
the same result using GotoKey requires three lines of code, since you must first
enter the dsSetKey state and edit the search key buffer. The following lines of
code, which use GotoKey, perform precisely the same search as the preceding line of
code:

Locating Data

One of the drawbacks to the Find and Goto methods is that the search is based
on the current index. Depending no the data you are searching for, you might
have to change the current index before performing the search. Fortunately,
ClientDataSets support two generally high-performance searching mechanisms that
do not require you to change the current index. These are Locate and
Lookup.

Locate, like FindKey and GotoKey, makes the located record the current record
if a match is found. In addition, Locate is a function method, returning a
Boolean True if the search results in a match. Lookup is somewhat different,
returning specific fields from a located record, but never moving the current
record pointer. Lookup is described separately in the following section.

What makes Locate and Lookup so special is that they do not require you to create or switch indexes,
but still provide much faster performance than scanning. In a number of tests that I have
conducted, Locate found a record four times faster than did scanning. For example, when
searching for data in a record at position 90,000 of a 100,000 record table, Locate located the record in about
500 milliseconds, while scanning for that record took longer than 2 seconds. Admittedly, FindKey took
only 10 milliseconds to find that record. But the index that FindKey required for the search took almost 1 second to build.

If you are locating a record based on a single field, the first argument is the
name of that field and the second argument is the value you are searching for.
To search on more than one field, pass a semicolon-separated string of field
names in the first argument, and a variant array containing the search values
corresponding to the field list in the second argument.

The third argument of Locate is a TLocateOptions set. This set can contain up
to two flags, loCaseInsensitive and loPartialKey. Include loCaseInsensitive to
ignore case in your search and loPartialKey to match any value that begins with
the values you pass in the second argument.

If the search is successful, Locate makes the located record the current
record and returns a value of True. If the search is not successful, Locate
returns False, and the cursor does not move.

Imagine that you are searching the Customer.xml file that ships with Delphi.
The following statement will locate the first record in the ClientDataSet whose
Company name is Ocean Paradise.

ClientDataSet1.Locate('Company', 'Ocean Paradise',[]);

The
next example demonstrates a partial match, searching for the first company whose
name starts with the letter u or U.

Searching
for two or more fields is somewhat more involved, in that you must pass the
search values using a variant array. The following lines of code demonstrate how you
can search for the record where the Company field
contains Unisco and the City field contains Freeport.

Instead of using VarArrayCreate, you can use VarArrayOf. VarArrayOf
takes a constant array of the values from which to create the variant array.
This means that you must know at design-time how many elements your variant array
will have. By comparison, the dimensions of the variant array created using
VarArrayOf can include variables, which permits you to determine the array size
at runtime. The following code performs the same search as the preceding code,
but makes use of an array created using VarArrayOf.

<
p>If
you refer back to the CDSSearch project main form shown in the earlier figures
of this article, you will notice a StringGrid in the
upper-right corner. Data entered into the first two columns of
this grid are used to create the KeyFields and KeyValues arguments of Locate,
respectively. The following methods, found in the CDSSearch project, generate
these parameters.

The following code is associated with the OnClick event handler of the button
labeled Locate in the CDSSearch project. As you can see, in this code the Locate
method is invoked based on the values returned by calling GetKeyFields and
GetKeyValues.

Using Lookup

Lookup is similar in many respects to Locate, with one very important
difference. Instead of moving the current record pointer to the located record,
Lookup returns a variant containing data from a located record without moving
the current record pointer. The following is the syntax of Lookup.

The
KeyFields and KeyValues parameters of Lookup are identical in purpose to those
in the Locate method. ResultFields is a semicolon separated string of field
names
whose values you want returned. If Lookup fails to find the record you are
searching for, it returns a null variant. Otherwise, it returns a variant
containing the field values requested in the ResultFields parameter.

The event handler associated with the Lookup button in the CDSSearch project
makes use of the GetKeyFields and GetKeyValues methods for defining the
KeyFields and KeyValues parameters of the call to Lookup, based again on the
first two columns of the StringGrid. In addition, this event handler makes use
of the GetResultFields method to construct the ResultFields parameter from the
third column of the grid. The
following is the code associated with the GetResultFields method.

The following figure shows the main form of the CDSSearch project following a
call to Locate. Notice that the current record is still the first record in the
ClientDataSet, even though the data returned from the call to Locate was found
much later in the current index order.

Summary

ClienDataSets provide a number of mechanisms for searching their columns. The
simplest, those often slowest, is to scan the ClientDataSet for
particular values. FindKey and FindNearest (and their GotoKey and GotoNearest
counterparts), are extremely fast, since they use an index. However, the Find and
Goto methods might require you to first select or build an appropriate index. By
comparison, Locate and Lookup provide relatively good performance without requiring an index, making them the preferred searching methods in applications where speed is not critical.

About the Author

Cary Jensen is President of Jensen Data Systems, Inc., a Texas-based training
and consulting company that won the 2002 Delphi Informant Magazine Readers
Choice award for Best Training. He is the author and presenter for Delphi
Developer Days (www.DelphiDeveloperDays.com), an information-packed Delphi
(TM) seminar series that tours North America and Europe. Cary is also an
award-winning, best-selling co-author of eighteen books, including Building
Kylix Applications (2001, Osborne/McGraw-Hill), Oracle JDeveloper (1999, Oracle
Press), JBuilder Essentials (1998, Osborne/McGraw-Hill), and Delphi In Depth
(1996, Osborne/McGraw-Hill). For information about onsite training and
consulting you can contact Cary at cjensen@jensendatasystems.com, or
visit his
Web site at www.JensenDataSystems.com.

Click
here for a
listing of upcoming seminars, workshops, and conferences where Cary Jensen is
presenting.

Copyright
) 2002 Cary Jensen, Jensen Data Systems, Inc.
ALL RIGHTS RESERVED. NO PART OF THIS DOCUMENT CAN BE COPIED IN ANY FORM WITHOUT
THE EXPRESS, WRITTEN CONSENT OF THE AUTHOR.