Accessing DB2 Universal Database tables using ASP.NET Web Services written in Delphi 8 for .NET

This article shows how to use Delphi 8 for .NET to build an ASP.NET Web Service application that publishes the data tables from the DB2 UDB SAMPLE database to the outside world.

Bob Swart (drbob@chello.nl), Author, Trainer, Consultant, and Webmaster, Bob Swart Training and Consultancy

Bob Swart (aka Dr.Bob - www.drbob42.com) is an author, trainer, consultant and Webmaster working for his own company, called Bob Swart Training & Consultancy (eBob42), in Helmond, The Netherlands. Bob, who writes his own Delphi training material, has spoken at Delphi and Borland Developer Conferences since 1993. Bob has written hundreds of articles, and is co-author of the Revolutionary Guide to Delphi 2, Delphi 4 Unleashed, C++Builder 4 Unleashed, C++Builder 5 Developer's Guide, Kylix Developer's Guide, Delphi 6 Developer's Guide, and the C++Builder 6 Developer's Guide.



26 February 2004

Introduction

In this article, I will demonstrate how you can use Borland® Delphi™ 8 for the Microsoft® .NET Framework (abbreviated to "Delphi 8 for .NET" from now on) to build an ASP.NET Web Service application that will publish the data tables from the IBM® DB2® Universal Database™ (UDB) SAMPLE database.

Apart from building the ASP.NET Web Service, I will also show how to build a Windows Forms client application with Delphi 8 for .NET that uses the ASP.NET Web Service to access the UDB SAMPLE database tables remotely.

In a follow-up article, I'll cover some enhancements by extending the ASP.NET Web Service with the ability to update the database, allowing the client to send updates through the ASP.NET Web Service.


Creating ASP.NET Web Services

Web Services can be used to connect different applications with each other (for example, B2B transactions), or split one application into a multi-tier or distributed application (a multi-tier application with the individual tiers running on different machines). In this article, I will build an ASP.NET Web Service that connects to the UDB SAMPLE database, shielding the actual database connectivity details from the outside world - and the clients that use this ASP.NET Web Service to get access to the actual database tables.

ASP.NET is an integral part of the .NET Framework, and you can use Delphi 8 for .NET (or Borland® C#Builder™) to build the ASP.NET Web Services. So, start Delphi 8 for .NET, and do File | New - Other to get to the Object Repository with the available targets for new application. Select the ASP.NET Web Service Application target in the ASP.NET section. This choice will result in the new ASP.NET Application dialog, as can be seen in Figure 1 below.

Figure 1. New ASP.NET Application
Figure 1. New ASP.NET Application

The name for the project that you specify here will also be used as the name for the virtual directory on your development machine (and you also need to create a similar virtual directory on the actual deployment Web server where you can install this Web Service). For our example, I'm using the name DB2SAMPLE.

Note that I've selected Internet Information Server (IIS) to be the Web server to test against. You can also use Cassini, the free personal Web server, to test the Web Service in case IIS is not available on your development machine (for example, if you're using Windows XP Home edition).


Identifying the Web Service

When you click on OK, a new ASP.NET Web Service project is created. It includes a file WebService1.pas and .asmx, that you may want to rename as DB2WebService. The resulting DB2WebService.pas file contains the actual Web Service of type TWebService1. This will be the name that is published to the outside world, including your clients, so you should use a more descriptive name. Using a global search and replace, you can rename TWebService1 to something like TDB2Data. Note that the T-prefix is actually a legacy from the Delphi developer's world (the "T" stands for "Type"), and you can decide not to use it if you want to blend in with the .NET world, or use it and be proud to use Delphi.

Apart from the name of the Web Service, you should also add a unique namespace to it. This is especially important if people want to use your Web Service - in this case, TDB2Data - and happen to encounter another Web Service that has the same name. This is unlikely, but not impossible. The combination of a Web Service name with a namespace makes this next to impossible - especially if the namespace is based on your company name, like eBob42 in my case.

The namespace can be defined as property of the WebService attribute, as follows:

[WebService(Namespace='http://www.eBob42.org',
 Description='IBM DB2 UDB SAMPLE Database Tables')]

The description property of the WebService attribute is used to further clarify the use of this Web Service.


Defining the Web Service

