分页：T-SQL存储过程和EF存储过程的使用

浏览数：25 / 时间：2015年06月12日

首先准备好分页的T-SQL语句：

 1 create proc usp_activityFenYe
 2 @pageIndex int,
 3 @pageSize int,
 4 @pageCount int output
 5 as
 6 declare @count int
 7 begin
 8 select @count=COUNT(*) from Activity
 9 set @pageCount=CEILING(@count*1.0/@pageSize)
10 select * from(select *,ROW_NUMBER()over(order by activityid) as num from Activity) as t
11 where num between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize
12 order by ActivityID asc
13 end

View Code

ADO.NET操作：

(1)DAL：

 1 public class ActivityDAL
 2     {
 3         public List<Activity> GetList(int pageIndex, int pageSize, out int pageCount)
 4         {
 5             SqlParameter[] pms = new SqlParameter[] { 
 6            new SqlParameter("@pageIndex",pageIndex),
 7            new SqlParameter("@pageSize",pageSize),
 8            new SqlParameter("@pageCount",0)
 9            };
10             pms[2].Direction = ParameterDirection.Output;
11             List<Activity> list = new List<Activity>();
12             DataTable dt = DBHelper.ExecuteTableSP("usp_activityFenYe", pms);
13             if (dt.Rows.Count > 0)
14             {
15                 Activity model = new Activity();
16                 foreach (DataRow row in dt.Rows)
17                 {
18                     model = LoadEntity(row);
19                     list.Add(model);
20                 }
21             }
22             pageCount = Convert.ToInt32(pms[2].Value);
23             return list;
24         }
25 
26         private Activity LoadEntity(DataRow row)
27         {
28             Activity model = new Activity();
29             model.ActivityID = Convert.ToInt32(row["ActivityID"]);
30             model.Name = row["Name"].ToString();
31             return model;
32         }
33     }

(2)BLL：

 1 public class ActivityBLL
 2     {
 3         ActivityDAL dal;
 4         public ActivityBLL()
 5         {
 6             dal = new ActivityDAL();
 7         }
 8         public List<Activity> GetList(int pageIndex, int pageSize, out int pageCount)
 9         {
10             return dal.GetList(pageIndex, pageSize, out pageCount);
11         }
12     }

(3)View：

 1 public class ActivityController : Controller
 2     {
 3         ActivityBLL bll;
 4         DBEntities db;//数据上下文
 5         public ActivityController()
 6         {
 7             bll = new ActivityBLL();
 8             db = new DBEntities();
 9         }
10         public ActionResult Index(int? pageIndex)
11         {
12             //1.ADO.NET
13             int pageSize = 5;
14             int pageCount;
15             List<Model.Activity> list = bll.GetList(pageIndex ?? 1, pageSize, out pageCount);
16             if (Request.IsAjaxRequest())
17             {
18                 return Json(list, JsonRequestBehavior.AllowGet);
19             }
20             ViewBag.pageCount = pageCount;//总页数
21 
22             //2.EF存储过程
23             ObjectParameter obj = new ObjectParameter("pageCount", typeof(int));//注意：前面不要写@
24             ObjectResult<usp_activityFenYe_Result> result = db.usp_activityFenYe(pageIndex ?? 1, pageSize, obj);//使用之前记得导入存储过程
25             //ViewBag.pageCount = obj.Value;//EF传出的总页数
26 
27             //3.另外一种基于EF的方法
28             SqlParameter pCount = new SqlParameter("pageCount", -1);//注意：前面不要写@
29             pCount.Direction = System.Data.ParameterDirection.Output;
30             SqlParameter pIndex = new SqlParameter("@pageIndex", 1);
31             SqlParameter pSize = new SqlParameter("pageSize", 5);
32             List<Models.Activity> data = db.Database.SqlQuery<Models.Activity>("exec usp_activityFenYe @pageIndex,@pageSize,@pageCount output", new object[] { pCount,pIndex,pSize}).ToList();
33             return View(list);
34         }
35     }

(4)Razor：

 1 @{
 2     Layout = null;
 3 }
 4 @model IEnumerable<Ado.Net.Model.Activity>
 5 <!DOCTYPE html>
 6 <html>
 7 <head>
 8     <meta name="viewport" content="width=device-width" />
 9     <title>Index</title>
10     <link href="~/Content/bootstrap.min.css" rel="stylesheet" />
11     <script src="~/Scripts/jquery-1.8.2.min.js"></script>
12     <script>
13         function show(index) {
14             $.getJSON("@Url.Action("Index")", { pageIndex: index }).success(function (data) {
15                 var text = "";
16                 for (var i = 0; i < data.length; i++) {
17                     text += "<tr><td>" + data[i].ActivityID + "</td><td>" + data[i].Name + "</td><td><a href=‘javascript:;‘ onclick=‘del(" + data[i].ActivityID + ")‘>删除</a></td><td><a href=‘javascript:window.location.href=@Url.Action("Modify")?id=" + data[i].ActivityID + ")‘>修改</a></td></tr>";
18                 }
19                 $("#table").html(text);
20                 $(".pagination li").removeClass("active").eq(index - 1).addClass("active");
21             })
22         };
23         function del(id) {
24             if (confirm("确定删除")) {
25                 $.post("@Url.Action("Del")", { id: id }).success(function (data) {
26                     if (data.status == 200)
27                         show(1);
28                 })
29             }
30         };
31     </script>
32 </head>
33 <body>
34     <div class="container">
35         <table class="table table-striped table-bordered">
36             <thead>
37                 <tr>
38                     <th>活动ID</th>
39                     <th>名称</th>
40                     <th>操作</th>
41                 </tr>
42             </thead>
43             <tbody id="table">
44                 @foreach (var i in Model)
45                 {
46                     <tr>
47                         <td>@i.ActivityID</td>
48                         <td>@i.Name</td>
49                         <td><a href="javascript:;" onclick="del(@i.ActivityID)">删除</a></td>
50                         <td><a href="@Url.Action("Modify", new { id = i.ActivityID })">修改</a></td>
51                     </tr>
52                 }
53             </tbody>
54         </table>
55         <nav>
56             <ul class="pagination pagination-lg">
57                 @for (int i = 1; i <= ViewBag.pageCount; i++)
58                 {
59                     <li @(i == 1 ? "class=active" : "")><a href="javascript:;" onclick="show(@i)">@i</a></li>
60                 }
61             </ul>
62         </nav>
63     </div>
64 </body>
65 </html>