Using Delphi for .NET to work with DB2 database tables in ASP.NET Web pages

This article shows how to use Borland Delphi 8 for the Microsoft .NET Framework to build ASP.NET Web applications that connect to IBM DB2 Universal Database tables; display the data in a special ASP.NET DataGrid control; and edit, apply, or undo changes in these database tables.

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.



22 January 2004

Introduction

In this article, I will demonstrate how you can use Borland® DelphiTM 8 for the Microsoft® .NET Framework (abbreviated to "Delphi 8 for .NET" from now on) to build ASP.NET Web applications that connect to IBM® DB2® Universal DatabaseTM (UDB) tables; display the data in a special ASP.NET DataGrid control; and edit, apply or undo changes in these database tables. I will use the Borland Data Provider (BDP) for ADO.NET as data access technology, and both the "plain" ASP.NET controls and the new Borland DB Web controls to display the data inside the ASP.NET Web pages.


Building ASP.NET Web pages

Delphi 8 for .NET contains full support for ASP.NET, including ASP.NET Web Services (a topic for another time) and ASP.NET Web Forms. With File | New - ASP.NET Web Application, you can start a new ASP.NET Web Forms application, after which you need to specify the name and location of the new project:

Figure 1. New ASP.NET Web application
Figure 1. New ASP.NET Web application

This will result in a new project called D8DB2SAMPLE in a virtual directory with the same name, which means you can view the results of your project at http://localhost/D8DB2SAMPLE on your development machine (I'll cover actual deployment steps at the end of this paper).

The Delphi 8 for .NET IDE will now use the special HTML Designer to allow you to design your ASP.NET Web Forms. The tool palette lists several components in the Components, Data Components, Borland Data Provider, DB Web, Enterprise Core Objects, HTML Elements, and Web Controls categories. Today, you'll use components from the Borland Data Provider, DB Web, and Web Controls categories, starting with the BDP controls.


Connecting with the Borland Data Providers

The Data Explorer is always available in the Delphi 8 for .NET IDE (even without an open project), and you can use it to open the connection to the DB2 SAMPLE database and list the tables, views, or stored procedures inside this DB2 UDB. See the first article that I wrote about Delphi 8 for .NET and IBM DB2 for the connection details.

Figure 2. Borland Data Explorer
Figure 2. Borland Data Explorer

You can drag the ADMINISTRATOR.EMPLOYEE table node (of the IBM DB2 SAMPLE database connection tree) from the Data Explorer to the HTML designer area. This will produce a BdpConnection and a BdpDataAdapter component in the non-visual components area of the HTML Designer. The BdpConnection component connects to the DB2 UDB SAMPLE database, and the BdpDataAdapter component acts as the "adapter" between a .NET DataSet and the DB2 SAMPLE database itself, using SELECT, UPDATE, DELETE, and INSERT SQL commands.

To customize the settings for the BdpDataAdapter component, click on the Configure the DataAdapter verb in the Object Inspector, which will start the Data Adapter Configuration dialog (see Figure 3 and further).

Figure 3. Data Adapter Configuration - Command
Figure 3. Data Adapter Configuration - Command

Using the Tables and Columns listboxes, you can create your own collection of fields for use in the SELECT, UPDATE, INSERT and DELETE SQL commands. For this example, I'll just select all fields from the ADMINISTRATOR.EMPLOYEE table.

Once you're happy with the SELECT statement, you can go to the DataSet tab to specify where to put the result of the SELECT statement - as DataTable in a new or existing .NET DataSet, for example:

Figure 4. Data Adapter Configuration - DataSet
Figure 4. Data Adapter Configuration - DataSet

You can now close the Data Adapter Configuration dialog, and return to Delphi for .NET. There are now three components in the non-visual components area of the WebForm designer: BdpConnection, BdpDataAdapter, and DataSet. When you activate the BdpDataAdapter component, by setting its Active property to True, Delphi will create a new table called EMPLOYEE inside the .NET DataSet, which will be the one to work with in the next sections.


Working with DB2 data using an ASP.NET DataGrid

Now you can start to work with the data inside the DB2 Employee dataset using the standard ASP.NET DataGrid control. This component can be found in the Web Controls category of the Tool Palette. Double-click on the component to place it on the Web form in the HTML Designer (in flow-layout). Assign its DataSource property to dataSet1, and its DataMember property to EMPLOYEE (the name of the DataTable that holds the result of the SELECT command). The DataGrid in the HTML Designer will now display the names of the columns as well as sample values based on the field types (that is, "abc" for string fields, "2003-12-29 00:00:00" for date types, etc.). No actual record values are shown, however. You can customize the look of the DataGrid with the Auto Format verb in the Object Inspector. I usually set it to Professional 1 (but you can make your own choice here, of course).

