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

This article shows how to extend an ASP.NET Web Service application (covered in a previous article) to let clients update the database.

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.



04 March 2004

Introduction

In my previous article, I showed how to use Borland® Delphi™ 8 for the Microsoft® .NET Framework (abbreviated to "Delphi 8 for .NET") to build an ASP.NET Web Service application that publishes the data tables from the IBM® DB2® Universal Database™ (UDB) SAMPLE database. I also showed how to build a Windows Forms client application with Delphi 8 for .NET, that uses this ASP.NET Web Service to access the UDB SAMPLE database tables remotely.

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


Extending the ASP.NET Web Service

In this article, you'll use and extend the two projects from the previous article. You will start with the ASP.NET Web Service project DB2SAMPLE, so reopen that project in the Delphi 8 for .NET IDE. Go to the DB2WebService.pas unit, and look at the TDB2Data class definition. This Web Service class contains the eleven GetXXX methods, where XXX corresponds to the names of the tables inside the DB2 SAMPLE database. The Web Service client can receive those DataSets and view them in a DataGrid. However, there is currently no way that the client can send updates back to the DB2 SAMPLE database. In order to allow the client to do that, you need to add corresponding SetXXX methods, with the eleven tablenames for the XXX again, passing the DataSet with changes as arguments.

The definition of the new methods is as follows:

    { Specific DB2 UDB SAMPLE Database interface }
    [WebMethod(Description='Update the CL_SCHED table.')]
    procedure SetCL_SCHED(ClientDS: DataSet);
    [WebMethod(Description='Update the DEPARTMENT table.')]
    procedure SetDEPARTMENT(ClientDS: DataSet);
    [WebMethod(Description='Update the EMP_ACT table.')]
    procedureSetEMP_ACT(ClientDS: DataSet);
    [WebMethod(Description='Update the EMP_PHOTO table.')]
    procedure SetEMP_PHOTO(ClientDS: DataSet);
    [WebMethod(Description='Update the EMP_RESUME table.')]
    procedure SetEMP_RESUME(ClientDS: DataSet);
    [WebMethod(Description='Update the EMPLOYEE table.')]
    procedure SetEMPLOYEE(ClientDS: DataSet);
    [WebMethod(Description='Update the IN_TRAY table.')]
    procedure SetIN_TRAY(ClientDS: DataSet);
    [WebMethod(Description='Update the ORG table.')]
    procedure SetORG(ClientDS: DataSet);
    [WebMethod(Description='Update the PROJECT table.')]
    procedure SetPROJECT(ClientDS: DataSet);
    [WebMethod(Description='Update the SALES table.')]
    procedure SetSALES(ClientDS: DataSet);
    [WebMethod(Description='Update the STAFF table.')]
    procedure SetSTAFF(ClientDS: DataSet);

Note that these WebMethods are procedures; that is, they do not return a result in the form of a success or error message. You may want to consider adding this in your situation, although it's not hard to verify the results, as I'll demonstrate in a minute.

Note that the methods only get a DataSet as argument, not the original TableName (which is hardcoded inside the routine itself), and also not the original starting point of the maximum number of records that were placed in the DataSet in the first place. It turns out that this information is not needed in order to be able to update the database table. But before you can implement the eleven specific methods, you should first consider a more generic solution called SetDataSet. Like the GetDataSet from last time, this method is a bit too dangerous to be published as a Web method, so you'll keep it internal again. The definition of SetDataSet inside the TDB2Data class is as follows:

    { Generic BdpConnection interface }
//  [WebMethod]
    procedure SetDataSet(TableName: String; ClientDS: DataSet);

The SetDataSet procedure gets both the TableName and DataSet as arguments. Using this information, you can update the underlying database table.


Implementing the Update

