An Extensive Examination of the DataGrid Web Control: Part 13

By Scott Mitchell

---

The 13th Part in a Multi-Part Series

This article is the thirteenth piece of a multi-part series on using the DataGrid Web control that will span several months. The ASP.NET DataGrid Web control, which displays database information in an HTML table, is highly versatile. The basics of the DataGrid were discussed in Part 1; information on specifying display properties of the DataGrid was discussed in Part 2. In Part 3 we examined how to associate custom events with the DataGrid. In Part 4 we looked at how to extend Part 3 to provide custom sorting on the results of a DataGrid. In Part 5 we examined how to use templates to further customize the DataGrid's appearance. Part 6 examined how to use the DataGrid's built-in editing capabilities, while Part 7 looked at customizing the editing interface using the EditItemTemplate. In Part 8 we looked at how to add client-side code to a ButtonColumn's client-side onclick event. In Part 9 we examined how to enhance the DataGrid's editing interface by having the editing interface's TextBox receive focus when the page is loaded. In Part 10 we looked at how to (automatically) add filtering buttons so that one can filter the data in a DataGrid. In Part 11 we examined how to create a DataGrid Web control with a column of related radio buttons. In Part 12 we examined how to create a sortable DataGrid that can be sorted in ascending and descending order. In this thirteenth part, we'll see how to sum up a DataGrid column and have the sum displayed in the footer. - continued - Read Part 14 Read Part 15 Read Part 16 Read Part 17 Read Part 18

ASP.NET Data Web Controls Kick Start ASP.NET Data Web Controls Kick Start is author Scott Mitchell's most recent book, which thoroughly examines three of the most commonly used ASP.NET Web controls: the DataGrid, DataList, and Repeater. These three Web controls can be difficult to master due to their numerous features and capabilities. With this book, you'll quickly become an expert, learning the gritty details and true capabilities of each. This 400+ page book explores the topics in this article series in much greater depth, along with examining various topics and techniques not covered here. Scott Mitchell is the editor and founder of 4GuysFromRolla.com, author of the An Extensive Examination of the DataGrid Web Control article series, and author of numerous other ASP and ASP.NET books. [Buy this Book] [Visit the Book's Companion Web Site]

Introduction

Throughout this article series we have been looking at an example that displays information about the ASPFAQs.com FAQs. Specifically, the data displayed has been the results from a stored procedure that returns the 10 most-viewed FAQs. (For example, this live demo shows the data returned by the stored procedure.)

One piece of data returned by the stored procedure is a column called ViewCount, which returns the number of times a particular FAQ has been viewed. What would be nice, though, is to not only see the views for each of the 10 most popular FAQs, but to also be shown the sum of these views at the bottom of the DataGrid. (To see the desired output, check out this live demo.) In this article we will see how to compute the sum of a DataGrid column and how to display this sum in the DataGrid's footer.

Computing the Sum of a Column

There are a couple of ways to compute the sum of a DataGrid column. One way is to not actually compute the sum of the DataGrid column, per se, but to compute the sum of the data through a SQL query. For example, to compute the sum of all ASPFAQs.com FAQs, the following SQL statement could be used:

SELECT SUM(ViewCount)
FROM tblFAQs

The results of the SQL query, however, must be retrieved in a DataReader or DataSet other than the one that's being bound to the DataGrid - this means two round trips to the database server. Also, if you have, say, three DataGrid column that you want to compute sums for, then you'll need four separate SQL queries - one for all the data and then one for the sum of each of the three columns.

A simpler way to compute the sum of a DataGrid column is to do it on the fly, as the DataGrid is being constructed. In Part 8 of this article series we saw that when the DataGrid's DataBind() method is called, the DataSource object is enumerated over. For each record in the DataGrid's DataSource, a new DataGridItem is created. After the DataGridItem is created and the DataSource's is bound to the row, the ItemDataBound event is fired. Again, this happens for each row added to the DataGrid.

We can provide an event handler for the DataGrid's ItemDataBound event. In this event handler, all that we need to do is determine the value of the column that we want to sum and add it to a variable that holds the running total. The code for this isn't particularly difficult, and can be seen below:

<script language="VB" runat="server"> Dim viewCountSum as Integer = 0 Sub ComputeSum(sender As Object, e As DataGridItemEventArgs) 'First, make sure we are dealing with an Item or AlternatingItem If e.Item.ItemType = ListItemType.Item OR _ e.Item.ItemType = ListItemType.AlternatingItem then 'Snip out the ViewCount Dim viewCount as Integer = _ Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "ViewCount")) viewCountSum += viewCount End If End Sub </script> <asp:DataGrid runat="server" id="dgPopularFAQs" ... OnItemDataBound="ComputeSum"> <Columns> <asp:BoundColumn DataField="Description" HeaderText="Description" /> <asp:BoundColumn DataField="ViewCount" HeaderText="Page Views" DataFormatString="{0:#,###}" ItemStyle-HorizontalAlign="Right" ItemStyle-Width="15%" /> </Columns> </asp:DataGrid>

