Question

“How can I create DADE QueryDataViews via code?”

Solution

The following example shows how to create a DADE datamodule that contains two query dataviews, customer/orders linked in a master/detail relationship.

Download: CreateDataModCustOrderLinkedDataViews.zip

Delphi code sample:

procedure TForm1.CreateDataViews;
var
  lDataModule: TdaDataModule;
begin

  // create data module
  lDataModule := daGetDataModule(ppReport1);

  if lDataModule = nil then
    lDataModule := TdaDataModule.CreateForReport(ppReport1);

  // create master customer dataview
  ppReport1.DataPipeline := CreateCustomers(lDataModule, ppDesigner1.DataSettings);

  // create detail orders dataview
  ppSubReport1.DataPipeline := CreateOrders(lDataModule, ppDesigner1.DataSettings);

  // create a link on CustNo
  FDetailDataView.CreateLink(FMasterDataView, 'CustNo', 'CustNo');

  // force sql refresh for linking
  FDetailDataView.Sync;

end;

function TForm1.CreateCustomers(aDataModule: TdaDatamodule; aDataSettings: TppDataSettings): TppDBPipeline;
var
  lSQL: TdaSQL;
  lDataView: TdaBDEQueryDataView;
  lPipeline: TppDBPipeline;
  lSQLBuilder: TdaSQLBuilder;
begin

  // create dataview
  lDataView := TdaBDEQueryDataView.Create(aDataModule);
  lDataView.Name := 'Customers';
  lDataView.Parent := aDataModule;
  lDataView.Init;

  FMasterDataview := lDataview;

  // initialize sql object
  lSQL := lDataView.SQL;
  lSQL.DatabaseName := aDataSettings.DatabaseName;
  lSQL.DatabaseType := aDataSettings.DataBaseType;
  lSQL.SQLType      := aDataSettings.SQLType;
  lSQL.Session := lDataView.Session;

  // build sql
  lSQLBuilder := TdaSQLBuilder.Create(lSQL);

  try
    lSQLBuilder.SelectTables.Add('Customer');
    lSQLBuilder.SelectFields.Add('Customer', 'CustNo');
    lSQLBuilder.SelectFields.Add('Customer', 'Company');

  finally
    lSQLBuilder.Free;
  end;

  // initialize datapipeline
  lPipeline := TppDBPipeline(lDataView.DataPipelines[0]);
  lPipeline.Name := 'Customer';

  Result := lPipeline;

end;

function TForm1.CreateOrders(aDataModule: TdaDatamodule; aDataSettings: TppDataSettings): TppDBPipeline;
var
  lSQL: TdaSQL;
  lDataView: TdaBDEQueryDataView;
  lPipeline: TppDBPipeline;
  lSQLBuilder: TdaSQLBuilder;
begin

  // create dataview
  lDataView := TdaBDEQueryDataView.Create(aDataModule);
  lDataView.Name := 'Orders';
  lDataView.Parent := aDataModule;
  lDataView.Init;

  FDetailDataview := lDataview;

  // initialize sql object
  lSQL := lDataview.SQL;
  lSQL.DatabaseName := aDataSettings.DatabaseName;
  lSQL.DatabaseType := aDataSettings.DataBaseType;
  lSQL.SQLType      := aDataSettings.SQLType;
  lSQL.Session := lDataView.Session;

  // build sql
  lSQLBuilder := TdaSQLBuilder.Create(lSQL);
  
  try
    lSQLBuilder.SelectTables.Add('Orders');

    lSQLBuilder.SelectFields.Add('Orders', 'CustNo');
    lSQLBuilder.SelectFields.Add('Orders', 'AmountPaid');
    lSQLBuilder.SelectFields.Add('Orders', 'OrderNo');

    lSQLBuilder.OrderByFields.Add('Orders', 'CustNo');
    lSQLBuilder.OrderByFields.Add('Orders', 'AmountPaid');

  finally
    lSQLBuilder.Free;
  end;

  // initialize pipeline
  lPipeline := TppDBPipeline(lDataView.DataPipelines[0]);
  lPipeline.Name := 'Orders';

  Result := lPipeline;

end;