Paging, editing, deleting, etc.

To really work with the data, you need to click on the Property Builder verb for the DataGrid. On the first page of the DataGrid Properties dialog (Figure 5), you can specify the data key field (set it to EMPNO), which will be used as the key in the SQL DELETE commands that you'll generate in a moment. This is also the place where you can enable sorting, although you'll then need to implement the SortCommand event handler of the DataGrid (something that I did in this article in October 2003 using C#Builder, which I will not do today - feel free to implement it yourself using the steps from the C#Builder article.

Figure 5. DataGrid Property Editor - General
Figure 5. DataGrid Property Editor - General

The next step is to go to the Column page of the DataGrid Property Editor dialog. Here, you first need to uncheck the Create columns automatically at run-time option, after which you can manually select which columns must be created. Apart from selecting the data columns, you may also want to display the special edit/update/cancel and delete buttons. These can be found at the end of the list with available columns (see Figure 6).

Figure 6. DataGrid Property Editor - Columns
Figure 6. DataGrid Property Editor - Columns

For each button, you can specify the type of button (link or pushbutton) and the actual caption to use. You will need to implement the EditCommand, CancelCommand, UpdateCommand, and DeleteCommand to make the four new buttons do their work.

Before you can do that, however, you should first go to the Paging page of the DataGrid Property Editor dialog, and check the allow paging option. You can then specify the page size (default set to 10 records per page), as well as the type of navigator buttons (next and previous buttons, or page numbers); see Figure 7.

Figure 7. DataGrid Property Editor - Paging
Figure 7. DataGrid Property Editor - Paging

For the navigation buttons to work, you'll need to implement the PageIndexChange command in a moment. And since this is the last change you need to do, just close the DataGrid Property Editor dialog and start implementing all events that you've enabled.

Implementing ASP.NET DataGrid events

Select the ASP.NET DataGrid component, and go to the Events page of the Object Inspector. Let's start with the Paging ability, so double-click on the PageIndexChanged event handler, and write the following code to assign a new value to the CurrentPageIndex property of the DataGrid:

procedure TWebForm1.DataGrid1_PageIndexChanged(source: System.Object;
  e: System.Web.UI.WebControls.DataGridPageChangedEventArgs);
begin
  dataGrid1.CurrentPageIndex := e.NewPageIndex;
  dataGrid1.DataBind // refresh data
end;

Next, implement the EditCommand and CancelCommand by assigning a value to the EditItemIndex property of the DataGrid. Setting this property to a value of 0 or higher will place that line in the DataGrid in edit mode. To remove the edit mode again, you can set EditItemIndex to -1.

procedure TWebForm1.DataGrid1_EditCommand(source: System.Object;
  e: System.Web.UI.WebControls.DataGridCommandEventArgs);
begin
  dataGrid1.EditItemIndex := e.Item.ItemIndex;
  dataGrid1.DataBind // refresh data
end;procedure TWebForm1.DataGrid1_CancelCommand(source: System.Object;
  e: System.Web.UI.WebControls.DataGridCommandEventArgs);
begin
  dataGrid1.EditItemIndex := -1;
  dataGrid1.DataBind // refresh data
end;

And now it's time to implement the UpdateCommand event handler. There are several ways to do this, but usually it consists of building a custom SQL command to be used as an update statement for the EMPLOYEE table.

procedure TWebForm1.DataGrid1_UpdateCommand(source: System.Object;
  e: System.Web.UI.WebControls.DataGridCommandEventArgs);
const
  MyFields = 14;
var
  MyFieldData: Array [0..MyFields-1] of String;
  i: Integer;
  command: BdpCommand;
begin
  bdpDataAdapter1.Active := False;
  // retrieve the data from the asp:DataGrid
  for i:=0 to MyFields-1 do
    MyFieldData[i] := (e.Item.Cells[i].Controls[0] as TextBox).Text;
  command := BdpCommand.Create;
  command.Connection := bdpConnection1;
  bdpConnection1.Open();
  // build our own SQL Update statement
  command.CommandText := 'UPDATE ADMINISTRATOR.EMPLOYEE SET ' +
//  'EMPNO = ' + MyFieldData[0] + ', ' +
    'FIRSTNME = ''' + MyFieldData[1] + ''', ' +
    'MIDINIT = ''' + MyFieldData[2] + ''', ' +
    'LASTNAME = ''' + MyFieldData[3] + ''', ' +
    'WORKDEPT = ''' + MyFieldData[4] + ''', ' +
    'PHONENO = ''' + MyFieldData[5] + ''', ' +
    'HIREDATE = ''' + MyFieldData[6] + ''', ' +
    'JOB = ''' + MyFieldData[7] + ''', ' +
    'EDLEVEL = ' + MyFieldData[8] + ', ' +
    'SEX = ''' + MyFieldData[9] + ''', ' +
    'BIRTHDATE = ''' + MyFieldData[10] + ''', ' +
    'SALARY = ' + MyFieldData[11] + ', ' +
    'BONUS = ' + MyFieldData[12] + ', ' +
    'COMM = ' + MyFieldData[13] + ' ' +
    'WHERE EMPNO = ''' + MyFieldData[0] + '''';
  // execute our own SQL Update statement
  try
    command.ExecuteNonQuery;
  except on E: Exception do
      Response.Write(E.Message)
  end;
  bdpDataAdapter1.Active := True;
  dataGrid1.EditItemIndex := -1;
  dataGrid1.DataBind // refresh data
end;

In order to build the WHERE part of the DeleteCommand, you need to obtain the value of the key field for the current row. Remember how you specified the EMPNO field to be the key field (in the DataGrid configuration dialog). This means that when you click on the Delete button in the DataGrid, you can use the event argument's itemindex to get to the value of the DataGrid's DataKey (which corresponds to the value of the EMPNO field for the row that you clicked on). This value is then used to construct a custom SQL DELETE statement as follows:

procedure TWebForm1.DataGrid1_DeleteCommand(source: System.Object;
  e: System.Web.UI.WebControls.DataGridCommandEventArgs);
var
  EMPNO: String;
  command: BdpCommand;
begin
  bdpDataAdapter1.Active := False;
  // retrieve the data from the asp:DataGrid
  EMPNO := dataGrid1.DataKeys[e.Item.ItemIndex].ToString;
  command := BdpCommand.Create;
  command.Connection := bdpConnection1;
  bdpConnection1.Open;
  // build our own SQL Update statement
  command.CommandText := 'DELETE FROM ADMINISTRATOR.EMPLOYEE ' +
    'WHERE EMPNO = ' + EMPNO;
  // execute our own SQL Update statement
  Response.Write(command.CommandText);
  try
    command.ExecuteNonQuery;
  except 
    on E: Exception do
      Response.Write(E.Message)
  end;
  bdpDataAdapter1.Active := True;
  dataGrid1.DataBind // refresh data
end;

Finally, you should make sure that the DataGrid is actually shown on the Web form the first time the page is displayed, which can be done in the Page_Load event handler. Double-click on the Web Form in the HTML Designer to implement this event handler, and write the following code:

procedure TWebForm1.Page_Load(sender: System.Object; e: System.EventArgs);
begin
  // TODO: Put user code to initialize the page here
  if not IsPostBack then
    dataGrid1.DataBind
end;

After all this code, it's time to start the ASP.NET Web forms application. This can be done using the URL I specified earlier: http://localhost/D8DB2SAMPLE/WebForm1.aspx, or by doing Run | Run from the Delphi 8 for .NET IDE itself.

Figure 8. ASP.NET DataGrid in action
Figure 8. ASP.NET DataGrid in action

You can use the page numbers in the footer of the DataGrid to move from one page to another, and use the Edit button to set the DataGrid in edit mode (after which the Cancel and Update buttons will appear). The Delete button will delete a record from the DataGrid. The Update and Delete buttons use exception handlers to display the error message if something goes wrong (such as referential integrity violations when deleting a record).


Working with DB2 Data using DB Web controls

Now you can start to work with the data inside the DB2 Employee dataset using the new Borland DB Web controls. These controls are available in the Enterprise and Architect editions of Delphi 8 for .NET (but these are the only editions that include the DB2 driver with the Borland Data Providers for .NET, so everyone should still be able to play along from here).

Just as the Borland Data Provider offers enhancements over ADO.NET, the DB Web controls offer some significant enhancements over some of the plain ASP.NET controls, like the DataGrid that you've been using so far.

You can remove the ASP.NET DataGrid from the Web Form, or add another Web Form to the ASP.NET Web project (in which case you need to drag the EMPLOYEE table to the new page again). In both cases, you need to go to the DB Web category of the Tools Palette. Here you'll find a number of controls that use the DBWebDataSource component as a connector between the .NET DataSet and the DB Web data-aware controls. Start by dropping a DBWebDataSource control on the Web Form, and connect its DataSource property to dataSet1 (the .NET DataSet that holds the EMPLOYEE DataTable with our DB2 SAMPLE data).

Now, drop a DBWebGrid component - a special edition derived from the ASP.NET DataGrid. Point its DBDataSource property to the DBWebDataSource component, and then select EMPLOYEE as the value for the TableName property. You should now see "live data" at design-time!

Figure 9. DB Web Grid showing data at design-time
Figure 9. DB Web Grid showing data at design-time

You may not be impressed by this little feature, but it certainly helps (personally, I welcome the ability to watch a preview of my Web forms at design-time any day).

You can use the Auto Format verb to set the look of the DBWebGrid - just like with the regular ASP.NET DataGrid component. When you select a color scheme, you may notice that the first line of the DBWebGrid looks as if it's in edit mode already. And in fact, it is. The DBWebGrid already contains built-in support for Edit, Cancel, Update, and Delete (and Paging, by the way). You only need to make it a bit more visible by placing a DBWebNavigator component - for example, just above the DBWebGrid control. Again, you need to point the DBWebDataSource property to the DBWebDataSource component, and set EMPLOYEE as TableName property value.

Other than that, you only need to write a single line of code to implement the event in which the modified data from the DBWebGrid has to be applied to the DB2 UDB SAMPLE database again (in the so-called "Apply To Server" action). This can be done in the ApplyChangesRequest event handler of the DBWebDataSource component, calling the AutoUpdate method of the BdpDataAdapter:

procedure TWebForm1.DBWebDataSource1_OnApplyChangesRequest(
  sender: System.Object; e: Borland.Data.Web.WebControlEventArgs);
begin
  BdpDataAdapter1.AutoUpdate
end;

Note that this is the same as I did in the first Delphi 8 for .NET article.

Apart from this single line of code, you do not have to write anything else. The DBWebGrid is "smart" enough to grab the changes from the controls when in edit mode, and place the changes in the DataSet control. And the .NET DataSet offers the ability to undo changes (see last time), which is offered through the "Undo" and "Undo All" buttons of the DBWebNavigator control.

When you run the application, make some changes to the data, and then click on the Apply To Server button, you may get a dialog that tells you that ADMINISTRATOR.ADMINISTRATOR.EMPLOYEE is an undefined name. Nothing to worry about: the AutoUpdate method of the BdpDataAdapter prepended the username ADMINISTRATOR to the tablename, resulting in ADMINISTRATOR.ADMINISTRATOR.EMPLOYEE. You have to edit the SQL SELECT command (of the BdpDataAdapter) and remove the ADMINISTRATOR prefix from it, as can be seen in Figure 10.

Figure 10. Removing ADMINISTRATOR from TableName
Figure 10. Removing ADMINISTRATOR from TableName

This will make sure that the AutoUpdate statement generates the correct name.

Figure 11, the final figure, shows the DBWebGrid component, connected to the EMPLOYEE table from the DB2 UDB SAMPLE database, complete with the navigator that you can use to browse through the grid, edit records, apply the updates to the server, delete records, etc.

Figure 11. Editing DB2 UDB SAMPLE Database in ASP.NET Web Form
Figure 11. Editing DB2 UDB SAMPLE Database in ASP.NET Web Form

Note that if you feel that the DBWebGrid takes up too much space, then you can always use the Property Editor to remove some fields from the display.


Deploying BDP ASP.NET Web applications

The resulting ASP.NET Web application in the D8DB2SAMPLE virtual directory consists of several files, but you only need to deploy a few of them. From the bin subdirectory, you need the D8DB2SAMPLE.dll assembly, and from the virtual directory itself you need the WebForm1.aspx file, and - optionally - the globals.asax and web.config files. All other files are source files (or compiled units) that do not need to be deployed.

Apart from that, you need to deploy the BDP drivers. The Borland Data Provider consists of assemblies that are signed using a strong key, and are recommended to be deployed in the Global Assembly Cache (GAC). Specifically, you need to deploy the Borland.Data.Provider.dll, Borland.Data.Common.dll, and Borland.Data.Db2.dll, and also need to make sure that the bdpdb2.dll Win32 DLL can be found as well, since that's the one that links with the DB2 client in db2cli.dll.

The three Borland.Data assemblies can either be placed in the same directory as the client executable (so only our client application can see them and use them), or you can deploy them in the Global Assembly Cache using the gacutil command-line utility. In the latter case, they will be shared on your .NET machine and can be used by other .NET applications as well.

The Borland DB Web controls can be found in the Borland.Data.Web.dll assembly, which can be freely deployed with your ASP.NET projects. Note that Delphi 8 for .NET (Enterprise and Architect) even contains the C# source code for these controls.


Conclusion

In this article, I've shown that IBM DB2 UDB is a powerful DBMS that can be used by Delphi 8 for .NET to build ASP.NET Web form applications using either the standard ASP.NET controls (like the DataGrid) or using the new Borland DB Web controls - including the DBWebGrid and DBWebNavigator controls.

The combination of Delphi 8 for .NET and IBM's DB2 UDB offers a truly RAD way to build powerful data-driven ASP.NET Web applications.

Resources

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=13449
ArticleTitle=Using Delphi for .NET to work with DB2 database tables in ASP.NET Web pages
publish-date=01222004