Display Sum Total in Data Grid
The original idea and the implementation:
The idea was to use the ItemDataBound event of the DataGrid control, grabbing the corresponding item value of all the rows of the data grid and summing it up. Then we could display the total in the footer. Please see the code snippet below:
PROTECTED As sender myDataGrid_ItemDataBound(ByVal Sub System.Object, ByVal e As DataGridItemEventArgs) Handles myDataGrid.ItemDataBound Select Case e.Item.ItemType Case ListItemType.Item, ListItemType.AlternatingItem interimTotal += CType(e.Item.Cells(3).Text, Double) Case ListItemType.Footer e.Item.Cells(2).Text = "TOTAL: " e.Item.Cells(3).Text = interimTotal.ToString End Select End Sub
The code snippet shows the event method which is invoked whenever a row is bound with the data from the data source (the binding is not shown and it is assumed that the data source is a DataReader).
Each item in the data grid is one of these types:
- Item (Regular row)
- AlternatingItem (Alternating row)
The only items we are interested in are the Item and AlternatingItem types (and Footer of course). So we catch them with a Select Case statement. We need to remember that this method would be invoked for each row of the data grid. Items in a DataGrid control correspond to rows of output. Each Item has an array of Cells. Each column of the row in question is depicted as a Cell. The variable interimTotal is a Private variable in the code behind class. Once the Footer is reached the sum total could be displayed.
An alternative approach
The above solution works really well if the number of rows is small. If the rows are more than, say 500 rows, then we may need to adopt an alternative approach. The reason for this is that there would be a performance penalty as the method is invoked and all the work is done for each and every row of the data reader.
An alternative approach would be to use the DataSet. Using DataSet would be logically appropriate because what we are doing here is holding the data for a moment for manipulating the same. The DataReader, as we all know, is useful for grabbing the data and throwing it out for display.
The code snippet would look like as given below:
myDataGrid.DataSource = ds myDataGrid.DataBind() sumTotal = ds.Tables(0).Compute("SUM(amt_txn)", String.Empty).ToString lblMessage = “TOTAL :” & sumTotal
The code snippet shows the usage of Compute() method of DataTable object. The amt_txn is the column in the DataTable for which we needed the sum total. You could place the lblMessage below the DataGrid.
Before you throw that stone at me, let me defend myself. I know that I could not display the TOTAL in the footer of the DataGrid. I could, just by looping through the Items collection (of the DataGrid) and checking the ItemType, but that would be the same as the last example.