Articles Re-Introduction to FireDAC Data Access (4) Embarcadero Team Japan

[emailx45]

Re-Introduction to FireDAC Data Access (4)
Embarcadero Team Japan - 07/Jul/2020

Spoiler: Content - 1st part

[SHOWTOGROUPS=4,20]
Combine data access function in data module
In this blog, we will explain the basic usage of FireDAC for those who have used Delphi / C++Builder to some extent.

The fourth theme

• Resource octopus wiring?
• What is a data module
• Exercise of application creation using data module

The last time , before last , but we have carried out exercises to create an application that uses the FireDAC, also the one of the exercises, had been placed FireDAC components, such as direct TFDConnection and TFDQuery on the form.

As is often seen in sample programs, in an actual application, data access should be centrally managed. From that perspective, the previous exercise was not practical, but rather a bad example.

So, in this article, I will show you how to manage resources for accessing the database more efficiently. Perhaps in a real application, the method described here would be the recommended implementation.

Resource octopus wiring?
Why is it bad to put a FireDAC component on a form?

I'm sure that some of you who have read the blog have similar experience with other DB components, not just FireDAC. The FireDAC component is used here as an example, but this is a general caveat that applies to other DB components as well.

TFDConnection is equivalent to the resource for accessing the database, and if the Active property is True or the Open method is executed, a session will be established with the target database. At the same time, it consumes one database connection. This session will not be closed unless Active property=False or the Close method is executed, so it remains as it is (unless the database is forcibly disconnected).

What would happen if we had one TFDConnection for each form?

For example, suppose you create 20 form screens, all of which need to get data from a database and display the data on the screen.

In this case, when the screen is displayed, the TFDConnection connection is opened and a session is established, so if you change to another screen without closing the TFDConnection connection, the maximum number of database connections will increase to 20. With such a screen design style, the database resources that are consumed will be unnecessarily increased in proportion to the number of forms.

The same can be said for components other than TFDConnection.

For example, a dataset such as TFDQuery consumes memory according to the number of acquired data when opened. If unnecessary datasets are not closed properly, resources on the database server and client PC will be wasted.

Placing FireDAC components on each screen in this way consumes unnecessary resources and clutters individual management. In other words, placing FireDAC components on each form is like "octopus-wiring" resources.

There is a risk of wasted use of resources by routing resources, and in some cases, the maximum number of connections preset in the database may be exceeded.

In the next section, we will discuss solutions for managing resources efficiently.

What is a data module
So how can you manage your resources more efficiently?

To do this, instead of putting the FireDAC component directly on the form, use the "data module" instead. A data module is a special screen-invisible (hidden) form that allows you to place FireDAC components from the designer's screen in the IDE, just like a regular form.

The main purpose of the data module is to separate the GUI (screen) and business rules. To take this bad application as an example, I aggregate FireDAC components that were scattered on each screen into one data module, and share this data module between each form.

By using the data module

• Centrally manage TFD Connection (number of database connections)
• Consolidate duplicate datasets to save wasted memory
• Centrally manage property settings for each FireDAC component

You can manage resources efficiently.

By using the data module, the resources that used to be octopus wiring can be refreshed.

Now, let's practice the application creation that actually uses the data module.

Exercise of application creation using data module
In this section, you will practice building applications using data modules. However, instead of creating a project from the beginning, rewrite the previously created FireDAC application and change to a project that uses the data module.

The practice procedure is as follows.

1. Open the previously created FireDAC application project
2. Add data module to project
3. Moved FireDAC component from form to data module
4. Rewrite existing program code
5. Run the application

(1) Open an existing project

Select [File]-[Open Project] from the Delphi / C++Builder menu to open the project saved in step (12) of the previous exercise.

(2) Addition of data module

For Delphi, select "Data Module" from [File]-[New]-[Other]-[Delphi Project]-[Database].

For C++Builder, select "Data Module" from [C++Builder Project]-[Database] in [File]-[New]-[Other] of the menu.

(The image above shows the dialog box for creating a new Delphi data module)

When you select the "Data Module" menu, a blank data module form will be added as shown below (the unit name will be Unit2).

The figure below is for Delphi

The figure below is for C++Builder

[/SHOWTOGROUPS]

 

[emailx45]

Spoiler: Content - 2nd part

[SHOWTOGROUPS=4,20]
(3) Change the properties of the data module

On the designer screen, select the [Unit2] tab for Delphi or the [Unit2.cpp] tab for C++Builder, open the data module form, and change the following properties on the object inspector screen. To do.

Property name	value
Name	SampleDataModule
ClassGruop	System.Classes.TPersistent

The ClassGroup property of the data module can be selected from the following items. System.Classes.TPersistent-Delphi RTL only Vcl.Controls.TControl-Contains VCL framework and RTL elements FMX.Types.TControl-Contains FMX framework and RTL elements Especially when using the same data module for both FMX/VCL, select "System.Classes.TPersistent" which is independent of framework.

