ast month, I had a little break, but this month we’ll continue with the database development series. Previously, I’ve covered the BDE (Borland Database Engine) as well as ADO and the dbGo for ADO components in C++Builder using SQL Server/MSDE as our database. This time, we take a little step back and examine the TClientDataSet component, which is actually quite a powerful component that will be used for the next few parts in this series.

This month, we’ll cover the use of TClientDataSet in a stand-alone scenario using a local file format (binary or XML). Next month, we’ll see how TClientDataSet is also used in a dbExpress scenario, connecting to SQL Server/MSDE again (or any of the other dbExpress-compatible databases for that matter); and, after that we’ll move on to DataSnap, building multi-tier database applications with the TClientDataSet in the thin (or smart) clients.

 

Standalone TClientDataSet

Before we start, let me first explain the consequences of using a stand-alone TClientDataSet component. There are some compelling advantages: it’s free, it’s fast, and no DBMS is required. However, it’s also essentially single user only, since the data are stored in local files and loaded in memory. If this is no problem for you, then come along as we turn the DVD project into a stand-alone application. Otherwise, just wait until next month when I’ll introduce dbExpress which will include database drivers again (and multi-user capabilities—at a cost).

The TClientDataSet data format is also called the MyBase format, and can be binary or XML. For local use of TClientDataSet, the first step consists of migrating the data from the original database to the TClientDataSet (MyBase) format. The original database can be anything, from the BDE to our SQL Server/MSDE database (which we used with ADO and dbGo for ADO). And, this time we don’t even have to write a single line of code to migrate the data, as I’ll show you.

 

Migrating the data

To begin, open the application from two months ago, which still uses the dbGo for ADO components on the data module. Next, save the project as LocalCDS.bpr. The original project still contains two data modules: DataModuleBDE and DataModuleADO. Since we’ve added some more features to the application, we should use DataModuleADO as starting point now.

As shown in Figure A, DataModuleADO contains six TADODataSets: three acting as tables, two as queries, and one as a stored procedure. Place a TClientDataSet component next to them, which will be used help migrate the data to the MyBase format.

Figure A DataModuleADO at design-time.

Right-click on the TClientDataSet component, and then select the “Assign Local Data” option. This will give you the dialog shown in Figure B, which you can use to feed the TClientDataSet with the local data found in the other datasets. We need to ignore the stored procedure and two queries at this time, so start with the TTable called tbCategory; select this one and click on OK. As a result, the contents of the Category table will now be inside the TClientDataSet.

Figure B Dialog for assigning local data to a TClientDataSet.

Right-click on the TClientDataSet component again, but this time we need to save the data. We can save it as a binary MyBase file, or as an XML file in either normal or UTF-8 format. The latter is ideal when you’re working with data containing special characters like umlauts. The binary format is the smallest of all, but can be read only by the TClientDataSet component (of C++Builder, Kylix, and Delphi); and, if the contents are somehow corrupted, then you’ve lost your data. For this reason, I use the binary format only when sending data from one place to another (as will be done in the DataSnap scenario), but I prefer to store the data in the XML format. It’s bigger than the binary format, but at least anyone can read it.

To make a long story short: I’ve saved the contents of the TClientDataSet in an XML UTF-8 file called Category.xml. After this is done, we can assign local data again, this time to the tbCdDvd table, saving the result in CdDvd.xml, and finally do the same with the tbFriend table.

 

No queries!?

A TClientDataSet is an in-memory dataset, which can result in great speed. However, it’s equivalent to a TTable component and has no direct correspondence to a TQuery component. So, in order to “translate” the two queries, we need to find another solution.

Looking at the SQL commands, both queries serve to select fields from the Borrow table, passing either a FriendID or a CdDvdID. So instead of migrating the two queries to a TClientDataSet we should migrate the Borrow table to the MyBase format. In order to do this, we need to temporarily modify one of the query components to omit the WHERE part so that we perform only a SELECT * FROM Borrow. After that, we can again assign local data to the TClientDataSet component, and then save the result in the MyBase file Borrow.xml. In addition, because the stored procedure is also using the Borrow table, we will try to reconstruct that functionality in a minute as well.

 

New data module

Remove the TClientDataSet from DataModuleADO, as we no longer need it. We should now have four XML files with the contents of the SQL Server database. It’s time to reconstruct the data access components on an all-TClientDataSet data module.

Choose “File | New > Data Module” to create a new data module. Set its name to DataModuleCDS, and save it in DataModCDS.cpp. As shown in Figure C, we should now add four new TClientDataSet components to the new data module; call them cdsCategory, cdsCdDvd, cdsFriend, and cdsBorrow.

Figure C DataModuleCDS at design-time.

Instead of using the “Assign Local Data” approach, we can now point these four components to the actual MyBase files by setting their FileName properties to the four XML files. (I’m sure you can figure out which file belongs to which component.)

Note that once you’ve assigned a value to the FileName property using the “Open MyBase table” property editor (which you get when you click on the ellipsis for the FileName property), the file-name will be stored as fully qualified file-name. This is not something that I fancy, to be honest, since it means the application is now using hard-coded paths to the database files. I prefer to use relative paths so I can deploy the application on another machine as well. So, I always recommend removing the path part from the FileName property, leaving only the local XML file-name itself (in the current directory).

 

Adding lookup fields

Before we can continue, we first need to add persistent fields and additional lookup fields to the TClientDataSet components. This was first done in the March 2005 issue using the BDE, and it is no different when using stand-alone TClientDataSets. We should add all fields, as well as a field called Category, to the CdDvd dataset (see page 17 of the March issue for details).

Figure D shows the “New Field” dialog. If you compare this figure with Figure H on page 17 of the March issue, you’ll notice two additional field-types available for a TClientDataSet (compared to a BDE TTable), namely the “InternalCalc” and “Aggregate” field-types. These new types are available only for the in-memory TClientDataSet (but will be covered some other time, I’m afraid).

Figure D Adding the Category field.

 

Adding a calculated field

Apart from the Category lookup field, we should also add a Boolean-type calculated field, called Available, to the cdsCdDvd TClientDataSet (see Figure E).

Figure E Adding the Available field.

The original implementation of this calculated field used the query

 

SELECT * FROM Borrow WHERE CdDvdId =

 

followed by the actual CdDvdID, to determine if this DVD was still available. Since a TClientDataSet is only an in-memory table with no SQL capabilities, this solution is no longer possible.

Fortunately, we can still find out if a CD or DVD is borrowed by using the Filter property of a TClientDataSet component. In this Filter property, we can add an expression which corresponds to the WHERE clause of a regular SQL SELECT command. This means that the implementation of the OnCalcFields event for cdsCdDvd can be implemented as follows:

 

void __fastcall TDataModuleCDS::

  cdsCdDvdCalcFields(TDataSet *DataSet)

{ 

  cdsBorrow->Filtered =

    false; // just in case

  cdsBorrow->Filter = "CdDvdID = " +

    DataSet->FieldByName("CdDvdID")->

      AsString;

  cdsBorrow->Filtered = true;

  if (cdsBorrow->RecordCount == 0)

    DataSet->FieldByName("Available")->

      AsBoolean = true;

  else

    DataSet->FieldByName("Available")->

      AsBoolean = false;

  cdsBorrow->Filtered = false;

}

 

Note that this code expects cdsBorrow to be open in order to use the filter, something which is not automatically the case. We could set the Active properties of the TClientDataSets to true, which will open them by default, but not necessarily in the right order. The cdsBorrow dataset must be active before we activate cdsCdDvd. The only way to enforce this is via code, for example in the constructor of DataModuleCDS, as follows:

 

__fastcall TDataModuleCDS::

  TDataModuleCDS(TComponent* Owner)

    : TDataModule(Owner)

{

  cdsBorrow->Active = true;

  cdsCategory->Active = true;

  cdsCdDvd->Active = true;

  cdsFriend->Active = true;

}

We should also close the TClientDataSets in the destructor (which I will demonstrate, shortly).

 

Updates and undo

A TClientDataSet is an in-memory dataset, which means that as long as the application using the TClientDataSet is up and running, all changes and modifications are made only in memory. If you accidentally pull the plug on your computer, then these changes will be lost. Fortunately, if you close a TClientDataSet, then it will automatically save its contents, but only if the FileName property has been assigned (otherwise it wouldn’t know where to save its contents to).

The updates and changes to the client dataset stored back in the MyBase file are not just simple updates, however. In fact, if you examine the contents of the XML files after you’ve made some changes, you’ll notice that the XML files actually contain the original version, as well as all changes (inserts, updates, and deletes), step by step, so you can undo the changes as well, if you wish. In fact, the contents of the TClientDataSet are saved back to disk in the first place is only done if the ChangeCount property contains a value greater than zero.