A Web Service can contain many methods, but only the methods marked with the [WebMethod] attribute are actually made visible as Web Service methods, callable from the outside world. There's already a sample method HelloWorld - placed in comments - in the Web Service project generated by Delphi 8 for .NET. However, you don't need that sample method, so remove it (and remove it from the implementation as well), and add your own two method definitions to the TDB2Data Web Service class, as follows:

  [WebService(Namespace='http://www.eBob42.org',
   Description='IBM DB2 UDB SAMPLE Database Tables')]
  TDB2Data = class(System.Web.Services.WebService)
  ...
  public
    constructor Create; { Generic BdpConnection interface } [WebMethod] function GetTableNames: String; [WebMethod] function GetDataSet(TableName: String; start, max: Integer): DataSet; end;

The method GetTableNames will be used to return a list of available tablenames in the DB2 UDB SAMPLE database, in a comma-delimited string. The method GetDataSet will use the passed tablename to perform a "select * from tablename" returning a dataset that contains a datatable with max records starting at position start in the resultset of the query. So you can actually use this method to get the first 10 records, the next 10 records, etc.


Connecting to DB2

Both Web methods need to connect to the UDB SAMPLE database to be able to produce their result. This means that you need a BdpConnection component to share this connection. You can either add it to the TDB2Data Web Service definition, or - more conveniently - move to the design view of the Web Service and place the BdpConnection component on it. In fact, instead of simply placing a BdpConnection component from the Tool Palette, I suggest you use the Data Explorer (in the upper-right corner of the Delphi 8 for .NET IDE), and drag the DB2Connection component that is already configured to connect to the UDB SAMPLE database, and drop it on the design view (read Accessing DB2 Universal Database Tables with Delphi for .NET if you need any support with connecting the BdpConnection component to the UDB SAMPLE database).


Implementing the Web Service

With the BdpConnection to DB2 in place, you can implement the two Web methods that you've just defined. First, you need to add both the System.Text and the Borland.Data.Schema units to the uses clause (of the implementation section). The first one is needed for the StringBuilder class, the second one for the ISQLMetaData definition.

The method GetTableNames can call the GetMetaData function of the BdpConnection component to obtain the meta data information. This meta data contains a function that will return the tablenames, based on a name or specified type. The result of the GetTables function is actually a DataTable. You then need to walk through the rows of the DataTable and view the third field (with the actual tablename) and append the tablename to a stringbuilder class. The complete code is as follows:

function TDB2Data.GetTableNames: String;
var 
  MetaData: ISQLMetaData;
  Tables: DataTable;
  S: StringBuilder;
  i: Integer;
begin
  BdpConnection1.Open;
  try
    MetaData := BdpConnection1.GetMetaData;
    Tables := MetaData.GetTables('', TableType.Table);
    S := StringBuilder.Create;
    for i:=0 to Tables.Rows.Count-1 do
    begin S.Append(Tables.Rows[i].Item[3, DataRowVersion.Current]); if i < Tables.Rows.Count-1 then S.Append(',') end; Result := S.ToString finally BdpConnection1.Close end
end;

Note that there's no exception handling (apart from the try-finally block), so if anything goes wrong, the exception error message will be returned rather than a list of available tablenames.

The other method, GetDataSet, takes one of the tablenames that is returned by GetTableNames, and uses it to produce a "select * from " tablename query. With that query as parameter, you can create an instance of a BdpDataAdapter component and use it to Fill the DataSet that is returned by this method. The Fill method of the BdpDataAdapter allows you the option of specifying a starting position and a maximum number of records that are returned, and that's where you can use the start and max arguments as well. The complete code is as follows:

function TDB2Data.GetDataSet(TableName: &String; start, max: Integer): DataSet;
var
  DataAdapter: BdpDataAdapter;
begin
  Result := DataSet.Create;
  DataAdapter := BdpDataAdapter.Create('select * from ' + TableName,
    BdpConnection1);
  BdpConnection1.Open;
  try
    DataAdapter.Fill(Result, start, max, TableName)
  finally
    BdpConnection1.Close;
    DataAdapter.Free
  end
end;

Note that you don't have to open the BdpConnection before you create the BdpDataAdapter, but only just before you call the Fill method to produce the DataTable with the specified name in the DataSet that is returned as a function result.


Rethinking the Web Service

