asked on

Working with nested datasets

Hello,

I want to understand Delphi's Database mechanism and the many features it offers. I tried to use Nested Datasets to show the contents of two tables in one TDBGrid. For a test application I use the Demo-Database DBDEMOS (BDE). I can access and show the tables "Customer" and "Orders" with a TTable and a TDatasource for every table: tbCustomer, dsCustomer, tbOrders, dsOrders. Now I want to access the Orders of every Customer via the Customer's DBGrid: TDBGrid is able to show a nested dataset in a separate form (TDBGrid.ShowPopupEditor). I create two TClientDataset's: cdsCustomer, cdsOrders. I link these to the TTable's via TDatasetProviders dspCustomer, dspOrders. I create persistent fields for cdsCustomer with the field editor: I import every data field and add a new field "Orders" and set its Type in "Field properties" to "Dataset". This changes the FieldKind to fkCalculated, so I assume this is correct. Now I set cdsOrders.DatasetField to the new field component cdsCustomerOrders.

When I try to activate any of the ClientDataset's, I get an error message "cdsCustomer: Field 'Orders' cannot be a calculated or lookup field." I tried every other FieldKind - it won't work. I tried to create an Index on Orders.CustNo and make it the active Index for cdsOrders - same result.

Here is everything I did with the components (at design time) step by step:

tbCustomer.DatabaseName := 'DBDEMOS';
tbCustomer.TableName := 'customer.db';
dsCustomer.Dataset := tbCustomer;
tbCustomer.Active := true;

tbOrders.DatabaseName := 'DBDEMOS';
tbOrders.TableName := 'orders.db';
dsOrders.Dataset := tbOrders;
tbOrders.Active := true;

dspCustomer.Dataset := tbCustomer;
cdsCustomer.ProviderName := dspCustomer;

dspOrders.Dataset := tbOrders;
cdsOrders.ProviderName := dspOrders;

<Set persistent fields for cdsCustomer, add new Dataset-field "Orders">
cdsOrders.DatasetField := cdsCustomerOrders
cdsOrders.ProviderName := dspOrders; // have to reset it because SetDatasetField changed it to ''

cdsCustomer.Active := true; // error
cdsOrders.Active := true; // error

Maybe I understood something wrong. Why does TClientDataset.SetDatasetField delete the ProviderName? From where does the nested Dataset get it's data if not from the Provider? Is what I want possible at all? But if not, what is the idea behind nested datasets?

Thank you!

Wim ten Brink

Nested datasets are often exactly that: datasets in fields of another dataset. Some databases are capable of doing this. Nested datasets can also occur with specific queries where you ask something like:
Select * from Customers, Orders where Customer.CustomerID = Orders.CustomerID
In this case, you'd get a huge list of all data, but if you'd retrieve it as a nested dataset, you'd only get one record for each customer. But each customer record would have a nested dataset with orders.

The idea behind nested datasets are just levels of data, jumping from one level to another. You could, for example, include a third level for articles. Every order would have a list of articles, thus in the Orders nested dataset you'd get another nested dataset for articles.

However, you could also want things to be nested the other way around. E.g. start with the articles, with customers who ordered these articles in a nested dataset. Basically, they're used to view your data from a certain perspective.

But in general you could use normal master-detail datasets for this. But the Master-Detail model requires a new query every time a user selects a new master record to get the new detail records. With nested datasets, you don't have these roundtrips back to the server. The database just kicks over all the data you want and then you use it client-side. Means that you will be sending over a large amount of data but once the client has the data, it can walk through it pretty fast without the need to connect to the server again to beg for more data.
The old Master-Detail technique would just send over small amounts of data but while the user walks through the list, the client keeps asking new detail info from the server, keeping the server quite busy.

So basically, the use of nested datasets is a strategical decision. If you want to remove a lot of workload away from the server, nested datasets are more practical. If users need the latest data all the time, the old master-detail methods are preferred.

ASKER CERTIFIED SOLUTION

vadim_ti

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KDamm

ASKER

Hello Workshop_Alex,

thanks for your comment, I think nested datasets are better then master-detail-relationships for what I have in mind.

Thank you vadim_ti for your answer - I just needed to set tborders.MasterField to 'CustNo' to get the table to show just the orders for the selected customer.

For everyone who will have the same problem in future (the solution is different from Borland's documentation: Nowhere is suggested to set the field's name to the dataset name) - here the complete source of the working test form:

{ Source }

unit ufmMain;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, DB, DBClient, Provider, DBTables, Grids, DBGrids;

type
TForm1 = class(TForm)
Database1: TDatabase;
tbcustomer: TTable;
dsCustomer: TDataSource;
tborders: TTable;
dspCustomer: TDataSetProvider;
cdsCustomer: TClientDataSet;
cdsOrder: TClientDataSet;
cdsCustomerCustNo: TFloatField;
cdsCustomerCompany: TStringField;
cdsCustomerAddr1: TStringField;
cdsCustomerAddr2: TStringField;
cdsCustomertborders: TDataSetField;
DBGrid1: TDBGrid;
dsCustomerNestedOrders: TDataSource;
private
{ Private declarations }
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}

end.

{ Form }

object Form1: TForm1
Left = 192
Top = 133
Width = 544
Height = 375
Caption = 'Form1'
Color = clBtnFace
Font.Charset = DEFAULT_CHARSET
Font.Color = clWindowText
Font.Height = -11
Font.Name = 'MS Sans Serif'
Font.Style = []
OldCreateOrder = False
PixelsPerInch = 96
TextHeight = 13
object DBGrid1: TDBGrid
Left = 0
Top = 0
Width = 536
Height = 348
Align = alClient
DataSource = dsCustomerNestedOrders
TabOrder = 0
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'MS Sans Serif'
TitleFont.Style = []
end
object Database1: TDatabase
AliasName = 'DBDEMOS'
Connected = True
DatabaseName = 'db'
SessionName = 'Default'
Left = 8
Top = 8
end
object tbcustomer: TTable
DatabaseName = 'db'
TableName = 'customer.db'
Left = 56
Top = 8
end
object dsCustomer: TDataSource
DataSet = tbcustomer
Left = 56
Top = 40
end
object tborders: TTable
DatabaseName = 'db'
IndexName = 'CustNo'
MasterFields = 'CustNo'
MasterSource = dsCustomer
TableName = 'orders.db'
Left = 104
Top = 8
end
object dspCustomer: TDataSetProvider
DataSet = tbcustomer
Left = 56
Top = 80
end
object cdsCustomer: TClientDataSet
Active = True
Aggregates = <>
Params = <>
ProviderName = 'dspCustomer'
Left = 56
Top = 120
object cdsCustomerCustNo: TFloatField
FieldName = 'CustNo'
ProviderFlags = [pfInUpdate, pfInWhere, pfInKey]
end
object cdsCustomerCompany: TStringField
FieldName = 'Company'
Size = 30
end
object cdsCustomerAddr1: TStringField
FieldName = 'Addr1'
Size = 30
end
object cdsCustomerAddr2: TStringField
FieldName = 'Addr2'
Size = 30
end
object cdsCustomertborders: TDataSetField
FieldName = 'tborders'
end
end
object cdsOrder: TClientDataSet
Aggregates = <>
Params = <>
Left = 104
Top = 120
end
object dsCustomerNestedOrders: TDataSource
DataSet = cdsCustomer
Left = 156
Top = 120
end
end