Using Indexes to search for records

You can search against any dataset using the Locate and Lookup methods of TDataSet. However, by explicitly using indexes, some table-type datasets can improve over the searching performance provided by the Locate and Lookup methods.

ADO datasets all support the Seek method, which moves to a record based on a set of field values for fields in the current index. Seek lets you specify where to move the cursor relative to the first or last matching record.

TTable and all types of client dataset support similar indexed-based searches, but use a combination of related methods. The following table summarizes the six related methods provided by TTable and client datasets to support index-based searches:

Table 18.9 Index-based search methods

Method Purpose

EditKey Preserves the current contents of the search key buffer and puts the dataset into dsSetKey state so your application can modify existing search criteria prior to executing a search.

FindKey Combines the SetKey and GotoKey methods in a single method.

FindNearest Combines the SetKey and GotoNearest methods in a single method.

GotoKey Searches for the first record in a dataset that exactly matches the search criteria, and moves the cursor to that record if one is found.

GotoNearest Searches on string-based fields for the closest match to a record based on partial key values, and moves the cursor to that record.

SetKey Clears the search key buffer and puts the table into dsSetKey state so your application can specify new search criteria prior to executing a search.

GotoKey and FindKey are boolean functions that, if successful, move the cursor to a matching record and return True. If the search is unsuccessful, the cursor is not moved, and these functions return False.

GotoNearest and FindNearest always reposition the cursor either on the first exact match found or, if no match is found, on the first record that is greater than the specified search criteria.

Executing a search with Goto methods

To execute a search using Goto methods, follow these general steps:

1 Specify the index to use for the search. This is the same index that sorts the records in the dataset (see "Sorting records with indexes" on page 18-25). To specify the index, use the IndexName or IndexFieldNames property.

2 Open the dataset.

3 Put the dataset in dsSetKey state by calling the SetKey method.

4 Specify the value(s) to search on in the Fields property. Fields is a TFields object, which maintains an indexed list of field components you can access by specifying ordinal numbers corresponding to columns. The first column number in a dataset is 0.

5 Search for and move to the first matching record found with GotoKey or GotoNearest.

For example, the following code, attached to a button's OnClick event, uses the GotoKey method to move to the first record where the first field in the index has a value that exactly matches the text in an edit box:

procedure TSearchDemo.SearchExactClick(Sender: TObject); begin

ClientDataSetl.SetKey;

ClientDataSet1.Fields[0].AsString := Editl.Text; if not ClientDataSetl.GotoKey then ShowMessage('Record not found');

end;

GotoNearest is similar. It searches for the nearest match to a partial field value. It can be used only for string fields. For example,

Tablel.SetKey;

Table1.Fields[0].AsString := 'Sm'; Tablel.GotoNearest;

If a record exists with "Sm" as the first two characters of the first indexed field's value, the cursor is positioned on that record. Otherwise, the position of the cursor does not change and GotoNearest returns False.

Executing a search with Find methods

The Find methods do the same thing as the Goto methods, except that you do not need to explicitly put the dataset in dsSetKey state to specify the key field values on which to search. To execute a search using Find methods, follow these general steps:

1 Specify the index to use for the search. This is the same index that sorts the records in the dataset (see "Sorting records with indexes" on page 18-25). To specify the index, use the IndexName or IndexFieldNames property.

2 Open the dataset.

3 Search for and move to the first or nearest record with FindKey or FindNearest. Both methods take a single parameter, a comma-delimited list of field values, where each value corresponds to an indexed column in the underlying table.

Note FindNearest can only be used for string fields.

Specifying the current record after a successful search

By default, a successful search positions the cursor on the first record that matches the search criteria. If you prefer, you can set the KeyExclusive property to True to position the cursor on the next record after the first matching record.

By default, KeyExclusive is False, meaning that successful searches position the cursor on the first matching record.

Searching on partial keys

If the dataset has more than one key column, and you want to search for values in a subset of that key, set KeyFieldCount to the number of columns on which you are searching. For example, if the dataset's current index has three columns, and you want to search for values using just the first column, set KeyFieldCount to 1.

For table-type datasets with multiple-column keys, you can search only for values in contiguous columns, beginning with the first. For example, for a three-column key you can search for values in the first column, the first and second, or the first, second, and third, but not just the first and third.

Repeating or extending a search

Each time you call SetKey or FindKey, the method clears any previous values in the Fields property. If you want to repeat a search using previously set fields, or you want to add to the fields used in a search, call EditKey in place of SetKey and FindKey.

For example, suppose you have already executed a search of the Employee table based on the City field of the "CityIndex" index. Suppose further that "CityIndex" includes both the City and Company fields. To find a record with a specified company name in a specified city, use the following code:

Employee.KeyFieldCount := 2; Employee.EditKey;

Employee['Company'] := Edit2.Text; Employee.GotoNearest;

Was this article helpful?

+1 0

Post a comment