杨涛主页
Fork me on GitHub

 在线演示

 帮助文档

 源 代 码

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

本示例演示如何使用SQL Server存储过程获取数据配合PagedList来实现分页。

该示例演示使用传统的ADO.NET数据访问技术调用sql server存储过程，使用DataAdapter填充DataTable，并调用PagedList构造函数生成PagedList<DataRow>的分页数据对象从面实现分页。

序号 文章标题 作者 文章来源
16 延安市吴起县境内古城投资三亿欲变陕北名镇 杨涛 吴起政府网
17 延安市居住证首发仪式在吴起举行 Webdiyer 吴起热线
18 黄土高原上的绿色丰碑——吴起县生态环境建设巡礼 杨涛 吴起热线
19 吴起的追梦之旅——吴起县争创“全国百强县”纪实 杨涛 吴起热线
20 延安铁边城入选第七批全国重点文保单位 杨涛 吴起热线
首页 上页 1 2 3 4 5 6 7 8 9 10 ... 下页 尾页 

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);
            }
        }