Back in the old days, connecting two data grids to enable master/detail relationships wasn’t that easy. Now with Visual Studio 2005 and ASP.NET 2.0, building master/detail relationship apps is just a matter of using the right controls for the job. This tutorial describes using DB2 with ASP.NET 2.0’s master/detail relationship enhancements. The intention is to help you leverage the latest features of Visual Studio 2005 to make data-driven Web application development a breeze.
You will learn how to build a master/detail application quickly using the grid view and details view controls—and writing little or no code. The example application uses a typical HR scenario: determining and displaying the employees who belong to a particular department.
A basic knowledge of VS 2005 and ASP.NET 2.0 is necessary to complete the tutorial, as are the following system and software requirements:
- A basic P4 Machine with 512 MB RAM and about 20 GB HDD space
- Windows 2000 and above
- A personal copy of DB2 UDB (You can download DB2 Express-C Edition for free, but any edition will do.)
- Microsoft Visual Studio.NET 2005 (Professional or Team System)
- An install of the DB2 Add-in and Data Provider for .NET 2.0, once you have completed the installation of DB2 UDB
To begin building the example master/detail application, create an employee (EMP) table and a department (DEPT) table. You can reuse the SAMPLE Database for creating these tables. The following are the structures for each:
EMP Table -- DDL Statements for table "ANIL "."EMP" ------------------------------------------------ CREATE TABLE "ANIL "."EMP" ( "EMPNO" CHAR(10) NOT NULL , "ENAME" VARCHAR(40) , "DEPTNO" CHAR(10) , "SALARY" DECIMAL(10,2) , "EMAIL" VARCHAR(50) ) COMPRESS YES IN "USERSPACE1" ; -- DDL Statements for primary key on Table "ANIL "."EMP" ALTER TABLE "ANIL "."EMP" ADD CONSTRAINT "PK_EMPNO" PRIMARY KEY ("EMPNO"); DEPT Table -- DDL Statements for table "ANIL "."DEPT" ------------------------------------------------ CREATE TABLE "ANIL "."DEPT" ( "DEPTNO" CHAR(10) NOT NULL , "DNAME" VARCHAR(50) , "LOCATION" VARCHAR(10) ) IN "USERSPACE1" ; -- DDL Statements for primary key on Table "ANIL "."DEPT" ALTER TABLE "ANIL "."DEPT" ADD CONSTRAINT "PK_DEPTNO" PRIMARY KEY ("DEPTNO");
Next, run the following script to enable primary and foreign key relationships between the two tables:
-- DDL Statements for primary key on Table "ANIL "."EMP" ALTER TABLE "ANIL "."EMP" ADD CONSTRAINT "PK_EMPNO" PRIMARY KEY ("EMPNO");
Now that the tables are ready, you can insert some valid data. The DEPT table will have four departments consisting of DEPTNO ranges from 10, 20, 30, and 40, respectively. The department names will be IT, Mktng, Finance, and HR. For the EMP table, make sure that you enter the correct DEPTNO for a particular employee to maintain referential integrity.
Once you have done that, you can visually build your master/detail application in Visual Studio 2005. You may run the SQL statements for the DEPT and EMP tables in the Command Editor and change the schema from ANIL to anything you choose.
Building the Application
Launch Visual Studio 2005, and then create a new Web site. Choose either C# or VB. Next, switch to design view, and then drag and drop a GridView Control and a Details View Control onto the Web form. Your application should now look like the one shown in Figure 1.
Figure 1: Web Form of Your Demo Master/Detail Application
Next, you configure the data source for your Grid View and Details View. Click on your Grid View Control and under Data Source choose New Data Source. Select Database and name it MasterDetail. Click OK.
Configuring the Details View
You now will see a dialog in which you need to specify an existing connection to a DB2 database. Click next and name this connection MasterDetailconString. Click Next. (Note: The DB2 Connection Wizard is being replaced and added to the Server Explorer to help maintain consistency when connecting to a variety of databases.) Now you will be asked to choose the table from DB2. Select DEPT table and click on the asterisk (*) checkbox to display all records. Finally, click Finish.
Configuring the Grid View
Follow the same steps for configuring the Grid View, except when you come to the screen where you chose DEPT as your table, click on the WHERE button instead. You need to modify your SQL statement with the following options in order to display the appropriate employee that belongs to a particular department:
- Column: DEPTNO
- Operator: =
- Source: Control (This enables you to choose GridView1 as your source control for retrieving the appropriate employee information.)
- Control ID: GridView1, and then click the Add button to complete the SQL query expression.
Figure 2: WHERE Clause With All the Correct Selections
Click OK and Finish, and your Grid View will be ready to go. You may want to auto-format it as well as your Details View for a better presentation.
You now have both your views configured, and you are ready to run the application. Your final application will look like the one shown in Figure 3.
Figure 3: Your Final Demo Master/Detail Application
Run the application by pressing F5. You should be able to view the final application as shown in the browser in Figure 4.
Figure 4: Browser View of Your Final Demo Master/Detail Application
Now wasn’t it easy to work with DB2 using ASP.NET 2.0?
About the Author
Anil Mahadev is a DB2 enthusiast and DB2 India User Group Lead Logo Architect. Send him your feedback and comments at [email protected].