The article Server-Side Paging with the Entity Framework and ASP.NET MVC 3 describes how to paging using Entity Framework and ASP.NET MVC 3. Using Skip() and Take() extension methods is main idea of the article. But you can’t do it if your project using Oracle database. Because EF is not supported from Microsoft for Oracle database. So, I’ll describe how to implement paging from stored procedures in ASP.NET MVC 2.0. This approach bellow using Northwind database but it’s also applied for Oracle database.
1. Define the GetProductsByFilter stored procedures
This stored procedures will contain total rows of the table and selection of records by page size
CREATE PROCEDURE GetProductsByFilter
-- Add the parameters for the stored procedure here
@i_Page_Index int = 0,
@i_Page_Count int = 10,
@o_total_rows int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Select
SELECT TOP (@i_Page_Count) a.* FROM
(
SELECT p.*,
ROW_NUMBER() OVER (ORDER BY p.ProductID) AS num
FROM dbo.Products p
) AS a
WHERE num > @i_Page_Index * @i_Page_Count;
-- Get Total Rows
SET @o_total_rows = (SELECT COUNT(1)
FROM Products);
END
GO
2. Define PagerParams class
This class used to store paging information. It’s include the current page index, the page size and the total records.
[Serializable]
public class PagerParams
{
/// <summary>
/// Page size
/// </summary>
private int _pageSize;
/// <summary>
/// Total record count
/// </summary>
private int _totalRecords;
/// <summary>
/// Page index
/// </summary>
private int _pageIndex;
/// <summary>
/// Gets the size of the page.
/// </summary>
/// <value>The size of the page.</value>
public int PageSize
{
get { return _pageSize; }
}
/// <summary>
/// Gets or sets the index of the page.
/// </summary>
/// <value>The index of the page.</value>
public int PageIndex
{
get { return _pageIndex; }
set
{
if (_pageSize == 0 && value > 0)
{
throw new ArgumentOutOfRangeException("error");
}
if (_totalRecords > 0)
{
if (value * _pageSize >= _totalRecords)
{
throw new ArgumentOutOfRangeException("error");
}
}
else if (_totalRecords == 0)
{
if (value > 0)
{
throw new ArgumentOutOfRangeException("error");
}
}
_pageIndex = value;
}
}
/// <summary>
/// Gets or sets the total records.
/// </summary>
/// <value>The total records.</value>
public int TotalRecords
{
get { return _totalRecords; }
set
{
if (_totalRecords != value)
{
_totalRecords = value;
if (_totalRecords > 0)
{
if (_pageIndex * _pageSize >= _totalRecords)
{
PageIndex = (_totalRecords - 1) / _pageSize;
}
}
else if (_totalRecords == 0)
{
PageIndex = 0;
}
}
}
}
/// <summary>
///
/// </summary>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="totalRecords"></param>
public PagerParams(int pageSize, int pageIndex, int totalRecords)
{
_pageSize = pageSize;
_totalRecords = totalRecords;
PageIndex = pageIndex;
}
/// <summary>
/// Gets the default pager parameters.
/// </summary>
/// <value>The default pager parameters.</value>
public static PagerParams Default
{
get
{
return new PagerParams(20, 0, -1);
}
}
/// <summary>
/// Gets the no paging parameters.
/// </summary>
/// <value>The no paging parameters.</value>
public static PagerParams NoPaging
{
get
{
return new PagerParams(int.MaxValue, 0, -1);
}
}
}
3. Implement the IPagedList
After that We defines IPagedList from the idea of Rob Conery's blog.
IPagedList interface
public interface IPagedList
{
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; }
}
PagedList class
public partial class PagedList : IPagedList
{
public PagedList(PagerParams pagerParams)
{
Initialize(pagerParams);
}
#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
protected void Initialize(PagerParams pagerParams)
{
//### argument checking
if (pagerParams.PageIndex < 0)
{
throw new ArgumentOutOfRangeException("PageIndex cannot be below 0.");
}
if (pagerParams.PageSize < 1)
{
throw new ArgumentOutOfRangeException("PageSize cannot be less than 1.");
}
//### set properties
TotalItemCount = pagerParams.TotalRecords;
PageSize = pagerParams.PageSize;
PageIndex = pagerParams.PageIndex;
if (TotalItemCount > 0)
{
PageCount = (int)Math.Ceiling(TotalItemCount / (double)PageSize);
}
else
{
PageCount = 0;
}
HasPreviousPage = (PageIndex > 0);
HasNextPage = (PageIndex < (PageCount - 1));
IsFirstPage = (PageIndex <= 0);
IsLastPage = (PageIndex >= (PageCount - 1));
}
}
3. Create HtmlHelpers methods
Now We create a Pager class to render pagination.
public class Pager
{
private ViewContext viewContext;
private readonly int pageSize;
private readonly int currentPage;
private readonly int totalItemCount;
private readonly RouteValueDictionary linkWithoutPageValuesDictionary;
public Pager(ViewContext viewContext, int pageSize, int currentPage, int totalItemCount, RouteValueDictionary valuesDictionary)
{
this.viewContext = viewContext;
this.pageSize = pageSize;
this.currentPage = currentPage;
this.totalItemCount = totalItemCount;
this.linkWithoutPageValuesDictionary = valuesDictionary;
}
public string RenderHtml()
{
int pageCount = (int)Math.Ceiling(this.totalItemCount / (double)this.pageSize);
int nrOfPagesToDisplay = 10;
var sb = new StringBuilder();
// Previous
if (this.currentPage > 1)
{
sb.Append(GeneratePageLink("<", this.currentPage - 1));
}
else
{
sb.Append("<span class=\"disabled\"><</span>");
}
int start = 1;
int end = pageCount;
if (pageCount > nrOfPagesToDisplay)
{
int middle = (int)Math.Ceiling(nrOfPagesToDisplay / 2d) - 1;
int below = (this.currentPage - middle);
int above = (this.currentPage + middle);
if (below < 4)
{
above = nrOfPagesToDisplay;
below = 1;
}
else if (above > (pageCount - 4))
{
above = pageCount;
below = (pageCount - nrOfPagesToDisplay);
}
start = below;
end = above;
}
if (start > 3)
{
sb.Append(GeneratePageLink("1", 1));
sb.Append(GeneratePageLink("2", 2));
sb.Append("...");
}
for (int i = start; i <= end; i++)
{
if (i == this.currentPage)
{
sb.AppendFormat("<span class=\"current\">{0}</span>", i);
}
else
{
sb.Append(GeneratePageLink(i.ToString(), i));
}
}
if (end < (pageCount - 3))
{
sb.Append("...");
sb.Append(GeneratePageLink((pageCount - 1).ToString(), pageCount - 1));
sb.Append(GeneratePageLink(pageCount.ToString(), pageCount));
}
// Next
if (this.currentPage < pageCount)
{
sb.Append(GeneratePageLink(">", (this.currentPage + 1)));
}
else
{
sb.Append("<span class=\"disabled\">></span>");
}
return sb.ToString();
}
private string GeneratePageLink(string linkText, int pageNumber)
{
var pageLinkValueDictionary = new RouteValueDictionary(this.linkWithoutPageValuesDictionary);
pageLinkValueDictionary.Add("page", pageNumber);
//var virtualPathData = this.viewContext.RouteData.Route.GetVirtualPath(this.viewContext, pageLinkValueDictionary);
var virtualPathData = RouteTable.Routes.GetVirtualPath(this.viewContext.RequestContext, pageLinkValueDictionary);
if (virtualPathData != null)
{
string linkFormat = "<a href=\"{0}\">{1}</a>";
return String.Format(linkFormat, virtualPathData.VirtualPath, linkText);
}
else
{
return null;
}
}
}
and a PagingExtentions class
public static class PagingExtensions
{
#region HtmlHelper extensions
public static string Pager(this HtmlHelper htmlHelper, int pageSize, int currentPage, int totalItemCount)
{
return Pager(htmlHelper, pageSize, currentPage, totalItemCount, null, null);
}
public static string Pager(this HtmlHelper htmlHelper, int pageSize, int currentPage, int totalItemCount, string actionName)
{
return Pager(htmlHelper, pageSize, currentPage, totalItemCount, actionName, null);
}
public static string Pager(this HtmlHelper htmlHelper, int pageSize, int currentPage, int totalItemCount, object values)
{
return Pager(htmlHelper, pageSize, currentPage, totalItemCount, null, new RouteValueDictionary(values));
}
public static string Pager(this HtmlHelper htmlHelper, int pageSize, int currentPage, int totalItemCount, string actionName, object values)
{
return Pager(htmlHelper, pageSize, currentPage, totalItemCount, actionName, new RouteValueDictionary(values));
}
public static string Pager(this HtmlHelper htmlHelper, int pageSize, int currentPage, int totalItemCount, RouteValueDictionary valuesDictionary)
{
return Pager(htmlHelper, pageSize, currentPage, totalItemCount, null, valuesDictionary);
}
public static string Pager(this HtmlHelper htmlHelper, int pageSize, int currentPage, int totalItemCount, string actionName, RouteValueDictionary valuesDictionary)
{
if (valuesDictionary == null)
{
valuesDictionary = new RouteValueDictionary();
}
if (actionName != null)
{
if (valuesDictionary.ContainsKey("action"))
{
throw new ArgumentException("The valuesDictionary already contains an action.", "actionName");
}
valuesDictionary.Add("action", actionName);
}
var pager = new Pager(htmlHelper.ViewContext, pageSize, currentPage, totalItemCount, valuesDictionary);
return pager.RenderHtml();
}
#endregion
#region
public static IPagedList ToPagedList(this PagerParams pagerParams)
{
return new PagedList(pagerParams);
}
#endregion
}
4. Create ProductViewModel ViewModel
This model will have IPagedList properties and collection of Product Model
public class ProductViewModel
{
public IPagedList PagedList { get; set; }
public List<ProductModel> Products { get; set; }
}
5. Create ProductServices to accessing to database using the GetProductsByFilter stored procedures.
In this procedure, we have to update TotalRecords for PagerParams
cmd.CommandText = "GetProductsByFilter"; //store procedure name
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter i_Page_Index = cmd.Parameters.Add("@i_Page_Index", SqlDbType.Int);
i_Page_Index.Direction = ParameterDirection.Input;
i_Page_Index.Value = param.PageIndex;
SqlParameter i_Page_Count = cmd.Parameters.Add("@i_Page_Count", SqlDbType.Int);
i_Page_Count.Direction = ParameterDirection.Input;
i_Page_Count.Value = param.PageSize;
cmd.Parameters.Add("@o_total_rows", SqlDbType.Int).Direction = ParameterDirection.Output;
//cmd.ExecuteNonQuery();
//int total = Convert.ToInt32(cmd.Parameters["@o_total_rows"].Value);
using (SqlDataReader reader = cmd.ExecuteReader())
{
//total = Convert.ToInt32(cmd.Parameters["@o_total_rows"].Value);
while (reader.Read())
{
int id = Int32.Parse(reader["ProductID"].ToString());
string name = reader["ProductName"].ToString();
ProductModel model = new ProductModel
{
Id = id,
ProductName = name
};
list.Add(model);
}
reader.NextResult();
param.TotalRecords = Convert.ToInt32(cmd.Parameters["@o_total_rows"].Value);
}
6. The controller will look like this
The pageIndex is start from 0
public ActionResult Index(int? page)
{
int currentPageIndex = page.HasValue ? page.Value - 1 : 0;
PagerParams param = new PagerParams(5, currentPageIndex, -1);
ProductViewModel model = new ProductViewModel
{
Products = ProductServices.ListProducts(param)
};
model.PagedList = param.ToPagedList();
return View(model);
}
7. Create View and implement paging using HtmlHelpers. The view like this
<table class="gridview" width="100%">
<tr>
<th>
ID
</th>
<th>
NAME
</th>
</tr>
<% foreach (var item in Model.Products) { %>
<tr>
<td style="width:50px">
<%= Html.Encode(item.Id) %>
</td>
<td>
<%= Html.Encode(item.ProductName) %>
</td>
</tr>
<% } %>
</table>
<div class="pager">
<%= Html.Pager(ViewData.Model.PagedList.PageSize, ViewData.Model.PagedList.PageNumber, ViewData.Model.PagedList.TotalItemCount) %>
</div>
8. Now is the results
