GETTING ORDERS ON THE WEB 1075

One drawback of the ProductSearch application is that it doesn’t allow the user to remove an item from the basket. You can add a second column with hyperlinks or buttons to the DataGrid of the first page and, every time the user clicks it, remove the corresponding product from the basket. To add a column with Remove buttons, open the Property Builder of the DataGrid control on the form. Add a new Button column and set its Text property to Remove and its CommandName property to RemoveItem.

When any of the buttons in this column are clicked, the ItemCommand event will be raised. You must add some code in this event’s handler to figure out what button was clicked (the Buy or Remove link) and act accordingly. Here’s the code you must insert in the ItemCommand event handler:

If e.CommandName = “RemoveItem” Then

If Not Session(sItemID) Is Nothing Then

Session.Remove(sItemID)

End If

Exit Sub

End If

The ProductSearch project on the CD contains the code for saving the new order to the database. It requires that the NewOrder and NewOrderLine stored procedures (from Chapter 22) be attached to the database.

Paging Large DataSets

Large DataSets have always been a problem in presenting data on Web pages. Displaying hundreds of rows on a single page isn’t very practical, and designers have come up with a technique for breaking the DataSet into pages. A page is a group of rows that are displayed on the same form. At the bottom of the form, there are usually links for the next and previous page, or links to all the pages that make up the DataSet. The user can click a link and jump to any other page of the DataSet. Paged DataSets are useful when they contain a relatively small number of pages. A DataSet with 300 pages isn’t very practical, even though you’re not displaying all the rows at once. How’s the user supposed to figure out which page contains the row he’s interested in? Very large DataSets aren’t appropriate for typical Web applications. If a search returns more than 100 rows, you should probably ask users to be more specific.

Among its many other features, the DataGrid control supports paging. It provides properties and recognizes events that simplify displaying the page numbers at the bottom of the control and moving to the appropriate page when a page hyperlink is clicked. To enable paging, you must set the control’s AllowPaging property to True and the PageSize property to the number of rows per page. The PagerStyle property determines the layout of the paging section of the control. This property is an object that exposes properties for setting the background/foreground color of the pager, the font, and so on. One of the properties is exposes is the Mode property, which can have one of the two values: NextPrev and NumericPages. These are the two pager modes that are supported automatically. You can also turn on AllowCustomPaging, in which case you must provide your own mechanism for paging through the DataSet (we will not discuss custom paging techniques in this book).

Once you’ve enabled paging, the control will receive the PageIndexChanged event every time one of the paging links is clicked. These links may be the previous/next arrows or page numbers. In this event’s handler, you must set the page to jump to, and refill the DataSet. Listing 24.15 is a typical PageIndexChanged event handler.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

1076 Chapter 24 ACCESSING DATA ON THE WEB

Listing 24.15: Handling Paging Events

Private Sub DataGrid1_PageIndexChanged(ByVal source As Object, _

ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) _ Handles DataGrid1.PageIndexChanged

DataGrid1.CurrentPageIndex = e.NewPageIndex DACustomers.Fill(DSCustomerNames1, “Customers”) DataGrid1.DataBind()

End Sub

The property e.NewPageIndex is the page number selected by the user on the control. The transition to the new page isn’t automatic; you must explicitly set the CurrentPageIndex property. You can also set this property to –1 to effectively cancel the paging action.

Then you must refill the DataSet and bind the control to it again to force its contents to change. The DataSet doesn’t reside anywhere in the server’s memory, so you must recreate it.

There’s one last implication in programming DataSet paging events. Every time an item is clicked on the control, the ItemCommand event is also fired. We use this event to retrieve the selected item’s ID, in order to retrieve additional information from the database and update another control on the form. This event will also take place even when a paging link is clicked; you must ignore this type

of event in the ItemCommand event by inserting the following line at the beginning of the ItemCommand event handler:

If e.Item.ItemType = ListItemType.Pager Then Exit Sub

When to Use Paged DataSets

A word of caution on using the paging capabilities of the DataGrid control. The control doesn’t know how to retrieve the rows of the current page from the database. Instead, it retrieves all the qualifying rows and then displays the appropriate subset, taking into consideration the current paging settings. Every time you click the Next button, for example, you read all the qualifying rows from the database, display a small subset of the rows, and discard the rest. This isn’t the most efficient method of handling paging. If you want to add paging capabilities to your application, you must customize the default pager and provide your own code. However, we have already limited the number of qualifying rows to 100. We can use the default pager to create 10 pages of 10 rows each (at most) without placing a real burden on the database server. I have seen paging schemes on the Web with lists of page numbers that take up a dozen lines on the page. Use the DataGrid’s built-in paging features carefully, and make sure your pages will look good even if users select an enormous DataSet, like all history books. There’s nothing wrong with limiting the selection to 100 or 200 rows. Users will have to be more specific about the rows they’re searching for. Besides, what’s the average user going to do with 2,500 rows that meet their criteria?

Paging has always been a sore point in designing Web applications, and programmers have tried all kinds of paging schemes. The problem is that the rows in a table are not numbered, and there’s no SQL statement that can retrieve the N rows following a specific row. Even if the existing rows were numbered, how would you handle insertions and deletions? Paging is a technique for guiding the user close to the desired row. To use paging techniques efficiently, limit the number of rows returned by a query with the TOP keyword, so that you won’t have to display 300 pages of 20 rows each.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

A MASTER/DETAIL WEB PAGE 1077

A Master/Detail Web Page

This is one of the most common types of Web applications. Figure 24.17 shows a page with customers, orders, and order details. The top DataGrid control displays 10 customers at a time in paged mode. As explained already, paging doesn’t save you from downloading the entire Customers table every time the user switches to another page. If your Customers table contains thousands of customers, you should combine this application with the techniques discussed in the WebProducts or ProductSearch applications to limit the number of customers in the DataSet (you can force users to select customers by country or state, company, and so on).

Figure 24.17

A master/detail Web form with two nested tables

When the Orders button on a customer row is clicked, the selected customer’s orders are displayed on the DataGrid below. This DataGrid control isn’t paged (you can limit the orders to the current year, or even month, to make sure that the page doesn’t grow too long or you can add paging to this control as well). When the Details button on an order row is clicked, the order’s details are displayed on the third DataGrid control. MasterDetail is an interesting application that demonstrates data binding in a Web app, how to customize the DataGrid, and how to program the events of the DataGrid control.

Building the MasterDetail application is a lengthy process, but it’s also an overview of the material covered in Chapter 21 (how to populate DataSets with the visual tools). Start a new ASP.NET Web application and name it MasterDetail. Then drop the Customers, Orders, and Order Details

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

1078 Chapter 24 ACCESSING DATA ON THE WEB

tables from the Server Explorer onto the design surface. Rename the three DataAdapter object that will be created to DACustomers, DAOrders, and DADetails. Configure them as follows.

DACustomers This DataAdapter retrieves all the customers of the Northwind database with the following SELECT statement:

SELECT CustomerID, CompanyName, ContactName FROM dbo.Customers

DAOrders This DataAdapter retrieves the orders of a selected customer. In Figure 24.17, you’ll see that it includes a calculated field, the Order Total field. The total of an order isn’t stored anywhere in the database and must be calculated by the SELECT statement that retrieves the orders. The DAOrders object’s SELECT command is shown next (I’ve removed the table qualifiers to fit it nicely on the printed page):

SELECT	Orders.OrderID, Orders.OrderDate,
	SUM ((UnitPrice * Quantity) * (1 - Discount)) AS Total
FROM	dbo.Orders INNER JOIN Order Details
	ON dbo.Orders.OrderID = dbo.[Order Details].OrderID
WHERE	(Orders.CustomerID = @CustID)
GROUP BY	Orders.OrderID, OrderDate

DADetails This DataAdapter retrieves the details of a selected order and also calculates the subtotal of each line (quantity × price × (1 – discount)). Here’s the SELECT statement of the DataAdapter:

SELECT [Order Details].OrderID, [Order Details].ProductID, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, Products.ProductName,

[Order Details].UnitPrice * [Order Details].Quantity) * (1 - dbo.[Order Details].Discount) AS Total

FROM dbo.[Order Details] INNER JOIN dbo.Products

ON dbo.[Order Details].ProductID = dbo.Products.ProductID WHERE ([Order Details].OrderID = @orderID)

After configuring the three DataAdapters, create the corresponding DataSets. Create a separate DataSet for each DataAdapter, and name them DSCustomerNames, DSOrders, and DSDetails. Three instances of the DataSet class will appear on the Component tray, and they’ll be named after the corresponding DataSet suffixed by the digit 1.

Your next step is to bind the DataGrid controls to the corresponding DataSets. The data-binding properties of the three controls are shown next:

Property	Setting
DataGrid1 Control
DataSource	DSCustomerNames1
DataMember	Customers
DataKeyField	CustomerID

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com