How To…Create Query DataViews via Code

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;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

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;