大匠之風: NestedDataSets in ClientDataSets

Overview

The nested dataset feature of ClientDataSet is used to solve the Master-Details design pattern in which a TDataSet field is inserted into the column list. This feature has always been considered a magical technique.

However, the core of database technology is still SQL, so let's take a look at how Delphi uses SQL to pull data in the nested dataset mode.

Surprisingly, the way to pull data is to fetch all details to the client-side at once when fetching data from the header.

Advantages

The design pattern can complete all the work.
It can have up to 15 detail datasets (15 TDataSetFields).
Since both the header and all related details are pulled to the client, there is no need to request data from the server when switching data in the header.

Disadvantages:

Because both the header and all related details are pulled to the client, it can take a very long time when dealing with large amounts of data. Therefore, the actual data size should be considered when designing.

Practical Exercise 1 - Design using Table

Table Step 1 - TFDConnection

In this example, we will use FireDAC for the design. First, drag and drop TFDConnection and TFDGUIxWaitCursor onto the form.

Also, let's test the ability of FireDAC to load third-party drivers for DBX. Here, we will use the Devart dbExpress driver for SQL Server and confirm that FireDAC can successfully load and connect to Microsoft SQL Server, thus proving the statement in the EMBT DocWiki to be true.

Table Step 2 - TFDTable

This step requires the following components:

TFDTable * 2
TDataSource * 1

The master table, MasterFDTable1, should be linked to the Vendors table in DBDemos. Set the DataSource1.DataSet property to MasterFDTable1.

The detail table, DetailFDTable2, should be linked to the Parts table in DBDemos. Set the DetailFDTable2.MasterSource property to DataSource1.

Double-click on the MasterFields cell to bring up the editor, and set the linking key between the master and detail tables. In this example, VendorNo is the linking key between the two tables.

The design for step 3 is the same for both, so we'll cover the settings for Practical 2 first. For those who can't wait, you can scroll down to step 3.

Practical 2 - Design using Query

Query Step 1 - TSQLConnection

Since there is less data for dbExpress, TSQLConnection of dbExpress is used here and placed on the form, and Microsoft SQL Server connection settings are completed.

Query Step 2 - TQuery

This step requires the following components:

TSQLQuery * 2
TDataSource * 1

MasterSQLQuery1.SQL contents: SELECT * FROM Vendors; DataSource1.DataSet set to MasterSQLQuery1。

DetailSQLQuery2.SQL contents: SELECT * FROM Parts WHERE VendorNo=:VendorNo.

Note! The parameter name ":VendorNo" must be the same as the master key name of MasterSQLQuery1.

Step 3 - TDataSetProvider and TClientDataSet

Master: Organize the header and detail into a client-side component, use TDataSetProvider, TClientDataSet, and TDataSource on the form.

DataSetProvider1.DataSet set to MasterSQLQuery1; MasterClientDataSet1.ProviderName set to DataSetProvider1; MasterCDSDataSource.DataSet set to MasterClientDataSet1。

Double-click on MasterClientDataSet1 with the left mouse button, which will bring up the Fields Editor window. Right-click in the frame and select "Add all fields" to add all fields. At this point, you can see the TDataSetField named DetailSQLQuery2 appear in the list.

Details: Since the detail is now a field within the master, another ClientDataSet and a DataSource are needed to present it. Drag a TClientDataSet and a TDataSource onto the form.

For the detail, the DetailClientDataSet2 does not need to set the ProviderName. Instead, the DataSetField needs to be set to the MasterClientDataSet1DetailSQLQuery2 field of the master. DetailCDSDataSource2.DataSet should be set to DetailClientDataSet2.

Step 4 - Displaying the master and detail data using separate DBGrids.

Summary

The use of nested datasets can solve the problem of consuming resources by frequently requesting detailed data from the server, especially when network resources are limited. Although there are concerns about transmitting all detailed data to the client at once, the use of efficient data connection technology can improve the transmission performance of data retrieval from the server. The nested dataset feature can be considered as a beneficial technology with more pros than cons.

Eden recommends its use. 😊

=== 中文 ===

概述

ClientDataSet 的嵌套資料集在書本上看過，目的在解決表頭-明細（Master-Details）設計模式。其特性是在欄位清單裡塞入一個名為TDataSet的欄位，一直以來就覺得嵌套資料集是很神奇的技術。

資料庫技術核心還是 SQL，當然就要看 Delphi 在嵌套資料集模式下是怎麼使用 SQL 拉資料，有想法就來實作看看。

原來，拉資料的方式是在表頭拉資料時，也一併把【所有】，對你沒看錯，是一口氣把所有明細全抓到客戶端！

優點

1. 設計模式可完成所有工作
2. 最大可以有 15 個明細資料集 (15 TDataSetField )
3. 因為表頭和所有關聯明細全都抓到客戶端，在表頭切換資料時就不會再和伺服器要資料。

