Working with Data in DB2 UDB Tables Using C#Builder and ASP.NET

This article builds on an earlier article that showed how to build an application using IBM DB2 UDB tables, C#Builder, and BDP for .NET, and then deploy it as an ASP.NET Web form (with a powerful asp:DataGrid control).

Bob Swart (drbob@chello.nl), Trainer and Consultant, 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.



23 October 2003

Introduction

In my last article, I used Borland®; C#BuilderTM and the Borland Data Providers for ADO.NET to build an ASP.NET Web Forms application (with a powerful asp:DataGrid control) to illustrate how you can publish data from IBM® DB2® UDBTM SAMPLE tables on the Web. This time I will take the example a bit further, to allow users not only to view the data and browse through the tables, but also to update the data or delete records from the DB2 UDB SAMPLE tables.


Installing DB2 UDB Version 8

Since that last article, Borland has released the Architect version of C#Builder. This version, like the Enterprise version, comes with an additional CD-ROM containing DB2 UDB Version 8 - Enterprise Developer Edition. It's not a trial version, but a real developer version, so for this article (and the next ones about C#Builder), I decided to install and use that edition of DB2 UDB v8.

Right after you've installed DB2 UDB v8, the "IBM DB2 First Steps" wizard starts, which you can use to generate the special SAMPLE database that we used previously (and will use this time as well). The DB2 UDB product documentation and examples also refer to the tables from the DB2 UDB SAMPLE database, by the way, so it's a good idea to generate them anyway.


Building C#Builder ASP.NET Web Forms

C#Builder Professional, C#Builder Enterprise, and C#Builder Architect all contain the Borland Data Providers for ADO.NET, but only Enterprise and Architect include the BDP driver to connect to the DB2 UDB SAMPLE database. (With C#Builder Professional, you can use IBM's own native .NET driver, but that's a story for another day.)

First, however, we need to create the ASP.NET project again to hold the BDP components. I'll briefly review the steps to reproduce the project from last time, and then you'll continue by adding editing support, followed by delete capabilities (and next time add input validation capabilities when updating the data in DB2 UDB tables).

Start C#Builder, do File | New - ASP.NET Web Application and specify the name of the project in the New ASP.NET Application dialog. The name of the project will be the name of the new virtual directory (one that should not already exist, otherwise you'll get an error message). For my example, I've used the project name DB2UDB, as can be seen in Figure 1 below.

Figure 1. DB2UDB as new ASP.NET application
Figure 1. DB2UDB as new ASP.NET application

When you click OK, a new C#Builder project is created in the new virtual directory. Note the detailed server options for the virtual directory with the alias DB2UDB. For ASP.NET Web applications, you only need "scripting" rights, and no execute rights. The latter are still required for old-fashioned CGI or ISAPI applications.


Connecting to DB2 UDB

Once the new ASP.NET project is created, you can go to the upper-right corner of the C#Builder IDE (where the project manager is located) and click on the Data Explorer tab. Here, you'll find the different providers, including of course the DB2 provider with the connection we built last time. If you haven't modified the DB2 Connection to point to the DB2 UDB SAMPLE database, then right-click on the DB2Conn1 node and start the Connections Editor. Make sure to specify SAMPLE as the database name, and the username and password that are required to connect to this database. You can click on the Test button of the Connections Editor to verify that a connection cp be made.

Once you have specified the correct values for the connection, you can open the DB2Conn1 node in the Data Explorer to view all tables in the DB2 UDB SAMPLE database, as shown in Figure 2.

Figure 2. Data Explorer with DB2 UDB SAMPLE Database
Figure 2. Data Explorer with DB2 UDB SAMPLE Database

Assuming you want to work with the data in the EMPLOYEE table, you can now click on this table (as shown in Figure 2) and just drag it to your Web Form in the designer. Although you can drop it on the Web Form, there will be nothing shown on it. However, two new components will appear in the non-visual components area of the Web Form designer, namely a BdpConnection and a BdpDataAdapter component.

The BdpConnection component contains all information from the DB2Conn1 connection that you specified in the Data Explorer. The BdpDataAdapter component contains the information specific for the EMPLOYEE table.


Further adapting the DB2 datasets

Although the BdpDataAdapter already exists, you may want to customize it a bit further using the Configure Data Adapter verb (a link in the lower left corner of the Object Inspector). Alternately, you can right-click on the BdpDataAdapter component and select the Configure Data Adapter option to start the Data Adapter Configuration dialog.

Last time, I unchecked the Update, Insert and Delete options in this dialog and set the Optimize option. This time, however, you should generate SQL statements for the Update, Insert and Delete commands since those are the very topic of this article. (The Optimize option is another matter, and open for debate.)

