Nesting DataSets in ClientDataSets

Abstract: Like the name suggests, a nested dataset is a dataset within a dataset. By nesting one dataset inside another, you can reduce your overall storage needs, increase the efficiency of network communications, and simplify data operations.

To put it simply, nested datasets are one-to-many relationships embodied in a single ClientDataSet memory store. When the ClientDataSet is associated with a local file, saved as either a CDS binary file or XML, this related data is stored in a single CDS or XML file. When associated with a ClientDataSet that obtains its data through a DataSetProvider, the data packet is assembled from data retrieved through two or more related datasets.

Nested datasets provide you with a number of important advantages. For one, they typically reduce the amount of memory required to hold your data, whether it is the in-memory data store itself, or the data file saved to disk through calls to the ClientDataSet's SaveToFile method.

Second, when used with DataSnap, Borland's multitier development framework, nested datasets reduce network traffic. Specifically, nested datasets permit data from two or more related datasets to be packaged in a single data packet, which can then be transmitted between the DataSnap server and client more efficiently. For the same reason, nested datasets reduce the overall size of data stored by ClientDataSets in local files.

While these are important characteristics, a third characteristic of nested datasets is the one that is commonly considered their most valuable. Nested datasets permit the acquisition of data from, and resolution of changes to, two or more underlying tables using a single ClientDataSet.

While developers who use nested datasets value this capability, it is more difficult to appreciate if you have never worked with them before. Consider this: using nested datasets you can access data in two or more tables by calling the Open method of a single ClientDataSet. Furthermore, when you are through making changes, all updates are saved or applied with just one call to SaveToFile (for local files) or ApplyUpdates (for data obtained from a database server). In addition, if you are saving changes by calling ApplyUpdates, the changes to the two or more involved tables can be applied in an all-or-none fashion in a single transaction.

But there is more. When nested datasets are involved, data being applied as a result of a call to ApplyUpdates is resolved to the underlying datasets in the appropriate order, respecting the needs of master-detail relationships. For example, a newly created master table record is inserted before any related detail table records are inserted. Deleted records, by comparison, are removed from detail tables prior to the deletion of any master table records.

A single ClientDataSet can have up to 15 nested datasets. Each of these nested datasets can, in turn, contain up to 15 nested datasets, which in turn can have nested datasets as well. In other words, nested datasets permit you to represent a complex hierarchical relationship using one ClientDataSet. In practice, however, nested datasets greater than two levels deep are uncommon.

Creating Nested DataSets

There are two distinct ways to create nested datasets, depending on how the structure of the ClientDataSet is obtained. If you are creating your ClientDataSet's structure at runtime by invoking CreateDataSet, nested datasets are defined using TFields of the type TDataSetField. These TDataSetField instances can be instantiated either at design-time or at runtime, depending on the needs of your application.

The second way to create nested datasets is to load a data into a ClientDataSet from a DataSetProvider. If the DataSetProvider is pointing to the master dataset of a master-detail relationship, and that relationship is defined using properties, the data packet returned to the ClientDataSet contains one nested dataset for each detail table linked to the master table. Each of these two approaches are covered in the following sections.

Defining TDataSetFields

When a ClientDataSet's structure needs to be created at runtime by calling CreateDataSet, you define its structure using TFields, where each of the nested datasets are represented by TDataSetField instances. If you are defining your structure at design time, you add the TFields that define the table's structure using the Fields Editor. If you need to define your structure at runtime, you call the constructor for each TField you want added to your table, setting the necessary properties to associate the newly created field with the ClientDataSet. In this second case, each nested dataset field is created by a call to the TDataSetField constructor.

Defining a ClientDataSet's structure using TFields was discussed at length in a previous article in this series titled "Defining a ClientDataSet's Structure Using TFields." While that article, which you can view by clicking here, mentioned the general steps used to define nested datasets using TFields, this section goes further, providing you with a step-by-step demonstration of the technique.

First, let's review the steps required to define the structure of a ClientDataSet to include nested datasets.

