Searching a ClientDataSet

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.

procedure TForm1.ScanBtnClick(Sender: TObject);
var
  Found: Boolean;
begin
Found := False;
ClientDataSet1.DisableControls;
Start;
try
  ClientDataSet1.First;
  while not ClientDataSet1.Eof do
  begin
    if ClientDataSet1.Fields[FieldListComboBox.ItemIndex].Value =
      ScanForEdit.Text then
      begin
        Found := True;
        Break;
      end;
    ClientDataSet1.Next;
  end;
  Done;
finally
  ClientDataSet1.EnableControls;
end;
if Found then ShowMessage(ScanForEdit.Text +
  ' found at record ' + IntToStr(ClientDataSet1.RecNo))
else
  ShowMessage(ScanForEdit.Text + ' not found');
end;

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.

procedure TForm1.Start;
begin
  StartTick := TimeGetTime;
end;

procedure TForm1.Done;
begin
  EndTick := TimeGetTime;
  StatusBar1.Panels[0].Text := 'Starting tick: ' +
    IntToStr(StartTick);
  StatusBar1.Panels[1].Text := 'Ending tick: ' +
    IntToStr(EndTick);
  StatusBar1.Panels[2].Text := 'Duration (in milliseconds): ' +
    IntToStr(EndTick - StartTick);
end;

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.

procedure TForm1.FindKeyBtnClick(Sender: 

TObject);
begin
Start;
if ClientDataSet1.FindKey([ScanForEdit.Text]) then
begin
  Done;
  StatusBar1.Panels[3].Text := ScanForEdit.Text +
    ' found at record ' +
    IntToStr(ClientDataSet1.RecNo);
end
else
begin
  Done;
  StatusBar1.Panels[3].Text :=
    ScanForEdit.Text + ' not found';
end;
end;

procedure TForm1.FindNearestBtnClick(Sender: TObject);
begin
Start;
ClientDataSet1.FindNearest([ScanForEdit.Text]);
Done;
StatusBar1.Panels[3].Text := 'The nearest match to ' +
  ScanForEdit.Text + ' found at record ' +
  IntToStr(ClientDataSet1.RecNo);
end;

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:

ClientDataSet1.SetKey;
ClientDataSet1.FieldByName('OrderNo').Value := 1278;

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:

ClientDataSet1.SetKey;
ClientDataSet1.FieldByName('OrderNo').Value := ScanForEdit.Text;
ClientDataSet1.GotoKey;

The following event handlers are associated with the buttons labeled Goto Key and Goto Nearest in the CDSSearch project.

procedure TForm1.GotoKeyBtnClick(Sender: 

TObject);
begin
Start;
ClientDataSet1.SetKey;
ClientDataSet1.Fields[IndexOnComboBox.ItemIndex].AsString :=
  Trim(ScanForEdit.Text);
if ClientDataSet1.GotoKey then
begin
  Done;
  StatusBar1.Panels[3].Text := ScanForEdit.Text +
    ' found at record ' +
    IntToStr(ClientDataSet1.RecNo);
end
else
begin
  Done;
  StatusBar1.Panels[3].Text :=
    ScanForEdit.Text + ' not found';
end;
end;

procedure TForm1.GotoNearestBtnClick(Sender: TObject);
begin
Start;
ClientDataSet1.SetKey;
ClientDataSet1.Fields[IndexOnComboBox.ItemIndex].AsString :=
  ScanForEdit.Text;
ClientDataSet1.GotoNearest;
Done;
StatusBar1.Panels[3].Text := 'The nearest match to ' +
  ScanForEdit.Text + ' found at record ' +
  IntToStr(ClientDataSet1.RecNo);
end;

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.

The following is the syntax of Locate:

function Locate(const KeyFields: string; 
  const KeyValues: Variant; Options: TLocateOptions): Boolean;

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.

ClientDataSet1.Locate('Company','u',[loCaseInsensitive, loPartialKey]);

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.

var
  SearchList: Variant;
begin
SearchList := VarArrayCreate([0, 1], VarVariant);
SearchList[0] := 'Unisco';
SearchList[1] := 'Freeport';
ClientDataSet1.Locate('Company;City', SearchList, [loCaseInsensitive]);

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.

var
  SearchList: Variant;
begin
SearchList := VarArrayOf(['Unisco','Freeport']);
ClientDataSet1.Locate('Company;City',SearchList,[loCaseInsensitive]);

function TForm1.GetKeyFields(var 

FieldStr: String): Integer;
const
  FieldsColumn = 0;
var
  i : Integer;
  Count: Integer;
begin
  Count := 0;
  for i := 1 to 20 do
  begin
    if StringGrid1.Cells[FieldsColumn,i] <> '' then
    begin
      if FieldStr = '' then FieldStr :=
        StringGrid1.Cells[FieldsColumn,i]
      else
        FieldStr := FieldStr + ';' +
          StringGrid1.Cells[FieldsColumn,i];
      inc(Count);
    end
    else
      Break;
  end;
  Result := Count;
end;

function TForm1.GetKeyValues(Size: Integer): Variant;
const
  SearchColumn = 1;
var
  i: Integer;
begin
  Result := VarArrayCreate([0,Pred(Size)], VarVariant);
  for i := 0 to Pred(Size) do
    Result[i] := StringGrid1.Cells[SearchColumn, Succ(i)];
end;

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.

procedure TForm1.LocateBtnClick(Sender: 

TObject);
var
  FieldList: String;
  Count: Integer;
  SearchArray: Variant;
begin
FieldList := '';
Count := GetKeyFields(FieldList);
SearchArray := GetKeyValues(Count);
Start;
if ClientDataSet1.Locate(FieldList, SearchArray, []) then
begin
  Done;
  StatusBar1.Panels[3].Text :=
    'Match located at record ' +
    IntToStr(ClientDataSet1.RecNo);
end
else
begin
  Done;
  StatusBar1.Panels[3].Text := 'No match located';
end;
end;

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.

function Lookup(const KeyFields: string; 
  const KeyValues: Variant; const ResultFields: string): Variant;

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.

function TForm1.GetResultFields: String;
const
  ReturnColumn = 2;
var
  i: Integer;
begin
  for i := 1 to Succ(StringGrid1.RowCount) do
    if StringGrid1.Cells[ReturnColumn, i] <> '' then
      if Result = '' then
        Result := StringGrid1.Cells[ReturnColumn, i]
      else
        Result := Result + ';' +
          StringGrid1.Cells[ReturnColumn, i]
    else
      Break;
end;

The following is the code associated with the OnClick event handler of the button labeled Lookup.

procedure TForm1.LookupBtnClick(Sender: 

TObject);
var
  ResultFields: Variant;
  KeyFields: String;
  KeyValues: Variant;
  ReturnFields: String;
  Count, i: Integer;
  DisplayString: String;
begin
Count := GetKeyFields(KeyFields);
DisplayString := '';
KeyValues := GetKeyValues(Count);
ReturnFields := GetResultFields;
Start;
ResultFields := ClientDataSet1.Lookup(KeyFields,
  KeyValues, ReturnFields);
Done;
if VarIsNull(ResultFields) then
  DisplayString := 'Lookup record not found'
else
  if VarIsArray(ResultFields) then
    for i := 0 to VarArrayHighBound(ResultFields,1) do
      if i = 0 then
        DisplayString := 'Lookup result: ' +
          VarToStr(ResultFields[i])
      else
        DisplayString :=