|
|
|
|
|
|
Displaying Total in Footer of GridView
|
During my training career, I have been asked one common question several times that how we can calculate summary totals inside GridView control and how we can show it at the footer of GridView control. So I have decided to write a tutorial on this topic. In this tutorial I will show you how you can calculate and display totals not only in GridView footer but also on any control outside GridView control.
|
|
|
For the purpose of this tutorial I have setup a simple ASP.NET page with data bound GridView control and a Label control on it. Following is the HTML source code of the GridView and Label control:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White"
BorderColor="#336699" BorderStyle="Solid" BorderWidth="1px"
CellPadding="2" Font-Names="Verdana" ShowFooter="true"
Font-Size="10pt" Width="50%" DataKeyNames="ProductID"
GridLines="Horizontal" onrowdatabound="GridView1_RowDataBound">
<Columns>
<asp:BoundField DataField="ProductID" HeaderText="ProductID" />
<asp:BoundField DataField="ProductName" HeaderText="ProductName" />
<asp:TemplateField HeaderText="UnitPrice">
<ItemTemplate>
<asp:Label ID="lblPrice" runat="server" Text='<%# Eval("UnitPrice")%>' />
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalPrice" runat="server" />
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="UnitsInStock">
<ItemTemplate>
<asp:Label ID="lblUnitsInStock" runat="server" Text='<%# Eval("UnitsInStock") %>' />
</ItemTemplate>
<FooterTemplate>
<asp:Label ID="lblTotalUnitsInStock" runat="server" />
</FooterTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#336699" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
<HeaderStyle BackColor="#336699" Font-Bold="True" ForeColor="White" HorizontalAlign="Left" />
</asp:GridView>
<br />
<b>Average Price: </b>
<asp:Label ID="lblAveragePrice" runat="server" Text="Label"></asp:Label>
In the above code, I am using TemplateField column inside GridView control which has two templates ItemTemplate and FooterTemplate containing Label controls to show data.
The Labels inside ItemTemplate are bound with UnitPrice and UnitsInStock fields using ASP.NET binding expression <%# %> syntax. The Labels inside FooterTemplate are left blank for showing totals later from the code.
I am also attaching an event handler for the RowDataBound event with the GridView. This event is where I will calculate the total for GridView data later. The RowDataBound event is raised whenever a row in the GridView is bound to data. It is raised for the header, the footer, and data rows. This event provides an opportunity to access each row before the page is finally sent to the client for display.
The Page_Load event of the page binds data with the GridView control as follows:
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindData();
}
}
private void BindData()
{
string constr = "Server=TestServer;Database=TestDatabase;uid=test;pwd=test;";
string query = "SELECT ProductID, ProductName, UnitPrice, UnitsInStock, OnSale FROM Products";
SqlDataAdapter da = new SqlDataAdapter(query, constr);
DataTable table = new DataTable();
da.Fill(table);
GridView1.DataSource = table;
GridView1.DataBind();
}
To calculate totals first you need to declare some variables outside the RowDataBound event. Two variables will keep track of the total of all the values bound inside GridView control and the third variable totalItems will be used to calculate the average price.
decimal totalPrice = 0M;
decimal totalStock = 0M;
int totalItems = 0;
As I mentioned above, RowDataBound event raised for all header, data or footer rows. I put one if condition to make sure currently it is binding DataRow. Then I am getting the reference of lblPrice and lblUnitsInStock labels using FindControl method. These labels have price and stock values of the current row in their text property. I am converting the text into decimal and then adding the value in totalPrice and totalStock variables. Once all the Data rows are finished I have similar check for Footer Row in which I am getting the reference of lblTotalPrice and lblTotalUnitsInStock Labels available in the FooterTemplate inside GridView and showing the totals on those labels.
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
Label lblPrice = (Label)e.Row.FindControl("lblPrice");
Label lblUnitsInStock = (Label)e.Row.FindControl("lblUnitsInStock");
decimal price = Decimal.Parse(lblPrice.Text);
decimal stock = Decimal.Parse(lblUnitsInStock.Text);
totalPrice += price;
totalStock += stock;
totalItems += 1;
}
if (e.Row.RowType == DataControlRowType.Footer)
{
Label lblTotalPrice = (Label)e.Row.FindControl("lblTotalPrice");
Label lblTotalUnitsInStock = (Label)e.Row.FindControl("lblTotalUnitsInStock");
lblTotalPrice.Text = totalPrice.ToString();
lblTotalUnitsInStock.Text = totalStock.ToString();
lblAveragePrice.Text = (totalPrice / totalItems).ToString("F");
}
}
|
RELATED TUTORIALS
|
Formatting GridView output based on Data
Display Microsoft Excel File in ASP.NET GridView
Using Button columns in GridView
Editing Data using ASP.NET GridView Control
Deleting multiple Rows in GridView using Checkbox
Using Hyperlink columns in GridView
Using Checkbox in ASP.NET GridView Control
Export ASP.NET GridView to Excel
|
|
|
|
|
|
|
|
|
|
|
|
|