Using the Fields Editor, create one field of data type Data for each regular field in the dataset (such as Customer Name, Title, Address1, Address2, and so forth).
For each nested dataset, add a new field, using the same technique that you use for the other data fields, but set its Data Type to DataSet.
For each DataSet field that you add to your first ClientDataSet, add an additional ClientDataSet. Associate each of these secondary ClientDataSets with one of the primary ClientDataSet's DataSet fields using the secondary ClientDataSet's DataSetField property.
Define the fields of each nested dataset by adding individual TFields to each secondary ClientDataSets you added in step 3. Just as you did with the initial ClientDataSet, you add these fields using the Fields Editor.

The following steps walk you through creating a project that includes a ClientDataSet whose structure includes nested datasets.

Begin by creating a new project.
On the main form, add two Panels from the Standard page of the Component Palette. In each panel place a DBNavigator and a DBGrid. These components are located on the Data Controls page. Align both DBNavigators to alTop, and both DBGrids to alClient. Set the Align property of the first Panel to alTop, and the second to alClient. (If you want to be really fancy, after you align the first panel to alTop, but before aligning the second panel, place a TSplitter from the Additional page of the Component Palette on your form, and align it to top as well. The splitter permits the user to customize the percentage of the form occupied by each panel at runtime.) Your form should look something like the following.
Add to this form two DataSources and two ClientDataSets from the Data Access page. Set the DataSource property of the DBNavigator and DBGrid in the top panel to DataSource1, and the DataSource property of the DBNavigator and DBGrid appearing in the bottom panel to DataSource2. In addition, set the DataSet property of DataSource2 to ClientDataSet1, and the DataSet property of DataSource2 to ClientDataSet2.

Right-click ClientDataSet1 and select Fields Editor. Add five fields to the Field Editor, one at a time, by either pressing Ctrl-N or right-clicking the Fields Editor and selecting Add field. Use the following table to set the Name and Type fields on the New Field dialog that each of these TFields require. Make sure that the Field Type radio group is set to Data. Accept the default values for all of the remaining fields

Name	Type
Invoice No	Integer
Invoice Date	Date
Customer No	Integer
Employee No	Integer
Details	DataSet

Close the Fields Editor for ClientDataSet1.
Select ClientDataSet2 and set its DataSetField property to ClientDataSet1Details.
Right-click ClientDataSet2 and select Fields Editor. Add three fields to the Field Editor, one at a time, by either pressing Ctrl-N or right-clicking the Fields Editor and selecting Add field. Use the following table to set the Name and Type field properties on the New Field dialog that each of these TFields require. Make sure that the Field Type radio group is set to Data. Accept the default values for all of the remaining fields. Close the Fields Editor for ClientDataSet2.

Name Type

Part No Integer

Quantity Integer

Price Currency
With ClientDataSet2 still selected, set its DataSetField property to ClientDataSet1Details.
Add the following event handler to the OnCreate property of the main form.

procedure TForm1.FormCreate(Sender: TObject);
begin
ClientDataSet1.FileName := ExtractFilePath(Application.ExeName) + 'data.xml';
if FileExists(ClientDataSet1.FileName) then
  ClientDataSet1.Open
else
  ClientDataSet1.CreateDataSet;
ClientDataSet1.LogChanges := False;
end;

Run the form. If you have not yet added any data, this form should look something like the following.
Now, add some data to both tables. Once some data is added, it might look something like the following.
Notice that if you click the dataset field named Details associated with ClientDataSet1 twice, you will get a small grid that you can use to enter, edit, and view the nested dataset, as shown in the following figure.

If you inspected the OnCreate event handler for the main form of this project, you will have noticed that any data that you enter is stored in a file named data.xml. The following image shows how the data in this XML file looks like, once it has been formatted by the FormatXMLData function, which is found in the XMLDoc unit (this unit only ships with Enterprise and Architect versions of Delphi 6 and later).

