Demystifying the Client Dataset - Using Filters and Ranges with the Client Dataset
(Page 3 of 4 )
As a descendant of TDataset, the client dataset inherits all of its methods using which a user can navigate, modify, search, sort, filter, index and perform any action typically provided in a dataset class. Since the data is stored locally, the time required to perform any of these actions would be far less when compared to the time it takes to perform the same actions when the data is on a remote server. Further, the chance of data corruption is lower when the underlying table is reduced since the table or file is open for a very short time.
The client dataset can be made to display only a specific set of records to the user by applying filters or ranges. You can use the Filter property of the client dataset to specify a condition that the record must meet to be included in the subset available to the user. To enable filtering of records set the Filtered property of the client dataset to true. A Filter can be created both at design time and runtime by specifying the condition in the filter property of the client dataset. In the following example we will filter the dataset to display only those records that have the value "Mindfire" in their Company field.
CDS1.Filter := 'Company =' + QuotedStr('Mindfire');
The dynamic filter condition can also be supplied at runtime by supplying the user input to the "Filter" property of the client dataset:
CDS1.Filter:=Edit1.Text;
Unlike other datasets which parse the filter strings into SQL commands that are implemented on the database server, the client dataset implements its own filter support and does not depend on the SQL dialect of the database server. This makes the client dataset much more powerful when working with filtered records since the client dataset can use most arithmetic and logical operators as well as string and DateTime operators and functions to filter records based on various criteria. For a complete list of operators and functions that can be used by the client dataset to filter records please refer to Delphi documentation.
However, the filter property requires you to specify the condition in a single text string. If you want to employ complex and multiple lines of texts for filtering, you can use the OnFilterRecord event handler. But unlike the filter property, the OnFilterRecord event can be implemented at design time only and cannot be created during runtime.
You can also use ranges to limit the numbers of records that are displayed to the user, but it is different from filtering as it displays only those records whose indexes fall between the specified boundary values. You can call the SetRangeStart method to specify the starting value of the range and the SetRangeEnd method to specify the ending value. To modify an existing range you can call the EditRangeStart method. Finally, when creating or modifying a range you can complete the process by calling the ApplyRange method to create a range and CancelRange method to cancel the process.
It should be borne in mind that filters and ranges display a set of records that meet the conditions specified, but the dataset itself contains all the records fetched from the data source and is not affected by filters or ranges. To retrieve specific records from the data source use conditional SQL queries in the CommandText property of the client dataset. The CommandText property of the client dataset allows you to specify a SQL statement, table name or stored procedure to retrieve specific records from the database. To use this property make sure that poAllowCommandText of the associated provider is set to true. The value specified in the CommandText property is sent to the provider when you call the client dataset's Open method or Execute method.
If you are using parameterized SQL queries or stored procedures make sure that corresponding parameters are passed using the Params property of the client dataset. You can also use the Params property to limit the records that are retrieved and cached by the client dataset. At design time you can create parameters by selecting the Params property in the Object Inspector and bringing up the collection editor. During runtime call the CreateParam method.
Next: Cache Updates >>
More Delphi-Kylix Articles
More By Danish Ahmed
