Paging with the Telerik MVC grid when using a sproc

Question

The documentation only shows how to bind to an IEnumerable (which uses linq to page and sort) ... but I need to go against a sproc because expressing the query I'm working on with linq is proving to be a bit slow.

Can anyone provide any guidelines or pointers on what's the best way to do this?

Answer 1

Are you talking about how to bind the Telerik grid to a result set emitted by a stored procedure, or how to implement paging in a stored procedure? It's not clear from your question.

The following T-SQL stored procedure is a very efficient implementation of paging. THE SQL optimiser can find the first ID very fast. Combine this with the use of ROWCOUNT, and you have an approach that is both CPU-efficient and read-efficient. For a table with a large number of rows, it certainly beats any approach that I've seen using a temporary table or table variable.

CREATE  PROCEDURE dbo.PagingTest
(
    @PageNumber int,
    @PageSize int
)
AS

DECLARE @FirstId int, @FirstRow int

SET @FirstRow = ( (@PageNumber - 1) * @PageSize ) + 1
SET ROWCOUNT @FirstRow

-- Add check here to ensure that @FirstRow is not
-- greater than the number of rows in the table.

SELECT   @FirstId = [Id]
FROM     dbo.TestTable
ORDER BY [Id]

SET ROWCOUNT @PageSize

SELECT   *
FROM     dbo.TestTable
WHERE    [Id] >= @FirstId
ORDER BY [Id]

SET ROWCOUNT 0
GO 

Answer 2

You need to to use custom binding. The linked example shows how to get the current page.