ASP.NET: Totaling a GridView part 2, the SQL

Not long ago, I wrote a quick guide on adding a total to a GridView.  It was based upon building a cumulative total while looping though the GridView rows during databinding.  Not a bad method to be sure, but it does place some of our code with our View layer and it has the limitation of only processing the rows the GridView is displaying.  If you have paged results, the total will be incorrect.

If you’re using DataSets there is a simpler method, that doesn’t have these problems: Expressions.  Expressions are simple, SQL-like statements you can run on a DataTable.  Since the DataTable is a disconnected data source, no overhead is placed on the database server.  Expressions come in two flavors; DataColumn.Expression and DataTable.Compute.  The first allows you to define a column as the result of an expressions, and the second – the one we’ll be using – allows you to preform calculations on the whole DataTable (or DataSet if there is more than one table in the set and relationships are defined).

Now down to the code.  We won’t be touching the GridView, except to add a label to hold the total in the footer.  We will add a method to catch the ObjectDataSources OnSelected event, which fires after the ObjectDataSrouce has selected the data, but before the data is returned to the GridView.

   1:  <asp:ObjectDataSource ID="odsOrders" runat="server" OnSelected="odsOrders_Selected"
   2:      SelectMethod="AllOrders" TypeName="OrdersTableAdapters.STORE_ORDERTableAdapter" />
   3:  <asp:GridView ID="gvOrders" runat="server" ShowFooter="true" AllowPaging="True" 
   4:      AutoGenerateColumns="False" DataKeyNames="STORE_ORDER_ID" DataSourceID="odsOrders">
   5:      <Columns>
   6:          <asp:BoundField DataField="BILL_NAME" />
   7:          <asp:BoundField DataField="ORDER_DATE" />
   8:          <asp:TemplateField>
   9:              <ItemTemplate>
  10:                  <asp:Label ID="Label1" runat="server" Text='<%# Bind("ORDER_TOTAL") %>' />
  11:              </ItemTemplate>
  12:              <FooterTemplate>
  13:                  <asp:Label ID="Label1" runat="server" Text='<%# total.ToString("c") %>' />
  14:              </FooterTemplate>
  15:          </asp:TemplateField>
  16:      </Columns>
  17:  </asp:GridView>

 

Now, the magic will happen in the OnSelect method:

   1:  protected void odsOrders_Selected(object sender, ObjectDataSourceStatusEventArgs e) {
   2:     Orders.STORE_ORDERDataTable dtOrders = e.ReturnValue as Orders.STORE_ORDERDataTable;
   3:     total = Convert.ToDecimal(dtOrders.Compute("sum(order_total)", String.Empty));
   4:  }

 

The first thing we do is cast the e.ReturnValue, which is the result of the Select, to our DataTable.  Next we use the Compute method to calculate the total with the expression “sum(order_total)”.  The Compute method also takes a filter clause, so if we wanted to only total orders greater than $100.00 we would do the following:

   1:  protected void odsOrders_Selected(object sender, ObjectDataSourceStatusEventArgs e) {
   2:     Orders.STORE_ORDERDataTable dtOrders = e.ReturnValue as Orders.STORE_ORDERDataTable;
   3:     total = Convert.ToDecimal(dtOrders.Compute("sum(order_total)", "order_total > 100"));
   4:  }

 

And thats it.  So what do we need to watch out for?  Caching.  In this code, we will only total the result when the ObjectDataSource fires a select to the database, which will not always happen if caching is enabled.  If we moved the label outside of the GridView we could set the text property and let the label’s ViewState handle the mess.  Not very clean is it?  We don’t want to do that, so instead we’ll adapt the total to use ViewState on it’s own.

   1:  public decimal total {
   2:     get { return Convert.ToDecimal(ViewState["total"]); }
   3:     set { ViewState["total"] = value; }
   4:  }

 

