How to Build an ASP.NET Web Site Using DB2 Data with Visual Studio 2005

Tuesday Aug 15th 2006 by Paul Zikopoulos
Share:

Paul Zikopoulos shows you how easy it is to be a .NET developer and work with DB2 UDB V8 or DB2 9 databases when building a .NET-based Active Server Pages (ASP.NET) Web site that's backed by a DB2 data store.

In a previous series of articles, I showed you all the great integration features between the IBM DB2 Universal Database for Linux, UNIX, and Windows Version 8.x (DB2 UDB) product and the Microsoft Visual Studio.NET 2003 integrated development environment (IDE).

In early June 2006, IBM announced the next release of the DB2 UDB product, DB2 9 (formerly known as DB2 Viper). Part of this announcement includes the support for Microsoft Visual Studio 2005 and its accompanying ADO.NET 2.0 driver.

As you may recall, Microsoft announced the Visual Studio 2005 product late in 2005, along with SQL Server 2005. Around the same time, the DB2 team delivered a beta of the now officially announced integration into Microsoft Visual Studio 2005 and ADO.NET 2.0.

In another article, I showed you how to quickly build a Windows Application that bound various controls (ComboBoxes, DataGrids) using the new Visual Studio 2005 support announced for DB2 9.

In this article, I want to show you how easy it is to be a .NET developer and work with DB2 UDB V8 or DB2 9 databases when building a .NET-based Active Server Pages (ASP.NET) Web site that's backed by a DB2 data store. Specifically, I will show you how to quickly create a Web page with Visual Studio 2005 using a DB2 UDB V8 database with this new tooling. (Of course, I could have easily used a DB2 9 database as well – the point here is to show off the integration of DB2 into the Visual Studio 2005 IDE.)

Note: Before you can create an ASP.NET-based Web site to which you will bind your DB2 data, a connection to that database must be defined in the Server Explorer. If you already have a database connection, continue with the steps outlined in this article. If you don't have a database connection, refer to the “Addendum: Adding a DB2 Data Source” section at the end of this article.

On Your Marks, Get Set....Just Before You Go

In order to follow along with all of the steps outlined in this article, ensure that your environment has the DB2 SAMPLE database. The examples in this article use this database (available to both DB2 UDB V8 and DB2 9 users). If it does not already exist, you can create this database quickly by entering the db2sampl command in a Windows command prompt.

Note that the SAMPLE database in DB2 UDB V8 doesn't come with any referential integrity (RI) constraints so ...? (The SAMPLE database in DB2 9 has been enhanced with RI constraints as well as a host of other objects like MDC and MQT tables, and more.)

Visual Studio 2005 will give you some added capabilities when generating an ASP.NET Web page when it can identify the primary key on a table. To demonstrate the rich features that Visual Studio 2005 provides for DB2 Web-based development, add a primary key (which is an online operation in a DB2 environment) to the EMPLOYEE table by performing the following steps:

1.  Ensure that the DB2 instance where the SAMPLE database resides has been started by entering the db2start command at a Windows command prompt.

2.  Start the Control Center by entering the db2cc command at a Windows command prompt.

3.  Expand the Control Center folders until you locate the Employee table in the Sample database, right-click this table, and select Alter. The Alter Table notebook opens.

4.  Select the Keys tab and click Add Primary. The Define Primary Key window opens, as shown below:

5.  Change the name of the primary key by typing EMPNO_PK in the Primary key name field.

6.  Select the EMPNO column in the Available columns box, click the > button to move it to the Selected columns box, and then click OK.

7.  You are returned to the Define Primary Key window. Click OK.

Go! Building a Web Page with a Back-end DB2 Data Server Using Visual Studio 2005

If you have a valid DB2 database connection, you can quickly build your first ASP.NET Web site that accesses a DB2 data server. In this section, I will outline how to build a quick Web page based on ASP.NET that quickly binds all of the rows in the EMPLOYEE table (which resides in the SAMPLE database).

To build this Web site, perform the following steps:

1.  Create a new ASP.NET application called QuickDB2WebSite by selecting File-->New Web Site-->ASP.NET Web Site, and clicking OK, as shown below:

2.  Ensure that you're working in the Visual Studio Web Designer mode by right-clicking the Default.aspx object in your Solution Explorer and selecting the View Designer option:

Note: If you cannot see the Solution Explorer, press Ctrl+Alt+L, or select View-->Solution Explorer from the menu bar.

3.  Select the Default.aspx object in the Solution Explorer, right-click, select Rename, and type DB2EMPLOYEEWebSite.aspx for the name of this Web page.


4.  Press Ctrl+Alt+X to open the Toolbox (or select View-->Solution Toolbox from the menu bar) and ensure that only the Data section is expanded. At this point, Visual Studio 2005 should now look similar to this:

5.  Drag a SqlDataSource object from the Toolbox to your designer palette. This operation adds the SqlDataSource object to your designer palette, at which point you can configure the object to connect to a DB2 data server, as shown below:

6.  Click Configure Data Source. The Configure Data Source Window opens.

