Summary Rows in DataGrid Controls
Dino Esposito
Wintellect
January 15, 2002
The ASP.NET DataGrid control renders a multi-column, fully templated grid, and is the most versatile and flexible of all data-bound Web controls in the .NET Framework. The DataGrid's user interface resembles, to some extent, a Microsoft Excel worksheet. Despite its rather advanced programming interface and a full set of attributes, the DataGrid simply generates an HTML table with interspersed hyperlinks to provide interactive functionality, such as sorting and pagination commands.
With the DataGrid control, you can create simple data-bound columns that show data retrieved from a data source, templated columns that let you design the layout of cell contents and, last but not least, command-based columns that allow you to add specific functionality to a grid.
The DataGrid control is great for data reporting and flexible enough to let you build complex, professional looking tables of data in which functionalities like pagination and sorting are free. Instead, other functionalities, such as drill-down and master/details, only require a little effort. In this month's column, I'll tackle one function that the control itself does not provide, but that many people would heartily welcome. So, let's examine how to automate the production of complex reports in which you have to show summary rows with partial totals.
DataGrid Items
You bind actual data to an instance of the DataGrid control by setting the control's DataSource property. This property is a generic Object type and supports two configuration scenarios. Normally you set it with a data object that implements the ICollection interface. Typical objects you would use are DataTable and DataView. Alternatively, you set it with another object type—for instance, a data reader object. In this case, though, you must turn the custom paging mechanism on; otherwise an exception would be thrown. In brief, either the DataGrid is bound to a paged data source—that is a collection object that implements an enumerator—or you must provide pagination for yourself.
For a Web control, data binding is enabled, and the user interface is refreshed only when you call the DataBind method. When this happens, the control walks its way through the data source and copies some rows into its Items collection. The Items property represents the contents of the currently displayed page. If the data source supports pagination—that is, implements ICollection—the DataGrid selects from DataSource the proper subset of rows that fits into the current page. Otherwise, it assumes that the whole contents of DataSource fits into the current page and loads all of it into Items. Once Items has been filled, the control's user interface is rendered.
What's the lesson here? All that a DataGrid control can safely and consistently display are rows contained in the bound data source. So, if you want to insert summary rows to group some records under a common key and display partial totals, you must figure out a way to insert those summary rows directly into the data source.
Having summary rows in the data source is not enough though. In fact, you must be able to distinguish summary and ordinary rows, and render the former with different visual styles.
You make sure that the data source contains all the summary rows it needs prior to attaching the data to the control. Next, you hook up the ItemCreated event, detect each summary row, and draw them with a different layout and style. Let's look at how to insert summary rows between the rows of a SQL query. The sample application I'll use to demonstrate my points is based on the Northwind SQL Server database. The application lists all the orders that each customer has issued in a given year. Orders are grouped by year and customer ID. For each customer an extra row summarizes the total number of orders and their total amount.
Grouping Data
Given a year, the following SQL command selects all the orders issued by all customers. Only the sum of all item prices for each order is displayed.
SELECT o.customerid, od.orderid, SUM(od.quantity*od.unitprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(o.orderdate) = @TheYear AND od.orderid=o.orderid
GROUP BY o.customerid, od.orderid
ORDER BY o.customerid
The GROUP BY clause of the SELECT statement in T-SQL language provides the WITH ROLLUP clause 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 statements illustrate how to modify the previous command to allow for summary rows.
DECLARE @TheYear int
SET @TheYear = 1998
SELECT
CASE GROUPING(o.customerid) WHEN 0
THEN o.customerid ELSE '(Total)' END AS MyCustomerID,
CASE GROUPING(od.orderid) WHEN 0
THEN od.orderid ELSE -1 END AS MyOrderID,
SUM(od.quantity*od.unitprice) AS price
FROM Orders o, [Order Details] od
WHERE Year(orderdate) = @TheYear AND od.orderid=o.orderid
GROUP BY o.customerid, od.orderid WITH ROLLUP
ORDER BY o.customerid, price
If you copy and paste this snippet into the SQL Query Analyzer, you get what is shown in the figure below.
.gif)
Figure 1. The WITH ROLLUP clause adds summary rows to the result set
GROUPING is the T-SQL aggregate function that works in conjunction with ROLLUP in the body of a GROUP BY clause. The use of the GROUPING operator 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 operator, and therefore, is a summary row. Otherwise, the column takes a value of 0. By using a CASE..WHEN..END statement you can merge this new column with the grouping column.
In the example above, the MyCustomerID column contains the value of the CustomerID column and the string '(Total)' in all rows created by grouping on that column. Likewise, the MyOrderID column contains the order ID and –1 when the row represents a subtotal.
To summarize data, SQL Server provides a few more options, such as the WITH CUBE operator and the COMPUTE BY clause. As you can imagine, all these options are not completely equivalent, although the functionality of one somehow intersects the working of the other. In particular, WITH CUBE generates a summary row for every possible combination of groups and subgroups in the result set. WITH ROLLUP, instead, groups hierarchically according to the order in which the grouping columns are specified. Finally, COMPUTE BY, which SQL Server 2000 supports only for backward compatibility, works in much the same way WITH ROLLUP does, but returns multiple result sets and is not treated as efficiently as ROLLUP by the query optimizer.
Presenting Grouped Data
When bound to a DataGrid control, the result set returned by the SQL command looks like the figure below.
.gif)
Figure 2. The result set displayed through a DataGrid control
The DataGrid control used in the sample application is declared as follows:
<asp:DataGrid id="grid" runat="server"
AutoGenerateColumns="false"
AllowPaging="true" PageSize="15"
Font-Size="xx-small"
CellSpacing="0" CellPadding="4" GridLines="both"
BorderStyle="solid" BorderColor="skyblue" BorderWidth="1"
OnItemCreated="ItemCreated"
OnPageIndexChanged="PageIndexChanged">
<headerstyle backcolor="skyblue" font-size="9pt" font-bold="true" />
<itemstyle backcolor="#eeeeee" />
<pagerstyle backcolor="skyblue" font-name="webdings"
font-size="10pt" PrevPageText="3" NextPageText="4" />
<Columns>
<asp:BoundColumn DataField="MyCustId" HeaderText="Customer" />
<asp:BoundColumn DataField="MyOrderId" HeaderText="Order #" />
<asp:BoundColumn DataField="price" HeaderText="Amount"
DataFormatString="{0:c}">
<itemstyle horizontalalign="right" />
</asp:BoundColumn>
</Columns>
</asp:DataGrid>
The data source you obtain using the WITH ROLLUP operator already contains all the information you may need to produce an effective report. As you may have noticed, the statement adds a top row that contains the total of all the orders issued by all customers. When you use the WITH ROLLUP operator, the number and the structure of the rows generated may change significantly if you modify the order of the grouping rows. The extra row is a consequence of the particular syntax I have chosen to use. If you don't need that piece of information, just drop it off the result set prior to bind. Alternatively, you could move that row at the bottom of the data set.
The code shown below demonstrates how to execute the rollup statement. The year to consider is a parameter read out of textbox. The result set is temporarily stored in a DataSet object. In this sample application, I'll cache the DataSet object in a Session slot. This is not a choice that should be taken lightly in a real-world context. In general, any byte you store in Session should have a good reason to be there.
private DataSet PhysicalDataRead()
{
string strCnn = "SERVER=localhost;DATABASE=northwind;Integrated Security=SSPI;";
SqlConnection conn = new SqlConnection(strCnn);
// Command text using WITH ROLLUP
StringBuilder sb = new StringBuilder("");
sb.Append("SELECT ");
sb.Append(" CASE GROUPING(o.customerid) WHEN 0 ");
sb.Append(" THEN o.customerid ELSE '(Total)' END AS MyCustID, ");
sb.Append(" CASE GROUPING(od.orderid) WHEN 0 ");
sb.Append(" THEN od.orderid ELSE -1 END AS MyOrderID, ");
sb.Append(" SUM(od.quantity*od.unitprice) AS price ");
sb.Append("FROM Orders o, [Order Details] od ");
sb.Append("WHERE Year(orderdate)=@nYear AND od.orderid=o.orderid ");
sb.Append("GROUP BY o.customerid, od.orderid WITH ROLLUP ");
sb.Append("ORDER BY o.customerid, price");
String strCmd = sb.ToString();
sb = null;
SqlCommand cmd = new SqlCommand();
cmd.CommandText = strCmd;
cmd.Connection = conn;
SqlDataAdapter da = new SqlDataAdapter(strCmd, strConn);
da.SelectCommand = cmd;
// Set the "year" parameter
SqlParameter p1 = new SqlParameter("@nYear", SqlDbType.Int);
p1.Direction = ParameterDirection.Input;
p1.Value = Convert.ToInt32(txtYear.Text);
cmd.Parameters.Add(p1);
DataSet ds = new DataSet();
da.Fill(ds, "Orders");
return ds;
}
To make summary rows clearly stand out of the grid's page, you need to change the style and the layout of the rows. This can be accomplished from within the ItemCreated event handler. The idea is to detect the summary row by checking the order ID, and then modify the cell layout and the style. In the result set, a summary row is characterized by an order ID of –1. The value of –1 is arbitrary and stems from the statement used.
CASE GROUPING(od.orderid) WHEN 0 THEN od.orderid ELSE -1 END AS MyOrderID
If you don't use the GROUPING operator on the orderid column, the column value would be NULL for all summary rows.
Modifying Layout and Style
A DataGrid allows you to modify both style and layout of the constituent cells. You do this by hooking the ItemCreated event. The event fires each time the control processes a child item—header, footer, rows, pager. The event handler receives a parameter of type DataGridItemEventArgs from which you extract the type of the item being processed.
A summary row is a DataGrid row, and as such it can be of type Item or AlternatingItem. So, when writing the ItemCreated handler, make sure you process the cell only if the item is of the right type. The following listing outlines the code you need.
public void ItemCreated(Object sender, DataGridItemEventArgs e)
{
// Get the type of the newly created item
ListItemType itemType = e.Item.ItemType;
if (itemType == ListItemType.Item ||
itemType == ListItemType.AlternatingItem)
{
// Get the data bound to the current row
DataRowView drv = (DataRowView) e.Item.DataItem;
if (drv != null)
{
// Check here the app-specific way to detect whether the
// current row is a summary row
:
}
}
}
If the item being created is a DataGrid item (or an alternating item), you access the data bound to the row through the DataItem property. Depending on the type of the object the DataGrid is bound to, the DataItem property points to a different row object. You get a DataRowView object if the grid is bound to a DataView and a DataRow object if the source is expressed in terms of a DataTable object. In the sample application, I used a DataView object to populate the grid. Subsequently, the data object for a single row is a DataRowView object.
Once you hold the data row object, you apply some application-specific rules to determine whether that row is or is not a summary row. In the sample application, a summary row has the MyOrderID field set to –1.
if ((int) drv["MyOrderID"] == -1)
{
// Modify style and layout here.
// --> Set the background color to white and use bold font
e.Item.BackColor = Color.White;
e.Item.Font.Bold = true;
}
The DataGrid now looks like the figure below.
.gif)
Figure 3. Summary rows with bold font and white background
Actually, a DataGrid row is merely a table row. As such, it lends itself well to cell removal and other adjustments. Let's see how to render summary row using a single cell that spans over all existing columns.
if ((int) drv["MyOrderID"] == -1)
{
// Modify style and layout here.
e.Item.BackColor = Color.White;
e.Item.Font.Bold = true;
e.Item.Cells.RemoveAt(0); // remove CustID
e.Item.Cells.RemoveAt(0); // remove Order #, now the first
// Span and right-align the cell left
e.Item.Cells[0].ColumnSpan = 3;
e.Item.Cells[0].HorizontalAlign = HorizontalAlign.Right;
}
.gif)
Figure 4. Summary rows with a custom layout
Of the original three cells, the first two are removed and the latter—now with an index of 0—is right aligned and spanned over the width of the outer table. If you want to display some custom text on the summary row, then be ready to face an additional problem.
Suppose that you want to add some text to comment the subtotal and, at the same time, have the subtotal appear in the same column as the single orders amount. In this case, you only need to remove one cell.
e.Item.Cells.RemoveAt(1); // remove the order # cell
e.Item.Cells[0].ColumnSpan = 2; // span the custID cell
e.Item.Cells[1].HorizontalAlign = HorizontalAlign.Right;
e.Item.Cells[0].Text = "Total is";
The results of this code are shown below. As you can see, it is not exactly what you hoped for. The first cell of the summary rows has no track of the text you just set. What's going on?
.gif)
Figure 5. Summary rows with a modified custom layout
The key fact to consider here is that both Item and AlternatingItem rows are bound rows. Their definitive text is set only during the OnItemDataBound event. As you may have guessed already, the OnItemDataBound event fires after that the item has been created. As a result, any text you assign to cells while processing ItemCreated is silently overwritten one event later. You hook up the OnItemDataBound event by setting the DataGrid's OnItemDataBound property.
<asp:DataGrid id="grid" runat="server"
AutoGenerateColumns="false"
:
OnItemCreated="ItemCreated"
OnItemDataBound="ItemDataBound"
OnPageIndexChanged="PageIndexChanged">
The structure of the code for ItemDataBound is shown below.
public void ItemDataBound(Object sender, DataGridItemEventArgs e)
{
DataRowView drv = (DataRowView) e.Item.DataItem;
if (drv == null)
return;
if ((int) drv["MyOrderID"] == -1)
{
if (drv["MyCustomerID"].ToString() == "(Total)")
{
e.Item.BackColor = Color.Yellow;
e.Item.Cells[0].Text = "Orders total";
}
else
e.Item.Cells[0].Text = "Customer subtotal";
}
}
The topmost row is drawn on a yellow background and displays a different text from the other summary rows. The final DataGrid is shown below.
.gif)
Figure 6. The final DataGrid
Summary
An effective Web database application comes from a good mix—in application-specific doses—of SQL code and ASP.NET techniques. The DataGrid control is the cutting-edge tool for building cool and powerful Web applications for the programming features it provides, but even more for the customization levels it supports.
Dialog Box: Critical Task Confirmation
How can I display a dialog box to force users to confirm before a critical task (i.e., a record deletion) starts?
I assume that you need to display such a dialog box after the user clicks on a button. Well, this can be obtained only through some client side JavaScript code that handles the onclick event.
Any ASP.NET control evaluates to one, or more, HTML tags. A push button maps to a <INPUT TYPE=button> tag. A link button maps to script-driven hyperlink. You can register a piece of script code for both tags using the Attributes collection of the ASP.NET control. For example, if you have a button (no matter if it is a LinkButton or a Button object), you can define the JavaScript code that runs after the click as follows:
String js = "return confirm('Do you really want to delete the record?');";
btn.Attributes["onclick"] = js;
As a result, the HTML tag contains an attribute:
<a … onclick="return confirm('…')">
When the user clicks the link, the onclick client side code runs, and if the user clicks No, the event automatically aborts. This behavior is built into the browser's logic and has very little to do with ASP.NET. If the onclick client side handler exits successfully, then the __doPostBack function executes and the page posts back to the server.
Dino Esposito is Wintellect's ADO.NET expert and a trainer and consultant based in Rome, Italy. Dino is a contributing editor to MSDN Magazine and writes the Cutting Edge column. He also regularly contributes to Developer Network Journal and MSDN News. Dino is the author of Building Web Solutions with ASP.NET and ADO.NET from Microsoft Press, and the cofounder of https://www.vb2themax.com/. You can reach Dino at dinoe@wintellect.com.