(4) Move FireDAC component on form to data module

On the designer screen, select the [Unit1] tab for Delphi or the [Unit1.cpp] tab for C++Builder and open the form (Form1).

While holding down the [Shift] key on the keyboard, select FDConnection1, FDQuery1, and FDPhysIBDriverLink1 located on the form with the mouse.

(The figure above shows the Delphi form screen with three components selected.)

After selecting the three components, right click the mouse and select [Edit]-[Cut] from the popup menu or press Ctrl+X on the keyboard. The three components disappear from the form.

Then, on the designer screen, select the [Unit2] tab for Delphi or the [Unit2.cpp] tab for C++Builder and open the data module (SampleDataModule).

Click anywhere on the data module form, right-click and select [Edit]-[Paste] from the popup menu, or press Ctrl+V on the keyboard. The three FireDAC components are moved inside the data module form.

Immediately after pasting to the form, the components may be displayed in a frozen state. In that case, expand the form of the data module with the mouse and shift each component to the appropriate position.

(The figure below shows a Delphi form screen with three components moved to a data module.)

This completes the placement in the data module.

(5) Add code to refer to data module from form

Even if a data module is added, it cannot be used as it is because it cannot be referenced from the form as it is. Let's add the code that references the data module from the form.

The red part is the corrected part.

For Delphi

Open "Unit1.pas" in the code editor of the IDE and add uses Unit2 under the implementation clause.

Unit1.pas

implementation

{$R *.dfm}

uses Unit2;

For C++Builder

Open "Unit1.h" in the code editor of the IDE and add #include "Unit2.h".

Unit1.h

..
..
#include "Unit2.h"
//------------------------------------------------ ---------------------------
class TForm1: :public TForm
...

You can now refer to the variable in the data module called SampleDataModule from your form.

(6) Modify the process to open the dataset

Modify the OnClick event code of Button1 placed on the form to reference the variable of the data module (SampleDataModule).

The red part is the corrected part.

Delphi:

procedure TForm1.Button1Click(Sender: TObject);
begin
with SampleDataModule do
begin
FDQuery1.Close;
FDQuery1.Open('select * from Employee');
end;
end;

C++Builder:

void __fastcall TForm1::Button1Click(TObject *Sender)
{
SampleDataModule-> FDQuery1->Close();
SampleDataModule-> FDQuery1->Open(“select * from Employee”);
}

(7) Modify the process to execute ApplyUpdate

Modify the OnClick event code of Button2 placed on the form to reference the variable of the data module (SampleDataModule).

The red part is the corrected part.

Delphi:

procedure TForm1.Button2Click(Sender: TObject);
var
ErrorCount: Integer;
begin
with SampleDataModule do
begin
FDConnection1.TxOptions.AutoCommit := False; // Auto commit mode OFF
FDConnection1.StartTransaction; // start explicit transaction

ErrorCount := FDQuery1.ApplyUpdates(0);

if ErrorCount = 0 then
FDConnection1.Commit // commit if there are no errors
else
FDConnection1.Rollback; // rollback if there is an error
end;
end;

C++Builder:

void __fastcall TForm1::Button2Click(TObject *Sender)
{
SampleDataModule-> FDConnection1->TxOptions->AutoCommit = False; // Auto-commit mode OFF
SampleDataModule-> FDConnection1->StartTransaction(); // Start explicit transaction

int ErrorCount = SampleDataModule-> FDQuery1->ApplyUpdates(0);
if (ErrorCount == 0) {
SampleDataModule-> FDConnection1->Commit(); // commit if there are no errors
}
else {
SampleDataModule-> FDConnection1->Rollback(); // rollback if there is an error
}
}

(8) Change the reference destination of DataSource1 to data module

On the designer screen, select the [Unit1] tab for Delphi or the [Unit1.cpp] tab for C++Builder and open the form (Form1). Change the properties of DataSource1 from the Object Inspector as follows.

DataSource1

Property name	value
DataSet	SampleDataModule.FDQuery1

(9) Save the project

Select [File]-[Save All] from the menu to save all files

(10) Run the application

Press the execute button on the toolbar (above figure) or the [F9] button on the keyboard.

(11) Verify application operation

Run the application and see the steps since (14) in the previous exercise. If you get the same results, the migration to the data module is complete.

This concludes the exercises in this section.

This exercise was a simple example of referencing a data module from one form, but if you have more forms, you can share data modules from multiple forms in a similar way. By using the data module, you can efficiently manage the database resources.

Furthermore, data modules can be developed using the same method not only for Windows applications but also for mobile applications and server-side applications. In other words, this data module development method can be used to transform applications that were previously built with single layers or two layers into multi-layer applications with three or more layers.

Let's take advantage of data modules that are not only resource efficient and maintainable, but also expandable.

Next time, I will explain how to use FireDAC's in-memory dataset TFDMemTable instead of the conventional TClientDataSet.


[/SHOWTOGROUPS]