basquang™ on clouds

March 18, 2011

Stored Procedures Paging Solution in ASP.NET MVC 2

Filed under: MVC — basquang @ 2:39 PM

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("&lt;", this.currentPage - 1));
            }
            else
            {
                sb.Append("<span class=\"disabled\">&lt;</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("&gt;", (this.currentPage + 1)));
            }
            else
            {
                sb.Append("<span class=\"disabled\">&gt;</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="\&quot;{0}\&quot;">{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

image

Advertisements

9 Comments »

  1. hi
    ur posts is really good. but i do not get it what is productservise? is it class or function? what is ListProducts? can u explain me in detail this flow? so i can implement in my project. i m waiting for ur reply.

    Comment by yesha — May 23, 2011 @ 3:45 PM | Reply

  2. Hi this looks pretty good. Does this support multiple pagination per page? Like can I use it more than 3 times?

    Comment by Benjamin K. Willard — July 21, 2011 @ 5:27 AM | Reply

  3. Yesha. ProductService is a class to access database. ListProduct is a method to retrieve list of Products from database. U can use any kind of database accessing for this method. For example ADO.NET, ODP.NET,….

    Comment by basquang — December 13, 2011 @ 11:41 AM | Reply

  4. Benjamin, what do you mean multiple pagination per page?

    Comment by basquang — December 13, 2011 @ 11:42 AM | Reply

  5. Excellent article.. Will this work in MVC 3 as well? or would changes have to be made?
    Also wondering how column sorting could be done with this paging?

    FYI: I’m just learning MVC .. specifically MVC 3

    Comment by Frankie — December 20, 2011 @ 4:43 AM | Reply

  6. This doesn’t work if you have exactly 12 pages. You only get links for pages 1 through 10, and no “…” is displayed. When you click on 1, 2, 3, 4, 5, 6, or 7 you get that current page highlighted but you still only see links for 1 through 10. When you click 8, 9, or 10, you then get links for 11 and 12, but the link for 1 disappears. And you never get the “…”.

    Comment by kicknwing — January 12, 2012 @ 9:50 PM | Reply

  7. I am not able to call …please help

    Comment by Tina — May 30, 2012 @ 2:05 PM | Reply

  8. Not able to call html.pager …please help

    Comment by Tina — May 30, 2012 @ 2:56 PM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: