Client Dataset Basics

文章出处:  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 with TCustomClientDataSet  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 if operations 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 from TDataSet .

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  LastName in 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  of Bonus  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  AutoCalcFields is  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  FirstName and  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  Bonus field.

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  TFieldDef object, 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, call TClientDataSet.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 named cdsEmployeeLastName .

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  Insert places 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 the FileName  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 of GetBookmark / 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 the IndexName  property. If you want to discontinue the use of an index and revert to the default record order, you can set the IndexName  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  IndexName property, 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 using AddIndex , 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  clears IndexName .

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, a TStrings  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- NULL value	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  Filtered from  True  to  False ).

You can apply more advanced filter criteria by handling the dataset's  OnFilterRecord  event (instead of setting the Filter  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, the loPartialKey  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 from Lookup ,  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 calling GotoKey . 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 the TSearchMethod , 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.