With the Optimize option checked, the WHERE clause of the UPDATE and DELETE statements only uses the key fields, like EMPNO, for example. While this is perfect for selecting records, it may be less than ideal for UPDATE and DELETE statements, since updating a record where you only specify the key fields will never be able to inform you that someone else has already changed this record (the non-keyfields on this record, that is). If, on the other hand, you do not use the Optimize option, then the WHERE clause of the UPDATE and DELETE statements will list all fields from the record, thereby making sure that you can only perform the UPDATE or the DELETE if the entire record, with all values as specified, can be found. This is especially important in the .NET world, since you are working with so-called disconnected datasets now, and the cached data shown in the client application (or in the browser, in our example) is only a snapshot, while the actual record in the database may have been changed already.

The decision is yours, but just be aware of the consequences of this option.

Figure 3. Data Adapter Configuration wizard amp;#151; command
Figure 3. Data Adapter Configuration wizard - command

You can now click on the DataSet tab, and select the New DataSet option to create a new DataSet (called dataSet1) that is used to fill in with the output of the SQL SELECT command.

If you wish, you can now click on the Preview Data tab to see the result of the SELECT command. You must click on the Refresh button to see the records, and can optionally limit the number of visual rows at design time (by default set to 100).

Once you're done here, you can close the Data Adapter Configuration wizard. In order to limit the number of selected records at runtime, you have to specify a value in the MaxRecords property of the BdpDataAdapter (by default also set to 100).


Showing data on the Web

When the connection is made, you need a visual component to display the data. Like last time, you want to use the asp:DataGrid component from the Web Controls category. You need to point the DataSource property to dataSet1, and the DataMember property to Table1. If you don't see Table1 in the drop-down combo box for the DataMember property, then the DataSet hasn't been filled with the results of the SELECT command yet. To do this, you have to set the Active property of the BdpDataSetAdapter component to True.

You can then click on the Auto Format verb for the asp:DataGrid to select a scheme such as Professional 1, that I always use.

Before you can compile and run the application to see the actual output, you must first make sure that the data is bound to the asp:DataGrid. This needs to be done at runtime in the ASP.NET Page_Load event handler. Double-click on the Web Form which will bring you to the code editor for the Page_Load event, where you need to write the following code:

private void Page_Load(object sender, System.EventArgs e) 
{ 
  // Put user code to initialize the page here 
  if (!IsPostBack) 
    dataGrid1.DataBind(); 
}

This will ensure that the data is shown when you run the ASP.NET Web application. Note that the if statement checks the IsPostBack value, to make sure the DataBind method is only called the first time.


Editing data on the Web

Last time, we added the ability to sort columns as well as paging support to the asp:DataGrid. This time, however, I want to add the capabilities to edit or delete the data in the asp:DataGrid (insert will be covered at a later time). To do this, you must first add the buttons for the edit, update, cancel, and delete commands to the asp:DataGrid itself.

Click on the Property Builder verb for the asp:DataGrid (or right-click on it and select the Property Builder option). In this dialog, you can control exactly which columns are shown in the asp:DataGrid. By default, each field in the record will be turned into a column of the asp:DataGrid. However, sometimes you do not want to see all fields, or mark some fields to be read-only (like the EMPNO keyfield, for example), or add a special column with command buttons to the asp:DataGrid amp;#151; see Figure 4.

For this example, go to the Columns section (in the list on the left), and then uncheck the Create columns automatically at run-time option, so that you can manually decide which columns to show. Now move all fields that you want from the list of available columns to the list of selected columns. Below the list of available columns, you get the "special" columns, like a Button Column, a HyperLink Column, and a Template Column.

Figure 4. DataGrid Property Builder amp;#151; adding special columns
Figure 4. DataGrid Property Builder amp;#151; adding special columns

You need to add a Button Column, with the Edit, Update, and Cancel buttons. For Button Columns, you can specify the button type, which can be a LinkButton or PushButton. I prefer the latter (see Figure 4 again). You can also specify the captions to use on the Edit, Cancel, and Update buttons amp;#151; this is handy if you need to write an application for Dutch end users, for example.


Commanding the Edit

Although you've now added the Edit, Update, and Cancel buttons, they will show up in the asp:DataGrid but won't respond to our clicks. For that, you need to implement three event handlers of the asp:DataGrid component: the EditCommand, the CancelCommand, and the UpdateCommand.

In the EditCommand event handler, you must assign the EditItemIndex property of the asp:DataGrid to point to the row that you clicked on. The index of that row can be found as Item.ItemIndex value of the event argument. After you've set the EditItemIndex property, you must bind the data to the asp:DataGrid again, so the selected row can be displayed with TextBox controls.

