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...initialize
protected 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 3
        Decimal 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