DataSnap and Advantage

Tuesday, March 01, 2011

Embarcadero's DataSnap is a Delphi technology that allows the development of multi-tier database applications. All of Embarcadero's DataSnap tutorials use DBExpress when connecting to a database. But since there’s no DBX driver for Advantage available, a lot of developers struggle on how to connect to ADS and retrieve data from their databases.  In this Tech Tip, we will demonstrate how to build multi-tier database applications with Delphi XE using DataSnap and the Advantage Delphi Components. We will use ADTDemoData, a set of demo tables that are shipped with most of the Advantage installs in the example. In addition to this example, a whitepaper Advantage Database Server and DataSnap - Good Friends! written by Dr. Bob is available.

The easiest way to create a DataSnap server is to use Delphi XE’s DataSnap Server wizard. Select File | New | Other and from the New Items dialog navigate to Delphi Projects | DataSnap Server category and double-click the DataSnap Server icon (see figure 1).

 
Figure 1: DataSnap Server in Project Templates

This opens the DataSnap Server wizard. You can use the default settings on the first three pages of the wizard.

Figure 2: Project Type

Figure 3: Server Features

Figure 4: Port Settings

On the last tab make sure to select TDSServerModule as the base class for our server methods class, then click Finish and the wizard should create a new project with three units.

Figure 5: Select Ancestor Class

View ServerMethodsUnit1 as a Form and add one TAdsConnection, one TAdsTable and one TDataSetProvider to the form.

Figure 6: Server Methods

Double-click inside the form to create an event handler for the form’s OnCreate event. We will use this event handler to connect the database:

procedure TServerMethods1.DSServerModuleCreate(Sender: TObject);
begin
  AdsConnection1.AliasName:='ADTDemoData';
  AdsConnection1.Connect;
  AdsTable1.AdsConnection:=AdsConnection1;
  AdsTable1.TableName:='employee';
  AdsTable1.Open;
  DataSetProvider1.DataSet:=AdsTable1;
end;

Save all (Shift+Ctrl+S) and then compile (Ctrl-F9) and run the server without debugging (Shift+Ctrl+F9).

Now right-click on your project group inside the Project Manager and select Add New Project.

Figure 7: New Project

From the New Items dialog select VCL Forms Application from the Delphi Projects category. Add the following components to the form. An example is shown below.

• TSQLConnection
• TDSProviderConnection
• TClientDataSet
• TDataSource
• TDBNavigator
• TDBGrid
• TCheckBox
• TButton

Figure 8: DataSnap Client in Designer

Double-click inside the form to create an event handler for the OnCreate event. We will use this event handler to set up our components. It is also possible to set all properties using Delphi’s Object Inspector, but setting all properties in code makes it easier to follow.

procedure TForm2.FormCreate(Sender: TObject);
begin
  SQLConnection1.DriverName:='Datasnap';
  SQLConnection1.LoginPrompt:=False;
  DSProviderConnection1.SQLConnection:=SQLConnection1;
  DSProviderConnection1.ServerClassName:='TServerMethods1';
  ClientDataSet1.RemoteServer:=DSProviderConnection1;
  ClientDataSet1.ProviderName:='DataSetProvider1';
  DataSource1.DataSet:=ClientDataSet1;
  DBGrid1.DataSource:=DataSource1;
  DBNavigator1.DataSource:=DataSource1;
  CheckBox1.Caption:='Active';
  Button1.Caption:='Apply Updates';
end;

A double-click on the checkbox will create an event handler for its default event which is the OnClick event. Inside this event handler we will activate and de-activate the ClientDataSet:

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

Create a default handler for the button in the same way. When clicking the button, all updates in the local data store should be sent back to the DataSnap server.

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

Test the application 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.

Figure 9: DataSnap client application at runtime

You've seen how easy it is to create a multi-tier database application using Delphi XE's DataSnap and Advantage. Most of the code written was just to set component properties, which could also be done using Delphi’s object inspector. There’s no reason not to use ADS as the backend database for these types of applications since DBX is not required.

This article is based on a tutorial written by Embarcadero evangelist Pawel Glowacki and published on the Embarcadero Developer Network.