For the implementation of the update, you first need to add the Borland.Data.Common unit to the uses clause, to enable you to create and use instances of BdpUpdateMode types. The generic implementation of SetDataSet creates an instance of the BdpDataAdapter the same way you did before: by passing a "select * from TableName" to the constructor. Don't worry, however; you won't actually execute the SELECT statement. It's only needed to make sure the BdpDataAdapter will "know" which table it is connected to, so the AutoUpdate method can do its job using the DataSet, TableName, and BdpUpdateMode set to All.

{ Generic BdpConnection interface }

procedure TDB2Data.SetDataSet(TableName: &String; ClientDS: DataSet);
var
  DataAdapter: BdpDataAdapter;
begin
  DataAdapter := BdpDataAdapter.Create('select * from ' + TableName,
    BdpConnection1);
  BdpConnection1.Open;
  try
    DataAdapter.AutoUpdate(ClientDS, TableName, BdpUpdateMode.All)
  finally
    BdpConnection1.Close;
    DataAdapter.Free
  end
end;

The AutoUpdate method of the BdpDataAdapter will use a BdpCommandBuilder to automatically generate the correct UPDATE, INSERT, and DELETE SQL statements that correspond to the SELECT statement - hence the reason I had to create the BdpDataAdapter with the "select * from TableName" as argument to the constructor.


Calling the Update from DB2 methods

With this generic SetDataSet method available, you can now implement the DB2 table specific methods, as follows:

{ Specific DB2 UDB SAMPLE Database interface }

procedure TDB2Data.SetCL_SCHED(ClientDS: DataSet);
begin
  SetDataSet(CL_SCHED, ClientDS)
end;

procedure TDB2Data.SetDEPARTMENT(ClientDS: DataSet); begin SetDataSet(DEPARTMENT, ClientDS) end;

procedure TDB2Data.SetEMP_ACT(ClientDS: DataSet); begin SetDataSet(EMP_ACT, ClientDS) end;

procedure TDB2Data.SetEMP_PHOTO(ClientDS: DataSet); begin SetDataSet(EMP_PHOTO, ClientDS) end;

procedure TDB2Data.SetEMP_RESUME(ClientDS: DataSet); begin SetDataSet(EMP_RESUME, ClientDS) end;

procedure TDB2Data.SetEMPLOYEE(ClientDS: DataSet); begin SetDataSet(EMPLOYEE, ClientDS) end;

procedure TDB2Data.SetIN_TRAY(ClientDS: DataSet); begin SetDataSet(IN_TRAY, ClientDS) end;

procedure TDB2Data.SetORG(ClientDS: DataSet); begin SetDataSet(ORG, ClientDS) end;

procedure TDB2Data.SetPROJECT(ClientDS: DataSet); begin SetDataSet(PROJECT, ClientDS) end;

procedure TDB2Data.SetSALES(ClientDS: DataSet); begin SetDataSet(SALES, ClientDS) end;

procedure TDB2Data.SetSTAFF(ClientDS: DataSet); begin SetDataSet(STAFF, ClientDS) end;

Note that tablenames are not simple strings anymore, like they were last time, but actual string constants, shared by the GetXXX and SetXXX methods.

const
  CL_SCHED = 'CL_SCHED';
  DEPARTMENT = 'DEPARTMENT';
  EMP_ACT = 'EMP_ACT';
  EMP_PHOTO = 'EMP_PHOTO';
  EMP_RESUME = 'EMP_RESUME';
  EMPLOYEE = 'EMPLOYEE';
  IN_TRAY = 'IN_TRAY';
  ORG = 'ORG';
  PROJECT = 'PROJECT';
  SALES = 'SALES';
  STAFF = 'STAFF';

This completes the changes that are required for the DB2SAMPLE Web Service, so save the project and recompile it.

You can test it from the Delphi 8 for .NET IDE again, and this time you should see 23 methods in total: GetTableNames, 11 GetXXX table methods and 11 SetXXX table methods.


Enhancing the Web Service client

