Advanced Data Reporting in ASP.NET
Figure 6-10 A more complex DataGrid control showing partial totals and summary rows.
SELECT o.customerid, od.orderid, SUM(od.quantity*od.uni tprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(o.orderdate) = @nYear AND od.orderid=o.o rderid
GROUP BY o.customerid, od.orderid
ORDER BY o.customerid
Figure 6-11 The results of a query displaying the total price of each order.
Notice in Figure 6-11 that the price column contains the total amount for each order. The summary row would add all the orders for a customers and also display the name of the customer. There are two basic ways of doing this, one of which uses the SQL language.
The GROUP BY clause of the SELECT statement in the T-SQL language provides the WITH ROLLUP extension that adds predefined summary rows to the result set. Of course, such a summary row has the layout of all other columns, but the content of each column can be customized to some extent. The following statement illustrates how to modify the previous command to allow for summary rows. Figure 6-12 shows the result set.
CASE GROUPING(o.customerid) WHEN 0 THEN o.customerid ELSE (Total)' END
CASE GROUPING(od.orderid) WHEN 0 THEN od.orderid ELSE -1 END
SUM(od.quantity*od.unitprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(o.orderdate) = 1998 AND od.orderid=o.ord erid
GROUP BY o.customerid, od.orderid WITH ROLLUP
ORDER BY AllCustomersSummary
GROUPING is the T-SQL aggregate function that works in conjunction with ROLLUP in the body of a GROUP BY clause. The use of GROUPING causes a new column to be added to the result set. This column contains a value of 1 if the row has been added by the ROLLUP operatortherefore, it is a summary row. Otherwise, the column has a value of 0. By using a CASE..WHEN..END statement, you can merge this new column with the grouping column. For example, the T-SQL statement below creates a new column called AllCustomersSummary which normally contains the value of the CustomerID column and the string "(Total)" in all rows created by grouping on that column.
CASE GROUPING(o.customerid) WHEN 0
END AS AllCustomersSummary
Figure 6-12 The results of a query displaying the total price of each order.
By using the WITH ROLLUP feature, you get data from the source already in a format suitable for display. In the rest of the chapter, though, I'm going to illustrate an alternate, more flexible, but less lightweight, approach. It exploits some new features of ADO.NET (such as data relations) and lets you use summary rows with any number of columns. Key to this approach is the fact that summary rows are stored in a separate result set.
Based on the SQL Server 2000 Northwind database, the query in the preceding code, run for the year 1998, results in 270 records and 81 customers. The next query shows how to get this second result set, with the item totals for each order listed in the summary row. The results are shown in Figure 6-13.
FROM Customers c, [Order Details] od, Orders o
WHERE Year(orderdate) = @nYear
AND c.CustomerID = o.CustomerID
AND o.OrderID = od.OrderID
GROUP BY c.CompanyName, c.CustomerID
ORDER BY c.customerid
Figure 6-13 The results of the query that provides the data for the summary row.
With the ADO.NET classes, you don't have to merge these two result sets because they can be more effectively processed in memory during the grid rendering. I grouped the queries in a new stored procedure that takes one input argumentthe year.
CREATE PROCEDURE Orders_Summary_By_Customers_And_Year
The data adapter runs the stored procedure and returns two result sets. The former result set is used to populate the data grid; the latter serves to fill up summary rows.
Adding Summary Rows
The DataGrid control does not allow you to add table rows at run time. Even if you could figure out a way to do so, often you would be adding rows to pages that already have the maximum number of rows. These new rows wouldn't affect the way in which the grid extracts the items for a given page. To prevent any problem with rows and pages, you add extra rows directly to the data source. Ideally, you add an extra blank row between the blocks of records with different customer IDs, but making an insertion requires you to scroll the whole result set. You could more effectively append rows, set the customerid field as appropriate, and then sort. After sorting, each semi-blank rowthe summary rowis magically in place.
foreach(DataRow row in dtOrdersSummary.Rows)
DataRow blank = dtOrders.NewRow();
blank["CustomerID"] = row["CustomerID"];
// Application- specific trick. Figure out a reliable
// way to identify the summary row later while drawing it ems
blank["OrderID"] = -1;
The key strategy in the preceding code is inserting information in the summary row that simplifies your detection of it later when you need to display the summary row. During the grid rendering, you hook into the ItemCreated event and check the contents of the row being drawn. If the row is the summary row, the graphical layout changes to display information from the OrdersSummary table. In our example, I decided to mark the summary row with a -1 value in the OrderID field. (This decision is application-specific.) Figure 6-14 shows how the grid looks at this point.
Figure 6-14 The grid with summary totals. An OrderID field of -1 identifies a summary row.
Populating the Summary Row
The layout (font, background color, and number of cells) and contents of the summary row must be modified. Any graphical update can be done in the ItemCreated event. For example, you can group the first two cells and render the whole row with a white background and bold fonts.
DataRowView drv = (DataRowView) e.Item.DataItem;
if ((int) drv["OrderID"] == -1)
e.Item.BackColor = Color.White;
e.Item.Cells.Font.Bold = true;
e.Item.Cells.HorizontalAlign = HorizontalAlign.R ight;
e.Item.Cells.RemoveAt(1); // the order# column
e.Item.Cells.ColumnSpan = 2;
The way you bind the cells with summary text depends on the type of the grid columns involved. If they are templated columns, you simply set the Text property of the desired cell with the desired text. For other column types, including bound columns, the binding approach is a little more complicated. In the case of a bound column, the association between the cell text and the data is handled in the ItemDataBound event. The ItemDataBound event fires after ItemCommand. As a result, any text you set in ItemCreated is soon overwritten by ItemDataBound. To work around this, hook into the ItemDataBound event.
Retrieving Summary Data
When the ItemDataBound event is processed, the grid is working on a row taken from the Orders in-memory table. When the row appears as a summary row, you need to replace this information with data coming from the OrdersSummary table. How do you get the corresponding summary row? Create a data relation between the two tables, which makes this retrieval quite straightforward.
DataColumn dc1 = dtOrders.Columns["CustomerID"];
DataColumn dc2 = dtOrdersSummary.Columns["CustomerID"];
DataRelation dr = new DataRelation("OrdersAndSummary", dc1, dc2);
ds.EnforceConstraints = false;
A DataRelation object creates a relationship between two tables that have a common column. Both tables must be part of the same DataSet object. After the relation is set, you get from each row of the parent table the array of child rows by calling the GetChildRows method.
You set the relation between the two tables upon loading. During the ItemDataBound event, you obtain the corresponding summary row with the following, surprisingly simple, code:
DataRowView drv = (DataRowView) e.Item.DataItem;
DataRow a = drv.Row.GetChildRows("OrdersAndSummary");
DataRow drSummaryRow = a; // Only one row selected by design
// Build the string to display
e.Item.Cells.Text = strTextToDisplay;
The final result of this code is shown in Figure 6-15. The full source code for the SuperGrid.cs, Summary.aspx, and Summary.cs applications is available on the companion CD. Note that the sample application also combines the summary rows with the select-by-condition feature I examined earlier in the chapter.
In this chapter, I examined techniques and tools for building sophisticated, professional reports using the DataGrid control. I also suggested the importance of having effective SQL code. The effectiveness of the SQL code has to be measured not only in terms of performance and query execution plans but also in terms of the capabilities supplied by the objects you will use on the Web server. ADO.NET classes offer a lot, and balancing the workload between SQL Server and the .NET Web server has never been so attainable.
Reporting does not exhaust the range of functionality provided by Web applications. In Chapter 7, I'll focus on disconnected applications, caching, and batch update.
|Sample Chapter from Building Web Solutions with ASP.NET and ADO.NET by Dino Esposito for MS Press (ISBN: 0735615780)|
# # #
Page 4 of 4