I'm trying to find an efficient and portable way to implement paging for records returned to a GridView in ASP.NET 2.0 and C#. What I mean by portable is something I can use with an MS SQL Server, Access, Oracle, MySQL, etc.. So I don't want to use SQL 2005's ROWNUMBER().
One way of doing it is returning all records to the GridView and let it handle the paging, but that's not efficient since I really have to pull all records out. Another way of doing it is assigning an AUTONUMBER to the table I'm querying, but I don't have that level of freedom with the database to just change the whole schema. I've heard of creating a temporary table and filling it up, but that sounds pretty inefficient for every time some calls the page.
Something I thought of is maybe sorting on the primary key in the table and doing something like "SELECT primarykey, column2, column3 FROM table ORDER BY primarykey LIMIT 10", then keeping track of the last primarykey returned, so the next page would query "SELECT primarykey, column2, column3 FROM table ORDER BY primarykey WHERE primarykey > @lastprimarykey LIMIT 10". Is this a good theory or is there any better ideas on this?? I would appreciate any feedback or experiences.