EntityDAC

Creating My First Application With Express Edition

In this tutorial we will create our first application using EntityDAC Express Edition.

Objective

Create an application with two lists. The first one displays a list of an enterprise departments. The second one displays a list of the selected department employees. The application data is stored in a SQLite database, and Devart Universal Data Access Components is used as a data-access layer.

Step 1. Create a database

First we have to create a database for our application. Lets create a new SQLite database named "demo.db3" using any of the SQLite management tools.

Then we have to create tables necessary for the application. We need two tables – DEPT and EMP – with the following structure:

CREATE TABLE DEPT (
  DEPTNO INTEGER PRIMARY KEY, 
  DNAME VARCHAR(14) NOT NULL, 
  LOC VARCHAR(13) NOT NULL
);

CREATE TABLE EMP (
  EMPNO INTEGER PRIMARY KEY, 
  ENAME VARCHAR(10) NOT NULL, 
  JOB VARCHAR(9) NOT NULL, 
  MGR INTEGER, 
  HIREDATE TIMESTAMP NOT NULL, 
  SAL REAL, 
  COMM REAL, 
  DEPTNO INT REFERENCES DEPT
);

Finally, let's fill test tables with data:


INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,'1981-2-20',1600,300,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,'1981-2-22',1250,500,30);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,'1981-4-2',2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,'1981-9-28',1250,1400,30);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,'1981-5-1',2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,'1981-6-9',2450,NULL,10);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,'1987-7-13',3000,NULL,20);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,'1981-9-8',1500,0,30);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,'1987-7-13',1100,NULL,20);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,'1981-12-3',950,NULL,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,'1981-12-3',3000,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,'1982-1-23',1300,NULL,10);

Step 2. Create the application

In RAD Studio, create a new VCL Forms Application project, and set its main form name to DemoMainForm. Save the project with the name "DemoProject.dproj", naming the main form unit as "DemoMainFormUnit.pas".

Step 3. Declare entity classes

EntityDAC Express Edition does not include Entity Developer, therefore we have to manually declare entity classes needed for the application. Since our database has two tables, we need to declare two corresponding entity classes.

Now, let's choose the mapping type we will use. EntityDAC provides four different mapping types which are described in the "Model Mapping" article. Every mapping type has its benefits and disadvantages. Our application will use the mapping type known as "attribute-mapped entities", because this mapping type is more easy to implement than "code-mapped entities", but at the same time it provides all advantages of using TEntity as a base class for entities.

Let's add a new unit to our project and save it as "DemoClasses.pas".

In the unit, declare TDept and TEmp classes corresponding to the tables structure.


  [Table('DEPT')]
  [Model('Demo')]
  [Key('FDeptno')]
  TDept = class(TMappedEntity)
  private
    [Column('DEPTNO', [ReadOnly])]
    FDeptno: Integer;
    [Column('DNAME', 14)]
    FDname: String;
    [Column('LOC', 13)]
    FLoc: String;

    [Column]
    [Collection('TEmp', 'FDept', 'FDeptno', 'FDeptno', srNone, drNone)]
    FEmps: TDeptEmps;

    function GetDeptno: Integer;
    function GetDname: String;
    procedure SetDname(const Value: String);
    function GetLoc: String;
    procedure SetLoc(const Value: String);
  protected
    constructor Create(AMetaType: TMetaType); overload; override;
  public
    constructor Create; overload; override;

    property Deptno: Integer read GetDeptno;
    property Dname: String read GetDname write SetDname;
    property Loc: String read GetLoc write SetLoc;

    property Emps: TDeptEmps read FEmps;
  end;

  [Table('EMP')]
  [Model('Demo')]
  [Key('FEmpno')]
  TEmp = class(TMappedEntity)
  private
    [Column('EMPNO', [ReadOnly])]
    FEmpno: Integer;
    [Column('ENAME', 10)]
    FEname: String;
    [Column('JOB', 9)]
    FJob: String;
    [Column('MGR', [CanBeNull])]
    FMgr: IntegerNullable;
    [Column('HIREDATE')]
    FHiredate: DateTime;
    [Column('SAL', [CanBeNull])]
    FSal: DoubleNullable;
    [Column('COMM', [CanBeNull])]
    FComm: DoubleNullable;
    [Column('DEPTNO', [CanBeNull])]
    FDeptno: IntegerNullable;

    [Column]
    [Reference('TDept', 'FEmps', 'FDeptno', 'FDeptno', srNone, drNone)]
    FDept: TMappedReference;

    function GetEmpno: Integer;
    function GetEname: String;
    procedure SetEname(const Value: String);
    function GetJob: String;
    procedure SetJob(const Value: String);
    function GetMgr: IntegerNullable;
    procedure SetMgr(const Value: IntegerNullable);
    function GetHiredate: TDateTime;
    procedure SetHiredate(const Value: TDateTime);
    function GetSal: DoubleNullable;
    procedure SetSal(const Value: DoubleNullable);
    function GetComm: DoubleNullable;
    procedure SetComm(const Value: DoubleNullable);
    function GetDeptno: IntegerNullable;
    procedure SetDeptno(const Value: IntegerNullable);
    function GetDept: TDept;
    procedure SetDept(const Value: TDept);
  protected
    constructor Create(AMetaType: TMetaType); overload; override;
  public
    constructor Create; overload; override;

    property Empno: Integer read GetEmpno;
    property Ename: String read GetEname write SetEname;
    property Job: String read GetJob write SetJob;
    property Mgr: IntegerNullable read GetMgr write SetMgr;
    property Hiredate: TDateTime read GetHiredate write SetHiredate;
    property Sal: DoubleNullable read GetSal write SetSal;
    property Comm: DoubleNullable read GetComm write SetComm;
    property Deptno: IntegerNullable read GetDeptno write SetDeptno;

    property Dept: TDept read GetDept write SetDept;
  end;

