ASP.NET Web Forms With DB2 UDB Tables Using C#Builder and BDP for .NET

This article shows you 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), so users can work with the DB2 UDB tables using a Web browser.

Share:

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.



17 July 2003

© 2003 International Business Machines Corporation. All rights reserved.

Introduction

In my first article on using IBM® DB2® Universal DatabaseTM (UDB) with Borland® C#BuilderTM, I showed how the Borland Data Providers (BDP) for .NET could be used to build a connection to DB2 UDB v8.1 and to use the DB2 SAMPLE tables. In the second article, I examined some individual Borland Data Provider classes in a bit more detail, especially the design-time editors, and deployed a WinForms application.

In this article, I will show how to build a similar application using DB2 UDB tables, but this time I will deploy it as an ASP.NET Web form (with a powerful asp:DataGrid control), so users can work with the DB2 UDB tables using a Web browser.


Building ASP.NET Web forms

First, let's first create an ASP.NET Web Form application using C#Builder, then add the connection to the DB2 UDB SAMPLE tables, and finally configure the user interface using an asp:DataGrid control.

Like the earlier articles, I'm using IBM DB2 UDB v8.1 in combination with C#Builder Enterprise on the Microsoft® .NET Framework 1.1. If you want to create ASP.NET Web forms, you need a Web server like Microsoft's Internet Information Server (IIS). If you don't have IIS installed, then you can use the free ASP.NET Cassini Web server—a sample Web server for which full source code and installation instructions can be found in the BDS\1.0\Examples\C#\Cassini directory of C#Builder.

In order to build the application, start C#Builder, and do File > New and enter ASP.NET Web Application. This will give you the New ASP.NET Application dialog, where you can specify the name of the project (see Figure 1). This name will be used to create a new virtual directory where the project files will be placed. It makes sense to use the same name of the virtual directory where the ASP.NET Web Form will be deployed (see Deploying your application), but it's not required. I've specified DB2UDB to illustrate that this is an ASP.NET Web form connecting and working with the DB2 UDB tables.

Figure 1. Select IIS or Cassini as Web server
Figure 1. Select IIS or Cassini as Web server

