0

I'm trying to wrap my head around custom paging in the ASP.NET Gridview, but all of the examples I read seem to be bloated with stuff I don't need (Scott Gu's for example).

Can someone point me in the right direction to a tutorial that is easy to understand the basics?

EXAMPE: If I have the following Stored Procedure... 

Alter Procedure dbo.GetReqeusts

@Category nvarchar(50)

As
Begin
  Select dbo.[Name], 
         dbo.[ID] 
  From   dbo.[Table] 
  Where  dbo.[Category] = @Category
End

And this example returns 200 rows, how would I convert this Stored Procedure into an efficient paging procedure?

Chase Florell
  • 46,378
  • 57
  • 186
  • 376

1 Answers1

2

4guysfromrolla.com have a whole series of articles about working with and displaying data. There are several about custom paging.

The key point for the stored procedure is to use the ROW_NUMBER() function to restrict the records to be returned:

SELECT RowNum, [Name], [ID]
FROM
   (SELECT [Name], [ID]
         ROW_NUMBER() OVER(ORDER BY [ID]) as RowNum
    FROM [Table] t
    WHERE [Category] = @Category
   ) as DerivedTableName
WHERE RowNum BETWEEN @startRowIndex AND (@startRowIndex + @maximumRows) - 1
M4N
  • 94,805
  • 45
  • 217
  • 260
  • yeah I looked at that example several times but it makes no sense to me... sorry. I'm hoping to find an A/B (normal)/(efficient) so that I can see the differences. – Chase Florell Feb 28 '10 at 22:33
  • the (dot dot dot), and the (From Employees e) kinda confuses me – Chase Florell Feb 28 '10 at 22:36
  • I think this one makes a bit more sense http://www.nikhedonia.com/notebook/entry/efficient-paging-for-gridview/ – Chase Florell Feb 28 '10 at 22:40
  • The example I had used was taken from the article linked in the answer. I tried to adapt it to your sample code. Hope this helps. – M4N Feb 28 '10 at 22:42
  • Ok,so I got this working, however I do have one little problem... I want EVERYTHING to populate in grid in DESC order. IE: I want to start with the last row and move up... how can I do this? – Chase Florell Mar 07 '10 at 21:58
  • @rockinthesixstring: You have to modify the *inner select query* to return the results in the desired order: e.g. `... WHERE [Category] = @Category ORDER BY [Category] DESC` – M4N Mar 07 '10 at 22:17
  • Yeah I tried that, but I got the following `The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified` – Chase Florell Mar 08 '10 at 00:03
  • I also tried putting in "Top 10000000" just to see, but it still lists the records in reverse. – Chase Florell Mar 08 '10 at 00:05
  • sorry, this seems to have fixed it `ROW_NUMBER() OVER(ORDER BY [ID] Desc) as RowNum` – Chase Florell Mar 08 '10 at 00:46