Navigational Actions with ADS and Delphi

The TDataSet class of the VCL supports a wide range of navigational actions. While all of these actions apply to AdsTables, many of them are also supported by the AdsQuery and AdsStoredProc components when they return a result set.

Setting an Index

If you are connected to a data dictionary, and you have designated a default index for a table, that index will automatically be used when you open that table using an AdsTable. If you do not have a default index, or want to switch to some other index, you set the IndexName property of the AdsTable to the name of the index you want to use. This is demonstrated in the following code segment, which is associated with the Select Invoice No Index button (shown in Figure 12-1):

procedure TForm1.SetIndexBtnClick(Sender: TObject); begin   if (AdsTable1.TableName <> 'INVOICE') and   (AdsTable1.Active) then   AdsTable1.Close;   AdsTable1.IndexName := 'Invoice No'; end;

Finding a Record Based on Data

TDataSets support a number of methods for locating records based on their contents. These include FindKey, FindNearest, and Locate, in addition to the brute-force method of scanning through every record in the table. Of these techniques, FindKey and FindNearest are the fastest and easiest to use, locating a record using the currently selected index.

Both of these methods take a single parameter consisting of a constant array. You represent a constant array by enclosing one or more values within parentheses, separating the values using commas when there is more than one value. Both of these methods search for the first element of the array in the first field of the current index, then search the second value, if present, in the second field of the current index, and so on.

FindKey is a Boolean function that returns True if a matching record is found. If FindKey returns True, the located record is made the current record. FindNearest is a procedure. It always repositions the current record to the closest match to the search criteria.

The use of FindNearest is demonstrated in the following event handler. This event handler is associated with the OnChange event of the Edit named SearchText. After clicking the Show Invoice Table and the Set Invoice No Index buttons, this code permits an incremental search through the INVOICE table.

procedure TForm1.SearchTextChange(Sender: TObject); begin   if (not AdsTable1.Active) or   (AdsTable1.TableName <> 'INVOICE') or   (AdsTable1.IndexName <> 'Invoice No') then   begin   ShowMessage('Click Show Invoice Table and Select Invoice '+   'No Index before performing an incremental search');   Exit;   end;   AdsTable1.FindNearest([SearchText.Text]); end; 

Note

Unlike the BDE, ADS employs indexes with the Locate and Lookup methods whenever appropriate indexes are available, and creates AOFs (Advantage Optimized Filters) when they are not. As a result, these methods are much faster than their BDE counterparts. However, Locate and Lookup are more complicated to use than FindKey and FindNearest. For information on using Locate and Lookup, refer to the ADS help.

Setting a Range

Delphi uses the term range instead of scope, but they mean the same thing. A range defines a subset of records to view in a table, based on an index.

You set a range in Delphi by calling SetRange. This method takes two parameters, both of which are constant arrays. The first constant array contains the beginning values of the range, where the first value defines the beginning of the range based on the first field of the current index, the second value, if present, defines the beginning of the range on the second field of the index, and so on. The second constant array contains the ending values of the range, again on a field-by-field basis, based on the current index.

The following code, which is associated with the OnClick event handler of the Set Range button, demonstrates how to apply a range. An example of a range set on the INVOICE table is shown in Figure 12-2. Notice that there are only 3 records, out of more than 2,000 records, in this range:

procedure TForm1.SetRangeBtnClick(Sender: TObject); begin   if (not AdsTable1.Active) or   (AdsTable1.TableName <> 'INVOICE') or   (AdsTable1.IndexName <> 'Invoice No') then   begin   ShowMessage('Click Show Invoice Table and Select Invoice '+   'No Index before setting a range');   Exit;   end;   AdsTable1.SetRange([StartRange.Text], [EndRange.Text]); end;

Figure 12-2: A range limits the records available in a table.

Setting a Filter

A filter is similar to a range in that it can limit a table to a subset of records. Unlike a range, however, a filter does not rely on the current index.

You set a filter by assigning a filter expression to an AdsTable’s Filter property, and then setting the Filtered property to True. You drop a filter by setting the Filter property to an empty string or by setting Filtered to False (or both). Setting and dropping a filter is demonstrated in the following code, which is located in the OnClick event handler for the Set Filter button (shown in Figure 12-1):

procedure TForm1.FilterBtnClick(Sender: TObject); begin if FilterBtn.Caption = 'Drop Filter' then   begin   AdsTable1.Filtered := False;   FilterBtn.Caption := 'Set Filter';   end   else   begin   if (not AdsTable1.Active) or   ( not (DataSource1.DataSet is TAdsTable)) then   begin   ShowMessage('Please open a table before '+   'setting a filter');   Exit;   end;   AdsTable1.Filter := FilterText.Text;   AdsTable1.Filtered := True;   FilterBtn.Caption := 'Drop Filter';   end; end;

If you run this project, click Show the Invoice Table button, and then enter the following filter expression, the DBGrid at the bottom of the form will display only two records if you click the Set Filter button:

Customer ID = 12037 and Employee ID = 89

Note

Although a filter does not rely on the current index, the speed with which a filter can be applied is directly related to the available indexes on the table. Specifically, filters use AOFs (AOFs are described in Chapter 3). As a result, filters can be applied quickly when the expressions in the filter expression map to available indexes on the underlying table. By comparison, filters applied when you use the BDE do not use indexes, making filters under ADS significantly faster than the corresponding filters under the BDE.

Scanning a Result Set

Scanning is the process of sequentially reading every record in a result set or table, or every record in the range and/or filtered view of the result set or table if either a filter or a range, or both, are active. In most cases, scanning involves an initial navigation to the first record of the result set or table, followed by repeated calls to advance one record, until all of the records have been visited.

Although scanning is a common task, it is important to note that it necessarily requires the client application to retrieve all of the records in the result set. This is not a problem when few records are involved, but if a large number of records are being scanned, network resources may be taxed.

Tip

If you are using ADS, and you must scan a large number of records, implement the operation using an AEP as described in Chapter 7. Scanning from an AEP installed on ADS requires no network resources.

The following code demonstrates the scanning of an AdsTable. This code, associated with the OnClick event handler of the List Products button (shown in Figure 12-1), navigates the entire PRODUCTS table, assigning data from each record to the ProductList list box:

procedure TForm1.ListProductsBtnClick(Sender: TObject); begin   DataSource1.DataSet := nil;   if AdsTable1.Active then AdsTable1.Close;   AdsTable1.TableName := 'PRODUCTS';   AdsTable1.Open;   AdsTable1.First;   while not AdsTable1.EOF do   begin   ProductList.Items.Add(AdsTable1.Fields[0].AsString +   '  ' + AdsTable1.Fields[1].AsString);   AdsTable1.Next;   end;   AdsTable1.Close; end;