Problem You have a GridView containing numeric information, and you need to display a total of the data in the last row of the grid. Solution Enable the output of the footer in the GridView, accumulate the total for the data in the RowDataBound event handler, and then output the total in the GridView footer. In the .aspx file, set the ShowFooter attribute of the asp:GridView element to TRue. In the code-behind class for the page, use the .NET language of your choice to: Initialize the totals to 0, and bind the data to the GridView in the normal fashion. In the RowDataBound event handler, add the values for each data row to the accumulated totals. In the RowDataBound event handler, set the total values in the footer when the footer is data bound.
Figure 2-25 shows some typical output. Examples 2-63, 2-64 through 2-65 show the .aspx file and code-behind files for an application that produces this output. Figure 2-25. GridView with totals row output 
Discussion The best way to describe the addition of a totals row to a GridView is by example. In this recipe, you'll want to create the GridView differently than normal. In the asp:GridView element, set the ShowFooter attribute to true to cause a footer to be output when the control is rendered. Then, you place the totals data in the footer. <asp:GridView Runat="Server" AllowPaging="false" AllowSorting="false" AutoGenerateColumns="false" BorderColor="#000080" BorderStyle="Solid" BorderWidth="2px" Caption="" HorizontalAlign="Center" ShowFooter="true" Width="90%" OnRowDataBound="gvBooks_RowDataBound" >
Next, add a FooterStyle element to format all of the columns in the footer with a stylesheet class and horizontal alignment: <FooterStyle Css HorizontalAlign="Right"/>
All columns are defined in the Columns element as asp:TemplateField columns. This provides a lot of flexibility in the display of the columns. The first column contains only an ItemTemplate that is bound to the Title field in the DataSource. The FooterText property of this column is set to "Total:" to display the label for the other values in the footer. <asp:TemplateField HeaderText="Title" FooterText="Total:"> <ItemTemplate> <%# Eval("Title") %> </ItemTemplate> </asp:TemplateField>
The second and third columns contain an ItemTemplate element to define the format of the data placed in the rows of the grid and a FooterTemplate element to define the format of the data placed in the footer of the respective columns: <asp:TemplateField HeaderText="List Price" ItemStyle-HorizontalAlign="Right"> <ItemTemplate> <asp:Literal runat="server" text='<%# Eval("ListPrice") %>' /> </ItemTemplate> <FooterTemplate> <asp:Literal runat="server" /> </FooterTemplate> </asp:TemplateField>
In the code-behind, two private variables (mListPriceTotal and mDiscountedPriceTotal) are declared at the class level to store the accumulated sum for each of the price columns. The Page_Load method is identical to previous recipes, except for the addition of the code to set mListPriceTotal and mDiscountedPriceTotal to zero before the data binding is performed. The RowDataBound event is used to accumulate the sum of the prices as the rows in the GridView are bound. You can do this because the data binding always starts at the top of the grid and ends at the bottom. Because the RowDataBound event method is called for every row in the grid, you must determine what row this event applies to by checking the ItemType of the passed event arguments. Several groups of item types are needed here, so a Select Case statement (switch in C#) is used. When the item type is a data row, you need to get the values in the list price and discounted price columns and add them to the appropriate total variables. Getting the price values requires getting the price values from the data passed to the method (e.Row. DataItem), adding the price data to the totals, getting a reference to the controls used to display the data, and setting the price value in the controls for the row. Getting a reference to the control is the trickiest part. The easiest and most flexible approach is to use Literal controls in the ItemTemplates of the GridView defined in the .aspx file. By setting the IDs of the literal controls, the FindControl method of the row being data bound can be used to get a reference to the desired control.  | If the IDs of the controls in the ItemTemplates are undefined, the only way to get a reference to a control is to index into the cells and controls collections of the row. In this example, the list price control is in the second column of the grid. Cells in a GridView are created with a literal control before and after the controls you define in a column; therefore, the list price control is the second control in the controls collection of the cell. Getting a reference to the list price control using this method would be done with listPriceControl = e.Row.Cells(1). controls(1). This approach depends on column layout: rearranging columns would break code that uses this approach. The FindControl method is easier to maintain and less likely to be broken by changing the user interface. Literal controls are used in this example because they are rendered without the addition of other controls and because accessing the price value is as simple as getting the value of the text property of the control. An asp:Label control would seem like a good option here; however, it is created as three literal controls in the GridView, making it necessary to index into the controls collection of the control returned by the FindControl method to get the needed price value. |
|
When the item is the footer, all data rows have been processed, and you have the totals for the price columns in the mListPriceTotal and mDiscountedPriceTotal variables. Now you need to output these totals in the controls placed in the footer. This is done by using the FindControl method of the passed item to get a reference to the controls in the footer. After a reference to the control is obtained, the text property is set to the total for the column. In our example, the totals are being formatted to be displayed in currency format with two decimal places. Example 2-63. GridView with totals row (.aspx) <%@ Page Language="VB" MasterPageFile="~/ASPNetCookbookVB.master" AutoEventWireup="false" CodeFile="CH02GridViewWithTotalsRowVB.aspx.vb" Inherits="ASPNetCookbook.VBExamples.CH02GridViewWithTotalsRowVB" Title="GridView With Totals Row" %> <asp:Content Runat="server" ContentPlaceHolder> <div align="center" > GridView With Totals Row (VB) </div> <asp:GridView Runat="Server" AllowPaging="false" AllowSorting="false" AutoGenerateColumns="false" BorderColor="#000080" BorderStyle="Solid" BorderWidth="2px" Caption="" HorizontalAlign="Center" ShowFooter="true" Width="90%" OnRowDataBound="gvBooks_RowDataBound" > <HeaderStyle HorizontalAlign="Center" Css /> <RowStyle css /> <AlternatingRowStyle css /> <FooterStyle Css HorizontalAlign="Right"/> <Columns> <asp:TemplateField HeaderText="Title" FooterText="Total:"> <ItemTemplate> <%# Eval("Title") %> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="List Price" ItemStyle-HorizontalAlign="Right"> <ItemTemplate> <asp:Literal runat="server" text='<%# Eval("ListPrice") %>' /> </ItemTemplate> <FooterTemplate> <asp:Literal runat="server" /> </FooterTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Discounted Price" ItemStyle-HorizontalAlign="Right"> <ItemTemplate> <asp:Literal runat="server" text='<%# Eval("DiscountedPrice") %>' /> </asp:Label> </ItemTemplate> <FooterTemplate> <asp:Literal runat="server" /> </FooterTemplate> </asp:TemplateField> </Columns> </asp:GridView> </asp:Content>
|
Example 2-64. GridView with totals row code-behind (.vb) Option Explicit On Option Strict On Imports Microsoft.VisualBasic Imports System.Configuration Imports System.Data Imports System.Data.OleDb Namespace ASPNetCookbook.VBExamples ''' <summary> ''' This class provides the code behind for ''' CH02GridViewWithTotalsRowVB.aspx ''' </summary> Partial Class CH02GridViewWithTotalsRowVB Inherits System.Web.UI.Page 'variables used to accumulate the sum of the prices Private mListPriceTotal As Decimal Private mDiscountedPriceTotal As Decimal '''*********************************************************************** ''' <summary> ''' This routine provides the event handler for the page load event. It ''' is responsible for initializing the controls on the page. ''' </summary> ''' ''' <param name="sender">Set to the sender of the event</param> ''' <param name="e">Set to the event arguments</param> Private Sub Page_Load(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Load Dim dSource As SqlDataSource = Nothing If (Not Page.IsPostBack) Then 'configure the data source to get the data from the database dSource = New SqlDataSource( ) dSource.ConnectionString = ConfigurationManager. _ ConnectionStrings("dbConnectionString").ConnectionString dSource.DataSourceMode = SqlDataSourceMode.DataSet dSource.ProviderName = "System.Data.OleDb" dSource.SelectCommand = "SELECT Title, ListPrice, DiscountedPrice " & _ "FROM Book " & _ "ORDER BY Title" 'set total values to 0 before data binding mListPriceTotal = 0 mDiscountedPriceTotal = 0 'set the source of the data for the gridview control and bind it gvBooks.DataSource = dSource gvBooks.DataBind( ) End If End Sub 'Page_Load '''*********************************************************************** ''' <summary> ''' This routine provides the event handler for the GridView's row data ''' bound event. It is responsible for formatting the data in the ''' columns of the GridView ''' </summary> ''' ''' <param name="sender">Set to the sender of the event</param> ''' <param name="e">Set to the event arguments</param> Protected Sub gvBooks_RowDataBound(ByVal sender As Object, _ ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Dim rowData As DataRowView Dim price As Decimal Dim listPriceLabel As System.Web.UI.WebControls.Literal Dim discountedPriceLabel As System.Web.UI.WebControls.Literal Dim totalLabel As System.Web.UI.WebControls.Literal 'check the type of item that was databound and only take action if it 'was a row in the gridview Select Case (e.Row.RowType) Case DataControlRowType.DataRow 'get the data for the item being bound rowData = CType(e.Row.DataItem, _ DataRowView) 'get the value for the list price and add it to the sum price = CDec(rowData.Item("ListPrice")) mListPriceTotal += price 'get the control used to display the list price 'NOTE: This can be done by using the FindControl method of the ' passed item because ItemTemplates were used and the anchor ' controls in the templates where given IDs. If a standard ' BoundField was used, the data would have to be accessed ' using the cellscollection (e.g. e.Row.Cells(1).controls(1) ' would access the label control in this example. listPriceLabel = CType(e.Row.FindControl("lblListPrice"), _ System.Web.UI.WebControls.Literal) 'now format the list price in currency format listPriceLabel.Text = price.ToString("C2") 'get the value for the discounted price and add it to the sum price = CDec(rowData.Item("DiscountedPrice")) mDiscountedPriceTotal += price 'get the control used to display the discounted price discountedPriceLabel = CType(e.Row.FindControl("lblDiscountedPrice"), _ System.Web.UI.WebControls.Literal) 'now format the discounted price in currency format discountedPriceLabel.Text = price.ToString("C2") Case DataControlRowType.Footer 'get the control used to display the total of the list prices 'and set its value to the total of the list prices totalLabel = CType(e.Row.FindControl("lblListPriceTotal"), _ System.Web.UI.WebControls.Literal) totalLabel.Text = mListPriceTotal.ToString("C2") 'get the control used to display the total of the discounted prices 'and set its value to the total of the discounted prices totalLabel = CType(e.Row.FindControl("lblTotalDiscountedPrice"), _ System.Web.UI.WebControls.Literal) totalLabel.Text = mDiscountedPriceTotal.ToString("C2") Case Else 'DataControlRowType.EmptyDataRow, DataControlRowType.Header, 'DataControlRowType.Pager, or DataControlRowType.Separator 'no action required End Select End Sub 'gvBooks_RowDataBound End Class 'CH02GridViewWithTotalsRowVB End Namespace
|
Example 2-65. GridView with totals row code-behind (.cs) using System; using System.Configuration; using System.Data; using System.Data.Common; using System.Data.OleDb; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; namespace ASPNetCookbook.CSExamples { /// <summary> /// This class provides the code behind for /// CH02GridViewWithTotalsRowCS.aspx /// </summary> public partial class CH02GridViewWithTotalsRowCS : System.Web.UI.Page { // variables used to accumulate the sum of the prices private Decimal mListPriceTotal; private Decimal mDiscountedPriceTotal; ///*********************************************************************** /// <summary> /// This routine provides the event handler for the page load event. /// It is responsible for initializing the controls on the page. /// </summary> /// /// <param name="sender">Set to the sender of the event</param> /// <param name="e">Set to the event arguments</param> protected void Page_Load(object sender, EventArgs e) { SqlDataSource dSource = null; if (!Page.IsPostBack) { // configure the data source to get the data from the database dSource = new SqlDataSource( ); dSource.ConnectionString = ConfigurationManager. ConnectionStrings["dbConnectionString"].ConnectionString; dSource.DataSourceMode = SqlDataSourceMode.DataReader; dSource.ProviderName = "System.Data.OleDb"; dSource.SelectCommand = "SELECT Title, ListPrice, DiscountedPrice " + "FROM Book " + "ORDER BY Title"; // set total values to 0 before data binding mListPriceTotal = 0; mDiscountedPriceTotal = 0; // set the source of the data for the gridview control and bind it gvBooks.DataSource = dSource; gvBooks.DataBind( ); } } // Page_Load ///*********************************************************************** /// <summary> /// This routine provides the event handler for the GridView's row created /// event. It is responsible for setting the icon in the header row to /// indicate the current sort column and sort order /// </summary> /// /// <param name="sender">Set to the sender of the event</param> /// <param name="e">Set to the event arguments</param> protected void gvBooks_RowDataBound(Object sender, System.Web.UI.WebControls.GridViewRowEventArgs e) { DbDataRecord rowData; Decimal price; System.Web.UI.WebControls.Literal listPriceLabel; System.Web.UI.WebControls.Literal discountedPriceLabel; System.Web.UI.WebControls.Literal totalLabel; // check the type of item that was databound and only take action if it // was a row in the gridview switch (e.Row.RowType) { case DataControlRowType.DataRow: // get the data for the item being bound rowData = (DbDataRecord)(e.Row.DataItem); // get the value for the list price and add it to the sum price = (Decimal)(rowData["ListPrice"]); mListPriceTotal += price; // get the control used to display the list price // NOTE: This can be done by using the FindControl method of the // passed item because ItemTemplates were used and the anchor // controls in the templates where given IDs. If a standard // BoundField was used, the data would have to be accessed // using the cells collection (e.g. e.Row.Cells(1).controls(1) // would access the label control in this example. listPriceLabel = (System.Web.UI.WebControls.Literal) (e.Row.FindControl("lblListPrice")); // now format the list price in currency format listPriceLabel.Text = price.ToString("C2"); // get the value for the discounted price and add it to the sum price = (Decimal)(rowData["DiscountedPrice"]); mDiscountedPriceTotal += price; // get the control used to display the discounted price discountedPriceLabel = (System.Web.UI.WebControls.Literal) (e.Row.FindControl("lblDiscountedPrice")); // now format the discounted price in currency format discountedPriceLabel.Text = price.ToString("C2"); break; case DataControlRowType.Footer: // get the control used to display the total of the list prices // and set its value to the total of the list prices totalLabel = (System.Web.UI.WebControls.Literal) (e.Row.FindControl("lblListPriceTotal")); totalLabel.Text = mListPriceTotal.ToString("C2"); // get the control used to display the total of the discounted prices // and set its value to the total of the discounted prices totalLabel = (System.Web.UI.WebControls.Literal) (e.Row.FindControl("lblTotalDiscountedPrice")); totalLabel.Text = mDiscountedPriceTotal.ToString("C2"); break; default: // DataControlRowType.EmptyDataRow, DataControlRowType.Header, // DataControlRowType.Pager, or DataControlRowType.Separator // no action required break; } // switch (e.Row.RowType) } //gvBooks_RowDataBound } // CH02GridViewWithTotalsRowCS }
|
|
