You didn’t specify a database or version. If you’re lucky enough to be able to use the brand new SQL Server 2012 and have access to MSDN, you can use the shiny new OFFSET and FETCH keywords. The following query will skip 20 records and return the next 5.
SELECT * FROM [Posts]
ORDER BY [InsertDate]
OFFSET 20 ROWS
FETCH NEXT 5 ROWS ONLY
Check out http://msdn.microsoft.com/en-us/library/ms188385(v=sql.110).aspx#Offset for more info.
Also, it’s easy enough to copy the way Massive does it and write your own extension method for IDbConnection. Here’s Massive’s code.
var query = string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {2}) AS Row, {0} FROM {3} {4}) AS Paged ", columns, pageSize, orderBy, TableName, where);