The two methods above can be tested in a local browser, and will work just fine. However, in a real-world environment, the second method is a bit too dangerous to allow it to be published. Passing a TableName as argument to a function that will execute an SQL statement is just asking for abuse. It would be easy to pass an "EMPLOYEE; DROP TABLE" as table name - trying to turn one SQL statement into two. And this is just one example of the security issues that you open yourself to when you allow Web Service clients to pass literal tablenames that are inserted into SQL statements.

In practice, you want to offer the ability to get access to X records from table Y starting at position Z, but without giving up the security of your database in the first place. The obvious solution is to call the first Web method that returns the list of TableNames, and use these tablenames to produce specific methods that will be bound - hardcoded - to those tablenames. For the UDB SAMPLE database, the tablenames are CL_SCHED, DEPARTMENT, EMP_ACT, EMP_PHOTO, EMP_RESUME, EMPLOYEE, IN_TRAY, ORG, PROJECT ,SALES, and STAFF.

This results in the following eleven new method definitions inside the TDB2Data Web Service class (note that I've abbreviated the description property of the WebMethod attribute for readability):

{ Specific DB2 UDB SAMPLE Database interface }
[WebMethod(Description='Return the CL_SCHED table, ...')]
function GetCL_SCHED(start, max: Integer): DataSet;
[WebMethod(Description='Return the DEPARTMENT table, ...')]
function GetDEPARTMENT(start, max: Integer): DataSet;
[WebMethod(Description='Return the EMP_ACT table,  ...')]
function GetEMP_ACT(start, max: Integer): DataSet;
[WebMethod(Description='Return the EMP_PHOTO table,  ...')]
function GetEMP_PHOTO(start, max: Integer): DataSet;
[WebMethod(Description='Return the EMP_RESUME table,  ...')]
function GetEMP_RESUME(start, max: Integer): DataSet;
[WebMethod(Description='Return the EMPLOYEE table,  ...')]
function GetEMPLOYEE(start, max: Integer): DataSet;
[WebMethod(Description='Return the IN_TRAY table,  ...')]
function GetIN_TRAY(start, max: Integer): DataSet;
[WebMethod(Description='Return the ORG table,  ...')]
function GetORG(start, max: Integer): DataSet;
[WebMethod(Description='Return the PROJECT table,  ...')]
function GetPROJECT(start, max: Integer): DataSet;
[WebMethod(Description='Return the SALES table,  ...')]
function GetSALES(start, max: Integer): DataSet;
[WebMethod(Description='Return the STAFF table,  ...')]
function GetSTAFF(start, max: Integer): DataSet;

Apart from adding these eleven new methods, you should also ensure that the [WebMethod] attribute is removed from the line preceding the GetDataSet method, since that method is considered too dangerous to be published to the outside world. However, you should not completely remove that method from the Web Service itself, but leave it as an "internal" method. The eleven new methods will gladly call it to produce their result, and this time safely, since it's the Web Service itself that passes the table names, as can be seen in their implementation below:

{ Specific DB2 UDB SAMPLE Database interface }

functionTDB2Data.GetCL_SCHED(start, max: Integer): DataSet;
begin
  Result := GetDataSet('CL_SCHED', start, max)
end;

function TDB2Data.GetDEPARTMENT(start, max: Integer): DataSet; begin Result := GetDataSet('DEPARTMENT', start, max) end;

function TDB2Data.GetEMP_ACT(start, max: Integer): DataSet; begin Result := GetDataSet('EMP_ACT', start, max) end;

function TDB2Data.GetEMP_PHOTO(start, max: Integer): DataSet; begin Result := GetDataSet('EMP_PHOTO', start, max) end;

functionTDB2Data.GetEMP_RESUME(start, max: Integer): DataSet; begin Result := GetDataSet('EMP_RESUME', start, max) end;

function TDB2Data.GetEMPLOYEE(start, max: Integer): DataSet; begin Result := GetDataSet('EMPLOYEE', start, max) end;

function TDB2Data.GetIN_TRAY(start, max: Integer): DataSet; begin Result := GetDataSet('IN_TRAY', start, max) end;

function TDB2Data.GetORG(start, max: Integer): DataSet; begin Result := GetDataSet('ORG', start, max) end;

function TDB2Data.GetPROJECT(start, max: Integer): DataSet; begin Result := GetDataSet('PROJECT', start, max) end;

function TDB2Data.GetSALES(start, max: Integer): DataSet; begin Result := GetDataSet('SALES', start, max) end;

function TDB2Data.GetSTAFF(start, max: Integer): DataSet; begin Result := GetDataSet('STAFF', start, max) end;

As you can see, the GetDataSet method is still needed internally to keep the source code comprehensible and maintainable.


Testing the Web Service

Now it's time to save your work, compile the application, and test it from the Delphi 8 for .NET IDE. You can do this by using the Run menu - either calling Run | Run (with the debugger), or - a bit faster - Run | Run without Debugging. Either way, this should produce your default Web browser with the URL of the Web Service, showing the 12 methods that you now have to remotely access the UDB SAMPLE database.

Figure 2. Testing TDB2Data Web Service
Figure 2. Testing TDB2Data Web Service

As an example, you can click on the GetEMPLOYEE method, which will allow you to call the method with sample values for the start and max arguments. Note that this testing can only be done locally (with the Web Service hosted on localhost), unless you edit the Web.config file to allow remote connections to test the Web Service as well.

Figure 3. Testing the GetEMPLOYEE method
Figure 3. Testing the GetEMPLOYEE method

You can specify some values for start and max, and click on the Invoke button to get a new page with the contents of "select * from EMPLOYEE" query, starting at start and returning max records.

When you have verified that this works as planned, it's time to build the client application that connects to this ASP.NET Web Service to get access to the UDB SAMPLE database tables.


Building the Web Service Client

For the Web Service Client, you need to start a new project, for example a WinForms application. Do File | New Windows Forms Application to create one. Save it in DB2WSClient, and right-click on the project node in the Project Manager to select the Add Web Reference menu option. This will allow you to add a reference to a Web Service to your project - helping you to import and use (consume) the TDB2Data Web Service.

Adding the Web Reference

The Add Web Reference option results in a dialog with the same name that you can use to locate Web Services using UDDI (Universal Description Discovery and Integration) or through a direct URL to the Web Service, if you know the location of its WSDL (Web Service Description Language).

Figure 4. Add Web Reference
Figure 4. Add Web Reference

In your case, the location of the WSDL for the TDB2Data Web Service is http://localhost/DB2SAMPLE/DB2WebService.asmx?WSDL on your local development machine, so enter that location and press enter (or click on the blue arrow) to make the Add Web Reference dialog list the WSDL and enable the Add Reference button at the lower-right corner of the dialog:

Figure 5. Add Web Reference of your WSDL
Figure 5. Add Web Reference of your WSDL

As soon as you click on the Add Reference button, the WSDL is parsed and a special import unit is generated and added to your Delphi 8 for .NET project.

Figure 6. Web Reference
Figure 6. Web Reference

Using the Web Service

Once the Web reference is added, you can actually use it. First, add the unit localhost.DB2WebService to the uses clause of the WinForms application. This will ensure that you can see and use the class definition TDB2Data inside the generated import unit. Note that although this classname is the same classname as the one you defined in the ASP.NET Web Service, it's really a proxy class that will communicate to the actual ASP.NET Web Service. However, as far as the client application is concerned, it's just as if a local object is present, and you can call the methods as if they are local methods.

In order to use the TDB2Data Web Service in the client application, I've added a private field in the definition of the WinForm, called DB2SAMPLE of type TDB2Data. This field will hold the instance to the proxy of the Web Service, and is created in the Load event of the WinForm with one line of code, as follows:

procedure TWinForm.TWinForm_Load(sender: System.Object; e: System.EventArgs);
begin
  DB2SAMPLE := TDB2Data.Create
end;

Now you can use the DB2SAMPLE field and call the methods like GetEMPLOYEE whenever you want.


Connecting to the DB2 EMPLOYEE Table

In order to actually show the data, drop a DataGrid control. Normally, when using the Borland Data Provider (BDP) components, you would connect the DataGrid to a DataSet at design-time. However, at this time you do not have a DataSet available. And furthermore, the client application doesn't even use the Borland Data Provider for .NET. In fact, the client application is a truly thin-client application since it doesn't even need (or want) the DB2-specific database drivers. The client just calls the DB2SAMPLE Web Services to get a native .NET dataset, which could be from a UDB SAMPLE database, or any other database for that matter. The Web Service is the only tier of this multi-tier application architecture that uses the DB2-specific database drivers.

Right under the DataGrid, drop three buttons, and call them btnFirst, btnNext, and btnPrev. These buttons will use the DB2SAMPLE Web Service and call the GetEMPLOYEE method, each getting 10 records. The first one will get the first 10 records, the next one will get the next 10 records, and the last one will get the previous 10 records again. This means you need to store a counter with the value of the CurrentRecord, so also add that as a private field to the WinForm class definition.

You can then implement the three Click events of the btnFirst, btnNext and btnPrev buttons as follows:

procedure TWinForm.btnFirst_Click(sender: System.Object; e: System.EventArgs);
begin
  CurrentRecord := 0;
  DataGrid1.DataSource := DB2SAMPLE.GetEMPLOYEE(CurrentRecord,10);
  DataGrid1.DataMember := 'EMPLOYEE'
end;
procedure TWinForm.btnNext_Click(sender: System.Object; e: System.EventArgs); begin CurrentRecord := CurrentRecord + 10; DataGrid1.DataSource := DB2SAMPLE.GetEMPLOYEE(CurrentRecord,10); DataGrid1.DataMember := 'EMPLOYEE' end;
procedure TWinForm.btnPrev_Click(sender: System.Object; e: System.EventArgs); begin CurrentRecord := CurrentRecord - 10; if CurrentRecord < 0 then CurrentRecord := 0; DataGrid1.DataSource := DB2SAMPLE.GetEMPLOYEE(CurrentRecord,10); DataGrid1.DataMember := 'EMPLOYEE' end;

Note that I directly assign the result from the GetEMPLOYEE method to the DataSource property of the DataGrid, and then specify the name of the DataTable (which is set to the actual tablename EMPLOYEE in this example). The CurrentRecord field will maintain the index of the first record in the DataGrid, so you can navigate from page to page.


Showing the DB2 Data

Now it's time to compile and run the application. It will show an empty grid and three buttons at first. But as soon as you click on the first button, it will connect to the DB2SAMPLE Web Service and retrieve the first 10 records.

Figure 7. First 10 records in thin-client
Figure 7. First 10 records in thin-client

Clicking on the Next and Prev buttons will give you 10 other records each. Note that the DataSource property of the DataGrid is assigned every time, ignoring the previous value of the DataSource, so you will only see a maximum of 10 records at a given time - never more than 10 (you could see fewer if the last page contains less than 10 records, of course).

There are a number of things to keep in mind: the WinForms client application currently only retrieves data from the DB2SAMPLE Web Service. This means that while you can make changes to the data in the DataGrid, you cannot send these changes back to the Web Service or to the UDB SAMPLE database table. This is something that I'll implement in a follow-up article that extends both the DB2SAMPLE Web Service and the WinForms client application. As a workaround to let the end user know that the DataGrid only holds "readonly" data, you should set the ReadOnly property of the DataGrid to True (this will prevent changes from being made at the client side in the first place).

Another thing worth noting is the fact that the Web Service completely shields the WinForm client from the UDB Database, in a way that makes sure there can be no direct access by the client (application) on the database. The only thing you may want to consider is adding an authentication layer DB2SAMPLE Web Service, since anyone can connect to this Web Service to retrieve the data from the SAMPLE tables. And in this case the data is public (and not secret or sensitive), but if you're dealing with sensitive data you have to add some kind of protection.


Summary

In this article, I have shown how to use Delphi 8 for .NET to build an ASP.NET Web Service application that publishes the data tables from the DB2 UDB SAMPLE database to the outside world. I have also shown how to build a Windows Forms client application with Delphi 8 for .NET that uses this ASP.NET Web Service to access the DB2 UDB SAMPLE database tables remotely, display the data in a DataGrid, and navigate through the grid by requesting next and previous pages of 10 records.

In a follow-up article, I'll cover some enhancements by extending the ASP.NET Web Service with the ability to update the database, thereby allow the client to send updates through the ASP.NET Web Service. See you next time!


Download

DescriptionNameSize
Code samplesource.zip  ( HTTP | FTP )23.2KB

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=14634
ArticleTitle=Accessing DB2 Universal Database tables using ASP.NET Web Services written in Delphi 8 for .NET
publish-date=02262004