![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Important: Read the disclaimer before reading this article.
If you're like me, developing applications for the Web usually involves exposing parts of your database so that it is viewable in a Web browser. By combining Borland C#Builder and the Borland Data Provider (BDP) components, you can easily get your DB2 Universal Database (UDB) running with data-driven Microsoft ASP.NET Web pages. For this solution, I used Borland C#Builder Enterprise (or Architect), DB2 UDB V8.1 Personal Edition and a Microsoft ASP.NET-compatible Web server.
ASP.NET is the next-generation Web application framework built on the .NET platform -- the successor to Microsoft's Active Server Pages (ASP) framework. ASP.NET allows rapid application development (RAD) of Web applications in a similar way to Windows Form applications. Creating Windows-style Web applications on the .NET platform is also known as WebForms.
To begin with WebForms, your components need to be placed onto a WebForm and, like typical Windows development, you set the properties for the component and write the event handlers for your components. In this way, developing Web applications is extremely simple.
Accessing DB2 from Borland C#Builder -- the Borland Data Providers (BDP)
Creating dynamic Internet applications is easy enough, but in most cases you will want to develop data-driven applications connecting to your DB2 UDB database. This is where the BDP components come in. Figure 1 shows the basic components that you need to connect to the data. All the components you need to connect through to DB2 UDB can be found on the BDP and Data Components pages of the Tool Palette.
Figure 1. Selecting the BDP components
DB2 UDB offers scalability, robustness, easy configuration, and -- my personal favorite -- the ability to create stored procedures in both SQL and Java, so it is very easy to connect to it with the BDP components. The main components that you will use to connect to the database are the BdpConnection component, the BdpDataAdapter component, the BdpCommand, and the DataSet component:
Figure 2. The BDP components work together for your data.
DataSets contain a collection of tables, the relationships between the tables, and other information such as the constraints for the data. Each query retrieved by the BdpDataAdapter is stored as a DataTable, and it is this DataTable object that holds the data from the database. For simplicity, we will work with a single DataSet that contains one DataTable.
The DataSet component, however, is designed to work without a permanent connection to the database. The process of retrieving data from the BDP components into the DataSet is to request that the BdpDataAdapter connect to the database, and then request that the BdpDataAdapter fill the contents of the DataSet with the results of one or more queries. Once your DataSet is populated, you can disconnect from the database. Whenever you need to retrieve or update data from the database, you simply reconnect to the BdpDataAdapter. That way, if network connectivity to DB2 UDB is lost you can still work with a local copy of the data, in preparation for when access to the server is available.
Starting to build your Web Application
Let's start on developing a Web grid displaying the results of a single database query. First, you will need to create a new Web Application. From the C#Builder IDE, select File | New | Other and then select a new ASP.NET Web Application.
After you have selected the options for your application, you have a WebForm that you can work with. In design mode, you can set up the display as if you were editing an HTML document in a WYSIWYG editor ( see Figure 3 ).
The magic really starts happening when you start dropping the ASP.NET components onto your Web form. The ASP.NET components are easily distinguishable from other elements on the page because they normally have 'asp' painted on the top left corner of the controls. To begin working with data, place a DataGrid component, a BdpConnection component, and a BdpDataAdapter on the designer:
Figure 3. A sample setup for a new Web form
To connect to your database, right-click the BdpConnection and select Connection Editor. From this menu, select the Add button to add a data connection. Then select the database you want to connect to and create a name to represent the connection to the database. On my system, I am returning all the entries of a table called PRODUCTLIST on my DB2 8.1 Personal database called SHOPPING ( see Figure 4 for the connection parameters for the BdpConnection). Here is the DDL for PRODUCTLIST:
CREATE TABLE "GLENN "."PRODUCTLIST" (
"PRODUCTCODE" VARCHAR(20) NOT NULL ,
"PRODUCTNAME" VARCHAR(50) NOT NULL ,
"DESCRIPTION" VARCHAR(255) ,
"UNITPRICE" DOUBLE NOT NULL ,
"CATEGORYCODE" INTEGER ,
"IMAGEURL" CHAR(150) )
IN "USERSPACE1" ;
COMMENT ON TABLE "GLENN "."PRODUCTLIST" IS 'A list of Products in the Shopping Cart';
ALTER TABLE "GLENN "."PRODUCTLIST"
ADD CONSTRAINT "CC1053568050795" PRIMARY KEY
("PRODUCTCODE");
|
After creating the settings, you will also need to provide the connection settings for the database connection. Once the connection settings are defined, you should be able to click on the Test button in the Connections Editor to find out if you can connect successfully or not.
Figure 4. The Connections editor form allows you to pre-define your connection to the database
Once the settings are defined and you can connect to the database, you are ready to set up the BdpDataAdapter component. Looking back at Figure 2 , you can see the relationship between the BdpDataAdapter component and the BdpConnection and the BdpCommand objects.
The BdpDataAdapter is the powerbroker for working with the BDP components. The BdpDataAdapter connects to the database via the BdpConnection component. The BdpDataAdapter component also has four BdpCommand properties -- SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand. These BdpCommands are used to perform the retrieval and update operations against the database. The command that represents the SelectCommand is used to select all the data used to populate a DataTable, whereas the other three BdpCommands are used to represent the database operations to insert, update, and delete a single DataTable row.
The basic concept behind the BDP components is that the BdpDataAdapter will fill the DataSet with content from your database so that you can work with the DataSet. The DataSet should then be disconnected from the BDP components. When the DataSet needs to retrieve more data or update data, it connects through to the BdpDataAdapter when needed. Now that you have the basic idea about the data connectivity to DB2 using the BDP components, let's start connecting your application to some data.
To configure your BdpDataAdapter, right-click on BdpDataAdapter on your WebForm, and then select Configure Data Adapter to get the Data Adapter Configuration dialog:
Figure 5. The Data Adapter Configuration allows you to define which BdpConnection component you will use
On the Select tab, enter the SQL needed to retrieve the data. In this example, we will be retrieving some data from a simple shopping cart database, and this query in particular will retrieve a list of products from the PRODUCTLIST table.
Figure 6. Selecting a DataSet to populate the DataAdapter
Once the SQL is entered, you can state that you want C#Builder to create a new DataSet component for you (see Figure 6 , above), or use an existing DataSet component that you may have dropped on your WebForm. It is this DataSet object that your Grid component will use to retrieve its data. You are free to create a new DataSet or use an existing DataSet that you have dropped onto your WebForm. In this example, though, we will be creating a new DataSet for our application. At this point, we have all the pieces to connect to our data. We just need to define the presentation of the data.
C#Builder Super-Speed Development Tip #1: Once you have defined the connection to your DB2 UDB Database, select Table, View from the Data Explorer and drag that table view or stored procedure onto your WebForm. For a Table or a View, C#Builder will automatically create the BDP components needed to connect to your DB2 UDB.
To display this presentation, use the DataGrid component that you placed on the Web Form. To be able to work with the BdpDataAdapter in design time, you will need to set Active on the BdpDataAdapter component to True so that the grid will be able to retrieve the schema information of the query that you will be using.
Once the BdpDataAdapter is active, select the DataGrid's Property Builder option. (You can find this option under the properties listing in the property editor.) You will be presented with a dialog that defines the data connectivity and also the display aspects of the DataGrid.
Figure 7. Defining the DataSet, the DataMember, and the primary key for the DataMember
The first of the DataGrid's Configuration options defines the connection details to the data, such as which DataSet you will be using and which DataMember (DataTable or DataView) of the DataSet you want to view. You also need to define which field in the DataMember is used as the key field for that DataView.
At this point, this is all you need to set up the data grid. In most cases, though, you will also want to define the columns that will be displayed on the grid. By selecting the Columns page for the DataGrid (see Figure 8), you can define which columns you want displayed, alter the order of the columns, and customize the formatting of the column header and of the column data. In my example, I am just renaming the headers for each of the fields, and stating that the UNITPRICE field should be displayed as a currency.
Figure 8. Customizing and formatting the columns
Now all that stands between you and a data-driven Web page is a few lines of code that tell the Web page to bind the data from the DataSet to the DataGrid. In ASP.NET, components with data connectivity are not connected by default. Instead, you must tell the code that you want to bind to the data. So in the WebForm's Page_Load event, use the DataBind method of the DataGrid to tell it to connect through to the data:
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack) {
dataGrid1.DataBind();
}
} |
ASP.NET uses a mechanism called Post Back to manage the state of the Web form. The first time a Web page is requested, you will initialize the properties of your WebForm. When the end-user connects with the data, they work with the same properties that have been set, so the data only needs to be bound to the grid the first time the page is loaded.
Once you have added the code to the Page_Load event, you are ready to run your Web application. After running the page, you will have a professional-looking Web page that contains dynamic Web data:
Figure 9. Your DB2 data displayed in your Web browser
Data Binding -- beyond the grid
Now that you've had a taste of data binding, you're going to love the way you can bind to components. In this example, you will display only the contents of a single record: the contents of a product on a single page.
Start by adding a new ASP.NET page to your application by selecting File | New | Other and then selecting a new ASP.NET Page. Go through the same steps as before for the result set for the grid, but instead of selecting all the records in a table, select a single record. Figure 10 shows the Data Adapter Configuration dialog for the SQL. Notice how the question mark (?) is used to denote a parameter in the SQL statement:
Figure 10. Creating a parameterized query
Once the SQL is defined, notice (just like the last time) that a bdpCommand object is created and added to your WebForm and this bdpCommand object represents the select query. Since you have created a parameterized query, you also need to define what each parameter represents.
Select the Parameters for the BdpCommand, and then set the parameters to represent those required for your database query. Adding a parameter is fairly straightforward. For each parameter that you need, define the parameter's data type, length, and a name, if desired. For this example there is a single parameter for the product code, which is a string. As a result, you need to set the BdpType of the parameter object to string. Because the field length of PRODUCTCODE is 20 characters long, you should also set the data length of the PRODUCTCODE field to 20.
After you have defined the parameters, you need to define the data bindings for the components on your WebForm. To bind data into a component, drop that component on the form, and select its DataBindings (see Figure 11). Then define which property of the component you want to assign the data to, and which field this property is getting its value from.
For a component such as a label, you need to bind only the Text of a Label component to a field like ProductName. But for a component like an Image, you will probably want to bind many different fields to different properties. Figure 11 shows the data field URLImage being retrieved from the IMAGEURL field in the database. Not only that, the AlternateText is being retrieved from the PRODUCTNAME field.
Figure 11. Using Data Binding to bind different fields to different component properties
As a result, you should be able to view the details of a single data request by having the product code be passed on the query string of the URL. And just like the DataGrid example, you need to load the page and bind the data. Because you are using a parameter, you need a different Page_Load event to retrieve the product code, place it into the BdpCommand, fill the data, and then bind the data to the components on the form:
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
if (!IsPostBack) {
//Get the Product Code from the Query String
string strProdCode = Request.QueryString["ProductCode"];
bdpCommand1.Parameters[0].Value = strProdCode;
bdpDataAdapter1.Fill(dataSet1, "Table1");
this.DataBind();
}
} |
Notice that instead of using DataBind on each data-aware component, you can call the DataBind method for the WebForm and it will call the DataBind method for each component on the WebForm. This option certainly makes things easy.
Also notice that I am calling the Fill method on the dataset passing the dataset I want to fill, and also the name of the Data Table. As the C#Builder IDE automatically uses Table1 ( see Figure 11 ), I have kept with that naming convention to avoid recoding.
Now that all the data is connected, placing the ProductCode in the URL should show you a solid data-driven page:
Figure 12. A flexible, data-driven Web page
As you can see, by using the data binding facilities of .NET, you can get powerful data-driven applications up and running in an extremely short timeframe.
Improving performance with inbuilt caching
Another good way to get speed improvements in your data-driven Web applications is to use the inbuilt caching features of ASP.NET. If your Web application gets lots of hits and the data is static, you can take advantage of the caching facilities of ASP.NET.
Caching works by adding an OutputCache declaration to your .aspx page that tells you the rules for the caching:
<%@ Page language="c#" Debug="true" Codebehind="WebForm1.aspx.cs"
AutoEventWireup="false"
<br />Inherits="MyNewWebApplication.WebForm1" %>
<%@ OutputCache Duration="300" VaryByParam="None" VaryByCustom="browser" %> |
This means that the page will be stored in the cache and each subsequent request will use the Cache for 300 seconds (five minutes) from the first request for the page. The VaryByParam attribute allows you to cache page results based on the results in the query string. This wouldn't be needed for the page with the DataGrid, but would work extremely well for the page that takes a parameter on the URL, such as the Single product page. In the example that displays a single record, you would have code that looks like this:
<%@ Page language="c#" Debug="true" Codebehind="WebForm2.aspx.cs"
AutoEventWireup="false"
>Inherits="MyNewWebApplication.WebForm2" %>
<%@ OutputCache Duration="60" VaryByParam="*" VaryByCustom="browser" %> |
The last attribute is the VaryByCustom, which is set to 'browser' to cache values specifically for certain types of browsers. Because the Web Server controls will render themselves differently for different browsers, the cache should only ever return the correct cached page specifically for the client's browser.
Once you implement caching in your application, you will see a dramatic improvement in the speed your pages take to load. Try it out by adding the caching techniques, and you will see the performance improvement.
Deployment issues for ASP.NET, DB2 and C#Builder
When you have your ASP.NET application ready, you will need to deploy it to a server that supports ASP.NET, such as Windows 2000 Server or Windows 2003 Server. Like most DB2 database applications, you will need to have the DB2 client installed on the server that accesses the database. This means that your ASP.NET server should have the DB2 client installed. As well as this, your ASP.NET server will need to have the BDP DLLs in order to use the Borland Data Adapter Components.
The main files that you will need are
Borland.Data.Common.dll
and
Borland.Data.Provider.dll
. You will also need the DB2 provider for the BDP, which is the
Borland.Data.Db2.dll
. Have this installed on a .NET-ready Web server and you're ready to go.
Note: It is worth mentioning that this article was written with a pre-release version of C#Builder, and while most things will most likely remain the same, it is advised that you read through the deploy.txt file found in the C#Builder installation directory for any changes for deploying the BDP with DB2.
combining ASP.NET with a high-performance, scalable database like DB2 UDB and the Borland Data Providers, you can get impressive data-driven Internet applications in a short amount of time with a minimum of coding.
In my next article, I'll show you how to start working with ASP.NET Web Services with your DB2 UDB database, retrieving and updating changes from remote WinForms and WebForm clients.
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.
Glenn Stephens is a Borland Certified Consultant, a Microsoft Certified Solution Developer, and the author of The Tomes of Kylix -- The Linux API . Feel free to contact Glenn via email at glenn@glennstephens.com.au
Table of contents
- Introduction
- So what is ASP.NET?
- Accessing DB2 from Borland C#Builder -- the Borland Data Providers (BDP)
- Starting to build your Web Application
- Configuring the Data Adapter
- Populating the DataGrid
- Data Binding -- beyond the grid
- Improving performance with inbuilt caching
- Deployment issues for ASP.NET, DB2 and C#Builder
- Conclusion
- Disclaimer
- Resources
- About the author
- Comments
Next steps from IBM
Tags
Use the slider bar to see more or fewer tags.
For articles in technology zones (such as Java technology, Linux, Open source, XML), Popular tags shows the top tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), Popular tags shows the top tags for just that product zone.
For articles in technology zones (such as Java technology, Linux, Open source, XML), My tags shows your tags for all technology zones. For articles in product zones (such as Info Mgmt, Rational, WebSphere), My tags shows your tags for just that product zone.
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
