a large paged GridView, Rollup and Grand Totals with SQLdataSource

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 gr开发者_运维百科unt 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?

When you mean large data set, I am assuming a million plus?

I would suggest caching the result of the grand total. There's no need to recalculate that every time you view/page the table. Create observable events when you create/update/delete the table to refresh the grand total info? (Because I do not see you mentioning filtering, which is the only time when the grand total changes without the dataset changing)

Or maybe have the grand total info on a timed expire cache. If the data is suffeciently large and volatile, sometimes you would have to consider the business value of having an exact uptodate grand total. Usually there's no business value in that. A somewhat close enough value from a few sec ago is fine.

As for SqlDataSource control, personally I hate it. Whatever happened to the Presentation/Logic/Data tiers?!...