2. Estad´ıstica descriptiva unidimensional
2.2. Observaciones sobre la presentaci´on, modificaci´on y grabaci´on de los resultados en la ventana del visor de
In this example, you will create a page that displays all the items available from the PPQ site, using the two tables MenuItemsand 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 skeletonproject 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 Itemdialog, and click Add to add it to your project. Then switch to Design view in the main editing window, and drag a SqlDataSourcecontrol from the Toolbox onto the page. In the pop-up SqlDataSource Tasks pane, select Configure Data Source (see Figure 3-29).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.configfile 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 SqlDataSourcecontrol. 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-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 —MenuItemsand SizeAndPrice. Hold down the Ctrl key while clicking to select more than one item in the Add Table dialog (see Figure 3-33).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 MenuItemIDcolumn in the MenuItemstable, and drag it onto the fkMenuItemscolumn in the SizeAndPricetable (see Figure 3-34).Figure 3-34: Dragging the MenuItemIDcolumn
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 thefkMenuItemIDforeign key column (see Figure 3-35). You can either tick the boxes in the tables,
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 JOINor 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 JOINin 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 SortTypecolumn of the Criteria grid, select Descendingfor the MenuItemTypecolumn, Ascendingfor ItemName, and Ascendingfor ItemPrice. The Query Builder automatically sets the SortOrdercolumn 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 SELECTclause), 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!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).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 SqlDataSourcewill return, so drag a GridViewcontrol from the Toolbox onto the page. In the GridView Tasks pane that appears, choose SqlDataSource1— the SqlDataSourcecontrol you just configured (see Figure 3-40). You can also use the Auto Format . . . option to change the appearance of the13.
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 GridViewcontrol (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