Note from Figure 1 that you can specify the Web server for which you want to deploy the project (I'm using IIS, but you can select Cassini if you want).

When you click OK, a new C#Builder project is created in the new virtual directory. Instead of a WinForm, however, you now have a WebForm where you can place special Web components. First, however, you should build the connection to the DB2 UDB SAMPLE tables.


Connecting to DB2 UDB

Like last time, you can build the connection to the DB2 UDB SAMPLE tables in a number of ways. You can use the Data Explorer and drag-and-drop a table to the Web Forms. Or you can do it in a more explicit way (which gives you a bit more control over the "magic"), which we'll do here. So, start by dropping a BdpConnection component on the WebForm. Note that it will appear in the non-visual part of the designer, right under the WebForm.

Select the BdpConnection component and use the Connections Editor verb from the Object Inspector, or right-click on the BdpConnection component to start the Connections Editor.

Figure 2. Borland Data Provider: Connections Editor
Figure 2. Borland Data Provider: Connections Editor

By default, only four connections are shown: for InterBase, MS SQL Server, Oracle, and DB2. These four are the "sample" connections that I never use myself, although I consider them helpful examples. If you have not connected to DB2 UDB before, click Add to create a specific DB2 connection to the DB2 SAMPLE database tables.

Figure 3. Add New Connection
Figure 3. Add New Connection

Click OK to get back to the Connections Editor, and then specify SAMPLE as the database name, and enter the correct password and username of your DB2 user. You can use the Test button to see if the connection properties are set correctly, so you can communicate with the DB2 UDB SAMPLE tables.


Adapting DB2 datasets

Next, drop a BdpDataAdapter component. You can use the verbs Configure Data Adapter or Generate Typed Dataset, or right-click on the BdpDataAdapter component to select the same one. You don't want to generate a typed dataset at this time, so select Configure Data Adapter.

In the Data Adapter Configuration dialog, uncheck the Update, Insert and Delete options, and set the Optimize option. Click on Generate SQL to produce the SQL statement (see Figure 4). Alternately, you can enter a SELECT * from ADMINISTRATOR.EMPLOYEE (yourself) here, of course.

Figure 4. Data Adapter Configuration Wizard—Command
Figure 4. Data Adapter Configuration Wizard - Command

Click the DataSet tab, and select New DataSet (see Figure 5). This will automatically create a new DataSet (call it dataSet1) and connect it to the output of the SQL command.

Figure 5. Data Adapter Configuration Wizard—DataSet
Figure 5. Data Adapter Configuration Wizard - DataSet

Now, click Preview Data to see the preview. Note that you have to click the Refresh button to see the data again. Also note that you can limit the rows that are requested here. The default is to show the first 100 rows, but for an ASP.NET Web form, you may want to limit it even further, for example to the first 10 records. So set the value of Rows to fetch to 10, and click Refresh.

Figure 6. Data Adapter Configuration Wizard—preview data
Figure 6. Data Adapter Configuration Wizard - preview data

If you close the Data Adapter Configuration wizard, you may notice that the 10 we've specified here has no connection to the value of the MaxRecords property (which is still set to 100). This means that you still get a maximum of 100 records instead of 10, but we'll solve that later in this article (when I get to paging).

Note that in the non-visual designer area of the WebForm, you will see no visual "proof" that the dataSet1 component was added. You need to explicitly right-click in this area of the designer and select the Line up Icons option in order to refresh the display and show dataSet1 as well (just to verify that nothing is missing).

So far it's been relatively easy, and when it comes to connecting to DB2 you could perform almost the same steps as the last time. But from this point on, things will change compared to the WinForms method.


Controlling the Web

Now it's time to add the ASP.NET Web controls, from the Web Controls category (note that you can also add components from the HTML Elements category, but these are far less powerful). In order to mimic the application from last time, you need to use an asp:DataGrid component, so drop one on the WebForm. Initially, it will show three columns and five rows (not counting the header) with "abc" values. But you can bind the asp:DataGrid to the dataSet1 component using the DataSource property. And apart from pointing DataSource to dataSet1, you also need to point the DataMember property to Table1.

Figure 7. C#Builder design time overview
Figure 7. C#Builder design time overview

Although this will show more columns (for each field, the title is also shown in the column header—obtained from the meta data of the dataset), it will not show the actual data at design-time. Also, you still see only five records, and not the 10 specified in the Data Adapter Configuration Wizard.

To quickly configure the asp:DataGrid a bit, you can click the Auto Format verb (at the lower left side of Figure 7), which will give you the Auto Format dialog of Figure 8, where you can select a special pre-defined scheme (like Professional 1 that I've selected).

Figure 8. DataGrid Auto Format
Figure 8. DataGrid Auto Format

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, and the best place is 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. 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 make sure that the DataBind method of the asp:DataGrid is only called the first time (when you enter the ASP.NET Web Form). All other times (such as when you post back information), the asp:DataGrid will already be bound to the data, so there will be no need to call DataBind again. I'll get back to this in a moment.


Running and debugging Web forms

Now, you can save the project, compile it and get ready to run it. The C:\Inetpub\wwwroot directory will contain a directory called DB2UDB with the project files. This directory has a Bin subdirectory where the DB2UDB.dll is placed. From a browser, you can start this project with the URL http://localhost/DB2UDB/WebForm1.aspx (the name of your main form) However, I think it's more impressive to be able to debug your code while running, so just press F9 to run the ASP.NET application, which will put the IDE in "debug mode" and start the browser on the starting page.

In both cases, the result is the same and can be seen in Figure 9.

Figure 9. ASP.NET Web Form with DataGrid (showing many records)
Figure 9. ASP.NET Web Form with DataGrid (showing many records)

Note that you get all records (or at least the first 100—specified by MaxRecords) from the EMPLOYEE table of the DB2 UDB SAMPLE database. This does not look really "professional," I think, because the user has to scroll down to see all records (and you won't see the header when you're at record 20 or later). Also, if you send many records from the server to the client, then you may experience a performance problem and potential bandwidth bottleneck. This can easily happen in real-world situations, especially since I've only created a simple SELECT statement without a restricting WHERE clause.


Sorting and paging DB2 data

To help solve this problem, you may want to configure the asp:DataGrid options in some more detail. Close the browser (which will get you back into the C#Builder IDE), select the asp:DataGrid, and this time click the Property Builder verb (lower left side of the screen) to get the DataGrid Properties dialog.

The first page can be used to confirm the value for the DataSource and DataMember properties, as well as the Data key field. Note the option for Allow sorting that I've checked as well.

Figure 10. DataGrid Properties—general
Figure 10. DataGrid Properties - general

In order to allow the sorting to work, you have to do two more things: specify the sort expression, and write some code for the SortCommand.

You can specify the sort expression on the Columns page of the DataGrid Properties Wizard (see Figure 11).

Figure 11. DataGrid Properties—Columns
Figure 11. DataGrid Properties - Columns

For each column, you can set the sort expression (which is typically equal to the field name). This will turn the header of the column into a hyperlink, and if you click on the hyperlink, then the SortCommand event handler is executed, passing the specific value of the Sort Expression as argument. Since this is the actual fieldname, all you have to do to sort the asp:DataGrid is to perform the SQL query against DB2 UDB again, but this time add an ORDER BY clause, followed by the fieldname which is passed as a sort expression.

The C# source code for the SortCommand event handler of the asp:DataGrid is as follows:

private void dataGrid1_SortCommand(object source, 
  System.Web.UI.WebControls.DataGridPageChangedEventArgs e) 
{ 
  Session["OrderBy"] = e.SortExpression; // Order By FieldName 
  bdpDataAdapter1.Active = false; 
  bdpSelectCommand1.CommandText = "SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, " + 
    " WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, " + 
    "BONUS, COMM FROM ADMINISTRATOR.EMPLOYEE"; 
  if ((Session["OrderBy"] != null) && ((string)Session["OrderBy"] != "")) 
    bdpSelectCommand1.CommandText += " ORDER BY " + (string)Session["OrderBy"]; 
  bdpDataAdapter1.Active = true; 
  dataGrid1.DataBind(); 
  label1.Text = this.bdpSelectCommand1.CommandText + 
    " (Page " + (dataGrid1.CurrentPageIndex+1).ToString() + ")"; 
}

Note that I store the SortExpression in the ASP.NET Session variable, so you can ensure that the same ordering is used when you get back to the page and have to perform the query again (for example when you want to jump to another page).

Speaking of going to the next page: you can specify the number of maximum records that the asp:DataGrid should show on a specific page (remember Figure 9, that showed all records in the grid—not a big deal for this EMPLOYEE table from the DB2 UDB SAMPLE database, but a potential problem when using a bigger table in the real world, of course). I've specified a page size of 10 rows. This means that you can get a number of pages—each with 10 rows—and your application has to respond to the right event when the user wants to navigate from one page to another (using the Next and Previous buttons, as specified, or alternately, the individual page numbers).

Figure 12. DataGrid Properties—Paging
Figure 12. DataGrid Properties - Paging

When the user clicks on one of the navigation buttons, then the PageIndexChanged event of the asp:DataGrid is fired. You can specify that the asp:DataGrid has to start at a specific page using the CurrentPageIndex property. Unfortunately, this property has to be set before the data is bound to the asp:DataGrid, otherwise the grid already contains the data. So, you have to bind the data again, meaning that you have to de-activate the BdpDataAdapter component, assign the right value to the CommandText property again (ordering the table on the current selected column), then re-activate the BdpDataAdapter and finally bind the data in the asp:DataGrid, as can be seen in the following source code:

private void dataGrid1_PageIndexChanged(object source, 
  System.Web.UI.WebControls.DataGridPageChangedEventArgs e) 
{ 
  dataGrid1.CurrentPageIndex = e.NewPageIndex; // Go to Page index 
  bdpDataAdapter1.Active = false; 
  bdpSelectCommand1.CommandText = "SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, " + 
    " WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, " + 
    "BONUS, COMM FROM ADMINISTRATOR.EMPLOYEE"; 
  if ((Session["OrderBy"] != null) && ((string)Session["OrderBy"] != "")) 
    bdpSelectCommand1.CommandText += " ORDER BY " + (string)Session["OrderBy"]; 
  bdpDataAdapter1.Active = true; 
  dataGrid1.DataBind(); 
  label1.Text = this.bdpSelectCommand1.CommandText + 
    " (Page " + (dataGrid1.CurrentPageIndex+1).ToString() + ")"; 
}

You can now close the DataGrid Property Builder dialog again. At design-time, the asp:DataGrid will now show hyperlinks in the column headers, and a Next and Previous button in the footer of the asp:DataGrid (see Figure 13).

Figure 13. C#Builder HTML design time overview
Figure 13. C#Builder HTML design time overview

There's one more thing that you may want to do: the first time the ASP.NET Web Form is created (in the Page_Load event handler), you may want to clear the OrderBy Session variable, and set a value to the asp:label control (which I mainly use for debugging purposes, by the way, but which is useful to show the SQL statement that was executed and the page number you're currently looking at).

private void Page_Load(object sender, System.EventArgs e) 
{ 
  // Put user code to initialize the page here 
  if (!IsPostBack) 
  { 
    Session["OrderBy"] = ""; 
    dataGrid1.DataBind(); 
    label1.Text = this.bdpSelectCommand1.CommandText + 
      " (Page " + (dataGrid1.CurrentPageIndex+1).ToString() + ")"; 
  } 
}

The result of compiling and running the ASP.NET application from the C#Builder IDE can be seen in Figure 14. Note that the initial SQL statement is performed (equivalent to select * from employee) without an order by clause, and starting at the first page.

Figure 14. Final ASP.NET output (initial page)
Figure 14. Final ASP.NET output (initial page)

We can click the > button to go to the next page, as shown in Figure 15.

Figure 15. Final ASP.NET Output (second page)
Figure 15. Final ASP.NET Output (second page)

Now you can click the FIRSTNME column to sort the asp:DataGrid by this field, as shown in Figure 16. Note that you stay at the second page, but this time the second page of the SQL query that performs the order by for the FIRSTNME field.

Figure 16. Final ASP.NET output (sorted by firstnme)
Figure 16. Final ASP.NET output (sorted by firstnme)

Optionally, you may want to reset the CurrentPageIndex property of the asp:DataGrid in the SortCommand event handler, so you always get back to the first page when you want to re-sort the data in the grid. You can do this by adding the following line as first line of the SortCommand event handler:

  dataGrid1.CurrentPageIndex = 0; // first page (start to count at 0)

Deploying your application

When it's time to deploy your application, you will likely want to deploy it on a Web server where you can access the DB2 UDB (so the DB2 client needs to be installed), where typically C#Builder would not be installed. This means that several files of your application must move to the final Web server machine. For the current demo, that means that you need to deploy the web.config, global.asax (which wasn't modified anyway), and the WebForm1.aspx (containing the HTML for the ASP.NET Web form). And, of course, you need to deploy the DB2UDB.dll from the Bin subdirectory. This file contains the compiled Code Behind part of the ASP.NET Web Form.

Apart from those (example) project-specific files, you also need to deploy the Borland Data Provider files. Starting with the bdpdb2.dll, which has to be placed in the path or, as is usually easier, in the same Bin directory where the DB2UDB.dll is deployed. That way, the ASP.NET Web Form will automatically find the dbpdb2.dll.

The three BDP assemblies—Borland.Data.Provider.dll, Borland.Data.Common.dll, and Borland.Data.Db2.dll—can be placed in the same directory as the Code Behind file, but in this case it's better to deploy them 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 (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 DB2 can be accessed using the Borland Data Providers for .NET. Connecting to DB2 UDB using the Borland Data Providers works in a uniform way, whether you're building GUI WinForms (as in my last article) or ASP.NET Web forms (this time).

Borland RAD Tools and DB2 UDB can connect on .NET, native Win32 as well as Linux, and with the flexible connection between the two, you can build GUI applications as well as Web applications that can be used to work 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.


Download

DescriptionNameSize
Code sample10 KB

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=13534
ArticleTitle=ASP.NET Web Forms With DB2 UDB Tables Using C#Builder and BDP for .NET
publish-date=07172003