Delphi - Database application tutorial (final paper)

1. Introduction

In prior paper of our database tutorial we looked into the theory.
In this final paper we are going to build a simple database application.
The application will show the befor discussed techniques. It certainly is not a complete application. It's only ment for showing
the various techniques.

Download here the source

2. The database tables

The main goal of our application is to manage projects and drawings. It is possible to add drawings to a project.
The complete application works with three tables :

• Project.db - For managing the projects.
• Drawing.db - For managing the drawings.
• DrawProject.db - For relating drawings to projects and vica versa.

Paradox tables are used with the following structure :
* = Index of table, A = Alpha numeric (Character) D=Datefield.

The tables Project and Drawing are related on a many-to-many relationship.
This means that a project can have more then one drawing, and a drawing can belong to more then one project.
The relation is maintained in the table DrawProject.

3. The Application

The application has one mainform with a pagecontrol on it having three tabsheets.
The first tabsheet shows the projects. Here we can :

• add, delete and change  projects.
• add drawings to a project.

The second tabsheet shows an overview of all the drawings.Here we can add, delete and change drawings.
The third tabsheet offers the possibility of using SQL query's to get specific resultsets from the database.
As said, not a complete application, but an application with a lot of databs techniques in it.

List of forms of this project :

frmMain - the main form
dmProject - a datamodule which contains the database components.
frmAddItem - a 'common' form which can be used in combination with a class for adding drawings to a project. (About this later on more)
modBrowse - an unit, not a form, which declarates the class for adding

3.1 Setting database components

First we need to make a database alias in the BDE-Administrator :

The name of the alias is Project and points to the physical data on disk.
Now that this is done we can put the database components in the datamodule and set their properties.
What do we need :

• a TTable and TDatasource for the projects.
• a TTable and TDatasource for the drawings
• a TTable and TDatasource for the relation between projects and drawings. (The master - detail relation)
• a TTable and TDatasource to show drawing details of the related drawings.
• a TQuery and TDataSource component for the query and resultset.

Above you see a screendump of the datamodule dmProject. The properties per component are set as follows :

Setting the properties of the data-access components :

The dataware components of the application must be set as follows :

The tab Drawings has a DBGrid where drawings can be updated. DBGrid has as source dsDrawing.

3.2 Adding a drawing to a project

With the button Add a drawing to this project you can add drawings to the current project.
A drawing must be selected from the table Drawing, this drawing will be added, together with the projectnr. in the tablel tbPrjDwg.
For selecting a drawing we need a new form.
We could build this form similar as the application using it only for drawings.
For this demoapplication that would be Ok. But you could also add a project to a drawing, or add a project to 'what ever'.
We must make the form as abstract as we can to re-use it for all adding operations in our application.
How could this be done :

1. To set the components in the calling code before we show the form.
2. By setting properties at the AddForm so the form can configure itself.
3. By using a class in which we can set easily the properties, and which we can use in the future to add 'add'features in our application.

Option 1 and 2 are Ok, but they bring a lot of 'look a like' code for each adding form in our application code.
From the OOP perspective option 3 is the best solution, so let give it a try.

The AddForm :

The addform is very simple and consists of a DBGrid and two buttons. In design time we don't need to set database properties.

The following class is used in the adding process :  (modBrowse.pas)

/////////////////////////////////////////////////////////////////////////////
// modBrowse class
// Used for setting the frmAddItem form
//
// (c) beensoft@yahoo.com    http://surf.to/beensoft
////////////////////////////////////////////////////////////////////////////


unit modBrowse;

interface

uses DB, Classes;

resourcestring
  SAddDWG = 'Add a drawing';


type
  TDBBrowseClass = class
  private
    { Private declarations }
    FDataSource : TDataSource;
    FFieldCaptionList : TStrings;
    FFieldList : TStrings;
  public
    { Public declarations }
    Constructor CreateBrowse (aDataSource : TDataSource);
    Destructor Destroy; Override;
    function GetCaption : string; virtual; abstract;
    function GetFieldCaptionList : TStrings; virtual;
    function GetFieldList : TStrings; virtual;
    property Datasource : TDataSource read FDataSource write FDatasource;
    property FieldCaptionList : TStrings read FFieldCaptionList write FFieldCaptionList;
    property FieldList : TStrings read FFieldList write FFieldList;
  end;


//TDBDWGBrowse : Adding drawings
type
  TDBDWGBrowse = class(TDBBrowseClass)
  private
    { Private declarations }

  public
    { Public declarations }
    function GetCaption : string; override;
    function GetFieldCaptionList : TStrings; override;
    function GetFieldList : TStrings; override;
  end;