The important lines of the code sample are displayed in bold. First, note that a variable outside of all event handlers, subroutines, and functions - viewCountSum - has been declared. This is the variable that will hold the running total. Next, in the DataGrid, the ItemDataBound event is wired up to the ComputeSum event handler.

The ComputeSum event handler is fairly straightforward. It first makes sure that the row we are dealing with is an Item or AlternatingItem row. Realize that the ItemDataBound event handler fires for all DataGrid rows, including the header and footer. (If we omitted the check to ensure that we were working with an Item or AlternatingItem row, we'd get a runtime error when trying to access e.Item.DataItem for the header and footer rows.)

After ensuring that we are working with an Item or AlternatingItem row, the ViewCount item from the DataSource's current DataItem is read and stored into a variable viewCount. This variable is then used to increment viewCountSum, the running total.

Displaying the Sum in the DataGrid's Footer

Now that the viewCountSum variable stores the running sum, how do we display the running sum in the DataGrid's footer? Recall that the ItemDataBound event handler fires for all DataGrid rows. This includes not only the DataGrid's Item and AlternatingItem rows, but also the header and... footer. Since the footer is created last, by the time the footer's ItemDataBound event fires, all of the ItemDataBound events for the databound rows would have fired, meaning that when the footer is created viewCountSum has already been computed.

All that remains is to emit the value of viewCountSum into the proper footer cell. This can be accomplished by altering the ComputeSum event handler to contain the following code:

Sub ComputeSum(sender As Object, e As DataGridItemEventArgs) 'First, make sure we are dealing with an Item or AlternatingItem If e.Item.ItemType = ListItemType.Item OR _ e.Item.ItemType = ListItemType.AlternatingItem then 'Snip out the ViewCount Dim viewCount as Integer = _ Convert.ToInt32(DataBinder.Eval(e.Item.DataItem, "ViewCount")) viewCountSum += viewCount ElseIf e.Item.ItemType = ListItemType.Footer then e.Item.Cells(1).Text = "Total: " & String.Format("{0:#,###}", viewCountSum) End If End Sub

[View a Live Demo!]

Note that the ComputeSum event handler has been updated to now also check to see if the ItemType being added is a Footer. If it is, then the 2nd column's Text property is set to the value of viewCountSum (with some formatting thrown in for aesthetics). As the code illustrates, the Footer contains a column for each column in the DataGrid, and to access the i^th column, simply use: e.Item.Cells(i). The one thing to remember is that the Cells collection is zero-based, so Cells(1) accesses the second column. (The reason the second column's footer displays the total is because the DataGrid's second column lists the number of page views for each FAQ.)

To complete this code, there's one very important step you must take. By default, the DataGrid does not show the footer, so in order to display a running sum in the footer, you must indicate that the footer should be displayed. To do this, set the ShowFooter DataGrid property to True. You can do this in the DataGrid's declaration, like so:

<asp:DataGrid runat="server" id="dgPopularFAQs" ... ShowFooter="True" OnItemDataBound="ComputeSum"> ... </asp:DataGrid>

Conclusion

In this article we examined how to use a DataGrid to display the sum of a column in the DataGrid's footer. This was accomplished by using the ItemDataBound event to store the sum of the viewCountSum variable and the value of a particular column for each row. Then, when the footer's ItemDataBound event fired, the value of the viewCountSum variable was displayed in the footer.

Happy Programming!

By Scott Mitchell