Delphi data set filtering skills

When we operate a data set, we often need to filter the data. For example: a data table named Customer has CustNo, CustName, Country, Address, Phone, State, TaxRate and other fields. If you only want to view the country, For customer records with China or customer numbers greater than 1000, the data set needs to be filtered. In summary, there are the following filtering methods:

1. Using the Filter properties of TTable and TQuery

1. Set the Filter property at design time

For example, set Filter to: Country='China' and then change the Filtered property to True (note that filtering is only effective when Filtered is True). Then you can only see the records whose corresponding Country field content is 'China'.

The operators that can be used when setting Filter are: ＜, ＞, ＜=, ＞=, =, ＜＞, AND, OR, NOT.

For example, if you set the Filter to: CustNo>=1000andCustNo<=5000, you can only see customer records with customer numbers between 1000 and 5000.

2. Dynamic filtering during program running

To change the Filter property while the program is running, this includes two situations:

(1) The right side of the operator is a constant, for example: Table1Filter:=′State′+′=′+′′′HI′′′;

Note: String constants must be enclosed in three pairs of single quotes.

(2) The right side of the operator is not a constant, it may be a value specified by a variable, or a value given by an input box. At this time, you need to use the Format function. The code form is: Table1Filter:=Format(′State′+′=′+′′′%S′′′,[StateValue]); where StateValue is a string variable that has been assigned a value, and can also be in other forms ,For example:Edit1Text.

2. Use ApplyRange to filter records in the data set

By executing the following code, you will only be able to see customer records with customer numbers between 1000 and 5000. The several processes that make up this routine are:

ApplyRange,SetRangeStart,SetRangeEnd.

Table1SetRangeStart;

Table1[′CustNo′]:=1000;

Table1SetRangeEnd;

Table1[′CustNo′]:=5000;

Table1ApplyRange;

Note: This process only works on indexed fields. If you want to filter based on non-indexed fields, you might as well use a little trick: create a fake index. The implementation method is: Table1.IndexFieldNames:=field name; Delphi also provides simple methods of calling SetRangeStart, SetRangeEnd and ApplyRange, for example: Table1.SetRange([Edit1.Text],[Edit2.Text]);

3. Use the OnFilterRecord event to filter. The OnFilterRecord event allows you to create filtering programs based on non-keyed fields, for example:

PRocedureTForm1.Table1FilterRecord(DataSet:TDataSet;varAccept:Boolean);

begin

Accept:=DataSet[′State′]=′CA′;

end;

4. SQL statements using TQuery control

1.SQL statements do not contain variables and parameters

Select*fromCustomer

WhereCustNo＞=1000andCustNo＜=5000

2.SQL statements contain parameters

Select*fromCustomer

WhereCustNo>=:CustNo

The parameter CustNo is assigned a value during runtime.

3.SQL statements contain variables

At this time, the code to add SQL statements to the TQuery control should be written like this:

Query1.Close;

Query1.SQL.Clear;

Query1.SQL.Add(Format(′Select＊fromCustomer′+′′+′whereState=′+′′′%S′′′,[StateValue]));

Query1.Open; Among the four methods above, the fourth is the most powerful and the most flexible to use.