The fact that a MyBase file contains both the data and the delta (the changes) has advantages as well as disadvantages. The disadvantage is that the MyBase file will grow (with all subsequent updates), and it will take increasingly longer to load. The advantage is that it’s now very easy to add an “undo” feature, which can be implemented using three different TClientDataSet methods: UndoLastChange(), RevertRecord(), and CancelUpdates(). Calling UndoLastChange() will—as its name indicates—undo the last change you’ve done. It has one argument, called FollowChange, which can be set to true in order to position the cursor in the dataset to the place where the change was undone (which is quite handy when undoing changes in a TDBGrid, for example). The RevertRecord() method on the other hand will revert the current record to its original value—this is the kind of undo that most customers will prefer. Finally, CancelUpdates() will cancel all updates that are currently stored in the delta.

Fortunately, there is a way to “merge” the delta with the data, so all changes are “accepted” and made final. This will shrink the MyBase file and also remove the changes from the undo list. This can be done by calling the MergeChangeLog() method of the TClientDataSet, as follows:

 

__fastcall TDataModuleCDS::

  ~TDataModuleCDS()

{

  if (cdsFriend->ChangeCount)

  {

    cdsFriend->MergeChangeLog();

    cdsFriend->SaveToFile();

    cdsFriend->Active = false;

  }

  if (cdsCdDvd->ChangeCount)

  {

    cdsCdDvd->MergeChangeLog();

    cdsCdDvd->SaveToFile();

    cdsCdDvd->Active = false;

  }

  if (cdsCategory->ChangeCount)

  {

    cdsCategory->MergeChangeLog();

    cdsCategory->SaveToFile();

    cdsCategory->Active = false;

  }

  if (cdsBorrow->ChangeCount)

  {

    cdsBorrow->MergeChangeLog();

    cdsBorrow->SaveToFile();

    cdsBorrow->Active = false;

  }

}

 

Note that after calling the MergeChangeLog() method, we explicitly have to call SaveToFile(), since ChangeCount will return to 0 and thus closing the TClientDataSet will no longer result in the file being saved automatically.

For my own applications, I prefer to be able to undo my changes as long as the application is up and running, so I use the delta at all times. However, as soon as I close the application, I want to store the smallest version of the MyBase file to disk, merging the delta and data. As a consequence, I can undo changes, until I close the application, but when I reopen it, the changes will no longer be undoable.

 

Connecting the GUI

When the CDS data module is fully configured, we can switch over to MainForm.cpp to connect the GUI to the data module (see Figure F).

Figure F The application's main form at design-time.

There are three TDataSource components currently connected to the dbGo for ADO components, namely dsCategory, dsFriend, and dsCdDvd. These should point to the cdsCategory, cdsFriend, and cdsCDDvd from the CDS data module (note that you have to press Alt+F11 to use unit DataModCDS).

We can now add an “Undo” button to undo changes made to the CdDvd TClientDataSet; for example, the “Undo” button can be implemented as mentioned before:

 

void __fastcall TForm1::

  btnUndoClick(TObject *Sender)

{

  DataModuleCDS->cdsCdDvd->RevertRecord();

  // or use either of the following:

  // DataModuleCDS->cdsCdDvd->

  //   UndoLastChange(true);

  // DataModuleCDS->cdsCdDvd->

  //   CancelUpdates();

}

 

The RevertRecord() method is the most convenient in my opinion.

 

Borrow DVDs

It’s time to add a button to borrow a DVD. This can be done by adding a record to the Borrow table, using the CdDvdID field from the current record in the cdsCdDvd dataset, and the FriendID from the current record in the cdsFriend dataset. The OnClick event handler of the Borrow button is implemented as follows:

 

void __fastcall TForm1::

  btnBorrowClick(TObject *Sender)

{

  DataModuleCDS->cdsBorrow->Append();

  DataModuleCDS->cdsBorrowCdDvdID->

    Value = DataModuleCDS->

      cdsCdDvdCdDvdID->Value;

  DataModuleCDS->cdsBorrowFriendID->

    Value = DataModuleCDS->

      cdsFriendFriendID->Value;

  DataModuleCDS->cdsBorrow->Post();

}

 

Obviously, once a DVD has been borrowed, its availability status should be false, which is easy to test and verify. Unfortunately, the Available field in the TDBGrid is not immediately updated! The solution for this little issue is left as exercise for the reader, and will be discussed next time (when we also move on to dbExpress).

 

Summary

In this article, I’ve migrated the SQL Server data to the TCLientDataSet MyBase local table format. We’ve seen how we can mimic the behavior of queries by using the TClientDataSet’s Filter property, and we’ve experimented with the data and delta (changes) of TClientDataSets. Finally, we’ve added the ability to actually borrow DVDs in our example application.

 

Next time