As you can see, the <FIELDS> element of this XML file contains one empty <FIELD> element for each field in the primary dataset. In addition, the <FIELD> with the attrname attribute value of Details itself contains a <FIELDS> element, which in turn contains the empty <FIELD> elements that describe this nested dataset's structure.

Likewise, the <ROWDATA> element, which contains the actual data, contains one empty <ROW> element for each field in the primary dataset. Here we find the <Details> element, which holds the data for the Detail nested dataset.

The source code for this project can be downloaded from Code Central by clicking here.

Created TDataSetFields at Runtime

One of the facts that you learn pretty early in your Delphi development is that if you can perform a task at design time you probably can perform that same task at runtime. This is certainly true with respect to nested datasets. In short, you create a nested dataset by performing the following tasks in code.

Call the constructor of the TDataSetField class, assigning the necessary values to the properties of the resulting object. As is the case with all TField that you create dynamically, one of the more important properties is the DataSet property, a property that identifies which TDataSet instance this field is to be associated with.
Assign the resulting TDataSetField to the DataSetField of the ClientDataSet that you will use to display and edit the data stored in the nested dataset.

In the article that I published previously concerning defining the structure of a ClientDataSet using TFields, I described a complicated project named VideoLibrary. This project includes two examples of a nested dataset's runtime construction. You can download this project from Code Central by clicking on this link.

All of the essential code for the process of creating a nested dataset and associating it with a ClientDataSet can be found in the OnCreate event handler of the data module for this project. The following segment, taken from this event handler, demonstrates the construction of a TDataSetField instance, including the setting of its properties.

  //Note: For TDataSetFields, FieldKind is fkDataSet by default
  with TDataSetField.Create(Self) do
  begin
    Name := 'VideosCDSTalentByVideo';
    FieldName := 'TalentByVideo';
    DataSet := VideosCDS;
  end;

Associating this field with a ClientDataSet is even simpler. This process is demonstrated in the DataSetField property assignment appearing at the top of the following code segment. The remaining lines demonstrate the creation of the actual fields of the nested dataset.

  //TalentByVideosCDS
  TalentByVideosCDS.DataSetField := 
    TDataSetField(FindComponent('VideosCDSTalentByVideo'));
  with TStringField.Create(Self) do
  begin
    Name := 'TalentByVideosID';
    FieldKind := fkData;
    FieldName := 'TalentID';
    Size := 42;
    DataSet := TalentByVideosCDS;
    Required := True;
  end; //ID
  with TStringField.Create(Self) do
  begin
    Name := 'TalentByVideosName';
    FieldKind := fklookup;
    FieldName := 'Name';
    Size := 50;
    DataSet := TalentByVideosCDS;
    KeyFields := 'TalentID';
    LookupDataSet := TalentCDS;
    LookupKeyFields := 'ID';
    LookupResultField := 'Name';
  end; //ID
  with TMemoField.Create(Self) do
  begin
    Name := 'TalentByVideosComment';
    FieldKind := fkData;
    FieldName := 'Comment';
    DataSet := TalentByVideosCDS;
  end; //ID

Creating Nested DataSets Using Dynamically Linked DataSets

Nested datasets are automatically created when a dataset provider's DataSet property points to the master dataset of a master-detail relationship. A master-detail relationship, as the term is being used here, exists when one dataset, the detail dataset, is linked to another, the master dataset, through properties of the detail dataset.

For example, a master-detail relationship exists when a BDE Table component is linked to another via the MasterSource and MasterFields properties. Likewise, a master-detail relationship exists when a SQLQuery component is linked to another dataset using the DataSource property in conjunction with a parameterized query (where one or more parameter names in the detail table query match field names in the master dataset).

When a DataSetProvider points to the master table one of these mater-detail relationships, the data packet that it provides to a ClientDataSet includes one DataSetField for each detail dataset.

Creating nested datasets through dynamically linked datasets is not limited to BDE and dbExpress datasets. Nested datasets can also be created using IBExpress, ADO, and MyBase datasets, as well as many third-party TDataSet descendants. For example, the ADSTable and ADSQuery components provided by Extended System to connect to the Advantage Database Server can by linked dynamically, which will then produce nested datasets when their data is provided through a DataSetProvider.