缺點

因為表頭和所有關聯明細全都抓到客戶端，資料量大的時候會等～非～常～久～。設計時要考慮實際資料大小

實戰1 - 使用 Table 進行設計

Table 步驟1 - TFDConnection

本例採用 FireDAC 設計，首先把 TFDConnection 和 TFDGUIxWaitCursor 拉到表單上。

順便測試一下 FireDAC 載入 DBX 第三方廠商提供的驅動程式。這裡使用 Devart dbExpress driver SQL Server，發現 FireDAC 確實可以正確載入並且連結到 Microsoft SQL Server，證明 EMBT docwiki 所言不假。

Table 步驟2 - TFDTable

此步驟需要以下零件：

TFDTable * 2
TDataSource * 1

表頭：MasterFDTable1 連結 DBDemos 的 Vendors 表格。DataSource1.DataSet 設定MasterFDTable1。

明細：DetailFDTable2 連結 DBDemos 的 Parts 表格。DetailFDTable2.MasterSource 設定 DataSource1。

雙擊 MasterFields 格子裡的延伸按鈕，設定表頭和明細關聯鍵值，以此例來說 VendorNo 是這兩張表格的關聯鍵。

步驟 3 的設計兩者相同，所以先講實戰2的設定，等不及的人可以再往下滑到步驟3。

實戰2 - 使用 Query 進行設計

Query 步驟1 - TSQLConnection

因為 dbExpress 的資料比較少，所以這裡把 dbExpress 的 TSQLConnection 拉到表單上，並完成 Microsoft SQL Server 連結設定。

Query 步驟2 - TQuery

此步驟需要以下零件：

TSQLQuery * 2
TDataSource * 1

MasterSQLQuery1.SQL 內容：SELECT * FROM Vendors；DataSource1.DataSet 設為 MasterSQLQuery1。

DetailSQLQuery2.SQL 內容：SELECT * FROM Parts WHERE VendorNo=:VendorNo。

注意！參數【:VendorNo】名稱必須要和 MasterSQLQuery1 的關聯鍵名稱相同。

步驟3 - TDataSetProvider、TClientDataSet

表頭：把表頭和明細整理成一個客戶端元件使用，把 TDataSetProvider、TClientDataSet 和 TDataSource 拉到表單上。

DataSetProvider1.DataSet 設為 MasterSQLQuery1；MasterClientDataSet1.ProviderName 設為 DataSetProvider1；MasterCDSDataSource.DataSet 設為 MasterClientDataSet1。

滑鼠左鍵雙擊 MasterClientDataSet1，會帶出欄位清單視窗，在框體中按滑鼠右鍵選擇【Add all fields】把所有欄位加入，此時可以看到名為 DetailSQLQuery2 的 TDataSetField 出現在清單內。

明細：明細已經變成表頭內的一個欄位，所以還需要一個 ClientDataSet 來呈現。再把一個 TClientDataSet 和 TDataSource 拉到表單中。

明細的 DetailClientDataSet2 不用設定 ProviderName，而是要把 DataSetField 設為表頭的 MasterClientDataSet1DetailSQLQuery2 欄位。DetailCDSDataSource2.DataSet 設為 DetailClientDataSet2。

步驟4 - 使用各自的 DBGrid 呈現表頭和明細資料

NestedDataSet in Mybase

ClientDataSet 的 Mybase 功能也可以制作巢狀資料集，範例如下：

var
  gStaffTable: TClientDataSet;
begin
  gStaffTable := ClientDataSet1;
  
  with gStaffTable do
  begin
    // Add fields
    FieldDefs.Add('FirstName', ftString, 255, False);         //0
    FieldDefs.Add('MiddleName', ftString, 255, False);        //1
    FieldDefs.Add('LastName', ftString, 255, False);          //2
  
    // Define a nested field for the 'Department' structure
    with FieldDefs.AddFieldDef do
    begin
      Name := 'Department';
      DataType := ftDataSet;
      ChildDefs.Add('Name', ftString, 255, False);
      ChildDefs.Add('id', ftInteger);
    end;
  end;

  // Create the DataSet
  gStaffTable.CreateDataSet;
end;

總結

在有限的網路資源下，客戶端向伺服器端要資料的次數是越少越好，嵌套資料集這功能剛好可以解決頻繁向主機拉明細資料的資源消耗問題。雖說一口氣把明細資料帶到客戶端有傳輸上的疑慮，不過，在利用高效的資料連結技術後可以改善伺服器端的拉資料的傳輸效能。嵌套資料集功能可說是利大於弊的技術。

Eden 向你推薦。 😊

大匠之風

網頁

2022/12/28

NestedDataSets in ClientDataSets