If you want to write a single-user database application in Delphi, the simplest approach is to use the ClientDataSet component and map it to a local file. This local file mapping is different from the traditional data mapping to a local file. The traditional approach is to read from the file a record at a time, and possibly have a second file that stores indexes. The ClientDataSet maps an entire table (and possibly a master/detail structure) to the file in its entirety: When a program starts, the entire file is loaded in memory, and then everything is saved at once.
This explains why you cannot use this approach in a multiuser or multiapplication situation. If two programs or two instances of the same program load the same ClientDataSet file in memory and modify the data, the last table saved will overwrite changes made by other programs.
This support for persisting the content of a ClientDataSet was created a few years ago as a way to implement the so-called briefcase model. A user could (and still can) download data from its database server to the client, save some of the data, work disconnected (while traveling with a laptop computer, for example), and finally reconnect to commit the changes.
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.
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
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;
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.dll library has no version number in its name. So, if a computer has an older version, your program will apparently run on it but may not behave properly.
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.
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.
While you have a ClientDataSet in memory, you can extract its XML representation by using the XMLData property without streaming out the data. The next example puts this technique into practice.
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."
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.
The component also supports indexes based on a calculated field, specifically an internally calculated field, available only for this dataset (as I'll describe later in this chapter). Unlike ordinary calculated fields, which are computed every time the record is used, values of internally calculated fields are calculated once and kept in memory. For this reason, indexes consider them plain fields.
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.
Defining a separate index is the only way to have a descending index, rather than an ascending index.
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.
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.
Using Locate makes sense when you're working with a local table, but it doesn't port well to client/server applications. On a SQL server, similar client-side techniques imply moving all the data to the client application first (which is generally a bad idea) and then searching for a specific record. You should locate the data with restricted SQL statements. You can still call Locate after you retrieve a limited dataset. For example, you can search for a customer by name after you select all the customers of a given town or area, obtaining a result set of a limited size. There's more about this topic in Chapter 14, which is devoted to client/server development.
As a user modifies the data in a ClientDataSet component, the updates are stored in a memory area called Delta. The reason for keeping track of user changes instead of holding the resulting table is due to the way the updates are handled in a client/server architecture. In this case, the program doesn't have to send the entire table back to the server, but only a list of the user's changes (by means of specific SQL statements, as you'll see in Chapter 14).
Because the ClientDataSet component keeps track of changes, you can reject those changes, removing entries from the delta. The component has an UndoLastChange method to accomplish this. The method's FollowChange parameter allows you to follow the undo operation—the client dataset will move to the record that has been restored by the undo operation. Here is the code you can use to connect to an Undo button:
procedure TForm1.ButtonUndoClick(Sender: TObject); begin cds.UndoLastChange (True); end;
An extension of the undo support is the possibility of saving a sort of bookmark of the change log position (the current status) and to restore it later by undoing all successive changes. You can use the SavePoint property either to save the number of changes in the log or to reset the log to a past situation. However, you can only remove records from the change log, not reinsert changes. In other words, the SavePoint property refers to a position in a log, so it can only go back to a position where there were fewer records! This log position is a number of changes, so if you save the current position, undo some changes, and then do more edits, you won't be able to get back to the position you bookmarked.
Delphi 7 has a new standard action mapped to the ClientDataSet's Undo operation. Other new actions include Revert and Apply, which you'll need when the component is connected to a dataset accessing a database.
Keeping track of changes makes sense if you need to send the updated data back to a server database. In local applications with data stored to a MyBase file, keeping this log around can become useless and consumes memory. For this reason, you can disable logging with the LogChanges property. This will also stop the undo operations, though.
You can also call the MergeChangesLog method to remove all current editing from the change log, and confirm the edits performed so far. Doing so makes sense if you want to keep the undo log around within a single session and then save the final dataset without the keeping the change log.
The MyBase2 example disables the change log as discussed here: You can remove that code and re-enable it to see the difference in the size of the CDS file and in the XML text after editing the data.