Friday, September 12, 2008

Multi-tier DB2 database applications in Delphi 2005

Original Source click here

Level: Intermediate

Bob Swart (, Trainer and Consultant, Bob Swart Training and Consultancy

05 Jan 2006

Learn how to use the DataSync and DataHub components in Delphi 2005 to access data in IBM® DB2® Universal Database™ (DB2 UDB) database tables using the Borland Data Provider. Other ADO.NET data providers can be used here (like IBM's own .NET® provider for DB2 UDB). This article first reviews the steps to build a simple database application, then examines the application in two parts: a server part that communicates with the IBM DB2 UDB database (using the DataSync component), and a client part that is independent of the database, and communicates only with the server application. Two new Delphi 2005 components are used to connect the client and the server: a RemoteServer at the server side, and a RemoteConnection at the client side.
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi

Using BDP and the DB2 UDB SAMPLE database

In this article, we will use the Borland Data Provider for ADO.NET, which is part of Borland's C#Builder, Delphi 8 for .NET, Delphi 2005, and the newly released Delphi 2006. The later two have both Win32 and .NET personalities, but in this article I want to focus specifically on the .NET capabilities to build multi-tier applications.

For the examples in this article, we'll use the DB2 UDB SAMPLE database again.

Building the .NET Remoting Server

Let's start by building the .NET Remoting Server using Borland Delphi 2005 (the Enterprise or Architect edition). Start Delphi 2005 and create a new Windows® Forms Application -- Delphi for .NET -- application. This will be the "shell" for our .NET Remoting Server functionalities. Save the project as D2005DB2Server, and the WinForm as ServerForm. You can change the caption of the WinForm to something meaningful, although the only purpose of this application is to connect to the DB2 UDB SAMPLE database on one end, and the .NET Remoting clients on the other end. There will be no end-user interaction with this tier.

Back to top

Connecting to the database

In previous articles using Delphi for .NET, we've used the Borland Data Provider for .NET, which contains drivers for DB2, SQL Server, Oracle, InterBase and other database systems. The Borland Data Provider comes with one set of components (with the Bdp prefix) that can connect to any of the supported databases, and can switch just by changing the connection string. This in contrast with the standard ADO.NET Data Providers that come with a specific set of components for each database.

From the Borland Data Provider category in the Tool Palette, double-click on the BdpConnection component. This will place the component in the non-visual component area at the bottom of the WinForms designer. Now, right-click on this component and select the Connection Editor option to configure the connection. Select the DB2 connection to the DB2 UDB SAMPLE database, or configure it as shown in Figure 1.

Figure 1. BDP Connections Editor
BDP Connections Editor

Use the Test button to verify that a connection can be made to the DB2 UDB SAMPLE database. Then, click on OK to close the Connections Editor.

Time to add a BdpDataAdapter component in order to retrieve data. From the Borland Data Provider category in the Tool Palette again, double-click on the BdpDataAdapter component. This will also place the component in the non-visual component area at the bottom of the WinForms designer. Right-click on the BdpDataAdapter and select the Data Adapter Configuration dialog.

So far, it's not much different from what we could do with C#Builder or Delphi 8. However, from this moment on we'll start to use new features and functionality. In the Data Adapter Configuration dialog, you'll notice that it's automatically hooked up to the BdpConnection component, and you'll see a list of Tables and Fields in the two listboxes. Select the EMPLOYEE table and the * for all fields; uncheck the checkboxes for the Insert, Update and Delete commands; and click on the Generate SQL button. The result is shown in Figure 2.

Figure 2. Data Adapter Configuration
Data Adapter Configuration

Now, instead of going to the DataSet tab and specifying a new or existing .NET DataSet to hold the result of the SELECT command, just click on OK to close the Data Adapter Configuration dialog.

Instead of using a .NET DataSet directly, we'll use two new Delphi 2005 components: DataSync and DataHub (available only in the Enterprise or Architect editions).

Back to top

Introducing DataSync and DataHub

Delphi 2005 introduces two components called the DataSync and DataHub that can use both the BDP .NET Provider and any of the ADO.NET Data Providers. In fact, they can work simultaneously with multiple different ADO.NET Data Providers, which can be useful if you want to combine the DB2 UDB SAMPLE database with another database (either an DB2 UDB database or an entirely different database type). For the .NET Remoting Server, we need a DataSync component, while the DataHub comes back in the .NET Remoting Client tier.

From the Borland Data Provider category in the Tool Palette, double-click on the DataSync component. This will place the component in the non-visual component area at the bottom of the WinForms designer. Make sure the DataSync component is selected, and then double-click on its Providers property in the Object Inspector. This will pop-up the DataProvider Collection Editor dialog where we can add different Data Providers, each hooked up to a BDP or ADO.NET Data Provider. At this time (for this example), we only use one BDP Data Provider, but the dialog allows you to use multiple data providers, as mentioned before.

In the DataProvider Collection Editor, click on the Add button to create new DataProviders. For each new DataProvider, you can hook it up to a DataAdapter, like the BdpDataAdapter (that holds the SELECT from the EMPLOYEE table). You can also edit the TableName property and enter a more useful name (like TableEmployees).

Finally, there's the UpdateMode property, which can be set to Key, All or Changed (see Figure 3).

Figure 3. DataProvider Collection Editor
DataProvider Collection Editor

The possible values for the UpdateMode property of the DataProvider reflect the contents of the generated SQL command when applying updates to the server. When you select Key, then only the Key field will be passed in the WHERE clause of the Update command to locate the record to update. With a value of All, all fields will be passed in the WHERE clause of the Update command. With a value of Changed, only the Key fields and the original values of the fields that were changed are passed in the WHERE clause. The unchanged fields are not used in the WHERE clause, since these fields are not updated in the first place. This is faster than using All, and safer than using Key. The possibility of having UpdateMode set to Changed is a great improvement over Key or All, so I recommend using Changed at all times.

Note that Changed can only be used with BDP Data Adapters, and not with regular ADO.NET Data Adapters. In the latter case, a CommandBuilder will be used to generate the Update command (using either Key or All).

Click on OK to close the DataProvider Collection Editor dialog when you're ready.

Back to top

Configuring the RemoteServer

Time to turn the WinForms application into a true .NET Remoting Server. For this, we need another new component from the Borland Data Provider category of the Tool Palette: the RemoteServer component. Double-click on its icon in order to add it to the non-visual components area of the WinForms designer.

The RemoteServer component is the one receiving the incoming requests from .NET Remoting clients. Using the ChannelType property, we can configure it to use either HTTP or TCP as communication protocol. When using HTTP, the message format is SOAP; when using TCP, the message format is binary. By default, the ChannelType property is set to Http, so we'll be using SOAP as message format. Feel free to change that, but remember that you need to specify the same ChannelType property value in the .NET Remoting clients later!

It's more important at this time to assign the DataSync property of the RemoteServer component, and point it to the DataSync component on our WinForm. Setting the AutoStart property to True will make sure that the RemoteServer component is active the moment the application itself starts. Otherwise you need to activate it by calling the Start method (and you can also call the Stop or Pause methods).

A final property of the RemoteServer component that you can change from its default value is the URI property. The default value of the URI property is RemoteServer1, but you can change it to anything you wish (just remember to use the same value for the URI property in the .NET Remoting client in a minute). For example, you can change it to RemoteServerDB2.

Now, save the project and compile it. You can run the .NET Remoting server -- preferably outside of the IDE so we can start to work on the client. Make sure it remains up and running, so the .NET Remoting client can immediately connect to it.

Back to top

Building the .NET Remoting Client

Close the D2005DB2Server project, but make sure the executable itself remains loaded. Start a new Delphi for .NET application, for example another Windows Forms Application. Save it as D2005DB2Client and the WinForm as ClientForm.

Back to top

Connecting to the Server

The first thing we need to do is establish a connection to the D2005DB2Server application. For this, we need a RemoteConnection component from the Borland Data Provider category of the Tool Palette. Double-click on this component to place it on the WinForm. The URI property of the RemoteConnection component is an important one: here we must specify the exact URI value that we used in the RemoteServer component. I changed it to RemoteServerDB2, so here we must specify the same value. Another property that must be set to exactly the same value as in the D2005DB2Server is the ChannelType. By default it is set to HTTP, but if you changed it to TCP in the server, you need to change it to TCP here as well, of course.

In order to find out if you've set these properties correctly, and if you can connect to the D2005DB2Server, just try to open up the drop-down combobox for the ProviderType property of the RemoteConnection component. If it remains empty, you haven't set the right property values. Otherwise, you should be able to select the right provider type (a list with just one entry), namely Borland.Data.Provider.DataSync.

Back to top

Retrieving DB2 UDB database data

The next step consists of placing both a DataSync component (from the Borland Data Provider category of the Tool Palette) and a .NET DataSet component (from the Data Components category). After you've placed these two components, select the DataHub component and set its DataPort property to the RemoteConnection component. This will ensure that the DataHub receives data from the .NET Remoting server. Apart from the DataPort property, you must also assign a value to the DataSet property of the DataHub component by pointing the later to the .NET DataSet you've placed on the WinForm.

Once these properties are assigned, you can use the Active property to activate the DataHub. But first let's add some visual components to view the data in the .NET Remoting client. Place a DataGrid component (from the Data Controls category) on the WinForm, as well as two Button controls. The DataGrid can only be connected to the DataSet and the DataTable inside the DataSet at design-time if we activate the DataHub at design-time. This might seem like a good idea, but activating the DataHub at design-time means that it tries to establish the connection with the .NET Remoting server. At design-time. And if the .NET Remoting Server cannot be found, this will result in a delay before an error message is given that it cannot connect to the .NET Remoting Server. This will happen if you open the D2005DB2Client project without starting the D2005DB2Server executable first. For that reason, I usually activate the DataHub component only at runtime, for example in response to a Button Click event.

We'll use the first Button to establish the connection. Set its Text property to "Connect" and write the following code in its Click event handler (double-click on the Button to get to the code editor):

Listing 1. Connecting to server

procedure TWinForm2.Button1_Click(sender: System.Object; e: System.EventArgs);
DataHub1.Active := True;
DataGrid1.DataSource := DataSet1;
DataGrid1.DataMember := DataSet1.Tables[0].TableName;
// you can also hardcode the above to 'EmployeeTable'

Note that instead of using the DataSet1.Tables[0].TableName construct to get the actual TableName of the DataTable inside the .NET DataSet, you can also hardcode the name of the table (as specified in the .NET Remoting Server), which is EmployeeTable.

Now, save the project, compile and run the .NET Remoting client application (make sure the .NET Remoting Server is already running). Once you click on the Connect button, the data will be shown inside the DataGrid.

Figure 4. .NET Remoting Client connected to .NET Remoting Server
.NET Remoting Client connected to .NET Remoting Server

Note that the .NET Remoting client application can be seen as a thin-client (or smart-client), since it doesn't (need to) know which (kind of) database it's connected to, nor does it need any database drivers. It only knows that it's connected to a .NET Remoting Server application (which at this time runs on the same machine, but we'll extend the example to work on different machines at the end of this article).

Back to top

Applying updates to the DB2 database

There's one Button left, and one thing left to do: when you make changes to the data in the DataGrid and close the .NET Remoting client, the changes are gone. There is no way in our example project -- yet -- to save the changes or send them back to the DB2 UDB SAMPLE database. Fortunately, it's really easy to collect the changes and send them to the server, and that's what we'll use the second Button for. Set the Text property of this second Button to Update, and write the following code in the Click event handler of this second Button:

Listing 2. Applying updates to Server

procedure TWinForm2.Button2_Click(sender: System.Object; e: System.EventArgs);

One line of code?! Yes, that's it. The DataHub (at the .NET Remoting client) will send the modified records to the DataSync component (at the .NET Remoting server), using the RemoteConnection which is talking to the RemoteServer component. Back at the .NET Remoting Server, the DataSync will use the received changes to update the DB2 UDB SAMPLE database, using the specified value of the UpdateMode property of the DataProvider to let the later generate the optimized SQL commands.

Back to top

Connect to a different machine

So far, I've kept it simple by connecting the .NET Remoting Client to the .NET Remoting Server running on the same machine. And although this results in a "thin" client, it's not really very useful since the fat server is running on the same machine. Fortunately, it's easy to expand this example to a truly remote example: just run the .NET Remoting Server on the target machine (where it can still connect to the DB2 UDB SAMPLE database, of course), and in the .NET Remoting client application make sure to specify the name of the server machine in the Host property of the RemoteConnection component. Optionally, you can also set the Port property (make sure both the server and the client use the same value for the Port property, but that should speak for itself, I trust). You can connect one or more than one .NET Remoting Client to the same .NET Remoting Server.

Note that error reconciliation (when two or more people are modifying the same record) is not generally available in ADO.NET, but has been added to Delphi 2006 as one of the new features in the Borland Data Provider for .NET. This feature will be covered in more detail at some other time, however.

Back to top


In this article, we've used the new DataSync and DataHub components in Delphi 2005 to demonstrate data access on DB2 UDB database tables using the Borland Data Provider. With the use of the RemoteServer and RemoteConnection components, this can lead to multi-tier applications using .NET Remoting. With this technology we can build thin .NET client applications that connect to .NET Remoting servers, exposing the DB2 UDB databases to the outside world, and making it available for the client applications to work with even from different machines.

Back to top

Next time

Next time we'll continue with two articles about Delphi 2005 and Enterprise Core Objects II -- building an UML model on top of the DB2 UDB SAMPLE database, producing WinForms or ASP.NET Web Forms applications.

Back to top