The following steps demonstrate how to create nested datasets using dynamically linked datasets.

Create a new project.
Design the main form to look similar to the one created earlier in this article. Specifically, add two Panels from the Standard page of the Component Palette. In each panel place a DBNavigator and a DBGrid. These components are located on the Data Controls page. Align both DBNavigators to alTop, and both DBGrids to alClient. Set the Align property of the first Panel to alTop, and the second to alClient. (Again, if you want a better interface, after you align the first panel to alTop, but before aligning the second panel, place a TSplitter from the Additional page of the Component Palette on your form, and align it to top as well. The splitter permits the user to customize the percentage of the form occupied by each panel at runtime.) Also add two DataSources from the Data Access page of the Component Palette onto this form. Set the DataSource property of the top DBNavigator and DBGrid to DataSource1, and the DataSource property of the bottom DBNavigator and DBGrid to DataSource 2. Your form should look something like the following.
Select File | New | Data Module to add a data module to your project. From the BDE page of the Component Palette add two Tables, and from the Data Access page add one DataSetProvider, one DataSource, and two ClientDataSets. Your data module should look something like the following.
Set the DatabaseName property of Table1 and Table2 to DBDEMOS. Set the TableName property of Table1 to customer.db, and the TableName property of Table2 to orders.db. Also, set the IndexName property of Table2 to CustNo. Next, set the DataSet property of DataSource1 to Table1.
Now it is time to create the dynamic link. Select the MasterFields property of Table2 and click the ellipsis to display the Field Link Designer. Select CustNo in both the Detail Fields and Master Fields lists, and then click the Add button. After you click the Add button, the string CustNo -> CustNo will appear in the Joined Fields list. Click OK to close the Field Link Designer.
Set the DataSet property of the DataSetProvider to Table1, and set the ProviderName property of ClientDataSet1 to DataSetProvider1.
At this point, if you make ClientDataSet1 active it will contain one TField for each field in Table1, as well as an additional DataSetField for the associated records of Table2. These detail records can be associated with ClientDataSet2 at runtime by assigning the DataSetField property of ClientDataSet2 to this nested dataset. To do this at design time, however, you must create persistent fields for ClientDataSet1.
To create persistent field for ClientDataSet1, right-click ClientDataSet1 and select Fields Editor. Right-click in the Fields Editor and select Add all fields. After you add all fields to the Fields Editor, you will see the DataSetField at the end of the list, as shown in the following figure.
Select ClientDataSet2 and set its DataSetField property to ClientDataSet1Table2.
Return to the main form. Add a main menu to this form. Right-click the main menu and select Menu Designer. Set the Caption property of the top-level menu item to File. Add two menu items under File, with the first one having the caption Open, and the second having the Caption Apply Updates. The menu designer should look something like the following.
Close the menu designer.
Select File | Use unit, and add the data module's unit to the main form's uses clause.
From the main form, select File | Open to add an OnClick event handler to this menu item. Edit the event handler to look like the following:

procedure TForm1.Open1Click(Sender: TObject);
begin
  DataModule2.ClientDataSet1.Open;
end;

Now select File | Apply Updates from the main form's menu to create an OnClick event handler for it. Edit this event handler to look like the following.

procedure TForm1.ApplyUpdates1Click(Sender: TObject);
begin
  DataModule2.ClientDataSet1.ApplyUpdates(0);
end;

Finally, add an OnClose event handler to the main form. Edit this event handler to look like this:

procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
with DataModule2 do
begin
  if ClientDataSet1.State in [dsEdit, dsInsert] then
    ClientDataSet1.Post;
  if ClientDataSet1.ChangeCount > 0 then
    if MessageDlg('Save changes?', mtConfirmation,
      mbOKCancel, 0) = mrOK then
      ClientDataSet1.ApplyUpdates(0);
end;
end;

