Delphi Labs: DataSnap XE - Multitier Database Application

By: Pawel Glowacki

Abstract: Steps for using Delphi XE for building a DataSnap XE multitier database application for read and write access to "Employee" information in the InterBase XE sample database. The server and clients applications are built from scratch in 10 minutes with Delphi components and just 2 lines of Delphi code. Video version is available on YouTube (http://www.youtube.com/watch?v=B4uxLLIUddg)

    Introduction

In this lab exercise we are going to use Delphi XE to build simple multitier database solution consisting of server and client applications.

For this lab exercise you can use any database of your choice; however I’m going to use Embarcadero InterBase XE RDBMS as my database server. If you do not have InterBase XE installed you can download a free developer edition from https://downloads.embarcadero.com/free/interbase.

InterBase XE comes with a sample “EMPLOYEE” database that we are going to use.

First we need to configure Delphi Data Explorer to point to this “EMPLOYEE” database.

    Configure Database Connection in Data Explorer

In the Data Explorer right-click on the “INTERBASE” node, select “Add New Connection” and give it a name “IBEMPLOYEE”.

Hide image

Right click on the new “IBEMPLOYEE” node in the Data Explorer and select “Modify Connection” to configure the database connection.

In the default InterBase XE installation the “EMPLOYEE” sample database is located in “c:\Embarcadero\InterBase\examples\database\employee.gdb”. Default username is “sysdba” and default password is “masterkey”.

Hide image

Click on the “Test Connection” button to verify the connection was configured properly.

Hide image

Now we are ready to build server and client applications of our multitier database system.


    Create DataSnap Server for Multitier Database Solution

The first step is to create a new DataSnap server application using “DataSnap Server” wizard.

Select “File -> New -> Other” and from the “New Items” dialog double-click on the “DataSnap Server” icon in the “Delphi Projects -> DataSnap Server” category.

Hide image

Hide image
Click to see full-sized image

In the first tab keep the default DataSnap “Project type” which is “VCL Forms Application”.

Hide image
Click to see full-sized image

On the second tab we keep all the default values.

Hide image
Click to see full-sized image

On the third screen we keep the default value “211” for the TCP/IP Port. It is always a good idea to click on the “Test Port” to make sure that it is available.

Hide image
Click to see full-sized image

On the last tab make sure to select “TDSServerModule” as the base class for our server methods class.

Click on “Finish” and the wizard should create a new project with three units.

Click on “File -> Save All”.

Create a new directory for all files in this lab – for example “C:\DataSnapLabs\MultiTier\”.

Save main application form as “FormServerUnit” and keep default names for all other units – typically “ServerContainerUnit1” and “ServerMethodsUnit1” – and save project as “MultiTierServer”.

Implementing database connectivity in a simple DataSnap server like ours does not require any coding! It is a matter of adding a few components to ”ServerMethodsUnit1”.

In this lab we are going to provide access to just one database table inside of the EMPLOYEE database called CUSTOMERS. The client application will be able to browse through customer information and update it.

The objective of the next step is to add “TSQLConnection”, “TSQLDataSet” and “TDataSetProvider” components to the design surface of the “ServerMethodsUnit1” and hooking them together. The easiest way to achieve this goal is through Data Explorer and the VCL Forms designer integration. Just select the desired table in the Data Explorer and drag it over onto the “ServerMethodsUnit1” to automatically create and configure the first two components.

Notice that not only TSQLConnection and TSQLDataSet components were automatically added to the server methods unit, but also they are already properly named and configured.

The last step is to drop a “TDataSetProvider” component to the data module and set its “DataSet” property to “CUSTOMER”. It is also a very good idea is to name the dataset provider to something meaningful, for example “dspCustomer”.

Hide image

Our demo server is now fully implemented. It is very simple and we did not have to write a single line of code!

In order to develop the client application, the server has to be running.

Select “Run -> Run Without Debugging” to run the server project and minimize its window.

Do not shut down the server until the end of this lab exercise.

    Create DataSnap Client for Multitier Database Solution

Right-click on the project group inside the Project Manager and select “Add New Project”.

Hide image

From the “New Items” dialog select “VCL Forms Application” from “Delphi Projects” category.

Hide image

Click “OK”. A new project should be added to the existing project group.

Click on “File -> Save All”.

Locate the folder where the server project has been saved and save there the main form unit of the client application as “FormClientUnit”, the new project as “MultiTierClient” and the project group as “MultiTierGrp”.

Drop a “TSQLConnection” component on the client’s form and Set its “Driver” property to “DataSnap”. Uncheck its “LoginPrompt” property, so it is set to “False”.

Drop “TDSProviderConnection” component on the form and set its “SQLConnection” property from the drop down, so it points to the “SQLConnection1” component already on the form.

Enter “TServerMethods1” as the value for the “DSProviderConnection1.ServerClassName” property.

Add “TClientDataSet” component to the form.

Set its “RemoteServer” property to “DSProviderConnection1”.

Drop down ClientDataSet1 “ProviderName” property and select “dspCUSTOMER” from the list.

Add “TDataSource” component to the form and set its “DataSet” property to “ClientDataSet1”.

Add “TDBGrid” component to the form and set its “DataSource” property to “DataSource1”.

Add “TDBNavigator” component to the form and set its “DataSource” property to “DataSource1”.

Add “TCheckBox” component to the form. Change its “Caption” property to “Active”.

Double-click “CheckBox1” component and enter the following line of code in its “OnClick” event:

procedure TForm3.CheckBox1Click(Sender: TObject);
begin
  ClientDataSet1.Active := CheckBox1.Checked;
end;

Add “TButton” component to the form. Double-click on it and add the following line of code in its “OnClick” event:

procedure TForm3.Button1Click(Sender: TObject);
begin
  ClientDataSet1.ApplyUpdates(-1);
end;

At this stage your client form should look similarly to this:

Hide image

Now it is time to see our client in action!

Run the application, click on the “Active” check-box and you should see customer information in the grid!

Make some changes to customer data, click on the “Post” button in the navigator component to post changes to the in-memory ClientDataSet1 data and click on “Apply Updates” button to send changes to the server.

Re run the client application to verify that your changes have been saved in the database.

I have reorganized visual components a little bit and my final client application looks like this.

Hide image

That’s it!

We just created Delphi multitier DataSnap database solution with wizards, some components and just two lines of code!

That’s called Rapid Application Development or “RAD” for short.

    Summary

In this Delphi Lab we have used Delphi XE and InterBase XE for building a simple multitier database application consisting of server and client native Win32 applications communicating with each other using TCP/IP protocol.

The full source code for this article is available from Embarcadero Code Central http://cc.embarcadero.com/item/28188

The video version of steps described in this article can be found on YouTube http://www.youtube.com/watch?v=B4uxLLIUddg.

More information about Delphi can be found on the Delphi home page http://www.embarcadero.com/products/delphi

Server Response from: ETNASC03