Click here to Skip to main content
Click here to Skip to main content

Dynamic Pagination

, 4 Jan 2013
Rate this:
Please Sign up or sign in to vote.
Dynamic paging in grids

Introduction  

This tip will provide developer do little wrap around T-SQL code and get pagination done easily on top of existing code probably.

Background 

Dynamic paging in grids is cumbersome and if someone wants to implement custom paging then that has no built in support in T-SQL and using paging for 3rd party controls is sometime is not easy, when it comes to sorting on columns of grid and doing paging makes it more difficult. I found it also slow performing at times

Using the code 

For handling pagination as I did it in one of our cloud based Products. I changed generic procedure that queries internally respective table to include 2 params @PageNo int=Null, @PageSize int=20

query would include 2 columns
,RANK() OVER (ORDER BY a.OIT_dbf_Id desc) AS Rank ,((RANK() OVER (ORDER BY a.OIT_dbf_Id desc))/(@PageSize)) + 1 as Page

Where clause will include Where Rank between IsNull(case when @PageNo is Null then @PageNo else case when @PageNo = 1 then 1 else (@PageNo-1)*@PageSize +1 end end,Page) and IsNull(case when @PageNo is Null then @PageNo else @PageNo * @PageSize end,Rank) so when I want particular page I would just set the procedure filter accordingly as well I can set pagesize.

Select * from ( 
select TOP 100 Percent
a.OIT_dbf_ID
as TaskID,
......
, RANK()OVER (ORDERBY a.OIT_dbf_Id desc)ASRank
,((RANK()OVER (ORDERBY a.OIT_dbf_Id desc))/(@PageSize))+ 1 as Page
from
OIT_tbl_Tasks a Where
a.OIT_dbf_Id =IsNull(@TaskID, a.OIT_dbf_Id) order by
a.OIT_dbf_Id desc
)a
Where Rank betweenIsNull(case when @PageNo isNull then @PageNo else 
							case when @PageNo = 1 then 1 else (@PageNo-1)*@PageSize +1 end
			      end,Page)andIsNull(casewhen @PageNo isNullthen @PageNo else @PageNo * @PageSize end,Rank)

Points of Interest

This I used and found performance improvement is pagination by 2-3 times compared to 3rd party controls. I am going to add caching to it i.e. getting next and prev page and also try out jQuery datatables etc..

History

This is just first version released for your opinion

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

RahulAJoshi
Architect
India India
I work as CTO for Orwell IT Solutions Pvt Ltd.
I like to read about new technologies and experiment, I am always watchful and also mentor newbies joined in organization. I have blog on wordpress.
 
http://rahulajoshi.wordpress.com

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web01 | 2.8.140902.1 | Last Updated 4 Jan 2013
Article Copyright 2013 by RahulAJoshi
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid