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.

Posted By Mike On Tuesday, November 28, 2006
Filed under asp.net gridview datasets | Comments (22)

Submit this story to DotNetKicks   

IndiraPriyaDarshini - Tuesday, February 27, 2007 1:00:02 PM

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..

Mike - Friday, March 02, 2007 9:29:53 PM

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 - Sunday, July 08, 2007 10:23:08 AM

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.

Mike - Sunday, July 08, 2007 4:59:45 PM

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 - Monday, July 09, 2007 8:49:55 PM

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

Mike - Tuesday, July 10, 2007 12:47:54 AM

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

Kim - Tuesday, July 10, 2007 12:51:21 PM

<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()

Mike - Tuesday, July 10, 2007 2:19:34 PM

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 - Friday, July 13, 2007 6:57:37 PM

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

Kim - Friday, July 13, 2007 7:20:54 PM

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

Mike - Friday, July 13, 2007 9:32:30 PM

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 - Thursday, January 17, 2008 6:24:34 PM

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

Mike - Thursday, January 17, 2008 7:30:49 PM

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

vicky - Thursday, June 12, 2008 7:44:56 PM

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

Mike - Friday, June 13, 2008 1:53:53 AM

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

vicky - Wednesday, June 18, 2008 6:16:37 PM

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

Mike - Wednesday, June 18, 2008 7:15:42 PM

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 - Wednesday, June 18, 2008 7:57:48 PM

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.

Mike - Wednesday, June 18, 2008 8:14:22 PM

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 - Wednesday, June 18, 2008 8:55:06 PM

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 - Wednesday, June 18, 2008 8:56:50 PM

thankyou, i will try with repeater

chakri - Thursday, November 27, 2008 10:48:45 AM

thanks a lot,i have tried its working

Leave a comment



Your name:
 

Your email (not shown):
 
Will display your Gravatar image.

Your website (optional):



About Michael

Michael C. Neel, born 1976 in Houston, TX and now live in Knoxvile, TN. Software developer, currently .Net focused. Board member of ETNUG and organizes CodeStock, East Tennessee's annual developers conference. .Net speaker, a Microsoft ASP.NET MVP and ASPInsider. Co-Founder of FuncWorks, LLC and GameMarx.

Proud father of two amazing girls, Rachel and Hannah, and loving husband to Cicelie who inflates and pops his ego as necessary.

 Subscribe to ViNull.com |  Comments

Follow me on Twitter | Contact Me

Related Posts

ASP.NET: Sorting a GridView with custom objects

This article builds upon the code started in GridView and ObjectDataSource with custom objects. An ObjectDataSource really only works with two types of ... Read more

ASP.NET: GridView and ObjectDataSource with custom objects

The GridView control is one of the great time savers of ASP.NET.  Sorting, paging, editing, deleting, and even AJAX (set EnableSortingAndPagingCallbacks ... Read more

ASP.NET: Adding a total to gridview

One difference between learning the framework and using the framework is rarely anything ever done the way you learned it. The GridView control is a perfect ... Read more

Upcoming speaking dates

Now hat the holidays are over (minus the SuperBowl of course), it's time to get back into the swing of things and line up those speaking dates.  I've ... Read more

ASP.NET: Cannot use a leading .. to exit above the top directory

This is a really quick post, mostly for myself so the next time I have this issue I can find the answer (yes, I often search my blog before google). The ... Read more

XNA 3D Primer by Michael C. Neel

XNA 3D Primer by Michael C. Neel
Buy Now: [ Amazon ] [ Wrox ]

GameMarx

CodeStock

ASPInsiders Member

ETNUG Member