// Here you could create the TDBPrjBrowse for adding projects

implementation

{ TDBBrowse }

constructor TDBBrowseClass.CreateBrowse(aDataSource: TDataSource);
begin
  Inherited Create;
  FDataSource := aDataSource;              // the datasource
  FFieldCaptionList := TStringList.Create; // the collumncaptions of the grid
  FFieldList := TStringList.Create;        // the fields in the grid
end;


Destructor TDBBrowseClass.Destroy;
begin
  FFieldCaptionList.Free;   // free the caption stringlist
  FFieldList.Free;          // free the field list
  Inherited Destroy;
end;


function TDBBrowseClass.GetFieldCaptionList : TStrings;
begin
  FFieldCaptionList.Clear;    // clear the list (should be clear)
  Result := FFieldCaptionList;
end;

function TDBBrowseClass.GetFieldList : TStrings;
begin
  FFieldList.Clear;           // clear the list (should be clear)
  Result := FFieldList;
end;


{ TDBDWGBrowse }

function TDBDWGBrowse.GetCaption: string;
begin
  Result := SAddDWG;          //set the caption for DWG adding
end;

// For the captions of the grid columns
function TDBDWGBrowse.GetFieldCaptionList : TStrings;
begin
  FFieldCaptionList.Add('Drawingnr');
  FFieldCaptionList.Add('Description');
  result := FFieldCaptionList;
end;

// The FieldName list
function TDBDWGBrowse.GeTFieldList : TStrings;

begin
  FFieldList.Add('DRAWINGNR');
  FFieldList.Add('DESCRIPTION');
  result := FFieldList;
end;

end.

The main class is used for initializing the class  (datasource) and to create the stringlists. (And destroying them after use)
The virtual declared methods can be overriden in the subclasses to set the specific properties like the used fields and captions in the grid.
Using this class is still a bit static, we could also use parameters for the fields to initialize them automatic.
But then it would maybe be easier to make a component of it on which you can set all the properties in designtime.
A very good idea indeed! Let's build a TDBBrowse component in the future.

The calling code is as follows :  (frmMain)

procedure TfrmMain.SetGridProps; // method can be used generally 
var I : Integer;                 // Browse : TDBBrowse 

begin
  frmAddItem.DBGridItems.Columns.Clear;              // Clear the 'normal'fieldlist
  For I := 0 to Browse.GetFieldList.Count - 1 do  // Add field and caption to DBGrid
  with frmAddItem do
  begin
    DBGridItems.Columns.Add;
    DBGridItems.Columns[I].FieldName := Browse.GetFieldList.Strings[I];
    DBGridItems.Columns[I].Title.Caption := Browse.GetFieldCaptionList.Strings[I];
  end;
end;


procedure TfrmMain.btnAddDWGClick(Sender: TObject); // Add a drawing button click

begin
  // First create the DWGBrowse object 
  Browse := TDBDWGBrowse.CreateBrowse(dmProject.dsDrawing);
  Try
    frmAddItem := TFrmAddItem.Create(self); // Create the form
    With frmAddItem do
    begin
      Caption := DWGBrowse.GetCaption;      // Set the caption of the form
      DBGridItems.DataSource := Browse.DataSource; // Parse the datasource to grid
      SetGridProps;        // Set grid properties method
      ShowModal;           // show the form
      With dmProject do    // Insert the drawing/project in tabel tbPrjDwg
      begin
        tbPrjDwg.Insert;
        tbPrjDwg.FieldByName('DRAWINGNR').AsString := tbDrawing.FieldByName('DRAWINGNR').AsString;
        tbPrjDwg.FieldByName('PROJECTNR').AsString := tbProject.FieldByName('PROJECTNR').AsString;
        tbPrjDwg.Post;
      end;
    end;
  Finally
    frmAddItem.Free;    // free the form
    Browse.Free;        // free the object
  end;
end;

3.3 Making a query

On the third tabsheet of the control we can edit SQL code in a memo component and execute it with the button  Execute query
The buttonClick event is as follows :

procedure TfrmMain.btnExecuteSQLClick(Sender: TObject);
begin
  with dmProject do
  begin
    Query.Close;
    Query.SQL.Clear;
    Query.SQL := mmSQL.Lines; // assign memolines to SQL
    Query.Open;
  end;
end;

The resultset of the querie is shown in the DBGrid below the memo.

With this demo application we tried to clear a few database techniques.
Th tutorial Tutorial Delphi basic-databasetechniques is now complete.

Top