7.  If you have an existing connection to a DB2 database in the Server Explorer, click the Which data connection should your application use to connect to the database? drop-down list, select the database connection that represents the SAMPLE database, and click Next.

If you don't have a database connection, click New Connection and refer to the “Addendum: Adding a DB2 Data Source to the Visual Studio 2005 Server Explorer” section at the end of this article.

8.  The Save the Connection String to the Application Configuration File window opens. Accept the default values for this example. Click Next.

9.  The Configure the Select Statement window opens. Select the Specify columns from a table or view radio button and select the <your_schema>.EMPLOYEE table in the Name drop-down list, as shown below:

10.  Select the * check box in the Columns window.

You can restrict columns from being populated in the data grid that you will bind to your Web page by individually not selecting each of the columns. The * operator has the effect of selecting all of the columns in the table, as you can see in the SELECT statement window. This window is refreshed based on the selections you make in the Columns window (as well as options available on this page – more on this in a bit).

Note: Don't make any of the changes shown in the remainder of this step; the information here is for illustrative purposes only. Of course, feel free to go back and experiment with these options after completing the steps in this article. Try rebuilding the same Web page using different options to get a good feel for how they work.

The Configure the Select Statement window has a lot of features and capabilities. For example, you can use an existing stored procedure to populate the data grid or build a query to restrict rows in that data grid, which ultimately gets displayed on your Web page. To perform either of the manual operations, select the Specify a custom SQL statement or stored procedure radio button. An example of defining a custom SQL statement is shown below:

