首先准备好分页的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
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>
