Database based techniques - only retrieving the particular rows you require in each request - are therefore generally to be preferred. But some of those provide their own headaches - for example as well as retrieving the particular set of rows for a page, you will often want to know what the full count of records to be able to display a message along the lines of: 200 records found. Page 1 of 20.
SQL Server Paging
Prior to the 2005 version, I would do this using SQL Server with two queries - one to get the page of data and another, using the same WHERE clause, to get the count. With SQL Server 2005 though, you can get both with a single query, using the following syntax:
CREATE PROCEDURE getProducts
WITH Results AS (
SELECT ROW_NUMBER() OVER ( ORDER BY Name ) RowNumber,
(SELECT Count(*) FROM Results) TotalRows
WHERE @page = 0 OR @recordsPerPage = 0 OR RowNumber BETWEEN @recordsPerPage * (@page - 1) AND @recordsPerPage * @page
ORDER BY Name
With this stored procedure, passing 0 for the page number or the records per page simply returns all of them, but with non-zero values passed only the required page of data is returned. In addition, every row will have an additional column TotalRows that returns the full count of the query.
MySQL Server Paging
On a recent project we had need to port an SQL server database to MySQL, at which point we discovered this is one of the features that isn't so easy to translate. MySQL supports the LIMIT clause in SELECT statements. It also supports a clause called
SQL_CALC_FOUND_ROW which returns the total number of records returned irrespective of the LIMIT clause. These two together can be used as the basis of a similar paging technique to that described above.
The crux of the problem though is that MySQL doesn't support the use of parameters in the LIMIT clause in functions (stored procedures).
Instead you have to make use of prepared statements and execute two of them within the function to have the same result as the SQL server version. An example follows:
CREATE PROCEDURE `getProducts`(page INT,@recordsPerPage INT)
SET @sql = CONCAT(
"SELECT SQL_CALC_FOUND_ROWS ID,Name
LIMIT ", page,",",@recordsPerPage,"; "
set @sql2 = "SELECT found_rows();";
prepare stmt from @sql;
prepare stmt from @sql2;
drop prepare stmt;
In this case, rather than having one result set with an additional column holding the total number of rows, there will be a second result set containing just a single field where the total record count can be extracted.
In .Net the IDataReader method NextResult can be used to access this additional recordset.