Connecting to an Existing Local Table

To map a ClientDataSet to a local file, you set its FileName property. To build a minimal program (called MyBase1 in the example), all you need is a ClientDataSet component hooked to a CDS file (there are a few in the Data folder available under \Program Files\Common Files\Borland Shared), a DataSource (more on this later), and a DBGrid control. Hook the ClientDataSet to the DataSource via the DataSource's DataSet property and the DataSource to the DBGrid via the grid's DataSource property, as in Listing 13.1. At this point turn on the Active property of the ClientDataSet and you'll have a program showing database data even at design time; see Figure 13.1.

Listing 13.1: The DFM File of the MyBase1 Sample Program

object Form1: TForm1
  ActiveControl = DBGrid1
  Caption = 'MyBase1'
  OnCreate = FormCreate
  object DBGrid1: TDBGrid
    DataSource = DataSource1
  end
  object DataSource1: TDataSource
    DataSet = cds
  end
  object cds: TClientDataSet
    FileName = 'C:\Program Files\Common Files\Borland
       Shared\Data\customer.cds'
  end
end

Figure 13.1: A sample local table active at design time in the Delphi IDE

As you make changes and close the application, the data will be automatically saved to the file. (You might want to disable the change log, as discussed later, to reduce the size of this data.) The dataset also has a SaveToFile method and a LoadFromFile method you can use in your code.

I also made another change: I disabled the ClientDataSet at design time to avoid including all of its data in the program's DFM file and in the compiled executable file; I want to keep the data in a separate file. To do this, close the dataset at design time, after testing, and add a line to the form's OnCreate event to open it:

procedure TForm1.FormCreate(Sender: TObject);
begin
  cds.Open;
end;

From the Midas DLL to the MidasLib Unit

To run any application using the ClientDataSet component, you need to also deploy the midas.dll dynamic library referenced by the DSIntf.pas unit. The ClientDataSet component's core code is not directly part of the VCL and is not available in source code format. This is unfortunate, because many developers are accustomed to debugging the VCL source code and using it as the ultimate reference.

The Midas library is a C-language library, but since Delphi 6 it can be bound directly into an executable by including the specific MidasLib unit (a special DCU produced by a C compiler). In this case you won't need to distribute the library in the DLL format.

XML and CDS Formats

The ClientDataSet component supports two different streaming formats: the native format and an XML-based format. The Borland Shared\Demo folder mentioned earlier holds versions of a number of tables in each of the two formats. By default, MyBase saves the datasets in XML format. The SaveToFile method has a parameter allowing you to specify the format, and the LoadFromFile method works automatically for both formats.

Using the XML format has the advantage of making the persistent data also accessible with an editor and with other programs not based on the ClientDataSet component. However, this approach implies converting the data back and forth, because the CDS format is close to the internal memory representation invariably used by the component, regardless of the streaming format. Also, the XML format generates large files, because they are text based. On average, a MyBase XML file is twice the size of the corresponding CDS file.

Defining a New Local Table

Besides letting you hook to an existing database table stored in a local file, the ClientDataSet component allows you to create new tables easily. All you have to do is use its FieldDefs property to define the structure of the table. After doing this, you can physically create the file for the table with the Create DataSet command on the ClientDataSet component's shortcut menu in the Delphi IDE or by calling its CreateDataSet method at run time.

This is an extract from the MyBase2 example's DFM file, which defines a new local database table:

object ClientDataSet1: TClientDataSet
  FileName = 'mybase2.cds'
  FieldDefs = <
    item
      Name = 'one'
      DataType = ftString
      Size = 20
    end
    item
      Name = 'two'
      DataType = ftSmallint
    end>
  StoreDefs = True
end

Notice the StoreDefs property, which is automatically set to True when you edit the collection of field definitions. By default, a dataset in Delphi loads its metadata before opening. Only if a local definition is stored in the DFM file is this local metadata used (saving field definitions in the DFM file is also helpful to cache this metadata in a client/server architecture).

To account for the optional dataset creation, the disabling of the log (described later), and the display of the XML version of the initial data in a Memo control, the program's form class has the following OnCreate event handler:

