0

I'm coding a report page. In this page, there are two date fields for user to filter the date and the GridView is filled depending on these dates in the CodeBehind (When user click the button view). Now I want to implement a paging/sorting feature for this GridView. I've researched and see there are default paging for GridView which is not very efficient (my report table may have thousands of records) and custom paging but this requires using ObjectDataSource (which I don't use). Thus anyone can recommend me some approaches that are best used in this situation? Some tutorials will be gladly appreciated :)

Thanks,

Leo
  • 2,173
  • 6
  • 28
  • 37

1 Answers1

1

Assuming you have the records stored in a database, I would get the record count and use that number for paging. Then I'd query for the ones I want to see on each page.

string.Format(@"
    SELECT TOP {0} * FROM Records WHERE pkId NOT IN (
        SELECT TOP {1} pkId FROM Records ORDER BY pkId
    ) ORDER BY pkId;",
    upperBoundary,
    lowerBoundary
);

Where upperBoundary would be lowerBoundary + itemsPerPage, for example. Using MSSQL, we don't have the luxury of MySQL's LIMIT feature, but this does the same.

Narrowing down on dates, would be something like

SELECT * FROM Records WHERE Date > earlyDate AND Date <= lateDate

This is the most effecient way in my oppinion, cause it causes little traffic and if you cache the record count, you won't have many queries flying around either.

Heki
  • 926
  • 2
  • 15
  • 34
  • In Mssql 2005 onward you can use row_number to select a subset rather than running multiple select statements. –  Nov 17 '10 at 08:21
  • After some looking around, I haven't been able to find an example that use row_number() to make a equally or more simple query. The examples look more precise though, but the queries look more complex. – Heki Nov 22 '10 at 08:48