Summing Columns in a GridView
Level: Beginner.
After getting information from a database, you may also want summary information about the data (totals, averages, counts, etc.). This can be done using SQL group by clauses or, it can be done while binding to a GridView. I've seen other examples of this but they seem more complicated than they need to be.
Note: If the GridView has more than one page, only the data on the current page can be summarized using this technique.
Note: The sample uses the OrderDetails table from the NorthWind database.
ASP code:
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="OrderID,ProductID" DataSourceID="SqlDataSource1"
ondatabinding="GridView1_DataBinding"
ondatabound="GridView1_DataBound"
onrowdatabound="GridView1_RowDataBound">
<Columns>
<asp:BoundField DataField="OrderID" HeaderText="OrderID" ReadOnly="True"
SortExpression="OrderID" />
<asp:BoundField DataField="ProductID" HeaderText="ProductID" ReadOnly="True"
SortExpression="ProductID" />
<asp:BoundField DataField="UnitPrice" HeaderText="UnitPrice"
SortExpression="UnitPrice" />
<asp:BoundField DataField="Quantity" HeaderText="Quantity"
SortExpression="Quantity" />
<asp:BoundField DataField="Discount" HeaderText="Discount"
SortExpression="Discount" />
</Columns>
</asp:GridView>
C# Code:
private Decimal OrderTotal; // holds total price of order
// ---- the grid is about to start getting data...initializeprotected void GridView1_DataBinding(object sender, EventArgs e)
{ OrderTotal = 0.0M;
}
// ---- Each row is data bound ------------------------------protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{if (e.Row.RowType == DataControlRowType.DataRow)
{DataRowView DRV = e.Row.DataItem as DataRowView;
// price is in column 2 // quantity is in column 3Decimal Price = (Decimal)DRV.Row.ItemArray[2];
short Quantity = (short)DRV.Row.ItemArray[3];
Decimal RowTotal = Price * Quantity;OrderTotal += RowTotal;
}
}
// ---- The grid is done getting data, show the Order Total --------protected void GridView1_DataBound(object sender, EventArgs e)
{ LabelOrderTotal.Text = OrderTotal.ToString("C");}
I hope you find this useful.
Steve Wellens
Copyright 2008 Steve Wellens