The complete unit source is available in the "DemoClasses.pas" appendix. Entity class mapping is defined using special mapping attributes. Detailed information about mapping attributes you can found in the "Attribute-mapped entities" article.

Step 4. Connect to the database

Now we are ready to establish the database connection in our project. As it specified in the objective, we use Devart UniDAC as a data-access layer, so make sure that you have UniDAC installed.

Since EntityDAC Express Edition does not provide any design-time components, we have to implement all needed functionality in the code. First, add several units to the main form USES clause:

uses
  EntityDAC.EntityConnection,
  EntityDAC.DataProvider.UniDAC,
  SQLiteUniProvider;

We need the EntityDAC.EntityConnection unit in order to use the TEntityConnection class. The EntityDAC.DataProvider.UniDAC unit provides intermediate layer between EntityDAC and UniDAC. The SQLiteUniProvider unit is a part of UniDAC and implements access to a SQLite database.

In the main form declaration, declare fields and methods needed for establishing a connection:

type
  TDemoMainForm = class(TForm)
    procedure FormCreate(Sender: TObject);
    procedure FormDestroy(Sender: TObject);
  private
    FConnection: TEntityConnection;
  public
    procedure Connect;
  end;

And write the methods implementation:

procedure TDemoMainForm.FormCreate(Sender: TObject);
begin
  FConnection := TEntityConnection.Create;
end;

procedure TDemoMainForm.FormDestroy(Sender: TObject);
begin
  FConnection.Free;
end;

procedure TDemoMainForm.FormShow(Sender: TObject);
begin
  Connect;
end;

procedure TDemoMainForm.Connect;
begin
  FConnection.ProviderName := 'UniDAC';
  FConnection.DialectName := 'SQLite';

  FConnection.ConnectionString := 'ProviderName=SQLite;Direct=True;DataBase=' + ExtractFilePath(Application.ExeName) + 'demo.db3';
  FConnection.Connect;
end;

As you can see from the Connect method implementation, the project database file - "demo.db3" - has to be previously placed in the project output folder (by default, <Project path>\Debug\Win32).

Now, we are ready to test the database connection. Compile and run the application. If no error messages appeared and the main application form is shown, then the connection is established successfully.

Note: if you are using not a UniDAC data provider or another database server, then replace the following code lines with yours:

uses
 ...
  EntityDAC.DataProvider.xxxDAC, //used provider name 
 ...
  

...
  FConnection.ProviderName := '...'; // used provider name
  FConnection.DialectName := '...';  // used SQL dialect

  FConnection.ConnectionString := '...'; // connection string for used data provider
  FConnection.Connect;
...

Step 5. Prepare to obtain data from the database

