Solved

Working with nested datasets

Posted on 2004-09-20
3
1,796 Views
Last Modified: 2011-09-26
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!

0
Comment
Question by:KDamm
3 Comments
 
LVL 17

Expert Comment

by:Wim ten Brink
ID: 12102388
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.
0
 
LVL 6

Accepted Solution

by:
vadim_ti earned 300 total points
ID: 12103004
try it:

unit source

unit Unit1;

interface

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

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;
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

end.

form source:
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 Database1: TDatabase
    AliasName = 'DBDEMOS'
    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'
    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
end
0
 

Author Comment

by:KDamm
ID: 12103638
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
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Hello everybody This Article will show you how to validate number with TEdit control, What's the TEdit control? TEdit is a standard Windows edit control on a form, it allows to user to write, read and copy/paste single line of text. Usua…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now