procedure TForm1.FormCreate(Sender: TObject);
begin
  if not FileExists (cds.FileName) then
    cds.CreateDataSet;
  cds.Open;
  cds.MergeChangeLog;
  cds.LogChanges := False;
  Memo1.Lines.Text := StringReplace (
    Cds.XMLData, '>', '>' + sLineBreak, [rfReplaceAll]);
end;

The last statement includes a call to StringReplace to provide a poor man's XML formatting: The code adds a new line at the end of each XML tag by adding a new line after the close angle bracket. You can see the table's XML display with a few records in Figure 13.2. You'll learn a lot more about XML in Delphi in Chapter 22, "Using XML Technologies."

Figure 13.2: The XML display of a CDS file in the MyBase2 example. The table structure is defined by the program, which creates a file for the dataset on its first execution.

Indexing

Once you have a ClientDataSet in memory, you can perform many operations on it. The simplest are indexing, filtering, and searching for records; more complex operations include grouping, defining aggregate values, and managing the change log. Here I'll cover only the simplest techniques; more complex material appears at the end of the chapter.

Filtering a ClientDataSet is a matter of setting the IndexFieldNames property. This is often accomplished when the user clicks the field title in a DBGrid component (firing the OnTitleClick event), as in the MyBase2 example:

procedure TForm1.DBGrid1TitleClick(Column: TColumn);
begin
  cds.IndexFieldNames := Column.Field.FieldName;
end;

Unlike other local databases, a ClientDataSet can have this type of dynamic index without any database configuration because indexes are computed in memory.

In addition to assigning a new value to the IndexFieldNames property, you can define an index using the IndexDefs property. Doing so allows you to define several indexes and keep them in memory, switching even faster from one to the other.

Filtering

As with any other dataset, you can use the Filter property to specify the inclusion in the dataset of portions of the data the component is bound to. The filtering operation takes place in memory after loading all the records, so this is a way to present less data to the user, not to limit the memory footprint of a large local dataset.

When you're retrieving large amounts of data from a server (in a client/server architecture) you should try to use a proper query so you don't retrieve a large dataset from a SQL server. Filtering up front in the server should generally be your first choice. With local data, you might consider splitting a large number of records into a set of different files, so you can load only those you need and not all of them.

Local filtering in the ClientDataSet can be useful, particularly because the filter expressions you can use with this component are much more extensive than those you can use with other datasets. In particular, you can use the following:

• The standard comparison and logical operators: for example, Population > 1000 and
  Area < 1000

• Arithmetic operators: for example, Population / Area < 10

• String functions: for example, Substring(Last_Name, 1, 2) = 'Ca'

• Date and time functions: for example, Year (Invoice_Date) = 2002

• Others, including a Like function, wildcards, and an In operator

These filtering capabilities are fully documented in the VCL Help file. You should look for the page "Limiting what records appear" linked from the description of the Filter property of the TClientDataSet class, or reach it from the Help Contents page following this path down
the tree: Developing Database Applications, Using client datasets, Limiting what records appear.

Locating Records

Filtering allows you to limit the records displayed to the program's user, but many times you want to display all the records and only move to a specific one. The Locate method does this. If you've never used Locate, at first sight the Help file won't be terribly clear. The idea is that you must provide a list of fields you want to search and a list of values, one for each field. If you want to match only one field, the value is passed directly, as in this case (where the search string is in the EditName component):

procedure TForm1.btnLocateClick(Sender: TObject);
begin
  if not cds.Locate ('LastName', EditName.Text, []) then
    MessageDlg ('"' + EditName.Text + '" not found', mtError, [mbOk], 0);
end;

If you search for multiple fields, you have to pass a variant array with the list of values you want to match. The variant array can be created from a constant array with the VarArrayOf function or from scratch using the VarArrayCreate call. This is a code snippet:

cds.Locate ('LastName;FirstName', VarArrayOf (['Cook', 'Kevin']), [])

Finally, you can use the same method to look for a record even if you know only the initial portion of the field you are looking for. All you have to do is to add the loPartialKey flag to the Options parameter (the third) of the Locate call.

procedure TForm1.ButtonUndoClick(Sender: TObject);
begin
  cds.UndoLastChange (True);
end;