In order to obtain any data from the database, we have to use the TEntityContext class that is designed for data manipulation in EntityDAC. Include the EntityDAC.EntityContext unit to the form USES clause, add the following field to the form's private declaration section:

    FContext: TentityContext;

and then expand the form OnCreate and OnDestroy methods to implement the entity context creation and destruction:

procedure TDemoMainForm.FormCreate(Sender: TObject);
begin
  FConnection := TEntityConnection.Create;

  FContext := TEntityContext.Create;
  FContext.Connection := FConnection;
  FContext.ModelName := 'Demo';
end;

procedure TDemoMainForm.FormDestroy(Sender: TObject);
begin
  FConnection.Free;
  FContext.Free;
end;

In the OnCreate method we create the entity context instance, associate it with the connection and then set the meta model name to the name defined in the entity classes declaration made on Step 3.

Step 6. Populate the departments list

EntityDAC Express Edition does not provide any dataset component, therefore we will obtain the departments list using a special IEntityEnumerable interface and then visualize the list using the default TListBox component.

Declare a form field for storing the departments collection in the form's private declaration section:

    FDepts: IentityEnumerable<TDept>;

Since the FDepts collection is of interface type, we dont need to care about its destruction. In order to use the IEntityEnumerable interface, add the EntityDAC.Entity unit to the form USES clause. Also, add the DemoClasses unit to obtain access to entity classes.

In the form designer, drop the TListBox component on the form and name it lbDepts:

Then, declare the PopulateDepts form method, write its implementation like the following and add the method call to the form OnShow event handler:

procedure TDemoMainForm.FormShow(Sender: TObject);
begin
  Connect;
  PopulateDepts;
end;

procedure TDemoMainForm.PopulateDepts;
var
  Dept: TDept;
begin
  FDepts := FContext.GetEntities<TDept>;

  for Dept in FDepts do
    lbDepts.Items.Add(Dept.DName);
end;

In the PopulateDepts method we first obtain a complete collection of TDept instances (a complete list of departments) and then populate the lbDepts with department names.

Try to compile and run the application, and you will see the form with the department list:

Step 7. Populate the employees list

Next task of the objective is to display a list of the selected department employees. For example, let's display names and job names of employees. Since the employee list will contain more than one data column, we will use the TListView component to implement it.

In the form designer, drop the TListView component on the form and name it lvEmps. Then set the component ViewStyle property to vsReport and create two columns in the Columns property editor:

Declare the PopulateEmps form method, write its implementation like the following and add the method call to the OnClick event handler of the lbDepts component:

procedure TDemoMainForm.lbDeptsClick(Sender: TObject);
begin
  PopulateEmps;
end;

procedure TDemoMainForm.PopulateEmps;
var
  Emps: IEntityEnumerable<TEmp>;
  Emp: TEmp;
  Item: TListItem;
begin
  lvEmps.Items.Clear;
  if lbDepts.ItemIndex < 0 then
    Exit;

  Emps := FDepts[lbDepts.ItemIndex].Emps;

  for Emp in Emps do begin
    Item := lvEmps.Items.Add;
    Item.Caption := Emp.Ename;
    Item.SubItems.Add(Emp.Job);
  end;
end;

In the PopulateEmps method we take the department entity instance from the FDepts collection by the index corresponding to the selected item index in the lbDepts component, and then access its employees collection directly through the TDept.Emps property. This is the easiest way to access entity's referenced collection.

Another way is to obtain employees list using the TEntityContext.GetEntities method with a condition. Here is an example of such approach:

procedure TDemoMainForm.PopulateEmps;
var
  Dept: TDept;
  Emps: IEntityEnumerable<TEmp>;
  Emp: TEmp;
  Item: TListItem;
begin
  lvEmps.Items.Clear;
  if lbDepts.ItemIndex < 0 then
    Exit;

  Dept := FDepts[lbDepts.ItemIndex];
  Emps := FContext.GetEntities<TEmp>('deptno = ' + IntToStr(Dept.Deptno));

  for Emp in Emps do begin
    Item := lvEmps.Items.Add;
    Item.Caption := Emp.Ename;
    Item.SubItems.Add(Emp.Job);
  end;
end;

Compile and run your application. We have a department list on the application main form. When clicking a department name, the employees list is filled with the department employees names.

You can find the project units sources in the article appendix.

Sources:

© 1997-2017 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback