文章出处: http://www.informit.com/articles/article.aspx?p=24094
In the preceding two chapters, I discussed dbExpress—a unidirectional database technology. In the real world, most applications support bidirectional scrolling through a dataset. As noted previously, Borland has addressed bidirectional datasets through a technology known as client datasets. This chapter introduces you to the basic operations of client datasets, including how they are a useful standalone tool. Subsequent chapters focus on more advanced client dataset capabilities, including how you can hook a client dataset up to a dbExpress (or other) database connection to create a true multitier application.
What Is a Client Dataset?
A client dataset, as its name suggests, is a dataset that is located in a client application (as opposed to an application server). The name is a bit of a misnomer, because it seems to indicate that client datasets have no use outside a client/server or multitier application. However, as you'll see in this chapter, client datasets are useful in other types of applications, especially single-tier database applications.
NOTE
Client datasets were originally introduced in Delphi 3, and they presented a method for creating multitier applications in Delphi. As their use became more widespread, they were enhanced to support additional single-tier functionality.
The base class in VCL/CLX for client datasets is TCustomClientDataSet. Typically, you don't work withTCustomClientDataSet directly, but with its direct descendent, TClientDataSet. (In Chapter 7, "Dataset Providers," I'll introduce you to other descendents of TCustomClientDataSet.) For readability and generalization, I'll refer to client datasets generically in this book as TClientDataSet.
Advantages and Disadvantages of Client Datasets
Client datasets have a number of advantages, and a couple of perceived disadvantages. The advantages include:
· Memory based. Client datasets reside completely in memory, making them useful for temporary tables.
· Fast. Because client datasets are RAM based, they are extremely fast.
· Efficient. Client datasets store their data in a very efficient manner, making them resource friendly.
· On-the-fly indexing. Client datasets enable you to create and use indexes on-the-fly, making them extremely versatile.
· Automatic undo support. Client datasets provide multilevel undo support, making it easy to perform what ifoperations on your data. Undo support is discussed in Chapter 4, "Advanced Client Dataset Operations."
· Maintained aggregates. Client datasets can automatically calculate averages, subtotals, and totals over a group of records. Maintained aggregates are discussed in detail in Chapter 4.
The perceived disadvantages include:
· Memory based. This client dataset advantage can also be a disadvantage. Because client datasets reside in RAM, their size is limited by the amount of available RAM.
· Single user. Client datasets are inherently single-user datasets because they are kept in RAM.
When you understand client datasets, you'll discover that these so-called disadvantages really aren't detrimental to your application at all. In particular, basing client datasets entirely in RAM has both advantages and disadvantages.
Because they are kept entirely in your computer's RAM, client datasets are extremely useful for temporary tables, small lookup tables, and other nonpersistent database needs. Client datasets also are fast because they are RAM based. Inserting, deleting, searching, sorting, and traversing in client datasets are lightening fast.
On the flip side, you need to take steps to ensure that client datasets don't grow too large because you waste precious RAM if you attempt to store huge databases in in-memory datasets. Fortunately, client datasets store their data in a very compact form. (I'll discuss this in more detail in the "Undo Support" section of Chapter 7.)
Because they are memory based, client datasets are inherently single user. Remote machines do not have access to a client dataset on a local machine. In Chapter 8, "DataSnap," you'll learn how to connect a client dataset to an application server in a three-tier configuration that supports true multiuser operation.
Creating Client Datasets
Using client datasets in your application is similar to using any other type of dataset because they derive fromTDataSet.
You can create client datasets either at design-time or at runtime, as the following sections explain.
Creating a Client Dataset at Design-Time
Typically, you create client datasets at design-time. To do so, drop a TClientDataSet component (located on the Data Access tab) on a form or data module. This creates the component, but doesn't set up any field or index definitions. Name the component cdsEmployee.
To create the field definitions for the client dataset, double-click the TClientDataSet component in the form editor. The standard Delphi field editor is displayed. Right-click the field editor and select New Field... from the pop-up menu to create a new field. The dialog shown in Figure 3.1 appears.
Figure 3.1 Use the New Field dialog to add a field to a dataset.
If you're familiar with the field editor, you notice a new field type available for client datasets, called Aggregate fields. I'll discuss Aggregate fields in detail in the following chapter. For now, you should understand that you can add data, lookup, calculated, and internally calculated fields to a client dataset—just as you can for any dataset.
The difference between client datasets and other datasets is that when you create a data field for a typical dataset, all you are doing is creating a persistent field object that maps to a field in the underlying database. For a client dataset, you are physically creating the field in the dataset along with a persistent field object. At design-time, there is no way to create a field in a client dataset without also creating a persistent field object.
Data Fields
Most of the fields in your client datasets will be data fields. A data field represents a field that is physically part of the dataset, as opposed to a calculated or lookup field (which are discussed in the following sections). You can think of calculated and lookup fields as virtual fields because they appear to exist in the dataset, but their data actually comes from another location.
Let's add a field named ID to our dataset. In the field editor, enter ID in the Name edit control. Tab to the Type combo box and type Integer, or select it from the drop-down list. (The component name has been created for you automatically.) The Size edit control is disabled because Integer values are a fixed-length field. The Field type is preset to Data, which is what we want. Figure 3.2 shows the completed dialog.
Figure 3.2 The New Field dialog after entering information for a new field.
Click OK to add the field to the client dataset. You'll see the new ID field listed in the field editor.
Now add a second field, called LastName. Right-click the field editor to display the New Field dialog and enter LastNamein the Name edit control. In the Type combo, select String. Then, set Size to 30—the size represents the maximum number of characters allowed for the field. Click OK to add the LastName field to the dataset.
Similarly, add a 20-character FirstName field and an Integer Department field.Finally, let's add a Salary field. Open the New Field dialog. In the Name edit control, type Salary. Set the Type to Currency and click OK. (The currency type instructs Delphi to automatically display it with a dollar sign.)
If you have performed these steps correctly, the field editor looks like Figure 3.3.
Figure 3.3 The field editor after adding five fields.
That's enough fields for this dataset. In the next section, I'll show you how to create a calculated field.
Calculated Fields
Calculated fields, as indicated previously, don't take up any physical space in the dataset. Instead, they are calculated on-the-fly from other data stored in the dataset. For example, you might create a calculated field that adds the values of two data fields together. In this section, we'll create two calculated fields: one standard and one internal.
NOTE
Actually, internal calculated fields do take up space in the dataset, just like a standard data field. For that reason, you can create indexes on them like you would on a data field. Indexes are discussed later in this chapter.
Standard Calculated Fields
In this section, we'll create a calculated field that computes an annual bonus, which we'll assume to be five percent of an employee's salary.
To create a standard calculated field, open the New Field dialog (as you did in the preceding section). Enter a Name ofBonus and a Type of Currency.
In the Field Type radio group, select Calculated. This instructs Delphi to create a calculated field, rather than a data field. Click OK.
That's all you need to do to create a calculated field. Now, let's look at internal calculated fields.
Internal Calculated Fields
Creating an internal calculated field is almost identical to creating a standard calculated field. The only difference is that you select InternalCalc as the Field Type in the New Field dialog, instead of Calculated.
Another difference between the two types of calculated fields is that standard calculated fields are calculated on-the-fly every time their value is required, but internal calculated fields are calculated once and their value is stored in RAM. (Of course, internal calculated fields recalculate automatically if the underlying fields that they are calculated from change.)
The dataset's AutoCalcFields property determines exactly when calculated fields are recomputed. If AutoCalcFieldsis True (the default value), calculated fields are computed when the dataset is opened, when the dataset enters edit mode, and whenever focus in a form moves from one data-aware control to another and the current record has been modified. If AutoCalcFields is False, calculated fields are computed when the dataset is opened, when the dataset enters edit mode, and when a record is retrieved from an underlying database into the dataset.
There are two reasons that you might want to use an internal calculated field instead of a standard calculated field. If you want to index the dataset on a calculated field, you must use an internal calculated field. (Indexes are discussed in detail later in this chapter.) Also, you might elect to use an internal calculated field if the field value takes a relatively long time to calculate. Because they are calculated once and stored in RAM, internal calculated fields do not have to be computed as often as standard calculated fields.
Let's add an internal calculated field to our dataset. The field will be called Name, and it will concatenate the FirstNameand LastName fields together. We probably will want an index on this field later, so we need to make it an internal calculated field.
Open the New Field dialog, and enter a Name of Name and a Type of String. Set Size to 52 (which accounts for the maximum length of the last name, plus the maximum length of the first name, plus a comma and a space to separate the two).
In the Field Type radio group, select InternalCalc and click OK.
Providing Values for Calculated Fields
At this point, we've created our calculated fields. Now we need to provide the code to calculate the values.TClientDataSet, like all Delphi datasets, supports a method named OnCalcFields that we need to provide a body for.
Click the client dataset again, and in the Object Inspector, click the Events tab. Double-click the OnCalcFields event to create an event handler.
We'll calculate the value of the Bonus field first. Flesh out the event handler so that it looks like this:
procedure TForm1.cdsEmployeeCalcFields(DataSet: TDataSet);
begin
cdsEmployeeBonus.AsFloat := cdsEmployeeSalary.AsFloat * 0.05;
end;
That's easy—we just take the value of the Salary field, multiply it by five percent (0.05), and store the value in the Bonusfield.
Now, let's add the Name field calculation. A first (reasonable) attempt looks like this:
procedure TForm1.cdsEmployeeCalcFields(DataSet: TDataSet);
begin
cdsEmployeeBonus.AsFloat := cdsEmployeeSalary.AsFloat * 0.05;
cdsEmployeeName.AsString := cdsEmployeeLastName.AsString + ', ' +
cdsEmployeeFirstName.AsString;
end;
This works, but it isn't efficient. The Name field calculates every time the Bonus field calculates. However, recall that it isn't necessary to compute internal calculated fields as often as standard calculated fields. Fortunately, we can check the dataset's State property to determine whether we need to compute internal calculated fields or not, like this:
procedure TForm1.cdsEmployeeCalcFields(DataSet: TDataSet);
begin
cdsEmployeeBonus.AsFloat := cdsEmployeeSalary.AsFloat * 0.05;
if cdsEmployee.State = dsInternalCalc then
cdsEmployeeName.AsString := cdsEmployeeLastName.AsString + ', ' +
cdsEmployeeFirstName.AsString;
end;
Notice that the Bonus field is calculated every time, but the Name field is only calculated when Delphi tells us that it's time to compute internal calculated fields.
Lookup Fields
Lookup fields are similar, in concept, to calculated fields because they aren't physically stored in the dataset. However, instead of requiring you to calculate the value of a lookup field, Delphi gets the value from another dataset. Let's look at an example.
Earlier, we created a Department field in our dataset. Let's create a new Department dataset to hold department information.
Drop a new TClientDataSet component on your form and name it cdsDepartment. Add two fields: Dept (an integer) and Description (a 30-character string).
Show the field editor for the cdsEmployee dataset by double-clicking the dataset. Open the New Field dialog. Name the field DepartmentName, and give it a Type of String and a Size of 30.
In the Field Type radio group, select Lookup. Notice that two of the fields in the Lookup definition group box are now enabled. In the Key Fields combo, select Department. In the Dataset combo, select cdsDepartment.
At this point, the other two fields in the Lookup definition group box are accessible. In the Lookup Keys combo box, select Dept. In the Result Field combo, select Description. The completed dialog should look like the one shown inFigure 3.4.
Figure 3.4 Adding a lookup field to a dataset.
The important thing to remember about lookup fields is that the Key field represents the field in the base dataset that references the lookup dataset. Dataset refers to the lookup dataset. The Lookup Keys combo box represents the Key field in the lookup dataset. The Result field is the field in the lookup dataset from which the lookup field obtains its value.
To create the dataset at design time, you can right-click the TClientDataSet component and select Create DataSet from the pop-up menu.
Now that you've seen how to create a client dataset at design-time, let's see what's required to create a client dataset at runtime.
Creating a Client Dataset at Runtime
To create a client dataset at runtime, you start with the following skeletal code:
var
CDS: TClientDataSet;
begin
CDS := TClientDataSet.Create(nil);
try
// Do something with the client dataset here
finally
CDS.Free;
end;
end;
After you create the client dataset, you typically add fields, but you can load the client dataset from a disk instead (as you'll see later in this chapter in the section titled "Persisting Client Datasets").
Adding Fields to a Client Dataset
To add fields to a client dataset at runtime, you use the client dataset's FieldDefs property. FieldDefs supports two methods for adding fields: AddFieldDef and Add.
AddFieldDef
TFieldDefs.AddFieldDef is defined like this:
function AddFieldDef: TFieldDef;
As you can see, AddFieldDef takes no parameters and returns a TFieldDef object. When you have the TFieldDefobject, you can set its properties, as the following code snippet shows.
var
FieldDef: TFieldDef;
begin
FieldDef := ClientDataSet1.FieldDefs.AddFieldDef;
FieldDef.Name := 'Name';
FieldDef.DataType := ftString;
FieldDef.Size := 20;
FieldDef.Required := True;
end;
Add
A quicker way to add fields to a client dataset is to use the TFieldDefs.Add method, which is defined like this:
procedure Add(const Name: string; DataType: TFieldType; Size: Integer = 0;
Required: Boolean = False);
The Add method takes the field name, the data type, the size (for string fields), and a flag indicating whether the field is required as parameters. By using Add, the preceding code snippet becomes the following single line of code:
ClientDataSet1.FieldDefs.Add('Name', ftString, 20, True);
Why would you ever want to use AddFieldDef when you could use Add? One reason is that TFieldDef contains several more-advanced properties (such as field precision, whether or not it's read-only, and a few other attributes) in addition to the four supported by Add. If you want to set these properties for a field, you need to go through the TFieldDef. You should refer to the Delphi documentation for TFieldDef for more details.
Creating the Dataset
After you create the field definitions, you need to create the empty dataset in memory. To do this, callTClientDataSet.CreateDataSet, like this:
ClientDataSet1.CreateDataSet;
As you can see, it's somewhat easier to create your client datasets at design-time than it is at runtime. However, if you commonly create temporary in-memory datasets, or if you need to create a client dataset in a formless unit, you can create the dataset at runtime with a minimal amount of fuss.
Accessing Fields
Regardless of how you create the client dataset, at some point you need to access field information—whether it's for display, to calculate some values, or to add or modify a new record.
There are several ways to access field information in Delphi. The easiest is to use persistent fields.
Persistent Fields
Earlier in this chapter, when we used the field editor to create fields, we were also creating persistent field objects for those fields. For example, when we added the LastName field, Delphi created a persistent field object namedcdsEmployeeLastName.
When you know the name of the field object, you can easily retrieve the contents of the field by using the AsXxx family of methods. For example, to access a field as a string, you would reference the AsString property, like this:
ShowMessage('The employee''s last name is ' +
cdsEmployeeLastName.AsString);
To retrieve the employee's salary as a floating-point number, you would reference the AsFloat property:
Bonus := cdsEmployeeSalary.AsFloat * 0.05;
See the VCL/CLX source code and the Delphi documentation for a list of available access properties.
NOTE
You are not limited to accessing a field value in its native format. For example, just because Salary is a currency field doesn't mean you can't attempt to access it as a string. The following code displays an employee's salary as a formatted currency:
ShowMessage('Your salary is ' + cdsEmployeeSalary.AsString);
NOTE
You could access a string field as an integer, for example, if you knew that the field contained an integer value. However, if you try to access a field as an integer (or other data type) and the field doesn't contain a value that's compatible with that data type, Delphi raises an exception.
Nonpersistent Fields
If you create a dataset at design-time, you probably won't have any persistent field objects. In that case, there are a few methods you can use to access a field's value.
The first is the FieldByName method. FieldByName takes the field name as a parameter and returns a temporary field object. The following code snippet displays an employee's last name using FieldByName.
ShowMessage('The employee''s last name is ' +
ClientDataSet1.FieldByName('LastName').AsString);
CAUTION
If you call FieldByName with a nonexistent field name, Delphi raises an exception.
Another way to access the fields in a dataset is through the FindField method, like this:
if ClientDataSet1.FindField('LastName') <> nil then
ShowMessage('Dataset contains a LastName field');
Using this technique, you can create persistent fields for datasets created at runtime.
var
fldLastName: TField;
fldFirstName: TField;
begin
...
fldLastName := cds.FindField('LastName');
fldFirstName := cds.FindField('FirstName');
...
ShowMessage('The last name is ' + fldLastName.AsString);
end;
Finally, you can access the dataset's Fields property. Fields contains a list of TField objects for the dataset, as the following code illustrates:
var
Index: Integer;
begin
for Index := 0 to ClientDataSet1.Fields.Count - 1 do
ShowMessage(ClientDataSet1.Fields[Index].AsString);
end;
You do not normally access Fields directly. It is generally not safe programming practice to assume, for example, that a given field is the first field in the Fields list. However, there are times when the Fields list comes in handy. For example, if you have two client datasets with the same structure, you could add a record from one dataset to the other using the following code:
var
Index: Integer;
begin
ClientDataSet2.Append;
for Index := 0 to ClientDataSet1.Fields.Count - 1 do
ClientDataSet2.Fields[Index].AsVariant :=
ClientDataSet1.Fields[Index].AsVariant;
ClientDataSet2.Post;
end;
The following section discusses adding records to a dataset in detail.
Populating and Manipulating Client Datasets
After you create a client dataset (either at design-time or at runtime), you want to populate it with data. There are several ways to populate a client dataset: You can populate it manually through code, you can load the dataset's records from another dataset, or you can load the dataset from a file or a stream. The following sections discuss these methods, as well as how to modify and delete records.
Populating Manually
The most basic way to enter data into a client dataset is through the Append and Insert methods, which are supported by all datasets. The difference between them is that Append adds the new record at the end of the dataset, but Insertplaces the new record immediately before the current record.
I always use Append to insert new records because it's slightly faster than Insert. If the dataset is indexed, the new record is automatically sorted in the correct order anyway.
The following code snippet shows how to add a record to a client dataset:
cdsEmployee.Append; // You could use cdsEmployee.Insert; here as well
cdsEmployee.FieldByName('ID').AsInteger := 5;
cdsEmployee.FieldByName('FirstName').AsString := 'Eric';
cdsEmployee.Post;
Modifying Records
Modifying an existing record is almost identical to adding a new record. Rather than calling Append or Insert to create the new record, you call Edit to put the dataset into edit mode. The following code changes the first name of the current record to Fred.
cdsEmployee.Edit; // Edit the current record
cdsEmployee.FieldByName('FirstName').AsString := 'Fred';
cdsEmployee.Post;
Deleting Records
To delete the current record, simply call the Delete method, like this:
cdsEmployee.Delete;
If you want to delete all records in the dataset, you can use EmptyDataSet instead, like this:
cdsEmployee.EmptyDataSet;
Populating from Another Dataset
dbExpress datasets are unidirectional and you can't scroll backward through them. This makes them incompatible with bidirectional, data-aware controls such as TDBGrid. However, TClientDataSet can load its data from another dataset (including dbExpress datasets, BDE datasets, or other client datasets) through a provider. Using this feature, you can load a client dataset from a unidirectional dbExpress dataset, and then connect a TDBGrid to the client dataset, providing bidirectional support.
Indeed, this capability is so powerful and important that it forms the basis for Delphi's multitier database support.
Populating from a File or Stream: Persisting Client Datasets
Though client datasets are located in RAM, you can save them to a file or a stream and reload them at a later point in time, making them persistent. This is the third method of populating a client dataset.
To save the dataset to a file, use the SaveToFile method, which is defined like this:
procedure SaveToFile(const FileName: string = '';
Format: TDataPacketFormat = dfBinary);
Similarly, to save the dataset to a stream, you call SaveToStream, which is defined as follows:
procedure SaveToStream(Stream: TStream; Format: TDataPacketFormat = dfBinary);
SaveToFile accepts the name of the file that you're saving to. If the filename is blank, the data is saved using theFileName property of the client dataset.
Both SaveToFile and SaveToStream take a parameter that indicates the format to use when saving data. Client datasets can be stored in one of three file formats: binary, or either flavor of XML. Table 3.1 lists the possible formats.
Table 3.1 Data Packet Formats for Loading and Saving Client Datasets
|
Value
|
Description
|
|
dfBinary
|
Data is stored using a proprietary, binary format.
|
|
dfXML
|
Data is stored in XML format. Extended characters are represented using an escape sequence.
|
|
dfXMLUTF8
|
Data is stored in XML format. Extended characters are represented using UTF8.
|
When client datasets are stored to disk, they are referred to as MyBase files. MyBase stores one dataset per file, or per stream, unless you use nested datasets.
NOTE
If you're familiar with Microsoft ADO, you recall that ADO enables you to persist datasets using XML format. The XML formats used by ADO and MyBase are not compatible. In other words, you cannot save an ADO dataset to disk in XML format, and then read it into a client dataset (or vice versa).
Sometimes, you need to determine how many bytes are required to store the data contained in the client dataset. For example, you might want to check to see if there is enough room on a floppy disk before saving the data there, or you might need to preallocate the memory for a stream. In these cases, you can check the DataSize property, like this:
if ClientDataSet1.DataSize > AvailableSpace then
ShowMessage('Not enough room to store the data');
DataSize always returns the amount of space necessary to store the data in binary format (dfBinary). XML format usually requires more space, perhaps twice as much (or even more).
NOTE
One way to determine the amount of space that's required to save the dataset in XML format is to save the dataset to a memory stream, and then obtain the size of the resulting stream.
Example: Creating, Populating, and Manipulating a Client Dataset
The following example illustrates how to create, populate, and manipulate a client dataset at runtime. Code is also provided to save the dataset to disk and to load it.
Listing 3.1 shows the complete source code for the CDS (ClientDataset) application.
Listing 3.1 CDS—MainForm.pas
unit MainForm;
interface
uses
SysUtils, Types, IdGlobal, Classes, QGraphics, QControls, QForms, QDialogs,
QStdCtrls, DB, DBClient, QExtCtrls, QGrids, QDBGrids, QActnList;
const
MAX_RECS = 10000;
type
TfrmMain = class(TForm)
DataSource1: TDataSource;
pnlClient: TPanel;
pnlBottom: TPanel;
btnPopulate: TButton;
btnSave: TButton;
btnLoad: TButton;
ActionList1: TActionList;
btnStatistics: TButton;
Populate1: TAction;
Statistics1: TAction;
Load1: TAction;
Save1: TAction;
DBGrid1: TDBGrid;
lblFeedback: TLabel;
procedure FormCreate(Sender: TObject);
procedure Populate1Execute(Sender: TObject);
procedure Statistics1Execute(Sender: TObject);
procedure Save1Execute(Sender: TObject);
procedure Load1Execute(Sender: TObject);
private
{ Private declarations }
FCDS: TClientDataSet;
public
{ Public declarations }
end;
var
frmMain: TfrmMain;
implementation
{$R *.xfm}
procedure TfrmMain.FormCreate(Sender: TObject);
begin
FCDS := TClientDataSet.Create(Self);
FCDS.FieldDefs.Add('ID', ftInteger, 0, True);
FCDS.FieldDefs.Add('Name', ftString, 20, True);
FCDS.FieldDefs.Add('Birthday', ftDateTime, 0, True);
FCDS.FieldDefs.Add('Salary', ftCurrency, 0, True);
FCDS.CreateDataSet;
DataSource1.DataSet := FCDS;
end;
procedure TfrmMain.Populate1Execute(Sender: TObject);
const
FirstNames: array[0 .. 19] of string = ('John', 'Sarah', 'Fred', 'Beth',
'Eric', 'Tina', 'Thomas', 'Judy', 'Robert', 'Angela', 'Tim', 'Traci',
'David', 'Paula', 'Bruce', 'Jessica', 'Richard', 'Carla', 'James',
'Mary');
LastNames: array[0 .. 11] of string = ('Parker', 'Johnson', 'Jones',
'Thompson', 'Smith', 'Baker', 'Wallace', 'Harper', 'Parson', 'Edwards',
'Mandel', 'Stone');
var
Index: Integer;
t1, t2: DWord;
begin
RandSeed := 0;
t1 := GetTickCount;
FCDS.DisableControls;
try
FCDS.EmptyDataSet;
for Index := 1 to MAX_RECS do begin
FCDS.Append;
FCDS.FieldByName('ID').AsInteger := Index;
FCDS.FieldByName('Name').AsString := FirstNames[Random(20)] + ' ' +
LastNames[Random(12)];
FCDS.FieldByName('Birthday').AsDateTime := StrToDate('1/1/1950') +
Random(10000);
FCDS.FieldByName('Salary').AsFloat := 20000.0 + Random(600) * 100;
FCDS.Post;
end;
FCDS.First;
finally
FCDS.EnableControls;
end;
t2 := GetTickCount;
lblFeedback.Caption := Format('%d ms to load %.0n records',
[t2 - t1, MAX_RECS * 1.0]);
end;
procedure TfrmMain.Statistics1Execute(Sender: TObject);
var
t1, t2: DWord;
msLocateID: DWord;
msLocateName: DWord;
begin
FCDS.First;
t1 := GetTickCount;
FCDS.Locate('ID', 9763, []);
t2 := GetTickCount;
msLocateID := t2 - t1;
FCDS.First;
t1 := GetTickCount;
FCDS.Locate('Name', 'Eric Wallace', []);
t2 := GetTickCount;
msLocateName := t2 - t1;
ShowMessage(Format('%d ms to locate ID 9763' +
#13'%d ms to locate Eric Wallace' +
#13'%.0n bytes required to store %.0n records',
[msLocateID, msLocateName, FCDS.DataSize * 1.0, MAX_RECS * 1.0]));
end;
procedure TfrmMain.Save1Execute(Sender: TObject);
var
t1, t2: DWord;
begin
t1 := GetTickCount;
FCDS.SaveToFile('C:/Employee.cds');
t2 := GetTickCount;
lblFeedback.Caption := Format('%d ms to save data', [t2 - t1]);
end;
procedure TfrmMain.Load1Execute(Sender: TObject);
var
t1, t2: DWord;
begin
try
t1 := GetTickCount;
FCDS.LoadFromFile('C:/Employee.cds');
t2 := GetTickCount;
lblFeedback.Caption := Format('%d ms to load data', [t2 - t1]);
except
FCDS.Open;
raise;
end;
end;
end.
There are five methods in this application and each one is worth investigating:
· FormCreate creates the client dataset and its schema at runtime. It would actually be easier to create the dataset at design-time, but I wanted to show you the code required to do this at runtime. The code creates four fields: Employee ID, Name, Birthday, and Salary.
· Populate1Execute loads the client dataset with 10,000 employees made up of random data. At the beginning of the method, I manually set RandSeed to 0 to ensure that multiple executions of the application would generate the same data.
NOTE
The Delphi Randomizer normally seeds itself with the current date and time. By manually seeding the Randomizer with a constant value, we can ensure that the random numbers generated are consistent every time we run the program.
· The method calculates approximately how long it takes to generate the 10,000 employees, which on my computer is about half of a second.
· Statistics1Execute simply measures the length of time required to perform a couple of Locate operations and calculates the amount of space necessary to store the data on disk (again, in binary format). I'll be discussing the Locate method later in this chapter.
· Save1Execute saves the data to disk under the filename C:/Employee.cds. The .cds extension is standard, although not mandatory, for client datasets that are saved in a binary format. Client datasets stored in XML format generally have the extension .xml.
NOTE
Please make sure that you click the Save button because the file created (C:/EMPLOYEE.CDS) is used in the rest of the example applications in this chapter, as well as some of the examples in the following chapter.
· Load1Execute loads the data from a file into the client dataset. If LoadFromFile fails (presumably because the file doesn't exist or is not a valid file format), the client dataset is left in a closed state. For this reason, I reopen the client dataset when an exception is raised.
Figure 3.5 shows the CDS application running on my computer. Note the impressive times posted to locate a record. Even when searching through almost the entire dataset to find ID 9763, it only takes approximately 10 ms on my computer.
Figure 3.5 The CDS application at runtime.
Navigating Client Datasets
A dataset is worthless without a means of moving forward and/or backward through it. Delphi's datasets provide a large number of methods for traversing a dataset. The following sections discuss Delphi's support for dataset navigation.
Sequential Navigation
The most basic way to navigate through a dataset is sequentially in either forward or reverse order. For example, you might want to iterate through a dataset when printing a report, or for some other reason. Delphi provides four simple methods to accomplish this:
· First moves to the first record in the dataset. First always succeeds, even if the dataset is empty. If it is empty, First sets the dataset's EOF (end of file) property to True.
· Next moves to the next record in the dataset (if the EOF property is not already set). If EOF is True, Next will fail. If the call to Next reaches the end of the file, it sets the EOF property to True.
· Last moves to the last record in the dataset. Last always succeeds, even if the dataset is empty. If it is empty, Last sets the dataset's BOF (beginning of file) property to True.
· Prior moves to the preceding record in the dataset (if the BOF property is not already set). If BOF is True,Prior will fail. If the call to Prior reaches the beginning of the file, it sets the BOF property to True.
The following code snippet shows how you can use these methods to iterate through a dataset:
if not ClientDataSet1.IsEmpty then begin
ClientDataSet1.First;
while not ClientDataSet1.EOF do begin
// Process the current record
ClientDataSet1.Next;
end;
ClientDataSet1.Last;
while not ClientDataSet1.BOF do begin
// Process the current record
ClientDataSet1.Prior;
end;
end;
Random-Access Navigation
In addition to First, Next, Prior, and Last (which provide for sequential movement through a dataset),TClientDataSet provides two ways of moving directly to a given record: bookmarks and record numbers.
Bookmarks
A bookmark used with a client dataset is very similar to a bookmark used with a paper-based book: It marks a location in a dataset so that you can quickly return to it later.
There are three operations that you can perform with bookmarks: set a bookmark, return to a bookmark, and free a bookmark. The following code snippet shows how to do all three:
var
Bookmark: TBookmark;
begin
Bookmark := ClientDataSet1.GetBookmark;
try
// Do something with ClientDataSet1 here that changes the current record
...
ClientDataSet1.GotoBookmark(Bookmark);
finally
ClientDataSet1.FreeBookmark(Bookmark);
end;
end;
You can create as many bookmarks as you want for a dataset. However, keep in mind that a bookmark allocates a small amount of memory, so you should be sure to free all bookmarks using FreeBookmark or your application will leak memory.
There is a second set of operations that you can use for bookmarks instead ofGetBookmark/GotoBookmark/FreeBookmark. The following code shows this alternate method:
var
BookmarkStr: string;
begin
BookmarkStr := ClientDataSet1.Bookmark;
try
// Do something with ClientDataSet1 here that changes the current record
...
finally
ClientDataSet1.Bookmark := BookmarkStr;
end;
end;
Because the bookmark returned by the property, Bookmark, is a string, you don't need to concern yourself with freeing the string when you're done. Like all strings, Delphi automatically frees the bookmark when it goes out of scope.
Record Numbers
Client datasets support a second way of moving directly to a given record in the dataset: setting the RecNo property of the dataset. RecNo is a one-based number indicating the sequential number of the current record relative to the beginning of the dataset.
You can read the RecNo property to determine the current absolute record number, and write the RecNo property to set the current record. There are two important things to keep in mind with respect to RecNo:
· Attempting to set RecNo to a number less than one, or to a number greater than the number of records in the dataset results in an At beginning of table, or an At end of table exception, respectively.
· The record number of any given record is not guaranteed to be constant. For instance, changing the active index on a dataset alters the record number of all records in the dataset.
NOTE
You can determine the number of records in the dataset by inspecting the dataset's RecordCount property. When setting RecNo, never attempt to set it to a number higher than RecordCount.
However, when used discriminately, RecNo has its uses. For example, let's say the user of your application wants to delete all records between the John Smith record and the Fred Jones record. The following code shows how you can accomplish this:
var
RecNoJohn: Integer;
RecNoFred: Integer;
Index: Integer;
begin
if not ClientDataSet1.Locate('Name', 'John Smith', []) then
raise Exception.Create('Cannot locate John Smith');
RecNoJohn := ClientDataSet1.RecNo;
if not ClientDataSet1.Locate('Name', 'Fred Jones', []) then
raise Exception.Create('Cannot locate Fred Jones');
RecNoFred := ClientDataSet1.RecNo;
if RecNoJohn < RecNoFred then
// Locate John again
ClientDataSet1.RecNo := RecNoJohn;
for Index := 1 to Abs(RecNoJohn - RecNoFred) + 1 do
ClientDataSet1.Delete;
end;
This code snippet first locates the two bounding records and remembers their absolute record numbers. Then, it positions the dataset to the lower record number. If Fred occurs before John, the dataset is already positioned at the lower record number.
Because records are sequentially numbered, we can subtract the two record numbers (and add one) to determine the number of records to delete. Deleting a record makes the next record current, so a simple for loop handles the deletion of the records.
Keep in mind that RecNo isn't usually going to be your first line of attack for moving around in a dataset, but it's handy to remember that it's available if you ever need it.
Listing 3.2 contains the complete source code for an application that demonstrates the different navigational methods of client datasets.
Listing 3.2 Navigate—MainForm.pas
unit MainForm;
interface
uses
SysUtils, Classes, QGraphics, QControls, QForms, QDialogs, QStdCtrls,
DB, DBClient, QExtCtrls, QActnList, QGrids, QDBGrids, QDBCtrls;
type
TfrmMain = class(TForm)
DataSource1: TDataSource;
pnlClient: TPanel;
pnlBottom: TPanel;
btnFirst: TButton;
btnLast: TButton;
btnNext: TButton;
btnPrior: TButton;
DBGrid1: TDBGrid;
ClientDataSet1: TClientDataSet;
btnSetRecNo: TButton;
DBNavigator1: TDBNavigator;
btnGetBookmark: TButton;
btnGotoBookmark: TButton;
procedure FormCreate(Sender: TObject);
procedure btnNextClick(Sender: TObject);
procedure btnLastClick(Sender: TObject);
procedure btnSetRecNoClick(Sender: TObject);
procedure btnFirstClick(Sender: TObject);
procedure btnPriorClick(Sender: TObject);
procedure btnGetBookmarkClick(Sender: TObject);
procedure btnGotoBookmarkClick(Sender: TObject);
private
{ Private declarations }
FBookmark: TBookmark;
public
{ Public declarations }
end;
var
frmMain: TfrmMain;
implementation
{$R *.xfm}
procedure TfrmMain.FormCreate(Sender: TObject);
begin
ClientDataSet1.LoadFromFile('C:/Employee.cds');
end;
procedure TfrmMain.btnFirstClick(Sender: TObject);
begin
ClientDataSet1.First;
end;
procedure TfrmMain.btnPriorClick(Sender: TObject);
begin
ClientDataSet1.Prior;
end;
procedure TfrmMain.btnNextClick(Sender: TObject);
begin
ClientDataSet1.Next;
end;
procedure TfrmMain.btnLastClick(Sender: TObject);
begin
ClientDataSet1.Last;
end;
procedure TfrmMain.btnSetRecNoClick(Sender: TObject);
var
Value: string;
begin
Value := '1';
if InputQuery('RecNo', 'Enter Record Number', Value) then
ClientDataSet1.RecNo := StrToInt(Value);
end;
procedure TfrmMain.btnGetBookmarkClick(Sender: TObject);
begin
if Assigned(FBookmark) then
ClientDataSet1.FreeBookmark(FBookmark);
FBookmark := ClientDataSet1.GetBookmark;
end;
procedure TfrmMain.btnGotoBookmarkClick(Sender: TObject);
begin
if Assigned(FBookmark) then
ClientDataSet1.GotoBookmark(FBookmark)
else
ShowMessage('No bookmark set!');
end;
end.
Figure 3.6 shows this program at runtime.
Client Dataset Indexes
So far, we haven't created any indexes on the client dataset and you might be wondering if (and why) they're even necessary when sequential searches through the dataset (using Locate) are so fast.
Indexes are used on client datasets for at least three reasons:
· To provide faster access to data. A single Locate operation executes very quickly, but if you need to perform thousands of Locate operations, there is a noticeable performance gain when using indexes.
· To enable the client dataset to be sorted on-the-fly. This is useful when you want to order the data in a data-aware grid, for example.
· To implement maintained aggregates.
Figure 3.6 The Navigate application demonstrates various navigational techniques.
Creating Indexes
Like field definitions, indexes can be created at design-time or at runtime. Unlike field definitions, which are usually created at design-time, you might want to create and destroy indexes at runtime. For example, some indexes are only used for a short time—say, to create a report in a certain order. In this case, you might want to create the index, use it, and then destroy it. If you constantly need an index, it's better to create it at design-time (or to create it the first time you need it and not destroy it afterward).
Creating Indexes at Design-Time
To create an index at design-time, click the TClientDataSet component located on the form or data module. In the Object Inspector, double-click the IndexDefs property. The index editor appears.
To add an index to the client dataset, right-click the index editor and select Add from the pop-up menu. Alternately, you can click the Add icon on the toolbar, or simply press Ins.
Next, go back to the Object Inspector and set the appropriate properties for the index. Table 3.2 shows the index properties.
Table 3.2 Index Properties
|
Property
|
Description
|
|
Name
|
-The name of the index. I recommend prefixing index names with the letters by (as in byName, byState, and so on).
|
|
Fields
|
-Semicolon-delimited list of fields that make up the index. Example: 'ID' or 'Name;Salary'.
|
|
DescFields
|
-A list of the fields contained in the Fields property that should be indexed in descending order. For example, to sort ascending by name, and then descending by salary, set Fields to 'Name;Salary' and DescFields to'Salary'.
|
|
CaseInsFields
|
-A list of the fields contained in the Fields property that should be indexed in a manner which is not case sensitive. For example, if the index is on the last and first name, and neither is case sensitive, set Fields to'Last;First' and CaseInsFields to 'Last;First'.
|
|
GroupingLevel
|
Used for aggregation.
|
|
Options
|
-Sets additional options on the index. The options are discussed in Table 3.3.
|
|
Expression
|
Not applicable to client datasets.
|
|
Source
|
Not applicable to client datasets.
|
Table 3.3 shows the various index options that can be set using the Options property.
Table 3.3 Index Options
|
Option
|
Description
|
|
IxPrimary
|
The index is the primary index on the dataset.
|
|
IxUnique
|
The index is unique.
|
|
IxDescending
|
The index is in descending order.
|
|
IxCaseInsensitive
|
The index is not case sensitive.
|
|
IxExpression
|
Not applicable to client datasets.
|
|
IxNonMaintained
|
Not applicable to client datasets.
|
You can create multiple indexes on a single dataset. So, you can easily have both an ascending and a descending index on EmployeeName, for example.
Creating and Deleting Indexes at Runtime
In contrast to field definitions (which you usually create at design-time), index definitions are something that you frequently create at runtime. There are a couple of very good reasons for this:
· Indexes can be quickly and easily created and destroyed. So, if you only need an index for a short period of time (to print a report in a certain order, for example), creating and destroying the index on an as-needed basis helps conserve memory.
· Index information is not saved to a file or a stream when you persist a client dataset. When you load a client database from a file or a stream, you must re-create any indexes in your code.
To create an index, you use the client dataset's AddIndex method. AddIndex takes three mandatory parameters, as well as three optional parameters, and is defined like this:
procedure AddIndex(const Name, Fields: string; Options: TIndexOptions;
const DescFields: string = ''; const CaseInsFields: string = '';
const GroupingLevel: Integer = 0);
The parameters correspond to the TIndexDef properties listed in Table 3.2. The following code snippet shows how to create a unique index by last and first names:
ClientDataSet1.AddIndex('byName', 'Last;First', [ixUnique]);
When you decide that you no longer need an index (remember, you can always re-create it if you need it later), you can delete it using DeleteIndex. DeleteIndex takes a single parameter: the name of the index being deleted. The following line of code shows how to delete the index created in the preceding code snippet:
ClientDataSet1.DeleteIndex('byName');
Using Indexes
Creating an index doesn't perform any actual sorting of the dataset. It simply creates an available index to the data. After you create an index, you make it active by setting the dataset's IndexName property, like this:
ClientDataSet1.IndexName := 'byName';
If you have two or more indexes defined on a dataset, you can quickly switch back and forth by changing the value of theIndexName property. If you want to discontinue the use of an index and revert to the default record order, you can set theIndexName property to an empty string, as the following code snippet illustrates:
// Do something in name order
ClientDataSet1.IndexName := 'byName';
// Do something in salary order
ClientDataSet1.IndexName := 'bySalary';
// Switch back to the default ordering
ClientDataSet1.IndexName := '';
There is a second way to specify indexes on-the-fly at runtime. Instead of creating an index and setting the IndexNameproperty, you can simply set the IndexFieldNames property. IndexFieldNames accepts a semicolon-delimited list of fields to index on. The following code shows how to use it:
ClientDataSet1.IndexFieldNames := 'Last;First';
Though IndexFieldNames is quicker and easier to use than AddIndex/IndexName, its simplicity does not come without a price. Specifically,
· You cannot set any index options, such as unique or descending indexes.
· You cannot specify a grouping level or create maintained aggregates.
· When you switch from one index to another (by changing the value of IndexFieldNames), the old index is automatically dropped. If you switch back at a later time, the index is re-created. This happens so fast that it's not likely to be noticeable, but you should be aware that it's happening, nonetheless. When you create indexes usingAddIndex, the index is maintained until you specifically delete it using DeleteIndex.
NOTE
Though you can switch back and forth between IndexName and IndexFieldNames in the same application, you can't set both properties at the same time. Setting IndexName clears IndexFieldNames, and setting IndexFieldNames clearsIndexName.
Retrieving Index Information
Delphi provides a couple of different methods for retrieving index information from a dataset. These methods are discussed in the following sections.
GetIndexNames
The simplest method for retrieving index information is GetIndexNames. GetIndexNames takes a single parameter, aTStrings object, in which to store the resultant index names. The following code snippet shows how to load a list box with the names of all indexes defined for a dataset.
ClientDataSet1.GetIndexNames(ListBox1.Items);
CAUTION
If you execute this code on a dataset for which you haven't defined any indexes, you'll notice that there are two indexes already defined for you: DEFAULT_ORDER and CHANGEINDEX. DEFAULT_ORDER is used internally to provide records in nonindexed order. CHANGEINDEX is used internally to provide undo support, which is discussed later in this chapter. You should not attempt to delete either of these indexes.
TIndexDefs
If you want to obtain more detailed information about an index, you can go directly to the source: TIndexDefs.TIndexDefs contains a list of all indexes, along with the information associated with each one (such as the fields that make up the index, which fields are descending, and so on).
The following code snippet shows how to access index information directly through TIndexDefs.
var
Index: Integer;
IndexDef: TIndexDef;
begin
ClientDataSet1.IndexDefs.Update;
for Index := 0 to ClientDataSet1.IndexDefs.Count - 1 do begin
IndexDef := ClientDataSet1.IndexDefs[Index];
ListBox1.Items.Add(IndexDef.Name);
end;
end;
Notice the call to IndexDefs.Update before the code that loops through the index definitions. This call is required to ensure that the internal IndexDefs list is up-to-date. Without it, it's possible that IndexDefs might not contain any information about recently added indexes.
The following application demonstrates how to provide on-the-fly indexing in a TDBGrid. It also contains code for retrieving detailed information about all the indexes defined on a dataset.
Figure 3.7 shows the CDSIndex application at runtime, as it displays index information for the employee client dataset.
Listing 3.3 contains the complete source code for the CDSIndex application.
Figure 3.7 CDSIndex shows how to create indexes on-the-fly.
Listing 3.3 CDSIndex—MainForm.pas
unit MainForm;
interface
uses
SysUtils, Classes, QGraphics, QControls, QForms, QDialogs, QStdCtrls,
DB, DBClient, QExtCtrls, QActnList, QGrids, QDBGrids;
type
TfrmMain = class(TForm)
DataSource1: TDataSource;
pnlClient: TPanel;
DBGrid1: TDBGrid;
ClientDataSet1: TClientDataSet;
pnlBottom: TPanel;
btnDefaultOrder: TButton;
btnIndexList: TButton;
ListBox1: TListBox;
procedure FormCreate(Sender: TObject);
procedure DBGrid1TitleClick(Column: TColumn);
procedure btnDefaultOrderClick(Sender: TObject);
procedure btnIndexListClick(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
frmMain: TfrmMain;
implementation
{$R *.xfm}
procedure TfrmMain.FormCreate(Sender: TObject);
begin
ClientDataSet1.LoadFromFile('C:/Employee.cds');
end;
procedure TfrmMain.DBGrid1TitleClick(Column: TColumn);
begin
try
ClientDataSet1.DeleteIndex('byUser');
except
end;
ClientDataSet1.AddIndex('byUser', Column.FieldName, []);
ClientDataSet1.IndexName := 'byUser';
end;
procedure TfrmMain.btnDefaultOrderClick(Sender: TObject);
begin
// Deleting the current index will revert to the default order
try
ClientDataSet1.DeleteIndex('byUser');
except
end;
ClientDataSet1.IndexFieldNames := '';
end;
procedure TfrmMain.btnIndexListClick(Sender: TObject);
var
Index: Integer;
IndexDef: TIndexDef;
begin
ClientDataSet1.IndexDefs.Update;
ListBox1.Items.BeginUpdate;
try
ListBox1.Items.Clear;
for Index := 0 to ClientDataSet1.IndexDefs.Count - 1 do begin
IndexDef := ClientDataSet1.IndexDefs[Index];
ListBox1.Items.Add(IndexDef.Name);
end;
finally
ListBox1.Items.EndUpdate;
end;
end;
end.
The code to dynamically sort the grid at runtime is contained in the method DBGrid1TitleClick. First, it attempts to delete the temporary index named byUser, if it exists. If it doesn't exist, an exception is raised, which the code simply eats. A real application should not mask exceptions willy-nilly. Instead, it should trap for the specific exceptions that might be thrown by the call to DeleteIndex, and let the others be reported to the user.
The method then creates a new index named byUser, and sets it to be the current index.
NOTE
Though this code works, it is rudimentary at best. There is no support for sorting on multiple grid columns, and no visual indication of what column(s) the grid is sorted by. For an elegant solution to these issues, I urge you to take a look at John Kaster's TCDSDBGrid (available as ID 15099 on Code Central at http://codecentral.borland.com).
Filters and Ranges
Filters and ranges provide a means of limiting the amount of data that is visible in the dataset, similar to a WHERE clause in a SQL statement. The main difference between filters, ranges, and the WHERE clause is that when you apply a filter or a range, it does not physically change which data is contained in the dataset. It only limits the amount of data that you can see at any given time.
Ranges
Ranges are useful when the data that you want to limit yourself to is stored in a consecutive sequence of records. For example, say a dataset contains the data shown in Table 3.4.
Table 3.4 Sample Data for Ranges and Filters
|
ID
|
Name
|
Birthday
|
Salary
|
|
4
|
Bill Peterson
|
3/28/1957
|
$60,000.00
|
|
2
|
Frank Smith
|
8/25/1963
|
$48,000.00
|
|
3
|
Sarah Johnson
|
7/5/1968
|
$52,000.00
|
|
1
|
John Doe
|
5/15/1970
|
$39,000.00
|
|
5
|
Paula Wallace
|
1/15/1971
|
$36,500.00
|
The data in this much-abbreviated table is indexed by birthday. Ranges can only be used when there is an active index on the dataset.
Assume that you want to see all employees who were born between 1960 and 1970. Because the data is indexed by birthday, you could apply a range to the dataset, like this:
ClientDataSet1.SetRange(['1/1/1960'], ['12/31/1970']);
Ranges are inclusive, meaning that the endpoints of the range are included within the range. In the preceding example, employees who were born on either January 1, 1960 or December 31, 1970 are included in the range.
To remove the range, simply call CancelRange, like this:
ClientDataSet1.CancelRange;
Filters
Unlike ranges, filters do not require an index to be set before applying them. Client dataset filters are powerful, offering many SQL-like capabilities, and a few options that are not even supported by SQL. Tables 3.5–3.10 list the various functions and operators available for use in a filter.
Table 3.5 Filter Comparison Operators
|
Function
|
Description
|
Example
|
|
=
|
Equality test
|
Name = 'John Smith'
|
|
<>
|
Inequality test
|
ID <> 100
|
|
<
|
Less than
|
Birthday < '1/1/1980'
|
|
>
|
Greater than
|
Birthday > '12/31/1960'
|
|
<=
|
Less than or equal to
|
Salary <= 80000
|
|
>=
|
Greater than or equal to
|
Salary >= 40000
|
|
BLANK
|
Empty string field (not used to test for NULL values)
|
Name = BLANK
|
|
IS NULL
|
Test for NULL value
|
Birthday IS NULL
|
|
IS NOT NULL
|
Test for non-NULLvalue
|
Birthday IS NOT NULL
|
Table 3.6 Filter Logical Operators
|
Function
|
Example
|
|
And
|
(Name = 'John Smith') and (Birthday = '5/16/1964')
|
|
Or
|
(Name = 'John Smith') or (Name = 'Julie Mason')
|
|
Not
|
Not (Name = 'John Smith')
|
Table 3.7 Filter Arithmetic Operators
|
Function
|
Description
|
Example
|
|
+
|
Addition. Can be used with numbers, strings, or dates/times.
|
Birthday + 30 < '1/1/1960'Name + 'X' = 'SmithX' Salary + 10000 = 100000
|
|
–
|
Subtraction. Can be used with numbers or dates/times.
|
Birthday - 30 > '1/1/1960' Salary - 10000 > 40000
|
|
*
|
Multiplication. Can be used with numbers only.
|
Salary * 0.10 > 5000
|
|
/
|
Division. Can be used with numbers only.
|
Salary / 10 > 5000
|
Table 3.8 Filter String Functions
|
Function
|
Description
|
Example
|
|
Upper
|
Uppercase
|
Upper(Name) = 'JOHN SMITH'
|
|
Lower
|
Lowercase
|
Lower(Name) = 'john smith'
|
|
SubString
|
Return a portion of a string
|
SubString(Name,6) = 'Smith'SubString(Name,1,4) = 'John'
|
|
Trim
|
Trim leading and trailing characters from a string
|
Trim(Name)Trim(Name, '.')
|
|
TrimLeft
|
Trim leading characters from a string
|
TrimLeft(Name)TrimLeft(Name, '.')
|
|
TrimRight
|
Trim trailing characters from a string
|
TrimRight(Name)TrimRight(Name, '.')
|
Table 3.9 Filter Date/Time Functions
|
Function
|
Description
|
Example
|
|
Year
|
Returns the year portion of a date value.
|
Year(Birthday) = 1970
|
|
Month
|
Returns the month portion of a date value.
|
Month(Birthday) = 1
|
|
Day
|
Returns the day portion of a date value.
|
Day(Birthday) = 15
|
|
Hour
|
Returns the hour portion of a time value in 24-hour format.
|
Hour(Appointment) = 18
|
|
Minute
|
Returns the minute portion of a time value.
|
Minute(Appointment) = 30
|
|
Second
|
Returns the second portion of a time value.
|
Second(Appointment) = 0
|
|
GetDate
|
Returns the current date and time.
|
Appointment < GetDate
|
|
Date
|
Returns the date portion of a date/time value.
|
Date(Appointment)
|
|
Time
|
Returns the time portion of a date/time value.
|
Time(Appointment)
|
Table 3.10 Other Filter Functions and Operators
|
Function
|
Description
|
Example
|
|
LIKE
|
Partial string comparison.
|
Name LIKE '%Smith%'
|
|
IN
|
Tests for multiple values.
|
-Year(Birthday) IN (1960, 1970, 1980)
|
|
*
|
Partial string comparison.
|
Name = 'John*'
|
To filter a dataset, set its Filter property to the string used for filtering, and then set the Filtered property to True. For example, the following code snippet filters out all employees whose names begin with the letter M.
ClientDataSet1.Filter := 'Name LIKE ' + QuotedStr('M%');
ClientDataSet1.Filtered := True;
To later display only those employees whose names begin with the letter P, simply change the filter, like this:
ClientDataSet1.Filter := 'Name LIKE ' + QuotedStr('P%');
To remove the filter, set the Filtered property to False. You don't have to set the Filter property to an empty string to remove the filter (which means that you can toggle the most recent filter on and off by switching the value of Filteredfrom True to False).
You can apply more advanced filter criteria by handling the dataset's OnFilterRecord event (instead of setting theFilter property). For example, say that you want to filter out all employees whose last names sound like Smith. This would include Smith, Smythe, and possibly others. Assuming that you have a Soundex function available, you could write a filter method like the following:
procedure TForm1.ClientDataSet1FilterRecord(DataSet: TDataSet;
var Accept: Boolean);
begin
Accept := Soundex(DataSet.FieldByName('LastName').AsString) =
Soundex('Smith');
end;
If you set the Accept parameter to True, the record is included in the filter. If you set Accept to False, the record is hidden.
After you set up an OnFilterRecord event handler, you can simply set TClientDataSet.Filtered to True. You don't need to set the Filter property at all.
The following example demonstrates different filter and range techniques.
Listing 3.4 contains the source code for the main form.
Listing 3.4 RangeFilter—MainForm.pas
unit MainForm;
interface
uses
SysUtils, Classes, QGraphics, QControls, QForms, QDialogs, QStdCtrls,
DB, DBClient, QExtCtrls, QGrids, QDBGrids;
type
TfrmMain = class(TForm)
DataSource1: TDataSource;
pnlClient: TPanel;
pnlBottom: TPanel;
btnFilter: TButton;
btnRange: TButton;
DBGrid1: TDBGrid;
ClientDataSet1: TClientDataSet;
btnClearRange: TButton;
btnClearFilter: TButton;
procedure FormCreate(Sender: TObject);
procedure btnFilterClick(Sender: TObject);
procedure btnRangeClick(Sender: TObject);
procedure btnClearRangeClick(Sender: TObject);
procedure btnClearFilterClick(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
frmMain: TfrmMain;
implementation
uses FilterForm, RangeForm;
{$R *.xfm}
procedure TfrmMain.FormCreate(Sender: TObject);
begin
ClientDataSet1.LoadFromFile('C:/Employee.CDS');
ClientDataSet1.AddIndex('bySalary', 'Salary', []);
ClientDataSet1.IndexName := 'bySalary';
end;
procedure TfrmMain.btnFilterClick(Sender: TObject);
var
frmFilter: TfrmFilter;
begin
frmFilter := TfrmFilter.Create(nil);
try
if frmFilter.ShowModal = mrOk then begin
ClientDataSet1.Filter := frmFilter.Filter;
ClientDataSet1.Filtered := True;
end;
finally
frmFilter.Free;
end;
end;
procedure TfrmMain.btnClearFilterClick(Sender: TObject);
begin
ClientDataSet1.Filtered := False;
end;
procedure TfrmMain.btnRangeClick(Sender: TObject);
var
frmRange: TfrmRange;
begin
frmRange := TfrmRange.Create(nil);
try
if frmRange.ShowModal = mrOk then
ClientDataSet1.SetRange([frmRange.LowValue], [frmRange.HighValue]);
finally
frmRange.Free;
end;
end;
procedure TfrmMain.btnClearRangeClick(Sender: TObject);
begin
ClientDataSet1.CancelRange;
end;
end.
As you can see, the main form loads the employee dataset from a disk, creates an index on the Salary field, and makes the index active. It then enables the user to apply a range, a filter, or both to the dataset.
Listing 3.5 contains the source code for the filter form. The filter form is a simple form that enables the user to select the field on which to filter, and to enter a value on which to filter.
Listing 3.5 RangeFilter—FilterForm.pas
unit FilterForm;
interface
uses
SysUtils, Classes, QGraphics, QControls, QForms, QDialogs, QStdCtrls,
QExtCtrls;
type
TfrmFilter = class(TForm)
pnlClient: TPanel;
pnlBottom: TPanel;
Label1: TLabel;
cbField: TComboBox;
Label2: TLabel;
cbRelationship: TComboBox;
Label3: TLabel;
ecValue: TEdit;
btnOk: TButton;
btnCancel: TButton;
private
function GetFilter: string;
{ Private declarations }
public
{ Public declarations }
property Filter: string read GetFilter;
end;
implementation
{$R *.xfm}
{ TfrmFilter }
function TfrmFilter.GetFilter: string;
begin
Result := Format('%s %s ''%s''',
[cbField.Text, cbRelationship.Text, ecValue.Text]);
end;
end.
The only interesting code in this form is the GetFilter function, which simply bundles the values of the three input controls into a filter string and returns it to the main application.
Listing 3.6 contains the source code for the range form. The range form prompts the user for a lower and an upper salary limit.
Listing 3.6 RangeFilter—RangeForm.pas
unit RangeForm;
interface
uses
SysUtils, Classes, QGraphics, QControls, QForms, QDialogs, QExtCtrls,
QStdCtrls;
type
TfrmRange = class(TForm)
pnlClient: TPanel;
pnlBottom: TPanel;
Label1: TLabel;
Label2: TLabel;
ecLower: TEdit;
ecUpper: TEdit;
btnOk: TButton;
btnCancel: TButton;
procedure btnOkClick(Sender: TObject);
private
function GetHighValue: Double;
function GetLowValue: Double;
{ Private declarations }
public
{ Public declarations }
property LowValue: Double read GetLowValue;
property HighValue: Double read GetHighValue;
end;
implementation
{$R *.xfm}
{ TfrmRange }
function TfrmRange.GetHighValue: Double;
begin
Result := StrToFloat(ecUpper.Text);
end;
function TfrmRange.GetLowValue: Double;
begin
Result := StrToFloat(ecLower.Text);
end;
procedure TfrmRange.btnOkClick(Sender: TObject);
var
LowValue: Double;
HighValue: Double;
begin
try
LowValue := StrToFloat(ecLower.Text);
HighValue := StrToFloat(ecUpper.Text);
if LowValue > HighValue then begin
ModalResult := mrNone;
ShowMessage('The upper salary must be >= the lower salary');
end;
except
ModalResult := mrNone;
ShowMessage('Both values must be a valid number');
end;
end;
end.
Figure 3.8 shows the RangeFilter application in operation.
Figure 3.8 RangeFilter applies both ranges and filters to a dataset.
Searching
In addition to filtering out uninteresting records from a client dataset, TClientDataSet provides a number of methods for quickly locating a specific record. Some of these methods require an index to be active on the dataset, and others do not. The search methods are described in detail in the following sections.
Nonindexed Search Techniques
In this section, I'll discuss the search techniques that don't require an active index on the client dataset. Rather than using an index, these methods perform a sequential search through the dataset to find the first matching record.
Locate
Locate is perhaps the most general purpose of the TClientDataSet search methods. You can use Locate to search for a record based on any given field or combination of fields. Locate can also search for records based on a partial match, and can find a match without respect to case.
TClientDataSet.Locate is defined like this:
function Locate(const KeyFields: string; const KeyValues: Variant;
Options: TLocateOptions): Boolean; override;
The first parameter, KeyFields, designates the field (or fields) to search. When searching multiple fields, separate them by semicolons (for example, 'Name;Birthday').
The second parameter, KeyValues, represents the values to search for. The number of values must match the number of key fields exactly. If there is only one search field, you can simply pass the value to search for here. To search for multiple values, you must pass the values as a variant array. One way to do this is by calling VarArrayOf, like this:
VarArrayOf(['John Smith', '4/15/1965'])
The final parameter, Options, is a set that determines how the search is to be executed. Table 3.11 lists the available options.
Table 3.11 Locate Options
|
Value
|
Description
|
|
loPartialKey
|
KeyValues do not necessarily represent an exact match.Locate finds the first record whose field value starts with the value specified in KeyValues.
|
|
loCaseInsensitive
|
Locate ignores case when searching for string fields.
|
Both options pertain to string fields only. They are ignored if you specify them for a nonstring search.
Locate returns True if a matching record is found, and False if no match is found. In case of a match, the record is made current.
The following examples help illustrate the options:
ClientDataSet1.Locate('Name', 'John Smith', []);
This searches for a record where the name is 'John Smith'.
ClientDataSet1.Locate('Name', 'JOHN', [loPartialKey, loCaseInsensitive]);
This searches for a record where the name begins with 'JOHN'. This finds 'John Smith', 'Johnny Jones', and 'JOHN ADAMS', but not 'Bill Johnson'.
ClientDataSet1.Locate('Name;Birthday', VarArrayOf(['John', '4/15/1965']),
[loPartialKey]);
This searches for a record where the name begins with 'John' and the birthday is April 15, 1965. In this case, theloPartialKey option applies to the name only. Even though the birthday is passed as a string, the underlying field is a date field, so the loPartialKey option is ignored for that field only.
Lookup
Lookup is similar in concept to Locate, except that it doesn't change the current record pointer. Instead, Lookup returns the values of one or more fields in the record. Also, Lookup does not accept an Options parameter, so you can't perform a lookup that is based on a partial key or that is not case sensitive.
Lookup is defined like this:
function Lookup(const KeyFields: string; const KeyValues: Variant;
const ResultFields: string): Variant; override;
KeyFields and KeyValues specify the fields to search and the values to search for, just as with the Locate method.ResultFields specifies the fields for which you want to return data. For example, to return the birthday of the employee named John Doe, you could write the following code:
var
V: Variant;
begin
V := ClientDataSet1.Lookup('Name', 'John Doe', 'Birthday');
end;
The following code returns the name and birthday of the employee with ID number 100.
var
V: Variant;
begin
V := ClientDataSet1.Lookup('ID', 100, 'Name;Birthday');
end;
If the requested record is not found, V is set to NULL. If ResultFields contains a single field name, then on return fromLookup, V is a variant containing the value of the field listed in ResultFields. If ResultFields contains multiple single-field names, then on return from Lookup, V is a variant array containing the values of the fields listed in ResultFields.
NOTE
For a comprehensive discussion of variant arrays, see my book, Delphi COM Programming, published by Macmillan Technical Publishing.
The following code snippet shows how you can access the results that are returned from Lookup.
var
V: Variant;
begin
V := ClientDataSet1.Lookup('ID', 100, 'Name');
if not VarIsNull(V) then
ShowMessage('ID 100 refers to ' + V);
V := ClientDataSet1.Lookup('ID', 200, 'Name;Birthday');
if not VarIsNull(V) then
ShowMessage('ID 200 refers to ' + V[0] + ', born on ' + DateToStr(V[1]));
end;
Indexed Search Techniques
The search techniques mentioned earlier do not require an index to be active (in fact, they don't require the dataset to be indexed at all), but TDataSet also supports several indexed search operations. These include FindKey, FindNearest, and GotoKey, which are discussed in the following sections.
FindKey
FindKey searches for an exact match on the key fields of the current index. For example, if the dataset is currently indexed by ID, FindKey searches for an exact match on the ID field. If the dataset is indexed by last and first name,FindKey searches for an exact match on both the last and the first name.
FindKey takes a single parameter, which specifies the value(s) to search for. It returns a Boolean value that indicates whether a matching record was found. If no match was found, the current record pointer is unchanged. If a matching record is found, it is made current.
The parameter to FindKey is actually an array of values, so you need to put the values in brackets, as the following examples show:
if ClientDataSet.FindKey([25]) then
ShowMessage('Found ID 25');
...
if ClientDataSet.FindKey(['Doe', 'John']) then
ShowMessage('Found John Doe');
You need to ensure that the values you search for match the current index. For that reason, you might want to set the index before making the call to FindKey. The following code snippet illustrates this:
ClientDataSet1.IndexName := 'byID';
if ClientDataSet.FindKey([25]) then
ShowMessage('Found ID 25');
...
ClientDataSet1.IndexName := 'byName';
if ClientDataSet.FindKey(['Doe', 'John']) then
ShowMessage('Found John Doe');
FindNearest
FindNearest works similarly to FindKey, except that it finds the first record that is greater than or equal to the value(s) passed to it. This depends on the current value of the KeyExclusive property.
If KeyExclusive is False (the default), FindNearest finds the first record that is greater than or equal to the passed-in values. If KeyExclusive is True, FindNearest finds the first record that is greater than the passed-in values.
If FindNearest doesn't find a matching record, it moves the current record pointer to the end of the dataset.
GotoKey
GotoKey performs the same function as FindKey, except that you set the values of the search field(s) before callingGotoKey. The following code snippet shows how to do this:
ClientDataSet1.IndexName := 'byID';
ClientDataSet1.SetKey;
ClientDataSet1.FieldByName('ID').AsInteger := 25;
ClientDataSet1.GotoKey;
If the index is made up of multiple fields, you simply set each field after the call to SetKey, like this:
ClientDataSet1.IndexName := 'byName';
ClientDataSet1.SetKey;
ClientDataSet1.FieldByName('First').AsString := 'John';
ClientDataSet1.FieldByName('Last').AsString := 'Doe';
ClientDataSet1.GotoKey;
After calling GotoKey, you can use the EditKey method to edit the key values used for the search. For example, the following code snippet shows how to search for John Doe, and then later search for John Smith. Both records have the same first name, so only the last name portion of the key needs to be specified during the second search.
ClientDataSet1.IndexName := 'byName';
ClientDataSet1.SetKey;
ClientDataSet1.FieldByName('First').AsString := 'John';
ClientDataSet1.FieldByName('Last').AsString := 'Doe';
ClientDataSet1.GotoKey;
// Do something with the record
// EditKey preserves the values set during the last SetKey
ClientDataSet1.EditKey;
ClientDataSet1.FieldByName('Last').AsString := 'Smith';
ClientDataSet1.GotoKey;
GotoNearest
GotoNearest works similarly to GotoKey, except that it finds the first record that is greater than or equal to the value(s) passed to it. This depends on the current value of the KeyExclusive property.
If KeyExclusive is False (the default), GotoNearest finds the first record that is greater than or equal to the field values set after a call to either SetKey or EditKey. If KeyExclusive is True, GotoNearest finds the first record that is greater than the field values set after calling SetKey or EditKey.
If GotoNearest doesn't find a matching record, it moves the current record pointer to the end of the dataset.
The following example shows how to perform indexed and nonindexed searches on a dataset.
Listing 3.7 shows the source code for the Search application, a sample program that illustrates the various indexed and nonindexed searching techniques supported by TClientDataSet.
unit MainForm;
interface
uses
SysUtils, Classes, Variants, QGraphics, QControls, QForms, QDialogs,
QStdCtrls, DB, DBClient, QExtCtrls, QActnList, QGrids, QDBGrids;
type
TfrmMain = class(TForm)
DataSource1: TDataSource;
pnlClient: TPanel;
pnlBottom: TPanel;
btnSearch: TButton;
btnGotoBookmark: TButton;
btnGetBookmark: TButton;
btnLookup: TButton;
DBGrid1: TDBGrid;
ClientDataSet1: TClientDataSet;
btnSetRecNo: TButton;
procedure FormCreate(Sender: TObject);
procedure btnGetBookmarkClick(Sender: TObject);
procedure btnGotoBookmarkClick(Sender: TObject);
procedure btnSetRecNoClick(Sender: TObject);
procedure btnSearchClick(Sender: TObject);
procedure btnLookupClick(Sender: TObject);
private
{ Private declarations }
FBookmark: TBookmark;
public
{ Public declarations }
end;
var
frmMain: TfrmMain;
implementation
uses SearchForm;
{$R *.xfm}
procedure TfrmMain.FormCreate(Sender: TObject);
begin
ClientDataSet1.LoadFromFile('C:/Employee.cds');
ClientDataSet1.AddIndex('byName', 'Name', []);
ClientDataSet1.IndexName := 'byName';
end;
procedure TfrmMain.btnGetBookmarkClick(Sender: TObject);
begin
if Assigned(FBookmark) then
ClientDataSet1.FreeBookmark(FBookmark);
FBookmark := ClientDataSet1.GetBookmark;
end;
procedure TfrmMain.btnGotoBookmarkClick(Sender: TObject);
begin
if Assigned(FBookmark) then
ClientDataSet1.GotoBookmark(FBookmark)
else
ShowMessage('No bookmark assigned');
end;
procedure TfrmMain.btnSetRecNoClick(Sender: TObject);
var
Value: string;
begin
Value := '1';
if InputQuery('RecNo', 'Enter Record Number', Value) then
ClientDataSet1.RecNo := StrToInt(Value);
end;
procedure TfrmMain.btnSearchClick(Sender: TObject);
var
frmSearch: TfrmSearch;
begin
frmSearch := TfrmSearch.Create(nil);
try
if frmSearch.ShowModal = mrOk then begin
case TSearchMethod(frmSearch.grpMethod.ItemIndex) of
smLocate:
ClientDataSet1.Locate('Name', frmSearch.ecName.Text,
[loPartialKey, loCaseInsensitive]);
smFindKey:
ClientDataSet1.FindKey([frmSearch.ecName.Text]);
smFindNearest:
ClientDataSet1.FindNearest([frmSearch.ecName.Text]);
smGotoKey: begin
ClientDataSet1.SetKey;
ClientDataSet1.FieldByName('Name').AsString :=
frmSearch.ecName.Text;
ClientDataSet1.GotoKey;
end;
smGotoNearest: begin
ClientDataSet1.SetKey;
ClientDataSet1.FieldByName('Name').AsString :=
frmSearch.ecName.Text;
ClientDataSet1.GotoNearest;
end;
end;
end;
finally
frmSearch.Free;
end;
end;
procedure TfrmMain.btnLookupClick(Sender: TObject);
var
Value: string;
V: Variant;
begin
Value := '1';
if InputQuery('ID', 'Enter ID to Lookup', Value) then begin
V := ClientDataSet1.Lookup('ID', StrToInt(Value), 'Name;Salary');
if not VarIsNull(V) then
ShowMessage(Format('ID %s refers to %s, who makes %s',
[Value, V[0], FloatToStrF(V[1], ffCurrency, 10, 2)]));
end;
end;
end.
Listing 3.8 contains the source code for the search form. The only interesting bit of code in this listing is theTSearchMethod, defined near the top of the unit, which is used to determine what method to call for the search.
Listing 3.8 Search—SearchForm.pas
unit SearchForm;
interface
uses
SysUtils, Classes, QGraphics, QControls, QForms, QDialogs, QExtCtrls,
QStdCtrls;
type
TSearchMethod = (smLocate, smFindKey, smFindNearest, smGotoKey,
smGotoNearest);
TfrmSearch = class(TForm)
pnlClient: TPanel;
pnlBottom: TPanel;
Label1: TLabel;
ecName: TEdit;
grpMethod: TRadioGroup;
btnOk: TButton;
btnCancel: TButton;
private
{ Private declarations }
public
{ Public declarations }
end;
implementation
{$R *.xfm}
end.
Figure 3.9 shows the Search application at runtime.
Figure 3.9 Search demonstrates indexed and nonindexed searches.