This uses the page’s ViewState to keep up with the value in total (Convert will return zero in the case of null), and our GridView is none the wiser.

  • IndiraPriyaDarshini

    Hi,
    The above is working quiet nicely.But ,since am a fresher to this field,i have one doubt..can i display
    more than one value to the footer,ie, i have to total
    more than one columns and display the sum in the footer in corresponding column..

  • http://www.vinull.com Mike

    I’m not exactly sure what you are asking, but every column in a gridview can have a footer. For totaling more than one column in the dataset, see the expressions documentation linked above.

  • Kevin

    dear friend
    i have a problem.
    in a gridview, i want to put sum of 3 columns values in a new column (at all records).
    please help me.

  • http://www.vinull.com Mike

    I assume you are using a datatable (you don’t provide much information)? In the objectdatasource OnSelected method, add a column to the data table and set the expression to the sum of the 3 columns.

  • Kim

    I have a problem. I need to calculate two fields together which has more than one row and put it at the bottom of the gridview. How do I do that

  • http://www.vinull.com Mike

    Hi Kim – I’m not sure what you are after – please provide a link to the code the shows the problem.

  • Kim

    <asp:GridView ID="gvResults" runat="server" CellPadding="3" ForeColor="Black" ShowFooter="true" GridLines="Vertical" BackColor="White" BorderColor="#999999" BorderStyle="Solid" BorderWidth="1px"><SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" /><PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" /> <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" /><AlternatingRowStyle BackColor="#CCCCCC" /></asp:GridView>
    there is my code for the gridview
    If drResults.HasRows Then
    txtOrderNumber.Text = ""
    gvResults.DataSource = drResults
    gvResults.DataBind()
    Else
    txtOrderNumber.Text = "Order#" & txtOrderNumber.Text & "invalid order number."
    gvResults.DataSource = ""
    gvResults.DataBind()
    End If
    Try
    While drResults.Read() = True
    ( ‘this part is not pulling information) Total = drResults("ACTSP") * drResults("QUANO") + drResults("ACTSP") * drResults("QUANO")
    lblTotalAmount.Text = (Total)
    End While
    Finally
    drResults.Close()
    mySelection.Dispose()
    End Try
    drResults.Close()
    mySelection.Dispose()

  • http://www.vinull.com Mike

    I don’t see any columns defined in the GridView, but I’m going to assume you have some columns with a Label ID="lblTotalAmount". Make sure you user the GridView.FindControl method to local this control – if it’s inside of a template it won’t be available at the Page level. You also may want to change the "Total =" to "Total +=" if you are wanting a running total (as it is you’ll only get the total of the last record).

  • Kim

    so Mike I need to change it to this Total +="drResults("ACTSP")*drResults("QUANO")

  • Kim

    See more records has 10 to 700 rows of data. I need to calculate all of those together

  • http://www.vinull.com Mike

    What is "drResults" – is it a DataTable? If so you can run drResults.Compute("sum(ACTSP) + sum(QUANO)") to get the total of all records, not just the set displayed in the GridView. You can use the contact link at the bottom to send me a zip file of the project if this doesn’t answer your question and I will take a look at it.

  • saurabh

    Hi , how i can display the total of any data tables’s field…….i need the code in asp.net1.1 using VB….

  • http://www.vinull.com Mike

    Saurabh, follow the link in the article to the MSDN documentation for DataTable.Compute()

  • vicky

    how to creat a subtotal in gridview , basing on column grouping in the database, for exm group by orderid i need the subtotal of unitcost, and quantity
    for a particularid

  • http://www.vinull.com Mike

    Hi Vicky, check out MSDN on DataColumn.Expression and DataTable.Compute which can get you what you need, depending on you situation.

  • vicky

    i am getting the value but how to put it in the grid as it doenst let me create an empty row ,
    how should i assign to the grid basing on a column
    for example
    myDataSet.Tables(0).Rows.InsertAt(myDataSet.Tables(0).NewRow(), i)
    ‘ myDataSet.Tables(0).Rows.Add()
    ViewState("_sumtotal") = myDataSet.Tables(0).Compute("SUM(price)", "id=" & curCat).ToString

  • http://www.vinull.com Mike

    I’m not actually adding a row in the above (or the other post I link to at the top) – I’m setting a label in the footer. You can see this by following the other post, and checking out the MSDN site for putting a total in a gridview footer, which I based the original off of.

  • vicky

    i want to add an empty row basing on column and then do the subtotalling for the all the rows for that column
    prodductid – 1
    price item
    10 1
    20 2
    ———–
    sub 30 3
    productid 2
    price item

    this is the format i need.

  • http://www.vinull.com Mike

    GridView doesn’t do subtotaling very well (not really supported), I’d recommend going with either a repeater or a server side table control, and you control the markup. You may want to look into the ReportViewer control, but that control has it’s own set of issues.

  • vicky

    i want to add an empty row basing on column and then do the subtotalling for the all the rows for that column
    prodductid – 1
    price item
    10 1
    20 2
    ———–
    sub 30 3
    productid 2
    price item

    this is the format i need.

  • vicky

    thankyou, i will try with repeater

  • chakri

    thanks a lot,i have tried its working