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.