It is now time to enhance the Web Service client. Open the DB2WSClient project from last time. The Web Reference node still contains the localhost subnode with the imported DB2Sample Web Service unit from last time. In order to make sure the client can work with the new and enhanced Web Service, you need to update the Web reference. Fortunately, you don't have to remove it and re-import it. You only have to right-click on the localhost node and select the Update Web Reference command. This will turn the mouse cursor into an hourglass for a few seconds, before it turns back to normal. During that time, the WSDL for the DB2Sample Web Service was reobtained (dynamically), and the corresponding import unit was regenerated. This is a convenient way to update the references to Web Services.

The Web Service import unit has just been updated, and the SetXXX methods are now available in the TDB2Data proxy class, so let's get ready to use them.


Editing the data in the grid

Before you start adding new components, first set the ReadOnly property of the DataGrid back to False again (you set it to True last time to make sure end users would not be able to edit the contents of the DataGrid, and only then discover that the updates could not be saved).

Now, drop two new buttons on the WinForm, calling them btnUndo and btnUpdate, and set their Enabled property to False, to make sure that they cannot be used initially (but only if there are any changes made in the DataSet, so they can be undone or sent as updates to the DB2 Web Service).

Figure 1. IDE with two new buttons in WinForm
Figure 1. IDE with two new buttons in WinForm

The implementation of the btnUndo Click event is normally very easy: just call the RejectChanges method of the DataSet. However, in this case, you obtained the DataSet through the call to GetEMPLOYEE (see last time) and directly assigned the result to the DataSource property of the DataGrid, without saving the DataSet itself anywhere. Now, before you start to rewrite the code from last time, I asked myself if it would be possible to just use the DataGrid's DataSource "as" a DataSet - using RTTI and .NET Reflection to make sure that you get out what you put in it: a .NET DataSet. That way, you can simply call the RejectChanges method on DataGrid.DataSource as DataSet, as shown below:

procedure TWinForm.btnUndo_Click(sender: System.Object; e: System.EventArgs);
begin
  (DataGrid1.DataSource as DataSet).RejectChanges;
  btnUndo.Enabled := False;
  btnUpdate.Enabled := False
end;

Note that you also disable the Undo and Update buttons, since all changes have been rejected.

In a similar way, you can implement the Click event for the Update button. This time, you can cast the DataGrid's DataSource to a DataSet and pass it as the argument to the SetEMPLOYEE method of the DB2SAMPLE Web Service, as follows:

procedure TWinForm.btnUpdate_Click(sender: System.Object; e: System.EventArgs);
begin
  DB2SAMPLE.SetEMPLOYEE(DataGrid1.DataSource as DataSet)
end;

This a good first attempt, but it's missing something. As I mentioned when you implemented the generic SetDataSet method in the TDB2Data Web Service class: I didn't add a way for (update) error messages to be passed from the Web Service to the client. And apart from that, the records that you're currently looking at - changed by ourselves or not - may have been changed by other users of the DB2WebService in the meantime. This means that a refresh of the contents of the DataSet is in order. And the easiest way to do that is to call the GetEMPLOYEE method again, passing the value of CurrentRecord to remain at the same position in the resultset (unless a record was inserted before the current position, which is not a very likely event, in my view).

This leads to the following amended implementation of the btnUpdate's Click event:

procedure TWinForm.btnUpdate_Click(sender: System.Object; e: System.EventArgs);
begin
  if (DataGrid1.DataSource as DataSet).HasChanges then
 begin DB2SAMPLE.SetEMPLOYEE(DataGrid1.DataSource as DataSet); DataGrid1.DataSource := DB2SAMPLE.GetEMPLOYEE(CurrentRecord,10); // Refresh DataGrid1.DataMember := 'EMPLOYEE' end
end;

Note that I've also included a test to see if the DataSet has any changes; this should always return true, since the Update button should only be enabled when there are changes in the dataset.

If you want to ensure that the two buttons are only enabled when the dataset contains changes, and are disabled if not, then you need to implement a few more events. The DataGrid offers a DataSourceChange event that is fired when a new DataSet is connected to the DataGrid's DataSource property (so Undo and Update should be disabled), and a CurrentCellChanged event, that is fired when a change is made in the DataGrid and posted to the underlying table (in which case Undo and Update should be enabled).

