Paging in SQL Server

declare @pagesize int
set @pagesize =25

declare @pagenumber int
set @pagenumber =2

declare @from int
set @from = (@pagenumber-1)*@pagesize+1
declare @to int
set @to = @from+@pagesize-1

select @from ,@to
SELECT * FROM ( SELECT * from Employee_tbl ,ROW_NUMBER() OVER (ORDER BY EmployeeID) as rownum FROM Employee_tbl )
seq WHERE seq.rownum BETWEEN @from AND @to ORDER BY seq.rownum

Make List Pageable

I have doing some work these days on MVC .I needed an functionality to show data on the screen with paging.

In the beginning I thought this could be really difficult to add new method for  IEnumerable list objects. Here I am going to share the concept to achieve this and some code snippet..(some of  from my application and some of them are from internet.)

Steps

1.  First we need an interface which has properties to provide necessary information for paging like Page count, page number etc. My interface looks like this :

 

 

	public interface IPagedList<T> : IList<T>
	{
		int PageCount { get; }
		int TotalItemCount { get; }
		int PageIndex { get; }
		int PageNumber { get; }
		int PageSize { get; }
		bool HasPreviousPage { get; }
		bool HasNextPage { get; }
		bool IsFirstPage { get; }
		bool IsLastPage { get; }
	}
 

2. Next we need to simply implement this interface but with

 
System.Collections.Generic.List
 
which look like this 
(I have reformatted the code to fit in the post )
	public class PagedList<T> : List<T>, IPagedList<T>
	{
public PagedList(IEnumerable<T> source, int index, int pageSize, int? totalCount = null)
	: this(source.AsQueryable(), index, pageSize, totalCount)
{
}

public PagedList(IQueryable<T> source, int index, int pageSize, int? totalCount = null)
{
if (index < 0)
throw new ArgumentOutOfRangeException("index", "Value can not be below 0.");
if (pageSize < 1)
throw new ArgumentOutOfRangeException("pageSize", "Value can not be less than 1.");

if (source == null)
source = new List<T>().AsQueryable();

var realTotalCount = source.Count();

PageSize = pageSize;
PageIndex = index;
TotalItemCount = totalCount.HasValue ? totalCount.Value : realTotalCount;
PageCount = TotalItemCount > 0 ? (int)Math.Ceiling(TotalItemCount / (double)PageSize) : 0;

HasPreviousPage = (PageIndex > 0);
HasNextPage = (PageIndex < (PageCount - 1));
IsFirstPage = (PageIndex <= 0);
IsLastPage = (PageIndex >= (PageCount - 1));

if (TotalItemCount <= 0)
return;

var realTotalPages = (int)Math.Ceiling(realTotalCount / (double)PageSize);

if (realTotalCount < TotalItemCount && realTotalPages <= PageIndex)
AddRange(source.Skip((realTotalPages - 1) * PageSize).Take(PageSize));
else
AddRange(source.Skip(PageIndex * PageSize).Take(PageSize));
}

#region IPagedList Members

public int PageCount { get; private set; }
public int TotalItemCount { get; private set; }
public int PageIndex { get; private set; }
public int PageNumber { get { return PageIndex + 1; } }
public int PageSize { get; private set; }
public bool HasPreviousPage { get; private set; }
public bool HasNextPage { get; private set; }
public bool IsFirstPage { get; private set; }
public bool IsLastPage { get; private set; }

#endregion
}

3..  Than we just need a simple extension

 

 

public static class PagingExtensions
{
#region IEnumerable<T> extensions

public static IPagedList<T> ToPagedList<T>(this IEnumerable<T> source, int pageIndex,
 int pageSize, int? totalCount = null)
{     return new PagedList<T>(source, pageIndex, pageSize, totalCount);
}

#endregion
}

4. and we are ready to go:::::

private static IList<Group_tbl> lstGroups = new List<Group_tbl>();
var pagedList=   lstGroups.ToPagedList(currentPageIndex, pageSize);

 

 

Enjoy..