小菜鸟的温暖小窝
0%

分页查询实现步骤

发表于 更新于
本文字数： 3k 阅读时长 ≈ 3 分钟

《分页查询实现步骤》

第一步：Common层

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
public class PageBarHelper
   {
       public  static string GetPagaBar(int pageIndex,int pageCount)
       {
           if (pageCount==1)
           {
               return string.Empty;
           }
           int start = pageIndex - 5;//计算起始的位置，要求页面上显示10个数字页码
           if (start < 1)
           {
               start = 1;
           }
           int end = start + 9;//计算终止位置
           if (end>pageCount)
           {
               end = pageCount;
               //重新计算一下Start值
               start = end - 9 < 1 ? 1 : end - 9;

           }
           StringBuilder sb = new StringBuilder();
           if (pageIndex>1)
           {
               sb.AppendFormat("<a href='?pageIndex={0}' class='myPageBar'>上一页</a>", pageIndex - 1);

           }
           for (int i = start; i <= end ; i++)
           {
               if (i==pageIndex)
               {
                   sb.Append(i);
               }
               else
               {
                   sb.AppendFormat("<a href='?pageIndex={0}' class='myPageBar'>{0}</a>", i);
               }
           }
           if (pageIndex<pageCount)
           {
               sb.AppendFormat("<a href='?pageIndex={0}' class='myPageBar'>下一页</a>", pageIndex + 1);
           }
           return sb.ToString();
       }
   }

第二步：DAL层

获取总页数

1
2
3
4
5
public int GetRecordCount()
        {
            string sql = "select count(*) from Books";
            return Convert.ToInt32(DbHelperSQL.GetSingle(sql));
        }

获取指定范围的分页数据

1
2
3
4
5
6
7
8
9
10
11
12
public DataSet GetPageList(int start,int end)
       {
           string sql = "select * from(select *,row_number() over(order by id) as num from Books)as  t where t.num>=@start and t.num<@end";
           SqlParameter[] parameters =
           {
               new SqlParameter("@start",SqlDbType.Int,4),
               new SqlParameter("@end",SqlDbType.Int,4)
           };
           parameters[0].Value = start;
           parameters[1].Value = end;
           return DbHelperSQL.Query(sql, parameters);//此方法是执行sql语句
       }

第三步：BLL层

获取总页数

1
2
3
4
5
6
public int GetPageCount(int pageSize)
     {
         int recordCount = dal.GetRecordCount();
         int pageCount = Convert.ToInt32(Math.Ceiling((double)recordCount / pageSize));
         return pageCount;
     }

获取指定范围的分页数据

1
2
3
4
5
6
7
public List<Book> GetPageList(int pageIndex,int pageSize)
      {
          int start = (pageIndex - 1) * pageSize + 1;
          int end = pageIndex * pageSize;
          DataSet ds = dal.GetPageList(start, end);
          return DataTableToList(ds.Tables[0]);//返回设置指定范围的的分页数据的列表
      }

第四步：Web层

aspx前台

1
2
3
<div class="page_nav" style="margin-top: 50px;">
        <%=Common.PageBarHelper.GetPagaBar(PageIndex,PageCount)%>
    </div>

aspx后台

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
public partial class BookList : System.Web.UI.Page
   {
       public int PageIndex { get; set; }
       public int PageCount { get; set; }
       protected void Page_Load(object sender, EventArgs e)
       {

           if (!IsPostBack)
           {
               BindBookList();
           }
       }

       protected void BindBookList()
       {
           int pageIndex;
           if (!int.TryParse(Request["pageIndex"], out pageIndex))
           {
               pageIndex = 1;
           }
           int pageSize = 11;
           BLL.BookManager bookManager = new BLL.BookManager();
           int pageCount = bookManager.GetPageCount(pageSize);
           PageCount = pageCount;
           pageIndex = pageIndex < 1 ? 1 : pageIndex;
           pageIndex = pageIndex > pageCount ? pageCount : pageIndex;
           PageIndex = pageIndex;
           this.BookListRepeater.DataSource = bookManager.GetPageList(pageIndex, pageSize);
           this.BookListRepeater.DataBind();
       }