private void dataGrid1_EditCommand(object source, 
  System.Web.UI.WebControls.DataGridCommandEventArgs e) 
{ 
  dataGrid1.EditItemIndex = e.Item.ItemIndex; 
  dataGrid1.DataBind(); 
}

The implementation of the CancelCommand event handler is even easier: this time you only need to assign -1 to the EditItemIndex property, to indicate that the row is currently being edited.

private void dataGrid1_CancelCommand(object source, 
  System.Web.UI.WebControls.DataGridCommandEventArgs e) 
{ 
  dataGrid1.EditItemIndex = -1; 
  dataGrid1.DataBind(); 
}

The implementation for the UpdateCommand is a bit more tricky. This time, you need to obtain the data from the TextBoxes (inside the asp:DataGrid) in order to construct your own update statement. For that, you can cast the controls in the cells to TextBox controls, and take the Text property from them and store them in temporary string variables (that I've given the names of the fields in the records amp;#151; just for clarity).

Once you have all values from the TextBoxes, you can create a new BdpCommand, and dynamically build the SQL UPDATE statement, making sure to place the field value inside quotes for string fields (and date fields), and not inside quotes for all other fields. The ExecuteNonQuery method from the BdpCommand component will send the UPDATE statement to the DB2 UDB, where it will succeed or return with an exception (next time, I'll show you how to deal with exceptions when you also add input validation to this UpdateCommand method).

private void dataGrid1_UpdateCommand(object source, 
  System.Web.UI.WebControls.DataGridCommandEventArgs e) 
{ 
  bdpDataAdapter1.Active = false; 
  // retrieve the data from the asp:DataGrid 
  string EMPNO = ((TextBox)e.Item.Cells[0].Controls[0]).Text; 
  string FIRSTNME = ((TextBox)e.Item.Cells[1].Controls[0]).Text; 
  string MIDINIT = ((TextBox)e.Item.Cells[2].Controls[0]).Text; 
  string LASTNAME = ((TextBox)e.Item.Cells[3].Controls[0]).Text; 
  string WORKDEPT = ((TextBox)e.Item.Cells[4].Controls[0]).Text; 
  string PHONENO = ((TextBox)e.Item.Cells[5].Controls[0]).Text; 
  string HIREDATE = ((TextBox)e.Item.Cells[6].Controls[0]).Text; 
  string JOB = ((TextBox)e.Item.Cells[7].Controls[0]).Text; 
  string EDLEVEL = ((TextBox)e.Item.Cells[8].Controls[0]).Text; 
  string SEX = ((TextBox)e.Item.Cells[9].Controls[0]).Text; 
  string BIRTHDATE = ((TextBox)e.Item.Cells[10].Controls[0]).Text; 
  string SALARY = ((TextBox)e.Item.Cells[11].Controls[0]).Text; 
  string BONUS = ((TextBox)e.Item.Cells[12].Controls[0]).Text; 
  string COMM = ((TextBox)e.Item.Cells[13].Controls[0]).Text; 
  Borland.Data.Provider.BdpCommand command = 
new Borland.Data.Provider.BdpCommand(); command.Connection = bdpConnection1; bdpConnection1.Open(); // build our own SQL Update statement command.CommandText = "UPDATE ADMINISTRATOR.EMPLOYEE SET " + "FIRSTNME = '" + FIRSTNME + "', " + "MIDINIT = '" + MIDINIT + "', " + "LASTNAME = '" + LASTNAME + "', " + "WORKDEPT = '" + WORKDEPT + "', " + "PHONENO = '" + PHONENO + "', " + "HIREDATE = '" + HIREDATE + "', " + "JOB = '" + JOB + "', " + "EDLEVEL = " + EDLEVEL + ", " + // no string "SEX = '" + SEX + "', " + "BIRTHDATE = '" + BIRTHDATE + "', " + "SALARY = " + SALARY + ", " + // no string "BONUS = " + BONUS + ", " + // no string "COMM = " + COMM + " " + // no string "WHERE EMPNO = '" + EMPNO + "'"; // execute our own SQL Update statement command.ExecuteNonQuery(); bdpDataAdapter1.Active = true; dataGrid1.EditItemIndex = -1; dataGrid1.DataBind(); }

When the update succeeds, the BdpDataAdapter can be activated again, and the data bound to the grid so the changes will be made visible again.

Note that I'm only passing the EMPNO value in the WHERE clause, and not all original field values as discussed earlier (recall the Optimize option in the DataAdapter Configuration dialog).


Deleting records

Now that you can edit and then cancel the edit or apply the update, it would be nice to also be able to delete an entire record. To do this, you must perform two steps again. The first one is adding a new Button Column using the asp:DataGrid Property Builder, this time for the Delete command. You can again specify the caption and the type of command (button or link).

The second step handles the implementation of the button click, which is done inside the DeleteCommand event handler of the asp:DataGrid component. The argument contains the Item.ItemIndex again, but this time you cannot obtain the value of the EMPNO field from the TextBoxes (there are no TextBoxes available).

The asp:DataGrid itself can be told what the keyfields are, with the Data key field combobox in the General page of the Property Builder. If you set that combobox to EMPNO, then the DataKeys property of the asp:DataGrid will hold the key values. With the right index (the e.Item.ItemIndex), you can then get the right key value for the WHERE clause of the DELETE command.

private void dataGrid1_DeleteCommand(object source, 
  System.Web.UI.WebControls.DataGridCommandEventArgs e) 
{ 
  bdpDataAdapter1.Active = false; 
  // retrieve the data from the asp:DataGrid 
  string EMPNO = (string)dataGrid1.DataKeys[e.Item.ItemIndex]; 
  Borland.Data.Provider.BdpCommand command = 
new Borland.Data.Provider.BdpCommand(); 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 command.ExecuteNonQuery(); bdpDataAdapter1.Active = true; dataGrid1.DataBind(); }

If you do not want to actually delete records from your DB2 UDB SAMPLE database tables, just place the command.ExecuteNonQuery() call in comments, and assign the command.CommandText value to an asp:label component that's placed just above the asp:DataGrid. Apart from using a test database, that's the safest way to test your code before going "live" with it.

The DataGrid with edit and delete buttons can be seen in the screenshot below (it may be a good idea to limit the number of visible columns in the asp:DataGrid, as you can see):

Figure 5. ASP.NET Web Application with asp:DataGrid and Edit, Delete buttons
Figure 5. ASP.NET Web Application with asp:DataGrid and Edit, Delete buttons

When the user clicks on the Edit button, this button is replaced by an Update and a Cancel button, and all fields not marked as read-only will get a TextBox control in their cell. In my next article, I'll show you how to extend the simple TextBox controls with input validation capabilities for even more control and a more user-friendly application.

Figure 6. ASP.NET Web Application with asp:DataGrid and Update, Cancel and Delete buttons
Figure 6. ASP.NET Web Application with asp:DataGrid and Update, Cancel and Delete buttons

After clicking on Cancel, all changes are ignored, but clicking on the Update button will perform the code to construct the UPDATE command.

The Delete button will build the DELETE command as shown in the last listing.


Deploying your application

Deploying the ASP.NET Web application means placing it on a Web server where it can access the DB2 UDB SAMPLE tables (with the DB2 client installed). In order to deploy the current sample application, you need to include the web.config, global.asax, and the WebForm1.aspx files (containing the HTML for the ASP.NET Web Form). And of course you need to deploy the generated DB2UDB.dll assembly (the result of our example project) from the Bin subdirectory, containing the compiled Code Behind part of the ASP.NET Web Form.

Apart from those project-specific files, you also need to deploy the Borland Data Provider files, including the bdpdb2.dll which has to be placed in the search path, or amp;#151; even easier amp;#151; in the same Bin directory where the DB2UDB.dll is deployed.

The three BDP assemblies amp;#151; Borland.Data.Provider.dll, Borland.Data.Common.dll, and Borland.Data.Db2.dll amp;#151; can be placed in the same directory as the Code Behind file, or deployed in the Global Assembly Cache using gacutil. In the latter case they will be shared on your .NET Web server, and can be used by other BDP for ASP.NET applications as well.


Conclusion

In this article, I've shown that DB2 UDB is a powerful DBMS to build ASP.NET Web Forms, where the data from the DB2 UDB SAMPLE database can be accessed and modified (using edit, cancel, update or delete) using the Borland Data Providers for .NET.

Borland RAD tools and DB2 UDB can connect on .NET using Win Forms or ASP.NET Web Forms resulting in a powerful combination for working on data from the DB2 UDB tables.


Disclaimer

This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

IBM, DB2, and DB2 Universal Database are trademarks or registered trademarks of IBM Corporation in the United States, other countries, or both.

Borland and C#Builder are trademarks or registered trademarks of Borland Corporation in the United States, other countries, or both.

Windows and Windows NT are registered trademarks of Microsoft Corporation in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

IBM copyright and trademark information


Download

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

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=13169
ArticleTitle=Working with Data in DB2 UDB Tables Using C#Builder and ASP.NET
publish-date=10232003