你的位置:首页 > ASP.net教程

[ASP.net教程]32、异步分页


既然做分页,首先要写好sql语句。

1、not in  --SQL2000

select top 10 * from huochetou where id not in (select top 10 id from huochetou)

2、row_number() --SQL2005

select * from ( select (row_number() over(order by id desc)) as num,* from huochetou) as t  where t.num between 1 and 10

3、offset/fetch next --SQL2012

select * from huochetou order by id asc offset 10 rows fetch next 10 rows only

可以封装成存储过程调用,传入参数即可调用,好处就是只需要编译一次sql语句。

编写实体类

 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5  6 namespace Model 7 { 8   /// <summary> 9   /// 采集实体10   /// </summary>11   public class Collection12   {13     public int num { get; set; }14     /// <summary>15     /// 标识16     /// </summary>17     public int id { get; set; }18     /// <summary>19     /// 标题20     /// </summary>21     public string title { get; set; }22     /// <summary>23     /// 内容24     /// </summary>25     public string content { get; set; }26     /// <summary>27     /// 创建日期28     /// </summary>29     public DateTime datetime { get; set; }30     /// <summary>31     /// 来源32     /// </summary>33     public string source { get; set; }34     /// <summary>35     /// 审核36     /// </summary>37     public int audit { get; set; }38     /// <summary>39     /// 分类40     /// </summary>41     public int typeId { get; set; }42   }43 }

Collection.cs

编写查询方法

 1 /// <summary> 2 /// 分页查询 3 /// </summary> 4 /// <param name="where">where条件</param> 5 /// <param name="pageSize">每页多少条</param> 6 /// <param name="pageIndex">第几页</param> 7 /// <param name="totalCount">总条数</param> 8 /// <param name="pageCount">页数</param> 9 /// <returns></returns>10 public static List<Collection> list(string where, int pageSize, int pageIndex, out int totalCount, out int pageCount)11 {12   totalCount = 0;13   pageCount = 0;14   string newWhere = "";15 16   if (where != "")17   {18     newWhere = " where ";19   }20   string sqlCount = "select count(*) from huochetou " + newWhere + where;21 22   if (SQLHelper.ExecuteScalar(sqlCount) != null)23   {24     totalCount = Convert.ToInt32(SQLHelper.ExecuteScalar(sqlCount));25     pageCount = Convert.ToInt32(Math.Ceiling(totalCount * 1.0 / pageSize));26   }27 28   string sql = "select * from (select (row_number() over(order by id desc)) as num,* from huochetou " + newWhere + where + ") as t where t.num between @begin and @end ";29   int begin = ((pageIndex - 1) * pageSize) + 1;30   int end = pageSize * pageIndex;31   SqlParameter[] sqls = new SqlParameter[]{32     new SqlParameter("@begin",begin),33     new SqlParameter("@end",end)34   };35   List<Collection> list = new List<Collection>();36   using (var dr = SQLHelper.ExecuteReader(sql, sqls))37   {38     while (dr.Read())39     {40       Collection v = new Collection();41       v.num = Convert.ToInt32(dr["num"]);42       v.id = Convert.ToInt32(dr["id"]);43       v.title = dr["title"].ToString();44       v.content = dr["content"].ToString();45       v.datetime = Convert.ToDateTime(dr["datetime"]);46       v.source = dr["source"].ToString();47       v.audit = Convert.ToInt32(dr["audit"]);48       v.typeId = Convert.ToInt32(dr["typeId"]);49       list.Add(v);50     }51   }52   return list;53 }

分页方法

可以指定条件和第几页每页显示几条,将查出总条数的结果返回,并根据每页显示几条算出共几页返回。

生成分页标签

 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5  6 namespace CL 7 { 8   public class createPager 9   { 10  11     public static string pageNav(int pageSize, int pageIndex, int totalCount, int pageCount) 12     { 13       StringBuilder sb = new StringBuilder(); 14       if (pageCount > 0) 15       { 16         int current = pageIndex; 17         sb.Append("<div class='pager'>"); 18         sb.Append("<div class='pleft'>共有:" + totalCount + "条记录,当前第:" + current + "/" + pageCount + "页,每页显示" + pageSize + "条</div>"); 19         sb.Append("<div class='pright'>"); 20         if (current != 1) 21         { 22           sb.Append("<a data='" + (current - 1) + "' href='javascript:void();'>上一页</a>"); 23         } 24         for (int i = 1; i <= pageCount; i++) 25         { 26           if (i == current) 27           { 28             sb.Append("<span class='pager_curr'>" + i + "</span>"); //当前页 29           } 30           else 31           { 32             if (i < current + 3 && i > current - 3) 33             { 34               sb.Append("<a data='" + i + "' href='javascript:void();'>" + i + "</a>"); 35             } 36             else 37             { 38               if (current - 3 <= 1) 39               { 40                 if (i == 1) //首页234 41                 { 42                   sb.Append("<a data='" + i + "' href='javascript:void();'>" + i + "</a>"); 43                 } 44                 if (i == pageCount) //123...尾页 45                 { 46                   sb.Append("<span>...</span>"); 47                   sb.Append("<a data='" + i + "' href='javascript:void();'>" + i + "</a>"); 48                 } 49               } 50               else 51               { 52                 if (current + 3 >= pageCount) 53                 { 54                   if (i == 1) //1..11 12 13首页 55                   { 56                     sb.Append("<a data='" + i + "' href='javascript:void();'>" + i + "</a>"); 57                     sb.Append("<span>...</span>"); 58                   } 59                   if (i == pageCount) //1..11 12 13尾页 60                   { 61                     sb.Append("<span>...</span>"); 62                     sb.Append("<a data='" + i + "' href='javascript:void();'>" + i + "</a>"); 63                   } 64                 } 65                 else 66                 { 67                   if (i == 1) //1..34567..首页 68                   { 69                     sb.Append("<a data='" + i + "' href='javascript:void();'>" + i + "</a>"); 70                     sb.Append("<span>...</span>"); 71                   } 72                   if (i == pageCount) //1..34567..尾页 73                   { 74                     sb.Append("<span>...</span>"); 75                     sb.Append("<a data='" + i + "' href='javascript:void();'>" + i + "</a>"); 76                   } 77                 } 78               } 79             } 80           } 81         } 82         if (current != pageCount) 83         { 84           sb.Append("<a data='" + (current + 1) + "' href='javascript:void();'>下一页</a>"); 85         } 86         sb.Append("<span class='pager_total'>"); 87         sb.Append(" 到第 <input id='pageJump' class='pager_skip' value='" + pageIndex + "' onkeyup=\"this.value=this.value.replace(/\\D/, '');\"> 页 "); 88         sb.Append("<button type='button' onclick=\"pager(" + pageSize + ",document.getElementById('pageJump').value)\">跳转</button>"); 89         sb.Append("</span>"); 90         sb.Append("</div>"); 91         sb.Append("</div>"); 92       } 93       else 94       { 95         sb.Append(""); 96       } 97       return sb.ToString(); 98     } 99   }100 }

createPager.cs

html请求的handler

 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text.RegularExpressions; 5 using System.Web; 6 using System.Web.Script.Serialization; 7  8 namespace UI.manage.Net.Collection 9 {10   /// <summary>11   /// pager 的摘要说明12   /// </summary>13   public class pager : IHttpHandler14   {15 16     public void ProcessRequest(HttpContext context)17     {18       int totalCount;19       int pageCount;20       int pageSize = context.Request["pageSize"] == null ? 10 : Convert.ToInt32(context.Request["pageSize"]);21       int pageIndex = context.Request["pageIndex"] == null ? 10 : Convert.ToInt32(context.Request["pageIndex"]);22       pageIndex = pageIndex < 1 ? 1 : pageIndex;23       string where = context.Request["where"] == null ? "" : context.Request["where"];24 25       var list = BLL.CollectionBll.list(where, pageSize, pageIndex, out totalCount, out pageCount);26       27       var page = CL.createPager.pageNav(pageSize, pageIndex, totalCount, pageCount);28       var sendData = new { DataList = list, pageNav = page };29 30       JavaScriptSerializer js = new JavaScriptSerializer();31       var json = js.Serialize(sendData);32       json = Regex.Replace(json, @"\\/Date\((\d+)\)\\/", match => {33         DateTime dt = new DateTime(1970, 1, 1);34         dt = dt.AddMilliseconds(long.Parse(match.Groups[1].Value));35         dt = dt.ToLocalTime();36         return dt.ToString("yyyy-MM-dd hh:mm:ss");37       });38       context.Response.Write(json);39     }40 41     public bool IsReusable42     {43       get44       {45         return false;46       }47     }48   }49 }

pager.ashx

html页

 1 <div class="widget-content "> 2   <div class="fl"> 3     <input id="title" type="text" class="span2" placeholder="按标题检索" /> 4     <input id="source" type="text" class="span2" placeholder="按来源检索" /> 5     <input id="ctTime" type="text" class="span2" placeholder="按日期检索" /> 6     <select id="audit" class="span2"> 7       <option value="0" selected="selected">未审核</option> 8       <option value="1">已通过</option> 9       <option value="2">未通过</option>10     </select>11     <button id="search" class="btn btn-success">搜索</button>12   </div>13   <div class="fr">14     <div class="btn-group">15       <button data-toggle="dropdown" class="btn btn-info dropdown-toggle">审核 <span class="caret"></span></button>16       <ul class="dropdown-menu">17         <li><a href="javascript:audit_all(1)">审核通过</a></li>18         <li><a href="javascript:audit_all(2)">审核不通过</a></li>19         <li><a href="javascript:audit_all(3)">未审核</a></li>20       </ul>21     </div>22     <button class="btn btn-danger" onclick="delete_all()">删除</button>23     <button class="btn btn-warning">导出到Excel</button>24     每页显示25     <select id="pageSize" class="span1">26       <option value="10">10</option>27       <option value="30">30</option>28       <option value="50">50</option>29       <option value="100">100</option>30     </select>31     条32   </div>33   <table class="table table-bordered table-striped with-check ">34     <thead>35       <tr>36         <th>37           <input type="checkbox" id="selectAll" />38         </th>39         <th width="20">序号</th>40         <th>标题</th>41         <th width="120">来源</th>42         <th width="120">日期</th>43         <th width="20">审核</th>44         <th width="20">发布</th>45         <th width="20">删除</th>46       </tr>47     </thead>48     <tbody id="bg"></tbody>49   </table>50   <div id="fy"></div>51 </div>

html

一开始绑定一次,在绑定方法中指定页面中条件,用作回调。

 1     $(function () { 2       bind(); 3     }); 4     function bind(curr) { 5       var pageSize = $("#pageSize").val(); 6       var pageIndex = 1; 7       if (curr != null) { 8         pageIndex = curr; 9       }10       var where = "";11       var pid = getQueryString("pid");12       if (pid != "") {13         where += " typeId = " + pid;14       }15       var txtTitle = $("#title").val();16       if (txtTitle != "") {17         where += " and title like '%" + txtTitle + "%'";18       }19       var txtSource = $("#source").val();20       if (txtSource != "") {21         where += " and source like '%" + txtSource + "%'";22       }23       var txtctTime = $("#ctTime").val();24       if (txtctTime != "") {25         where += " and datetime between '" + txtctTime + " 00:00:00' and '" + txtctTime + " 23:59:59'";26       }27       var txtAudit = $("#audit").val();28       if (txtAudit != "") {29         where += " and audit = " + txtAudit;30       }31       pager(pageSize, pageIndex, where);32 33       $("#selectAll").attr("checked", false);34     }

bind()

分页脚本

 1 function pager(pageSize, pageIndex, where) { 2       $.getJSON("pager.ashx", { pageSize: pageSize, pageIndex: pageIndex, where: where, random: Math.random() }, function (jsonData) { 3         if (jsonData != null) { 4           var data = jsonData.DataList; 5           if (data.length != 0) { 6             $("#bg").html(""); 7             var trs = ""; 8             for (var i = 0; i < data.length; i++) { 9               var tr = "<tr><td><input type='checkbox' name='item' value='" + data[i].id + "' /></td><td>" + data[i].num + "</td><td><a href='push.html?id=" + data[i].id + "'>" + data[i].title + "</a></td><td>" + data[i].source + "</td><td>" + data[i].datetime + "</td><td><a href='javascript:onclick='audit_one('" + data[i].id + "')''>"10               if (data[i].audit == 0) {11                 tr += "<i class='icon-warning-sign' ></i>";12               } else if (data[i].audit == 1) {13                 tr += "<i class='icon-ok' ></i>";14               } else {15                 tr += "<i class='icon-remove' ></i>";16               }17               tr += "</a></td><td><a href='push.html?id=" + data[i].id + "'><i class='icon-rss'></i></a></td><td><a href='javascript:delete_one(" + data[i].id + ")'><i class='icon-trash'></i></a></td></tr>";18               trs += tr;19             }20             $("#bg").html(trs);21 22             var pageNav = jsonData.pageNav;23             $("#fy").html(pageNav);24             $("#fy a").click(function () {25               var page = $(this).attr("data");26               bind(page);27               return false;28             });29           } else {30             $("#fy").html("");31             $("#bg").html("<tr><td colspan=7 style='text-align:center;margin-top:20px;color:red;'>没有找到相关数据</td></tr>")32           }33         }34       });35     }

pager()

这里要注意使用get方法时的随机数或时间戳,否则部分浏览器会缓存,如果参数一样将直接从缓存中调取,造成假数据。

搜索和选择分几页时调用回调函数。

1     $("#search").click(function () {2       bind();3     });4     $("#pageSize").change(function () {5       bind();6     });

搜索,分页回调

html中获取页面参数

1     function getQueryString(name) {2       var reg = new RegExp("(^|&)" + name + "=([^&]*)(&|$)", "i");3       var r = location.search.substr(1).match(reg);4       if (r != null)5         return unescape(r[2]);6     }

getQueryString()

全选和删除

 1     $("#selectAll").click(function () { 2       var item = $("input[name='item']").attr("checked"); 3       if (item) { 4         $("input[name='item']").attr("checked", false); 5       } else { 6         $("input[name='item']").attr("checked", true); 7       } 8     }); 9     //单个删除10     function delete_one(id) {11       layer.confirm('您确定要删除吗?', {12         btn: ['确定', '取消']13       }, function () {14         $.post("operation.ashx?action=delete", { ids: id + "," }, function (data) {15           if (data == "ok") {16             layer.msg("删除成功!");17             bind();18           } else {19             layer.msg("删除失败!");20           }21         });22       }, function () {23       });24     }25     //全选删除26     function delete_all(ids) {27       var nums = "";28       $("input[name='item']:checked").each(function (i) {29         nums += $(this).val() + ",";30       });31       if (nums != "") {32         layer.confirm("您确定要删除吗?", {33           btn: ['确定', '取消']34         }, function () {35           $.post("operation.ashx?action=delete", { ids: nums }, function (data) {36             if (data == "ok") {37               layer.msg("删除成功!");38               bind();39             }40           });41         }, function () {42         });43       } else {44         layer.msg("请至少选择一项!");45       }46     }

selectAll(),deleteAll()

审核类似不帖了。

实现异步等待提醒

1     $(document).ajaxStart(function () {2       layer.load(2, {3         shade: [0.5, '#fff']4       });5     }).ajaxStop(function () {6       layer.closeAll('loading');7         $(window.parent.document).find("#mainIframe").height(document.body.scrollHeight);8     });

ajaxStart(),ajaxStop()

日期插件->datetimepicker国人化设置

 1     $('#ctTime').datetimepicker({ 2       format: "yyyy-mm-dd", 3       weekStart: 1, 4       autoclose: true, 5       minView: 'month', 6       todayBtn: true, 7       todayHighlight: true, 8       language: 'zh-CN', 9       forceParse: true,10       pickTime: true11     });

datetimepicker

效果图:

用了一个layer插件 提示层删除时的询问,用到的js:

  <script src="../../js/jquery-1.7.2.min.js"></script>  <script src="../../js/bootstrap.min.js"></script>  <script src="../../js/layer/layer.js"></script>  <script src="../../js/bootstrap-datetimepicker.min.js"></script>  <script src="../../js/bootstrap-datetimepicker.zh-CN.js"></script>