You can see in this figure that you can manually build SELECT, UPDATE, INSERT, and DELETE statements for your Web page (something we'll have Visual Studio 2005 do automatically for us in a moment). You can even leverage encapsulated business logic in your DB2 stored procedures (which can be written in Java or .NET CLR code, as well as the SQL/PL and other languages). Also note in the previous figure the integration of the Microsoft Query Builder into this tool, which can also be used to manually generate queries to fill your Web site's data grid.

You can also use the WHERE, ORDER BY, and Advanced buttons to specify additional options that will apply to the generated query that will be used to populate your Web page's data grid.

The WHERE and ORDER BY buttons allow you to specify restriction predicates on the query, as well as an ordering mechanism. For example, if you wanted to order the data in your Web Page by an employee's Job type in descending alphabetical order (Z-->A, a favorite order of mine), for all the male employees, you could use these buttons, as shown below:

If you use any of these options, the SQL generated for your Web page will be automatically updated, as shown at the bottom of the previous figure.

Note: We'll turn our attention to the Advanced button on this window in the next step.

If you want to have DB2 populate your Web page only with unique rows, you can select the Return only unique rows check box.

11.  Click Advanced. The Advanced SQL Generation window opens.

12.  Select the Generate INSERT, UPDATE, and DELETE statement check box and click OK.

When you select this option, Visual Studio 2005 will automatically generate the supporting SQL data manipulation language (DML) for your DB2 database. Of course, you can do this manually as outlined in the previous step. However, if you're not planning to customize any of the default generated SQL, why not let Visual Studio 2005 handle it for you?

13.  Click Next. The Test Query window opens

14.  Click Test Query to preview the data that will be used to populate your Web page, and click Finish.

15.  From the Toolbox Data section, drag the GridView object (used to represent a data grid) and drop it just below the SqlDataSource object that you added in Step 5, as shown below:

16.  Click the Choose Data Source drop-down list and select SqlDatasource1 (the default name assigned to the SAMPLE database's EMPLOYEE table that you configured in the previous steps).

The data grid should be refreshed and look like this:

You can see that Visual Studio 2005 has started to bind the data columns to the data grid. The data that's displayed in the data grid are just place holders at this point and are used to represent each column's data type.

17.  You can configure some automated usability features that Visual Studio 2005 can automatically add to your Web page by clicking on the toggle at the right-most part of the grid view (if it did not open automatically, or if you closed it). For this example, select the Enable Paging, Enable Sorting, Enable Editing, Enable Deleting, and Enable Selection check boxes, as shown below, and then click anywhere in the data grid to return focus to the designer palette:

The GridViewTasks offers you a lot of options for the way the data is displayed on your Web page. For example, you can override what columns are displayed, their order, and so on, and perform a number of operations such as those shown in the previous figure. When you select any of these options, Visual Studio 2005 automatically adds links for these actions to the data grid – talk about easy!

You can see some of the results of the actions from this step below:

You can see links have been added to perform editing of data, deleting of data, and selecting of the data from your Web page (provided you have these privileges on the tables you are connecting to).

Also note the 1 2 links at the bottom of the data grid. Visual Studio 2005 attached these to your data grid because you selected the Enable paging option, which has the effect of breaking down large result sets into multiple pages and providing these controls to page through your query.

18.  In the same manner in which you dragged the DataGridView object to your designer palette, place a DetailsView object (also located in the Data section of the Toolbox) just below the DataGridView. The designer palette should now look like this:

19.  Click Choose Data Source and select the SqlDataSource1 object.

As you bound your DB2 data to your data grid, you also have to bind the details of a selected row to this control object as well.

20.  Ensure that the options for this object match the following figure:

You can see the designer palette will bind a row to the details view and provide you with similar options as it did in Step 17. Once again, if you look at the bottom of the previous figure, you can see Visual Studio 2005 is adding controls to the Web page automatically for you.

21.  Press Ctrl+F5 to see your Web site!

One of the great new features in Visual Studio 2005 is an integrated application server hosting environment (based on IIS) that allows you to quickly and easily test your Web applications. I love this feature and DB2 can take advantage of it.

If you scroll to the bottom of your Web page, you should see the controls added by Visual Studio 2005:

22.  Play around with the different links that were generated because of the options you selected for the data grids. When you're finished, close the Web page, and stop the application server, as shown below:

That Was Fast...

As you can see, creating this simple Web page was very quick. (The length of this article can be accounted for by the step-by-step documentation, which should allow even non-developers to successfully build the application outlined in this article.) Truly, this rapid application development experience is a result of the synergy between the Visual Studio 2005 IDE and the integration work that's gone into this environment for DB2 databases.

There's so much more for .NET developers who write applications for the DB2 platform to take advantage of. Stay tuned for my next article on this topic!

Addendum: Adding a DB2 Data Source to the Visual Studio 2005 Server Explorer

If you're familiar with the Visual Studio.NET 2003 support that DB2 UDB provides, you'll recall that .NET developers writing applications on the DB2 platform were required to work within the IBM Explorer. The IBM Explorer was functionally equivalent to the Server Explorer; however, there were no open interfaces into the Server Explorer in Visual Studio.NET 2003 by which DB2 UDB could leverage to provide some of the features unique to the DB2 UDB plug-in.

The architecture of Visual Studio 2005 changed such that there are now interfaces that let you develop applications that connect to DB2 UDB V8 and DB2 9 databases using the Server Explorer. This provides a more native experience for .NET application developers used to developing applications on SQL Server databases.

To add a DB2 database connection to your Server Explorer, perform the following steps:

Note: If you already have a database connection to the database that is to provide your ASP.NET Web site with data, you can skip this section.

1.  Right-click the Database Connections folder in the Server Explorer and select the Add Connection option. The Add Connection dialog box opens:

2.  Ensure that the Data source field points to the (.NET Framework Data Provider for IBM DB2) data provider so that the Server Explorer will use the ADO.NET data provider written by IBM specifically for DB2 UDB V8 and DB2 9 databases.

The DB2 ADO.NET provider is not the default provider shown in this field. To change the database provider to use the one for DB2, click Change and select the IBM DB2 option from the Data source box, as shown below. You should also ensure that the IBM DB2 Data Provider for .NET Framework is selected in the Data provider field, but this should be the default:

Note: If you plan to work frequently with DB2 database connections, select the Always use this selection check box so that, when you add another database connection, Visual Studio 2005 will automatically select the DB2 data provider.

3.  Enter the server name and port number (separated by a colon) in the Enter server name field. If you are connecting to a local database, you can use the localhost alias for your workstation.

Depending on the version of DB2 that you are running your beta on, you can optionally click Refresh to automatically list all the databases configured to respond to DB2 network database identification requests and automate the process of entering the required information.

4.  Enter your user account credentials in the User ID and Password fields. I recommend that you save these credentials in the connection string (they are encrypted) by selecting Save my password. Selecting this option makes application development more streamlined because you are not challenged to provide authentication details during subsequent access requests to the DB2 database.

5.  Select the database name from the Select or enter a database name drop-down list, or enter the name manually.

Note: In this article, I chose to connect to the SAMPLE database that is shipped with DB2 UDB V8. If you don't have the SAMPLE database created on your workstation, you can create it now by entering the db2sampl command from a Windows-based command prompt.

6.  Optional: You can use the Specify Connection Options and Specify Filtering Options sections to further customize your database connection. The options associated with these toggles are shown below:

The DB2 support for Visual Studio 2005 comes with a rich set of connection time and filtering options. For the purposes of this article, you can just accept the default values.

7.  Test the connection using the Test Connection button.

8.  Click OK.

After adding your database connection, the Visual Studio 2005 Server Explorer should look similar to this:

In the previous figure, you can see that I've expanded the SAMPLE database connection object; below it is a connection object to a SQL Server 2005 database. Notice the beside this database connection object: all databases appear this way until you click them to make the database connection.

About the Author

Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technologies team. He has more than ten years of experience with DB2 UDB and has written over one hundred magazine articles and several books about it. Paul has co-authored the books: DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloë – his new daughter. You can reach him at: paulz_ibm@msn.com.




Trademarks

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

Java is a trademark of Sun Microsystems, Inc. in the United States, other countries, or both.

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

UNIX is a registered trademark of The Open Group in the United States and other countries.

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

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

Copyright International Business Machines Corporation, 2006. All rights reserved.

Disclaimer

The opinions, solutions, and advice in this article are from the author's experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author's knowledge at the time of writing.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved