ASP.NET 2.0 Visual Web Developer 2005 Express Edition Starter Kit (2006)
.pdf
Chapter 3
7.Now close the Database Diagram window. You will be prompted to save the diagram (you can accept the default name). The Database Diagram window then adds the constraints that implement the new relationships to the MenuItems, OrderItems, and Orders tables, and prompts you to save these changes to the tables.
You now have all the tables you require for the PPQ Web site, and the next step is to add a page that displays the menu data from the MenuItems and SizeAndPrice tables. However, this is not as straightforward as the technique you saw in Chapter 1 of just dragging the table onto a page, because the data must come from two related tables. To finish this chapter, you will see how to create a page that accesses data in more than one table.
Extracting and Displaying the Menu Items
In this final section of the chapter, you will create a simple page that extracts all the data from the MenuItems and SizeAndPrice tables, and displays it in a grid. Because the data is in two tables, you will need to join the tables together to get the rows you need. There are a few ways of doing this, all based on a SQL statement that implements a table join. You will see three possibilities in the remainder of this chapter:
Using a custom SQL statement
Using a stored procedure
Using a database view
The first of these naturally leads to the other two, as you will see later in the chapter.
Extracting Data with a Custom SQL Statement
In this example, you will create a page that displays all the items available from the PPQ site, using the two tables MenuItems and SizeAndPrice. These contain all the data about the PPQ menu, but as it is divided over two tables, you must create and configure a data source control to access and combine this data into the correct format.
1.Open the skeleton project and add a new Web Form to it using the New File . . . option on the File menu. Specify the name TestMenu2.aspx for the new Web Form in the Add New Item dialog, and click Add to add it to your project. Then switch to Design view in the main editing window, and drag a SqlDataSource control from the Toolbox onto the page. In the pop-up SqlDataSource Tasks pane, select Configure Data Source (see Figure 3-29).
Figure 3-29: Selecting Configure Data Source
94
Building the PPQ Database
2.This starts the Configure Data Source Wizard. In the first page, select the existing connection string (see Figure 3-30). This connection string was automatically created and added to the project when you carried out the examples in Chapter 1. It is actually stored in the Web
.config file for the application.
Figure 3-30: Selecting an existing connection string
3.The next page in the wizard allows you to select the object in the database that will provide the data for the SqlDataSource control. However, you can select only one table (as shown in the left-hand side of Figure 3-31). You need to include data from two tables, so select the option to specify a custom SQL statement or stored procedure, and click Next.
Figure 3-31: Selecting one table
4.The next page in the wizard allows you to type in or create the SQL statement you need. The easiest way to build a SQL statement if you are not familiar with SQL is through the Query Builder. Click the Query Builder . . . button in the wizard (see Figure 3-32).
95
Chapter 3
Figure 3-32: Using the Query Builder
5.The Query Builder window opens, followed by the Add Table dialog. Select the two tables you require for the query — MenuItems and SizeAndPrice. Hold down the Ctrl key while clicking to select more than one item in the Add Table dialog (see Figure 3-33).
Figure 3-33: Add Table dialog
96
Building the PPQ Database
6.The two tables now appear in the top section of the Query Builder window. However, the relationship between them is not enforced. This allows you to create a relationship for this query that differs from the one in the database that is there to maintain referential integrity. To create the relationship you need for this query, click on the MenuItemID column in the MenuItems table, and drag it onto the fkMenuItems column in the SizeAndPrice table (see Figure 3-34).
Figure 3-34: Dragging the MenuItemID column
7.You will see the relationship between the two tables appear in the Query Builder window. Select all the columns in the two tables except for the two “* (All Columns)” entries and the fkMenuItemID foreign key column (see Figure 3-35). You can either tick the boxes in the tables, or drag the columns onto the Criteria grid below them.
Figure 3-35: Relationship between two tables
Right-clicking the diamond-shaped “icon” on the link opens a context menu where you can change the properties of the relationship. The default for a relationship is an INNER JOIN on the tables, which means that a row will be included in the results only if there is both a parent row and a child row with the same value for the columns that provide the relationship. In this example, a row will be present in the results only if there is a row in the SizeAndPrice table with the same key value in the fkMenuItemID column as it exists in the MenuItemID column of a row in the MenuItems table.
97
Chapter 3
However, you can specify that the query will retrieve all the rows from one of the tables (irrespective of whether there is a matching row in the related table), and leave the columns empty where there is no matching row in the other table. This kind of relationship is a LEFT JOIN or a RIGHT JOIN, depending on which table provides all of its rows. The context menu options allow you to specify “Select All Rows from MenuItems” or “Select All Rows from SizeAndPrice.” However, you need the default INNER JOIN in this example, so you do not need to change any of the properties.
8.Now you can specify the sort order for the results. In the SortType column of the Criteria grid, select Descending for the MenuItemType column, Ascending for ItemName, and Ascending for ItemPrice. The Query Builder automatically sets the SortOrder column as you specify the sort type, but you can change the order if you wish. In this case, the results will be sorted first by descending item type (“Pizza” then “Drink”), then by name, then by ascending price (see Figure 3-36).
Figure 3-36: Sorting by descending item type
9.As you build the query, the equivalent SQL statement that will be executed to extract the data is shown in the SQL pane below the Criteria grid. You can see in Figure 3-37 the columns that are included in the result (in the SELECT clause), the tables that provide the data (in the FROM clause, including the INNER JOIN), and the ordering of the resulting rows (in the ORDER BY clause). Click the Execute Query button at the bottom of the Query Builder window to see the results. You have succeeded in creating a list of all the items on the menu of the PPQ Web site, even though they are stored in two separate tables!
98
Building the PPQ Database
Figure 3-37: SQL statement execution
10.Click OK in the Query Builder window to go back to the Configure Data Source Wizard. You will see that it now contains the SQL statement you just created, so you can click Next to continue (see Figure 3-38).
Figure 3-38: Resulting SQL statement
99
Chapter 3
11.In the next page of the Configure Data Source Wizard, you can test the query you entered (see Figure 3-39). In fact, you already tested it in the Query Builder window, but this page is useful because you can test the query if you created it by selecting a table, or typed the SQL statement you want directly into the “SQL statement:” text box in the previous page of the wizard.
Figure 3-39: Testing a query
12.Click Finish in the Configure Data Source Wizard, and you are back in the editing window in VWD. The next stage is to add a control to display the data that the SqlDataSource will return, so drag a GridView control from the Toolbox onto the page. In the GridView Tasks pane that appears, choose SqlDataSource1 — the SqlDataSource control you just configured (see Figure 3-40). You can also use the Auto Format . . . option to change the appearance of the
GridView control.
Figure 3-40: GridView Tasks pane
100
Building the PPQ Database
13.Now press the F5 key, or click the Run icon on the main VWD toolbar, and you will see the rows that are returned by the custom SQL statement you created displayed in the GridView control (see Figure 3-41). This proves that it is possible to reassemble the data from separate tables at any time to get the set of rows you want. The rules of normalization that you followed in the design of the database give the benefits of improved storage efficiency within the database and simplified data updates (you can clearly see the repeated data in Figure 3-41). Yet you can still extract data in the format and structure you need using a custom SQL statement.
Figure 3-41: Rows returned by the custom SQL statement
Creating a Stored Procedure
The previous example used a SQL statement to extract the data required for the GridView control. However, this is not generally the best technique for a production application. If you switch to Source view, you will see that the SQL statement is stored in the page as the value of the SelectCommand attribute of the SqlDataSource control.
Instead, you can use a stored procedure to extract the data you want. A stored procedure is, as the name suggests, a query stored within the database that returns the results required to create the output you want. This approach hides the structure of the database and tables from the user, as they just execute the stored procedure and get back a rowset (a series of rows of data) or whatever result the stored procedure is designed to generate. It is also more efficient because the database server can precompile the stored procedure, and reuse the compiled code. In addition, in enterprise-level databases, the administrator can set access permissions on the tables so that users can only run a stored procedure, and not access the data in the tables directly.
101
Chapter 3
There are also benefits from separating the user interface code (the Web Form) from the data access code when using a stored procedure. Providing that the stored procedure returns the same results, the actual structure of the underlying tables can change as required — without requiring any changes to the pages that access the data.
A stored procedure that returns a rowset is, effectively, just a query. The code in the stored procedure is one or more SQL statements or other commands (called Transact SQL or T-SQL). You can use the same SQL statement you generated with the Query Builder in the Configure Data Source Wizard to create a stored procedure for the example page you saw in the previous section.
1.Start the Configure Data Source Wizard from the Configure Data Source link in the SqlDataSource Tasks pane (opened from the arrow icon that appears when you mouse over the SqlDataSource control in Design view). Click Next until you get to the “Define Custom Statements or Stored Procedures” page, and copy the custom SQL statement you created with the Query Builder to the clipboard by highlighting it and pressing Ctrl-C (see Figure 3-42).
Figure 3-42: Copying the custom SQL statement to the clipboard
2.Now click Cancel to close the Configure Data Source Wizard, go to the Database Explorer window, right-click the Stored Procedures entry, and select Add New Stored Procedure from the context menu that appears (see Figure 3-43).
102
Building the PPQ Database
Figure 3-43: Selecting Add New Stored Procedures
3.This opens the stored procedure editor in the main window, and you can paste the SQL statement into the procedure outline that is provided. The text in green is just comments or placeholders (the characters “/*” and “*/” are comment delimiters in a stored procedure) and can be ignored for now. Make sure you change the name of the stored procedure in the first line to dbo.usp_GetAllMenuItems, as shown in Figure 3-44.
Figure 3-44: Changing the name of the stored procedure
The prefix dbo for the stored procedure name indicates that the owner of the stored procedure (within the database) is the built-in default system user. The actual name you use to refer to the procedure later will generally not include this prefix. The characters “usp” at the start of the name is a convention that indicates this is a “user” stored procedure (as opposed to a “system” stored procedure). System stored procedures start with “sp” or “xp” and so using a different prefix makes it easier to see which stored procedures are yours and which were created by VWD or the database itself.
103