If I get my dataset into a DataTable then I can do with it as I like, but I have the complexity of handling sort, paging and caching manually. I'm trying to avoid this for now. If I instead use a SQLdataSource that's all free.
I need a grand total row, the contents of which I display outside the gridview.
- I know that I can get that by hooking the RowBound event in IIS and summing up every row I see, but that seems a but complicated and pedestrian.
- I know that I can manually do a SQLdataSource.Select on my SQLdataSource (it's cached) to extract a DataTable from that, and then use DataTable.Compute to sum the columns, but this seems a bit hack and I'm not sure how efficient two "selects" really is even with caching.
My preferred approach would be to get SQLserver to do the grunt work, using Group By Rollup, which gives me the last row of the result set with the totals I want in it. The problem then is that I have the totals row in my GridView, which I don't want in there as I need to put the totals somewhere else (no point in having them on the last page where they can be sorted and paged). I guess I could again catch row bound events and make this totals row invisible, but that's a bit hack and may confuse the paging.
So I'm wondering if there's a neat way to do this?