Set the DataSet property of DataSource1 to DataModule2.ClientDataSet1, and the DataSet property of DataSource to DataModule2.ClientDataSet2.
Finally, since you cannot call Refresh on a ClientDataSet that does not have a DataSetProvider (and ClientDataSet2 does not have a DataSetProvider, remove the nbRefresh flag from the VisibleButtons property of DBNavigator2.
Save your project, and then run it. After you select File | Open from the main form's main menu, your form should look something like the following.

Just as you do when using TFields to create nested datasets, the master ClientDataSet is used to control both the master and all detail tables. Specifically, when you open ClientDataSet1, both ClientDataSet1 and ClientDataSet2 are populated with data. Similarly, you call ApplyUpdates only on ClientDataSet1. Doing so saves all changes, even those made through ClientDataSet2.

You can even use the State and ChangeCount properties of ClientDataSet1 to determine the condition of all datasets. You will notice this if you run the project, select File | Open, and then make a single change to one of the records in the detail table. Before posting this change, try to close the application. This causes the OnClose event handler to trigger, where the code will determine that an unposted record needs to be posted, and then ask you whether you want your changes saved or not. In other words, the State property of ClientDataSet1 is in dsEdit state when a unposted change appears in a nested dataset, and calling ApplyUpdates applies all changes, even those posted to the nested datasets.

You can download the source code for this project from Code Central by clicking here.

Nested DataSets and Referential Integrity

Referential integrity refers to the relationships of master-detail records. You control referential integrity of nested datasets using the flags of the Options property of the DataSetProvider. The following figure shows the expanded Options property of a DataSetProvider displayed in the Object Inspector.

Add poCascadeDeletes to cause a master record deletion to delete the corresponding detail records. When poCascadeDeletes is not set, master records cannot be deleted if there are associated detail records.

Add poCascadeUpdates to Options to propagate changes to the master table key fields to associated detail records. If poCascadeUpdates is not set, you cannot change master table fields involved in the master-detail link.

Note also that the Options property also contains a poFetchDetailsOnDemand property. If this flag is set, detail records are not automatically loaded when you open the master ClientDataSet. In this case, you must explicitly call the master ClientDataSet's FetchDetails method in order to load the nested datasets.

About the Author

Cary Jensen is President of Jensen Data Systems, Inc., a Texas-based training and consulting company that won the 2002 Delphi Informant Magazine Readers Choice award for Best Training. He is the author and presenter for Delphi Developer Days (www.DelphiDeveloperDays.com), an information-packed Delphi (TM) seminar series that tours North America and Europe, and Delphi Developer Days Power Workshops, focused Delphi (TM) training. Cary is also an award-winning, best-selling co-author of eighteen books, including Building Kylix Applications (2001, Osborne/McGraw-Hill), Oracle JDeveloper (1999, Oracle Press), JBuilder Essentials (1998, Osborne/McGraw-Hill), and Delphi In Depth (1996, Osborne/McGraw-Hill). For information about onsite training and consulting you can contact Cary at cjensen@jensendatasystems.com, or visit his Web site at www.JensenDataSystems.com.

Click here for a listing of upcoming seminars, workshops, and conferences where Cary Jensen is presenting.

New!: Stay informed, stay in touch. Register online to receive the free Developer Days ELetter: information, observations, and events for the Delphi developer by Cary Jensen. Each Developer Days ELetter includes Delphi tips and tricks, .NET information, links to recent articles posted to the Borland Developers Network site, and events in the Delphi community. Click here to sign up now.

Server Response from: SC1

Creating Nested DataSets

Defining TDataSetFields

Created TDataSetFields at Runtime

Creating Nested DataSets Using Dynamically Linked DataSets

Nested DataSets and Referential Integrity

About the Author

Latest Comments View All Add New RSS ATOM

Rating

Creating Nested DataSets

Defining TDataSetFields

Created TDataSetFields at Runtime

Creating Nested DataSets Using Dynamically Linked DataSets

Nested DataSets and Referential Integrity

About the Author

Latest Comments View All Add New RSS ATOM

Rating

Share This