上QQ阅读APP看书，第一时间看更新

How to do it...

Let's look at the following steps:

Create a new VCL application by selecting File | New | VCL Forms Application.
Put a TFDConnection on the form and set its DriverName to SQLite (because SQL Local uses SQLLite in its engine).

Place on the form a DBEdit (aligned to the top), a TButton (aligned to the top), a DBNavigator (aligned to the top), a DBGrid (aligned to the client), and a DataSource. Set the DataSource property of DBNavigator1 and DBGrid1 to DataSource1.
From DataExplorer, drag and drop onto the form the SALES table from the DELPHICOOKBOOK connection under the InterBase voice.
Now, put on the form one TFDQuery, one TFDLocalSQL, and one TClientDataSet.
It's time to rename components:

    
      
        
Old           New
      
        
FDQuery1           LocalQuery
      
        
DataSource1           dsLocalQuery
      
        
ClientDataSet1           CustomersCDS

If you performed all the steps correctly, you should be in this situation:

Figure 1.33: Form layout at design time

Set the FDLocalSQL1 connection to FDConnection1.
Select the DataSets property of FDLocalSQL1 and click the ellipsis button (...) to enter the editor.

Click the Add New button on the editor twice to add two datasets to the DataSets collection.
Select the first dataset in the collection and set the DataSet property to SalesTable; set the Name property to Sales in order to use the Sales identifier in SQL to refer to this dataset.
Select the second dataset in the collection and set the DataSet property to CustomersCDS; set the Name property to Customers in order to use the customers identifier in SQL to refer to this dataset:

Fig 1.34: FDLocalSQL DataSets editor collection

In the private section of the form, declare a procedure named OpenDataSets and put in the following code:

procedure TMainForm.OpenDataSets;
begin
  SalesTable.Open();
  CustomersCDS.Active := True;
end;

In the private section of the form, declare a procedure named PrepareDataSets and put in the following code:

procedure TMainForm.PrepareDataSets;
begin
  CustomersCDS.FileName :=
    'C:\Users\Public\Documents\Embarcadero\Studio\19.0\Samples\Data\customer.xml';
  LocalQuery.SQL.Text := 'select distinct c.* from Customers c ' +
    ' JOIN Sales s on cast (s.CUST_NO as integer) = c.CustNo ' +
    ' where s.total_value > :v order by c.CustNo ';
end;

Generate a FormCreate event handler and put in this code:

procedure TMainForm.FormCreate(Sender: TObject);
begin
  PrepareDataSets;
end;

We have almost finished; now, we need to put everything together. Generate the Button1 Click event handler and put in this code:

procedure TMainForm.btnExecuteClick(Sender: TObject);
var
  LAmount: Integer;
begin

  // ensure amount is an integer
  if not TryStrToInt(Edit1.Text, LAmount) then
  begin
    ShowMessage('Amount must be integer...');
    exit;
  end;

  LocalQuery.Close;
  OpenDataSets;
  // apply user data
  LocalQuery.ParamByName('v').AsInteger := LAmount;
  // Execute the query through eterogeneous sources
  LocalQuery.Open;
end;

Run the application by hitting F9 (or by going to Run | Run).

Try different amounts to filter the different customers:

Figure 1.35: Data integration in action

Following image is an example showing different amounts to filter the different customers:

Figure 1.36: Another example of Data integration in action