Finding a Record (FireDAC)

From RAD Studio
Jump to: navigation, search

Go Up to Working with DataSets (FireDAC)


FireDAC provides methods to find a specific record in the dataset.

General

All FireDAC datasets provide approaches to find a record in a local dataset records cache. Depending on the current sorting, the record search can be optimized.

Standard Locating

The FireDAC datasets provide several options to locate a record or lookup a value for a specified key. These methods use the current dataset index, if it is appropriate for the method call.

  • The Locate method allows you to find a record for the specified key values:
FDQuery1.IndexFieldNames := 'ORDERID';
if FDQuery1.Locate('ORDERID', 10150, []) then
  ShowMessage('Order is found')
else
  ShowMessage('Order is not found');
  • The Lookup method allows you to lookup a value for the specified key values.

Extended Locating

The FireDAC datasets provide some extended methods to locate a record. These methods use the current dataset index, if it is appropriate for the method call.

  • The LocateEx method is similar to the Locate method, but provides more options, such as search forward or backward, search from beginning or from current records, search using textual predicate (expression). For example:
if FDQuery1.LocateEx('Price >= 1000 and Price <= 2000', []) then
  ShowMessage('Order is found')
else
  ShowMessage('Order is not found');
  • The LookupEx method is similar to the Lookup method, but provides options similar to those of the LocateEx method.

Locating Using a Filter

This approach to locate a record was introduced into FireDAC for compatibility with BDE components. It can be completely replaced by the LocateEx method. The approach is using the following methods:

  • FindFirst -- finds the first record satisfying the predicate.
  • FindNext -- finds the next record after the current one satisfying the predicate.
  • FindPrior -- finds the previous record before the current one satisfying the predicate.
  • FindLast -- finds the last record satisfying the predicate.

To start a search, the application must specify the predicate as a boolean expression in the Filter property. Note that this method cannot be used to locate a record in a filtered dataset. Also, the filter is not activated and is used only for locating a record. For example:

FDQuery1.Filter := 'Price >= 1000 and Price <= 2000';
if FDQuery1.FindFirst then
  ShowMessage('Found !');
...
if FDQuery1.FindNext then
  ShowMessage('Found !');

Locating Using an Index and a Key Value

We introduced this helpful approach to locate a record into FireDAC for compatibility with BDE components. The approach is using the following methods:

  • SetKey -- sets the dataset to dsSetKey mode, allowing you to specify the index field values.
  • GotoKey -- searches for an exact record using the index field values, specified after a SetKey method call.
  • GotoNearest -- searches for an exact or next record using the index field values, specified after a SetKey method call.
  • FindKey -- finds an exact record using specified index field values.
  • FindNearest -- finds an exact or next record using specified index field values.

Before calling these methods, the current index must be set. For example:

FDQuery1.IndexFieldNames := 'ORDERID';
FDQuery1.SetKey;
FDQuery1.FieldByName('ORDERID').AsInteger := 10150;
if FDQuery1.GotoKey then
  ShowMessage('Order is found')
else
  ShowMessage('Order is not found');

Other Options

Most of the search methods return True when a record is found. Alternatively, the application can check the dataset's Found property.

Also, note that FireDAC does not support locating on fields of fkCalculated and fkLookup types. But the application can use the fkInternalCalc and fkAggregate fields for locating operations.

See Also