For some reasons I do like to use DataSets (and Datatables) rather than DataReaders or other sort of IList collections to hold a list of records. Especially, typed DataSets are very helpful and am intended to write some articles about exploiting them later in this weblog. For example we can fill DataTables’s data/metadata by retrieving it from a database or directly through some code. Anyway the other day I was trying to create a GridView with subtotals (will explain about how to create such a gridview later) and I used DataTable.Compute method to sum up the values of a column of my GridView. Compute method can calculate Sum, Average, Count and … of a specific column. It’s syntax is like this:
DataTable.Compute (string expression, string filter)
expression contains an aggregate function like Sum, Average, Count and etc. For example to calculate the total cost you must use SUM(COST) as your expression.
If you must perform an operation on two or more columns, you should create a DataColumn, set its Expression property to an appropriate expression, and use an aggregate expression on the resulting column. In that case, given a DataColumn with the name “total,” and the Expression property set to:
"Quantity * UnitPrice"
The expression argument for the Compute method would then be:
The second parameter filter determines which rows are used in the expression. For example if your DataTable contains a column named Age, you can set filter expression to “age >= 30”.
Some of availuible aggregate functions are:
SUM, AVG , COUNT, MIN and MAX
P.S.1 : SUM of an Int32 column is long, not int!
P.S.2 : The result type of Computer method is Object so you must explicitly cast it to your desired type.