MvcPager 分页示例 — 使用存储过程

View:

@model PagedList<article>
@Html.Partial("_ArticleTable")
@Html.Pager(Model,new PagerOptions{PageIndexParameterName = "id",PagerItemTemplate = "{0}&nbsp;"})
@section Scripts{@{Html.RegisterMvcPagerScriptResource();}}

_ArticleTable.cshtml:

@model PagedList<Article>
<table class="table table-bordered table-striped">
    <tr>
        <th class="nowrap">序号</th>
        <th>
            @Html.DisplayNameFor(model => model.Title)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.PubDate)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Author)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Source)
        </th>
    </tr>
    @{ int i = 0;}
    @foreach (var item in Model)
    {
        <tr>
            <td>@(Model.StartItemIndex + i++)</td>
            <td>
                @Html.DisplayFor(modelItem => item.Title)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.PubDate)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Author)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Source)
            </td>
        </tr>
    }
</table>

Model:

    public class Article
    {
        [Display(Name="文章编号")]
        public int ID { get; set; }
        [Display(Name="文章标题")]
        [MaxLength(200)]
        public string Title { get; set; }
        [Display(Name = "文章内容")]
        public string Content { get; set; }
        [Display(Name = "发布日期")]
        public DateTime PubDate { get; set; }
        [Display(Name = "作者")]
        [MaxLength(20)]
        public string Author { get; set; }
        [Display(Name = "文章来源")]
        [MaxLength(20)]
        public string Source { get; set; }
    }

Stored Procedure:

CREATE PROCEDURE [dbo].[USP_GetPagedArticleList]
@startIndex int,
@endIndex int,
@totalItems int output
AS
WITH artList AS (
SELECT ROW_NUMBER() OVER (ORDER BY A.PubDate DESC)AS Row, A.* from Articles A)
SELECT [ID],[Title],[PubDate],[Author],[Source],'' as [Content]
     FROM artList WHERE Row between @startIndex and @endIndex
    select @totalItems= count(ID) from Articles

Controller:

        public ActionResult StoredProcedure(int id = 1)
        {
            using (var db = new DataContext())
            {
                var pageSize = 5;
                var startIndex = (id - 1)*pageSize + 1;
                SqlParameter prmStart = new SqlParameter("@startIndex", startIndex);
                SqlParameter prmEnd = new SqlParameter("@endIndex", startIndex + pageSize);
                SqlParameter prmTotal = new SqlParameter("@totalItems", SqlDbType.Int);
                prmTotal.Direction = ParameterDirection.Output;
                var articles =
                    db.Database.SqlQuery<article>(
                        "exec [USP_GetPagedArticleList] @startIndex,@endIndex,@totalItems out", prmStart, prmEnd,
                        prmTotal).ToList();
                var totalItems = (int) prmTotal.Value;
                var model = new PagedList<article>(articles, id, pageSize, totalItems);
                return View(model);
            }
        }