procedure TWinForm.DataGrid1_DataSourceChanged(sender: System.Object;
  e: System.EventArgs);
begin
  btnUndo.Enabled := False;
  btnUpdate.Enabled := False
end;

procedure TWinForm.DataGrid1_CurrentCellChanged(sender: System.Object; e: System.EventArgs); begin btnUndo.Enabled := True; btnUpdate.Enabled := True end;

Note that if you assign the Enabled property of the Undo and Update buttons to the status of the HasChanges property of the DataGrid's DataSource (as DataSet), then this will only return true if the record has been posted to the DataSet. In other words: only if you've moved to another row in the DataGrid, and not if you've changed a column and simply moved to another column in the same row.


Using the enhanced client

You can now compile and run the enhanced WinForms client application. When the application starts, it shows the empty DataGrid and disabled Undo and Update buttons.

Figure 2. Startup with Empty Grid and disabled Undo/Update
Figure 2. Startup with Empty Grid and disabled Undo/Update

If you click on the "First 10" button, then the first 10 records are requested and received from the DB2Sample Web Service application. You can now make a change to the data in the grid, like the last name of a "JOHN" to "WAYNE," and move the cursor to another cell in order to enable the Undo and Update buttons automatically.

Figure 3. After a change: enabled Undo and Update buttons
Figure 3. After a change: enabled Undo and Update buttons

When you click on Undo, the DataSet behind the DataGrid's DataSource will reject all its changes, showing the original 10 records again. When you click on Update, the DataSet - including the updates - will be sent to the DB2Sample Web Service, which will construct a BdpDataAdapter and call the AutoUpdate method to update the underlying EMPLOYEE table from the DB2 SAMPLE database. Right after this update is done, a new request is made to receive the same 10 records. This will result in a verification that the update is indeed done (otherwise the last name "WAYNE" will have been changed to the original value from the EMPLOYEE table again), and it will also fetch any other changes that may have been made by other users who were connected to the DB2Sample Web Service at the same time.


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, and can also receive updates in the datasets in order to modify the datatables as well. 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, navigate through the grid by requesting the next and previous pages of 10 records, and edit the data inside the DataGrid. Finally, I have shown how to implement the Undo and Update buttons to either reject the changes or send the changes inside the DataSet back to the ASP.NET Web Service, where they are resolved in the original IBM DB2 UDB database table.

The combination of IBM DB2 UDB and ASP.NET Web Services built with Delphi 8 for .NET allow you to build multi-tier and distributed applications resulting in thin clients that do not connect to the actual DB2 database tables, but to a middleware ASP.NET Web Service.

Further Enhancements...

Before you plan to use this architecture in a real-world situation, please realize that the SetXXX methods are just as public as the GetXXX methods, meaning that anyone who can connect to the DB2Sample Web Service has the ability to update the data in your database. This is almost as dangerous as giving them direct access to the database, so you should add a security layer around it. For example, force the user to log in, after which a cookie is sent to the client that is used in further authentication validations, or by passing additional parameters to the GetXXX methods, consisting of a user ID and password or PIN code to make sure only validated users can actually update the database tables.

I also leave it as an exercise for the reader to add the check for DataSet's changes when the user clicks on the buttons for the First, Next, or Prev 10 records. This should at least display a dialog that there are pending changes (updates) that need to be posted to the DB2Sample Web Service - or explicitly rejected - before continuing.

Finally, please note that apart from the Get and Set Web Service methods that return tables, you can also perform more complex queries, with joins, or a limited number of fields. I leave all that to you to experiment with: I've shown you the "plumbing" - the general infrastructure - now it's up to you to use it.


Download

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

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, IBM i
ArticleID=13464
ArticleTitle=Working with DB2 Universal Database tables using ASP.NET Web Services written in Delphi 8 for .NET
publish-date=03042004