Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Aug 15, 2006

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

By Paul Zikopoulos

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.

DB2 Archives