前端分页、及分页原理

分页原理：越过多少条。取多少条

 1         /// <summary>
 2         /// 分页嵌套查询
 3         /// </summary>
 4         /// <param name="strSql">查询SQL语句</param>
 5         /// <param name="orderBy">降序字段eg:order by id/order by id desc</param>
 6         /// <param name="start">开始</param>
 7         /// <param name="limit">一页多少条</param>
 8         /// <param name="total">总条数</param>
 9         /// <returns>返回DataTable</returns>
10         public static DataTable QueryDT(string strSql,string orderBy,int start,int limit,ref int total)
11         {
12             using (IDbConnection conn = defaultPro.GetConnection())
13             {
14                 if (conn.State != ConnectionState.Open)
15                 {
16                     conn.Open();
17                 }
18                 try
19                 {
20                     string query_sql = string.Format(@"SELECT CSON4.* FROM ( SELECT CSON3.* FROM (
21             Select CSON2.*, ROW_NUMBER() OVER({1}) as rownum from (
22                  Select Count(*) over() total_count,CSON.* From ({0}) CSON
23             ) CSON2  
24 )  CSON3 where CSON3.rownum<{3}) CSON4 WHERE CSON4.rownum>{2}", strSql, orderBy, (start - 1) * limit, start * limit + 1);
25                     PrintErrorStrSql(strSql);
26                     IDbDataAdapter adap = defaultPro.GetDataAdapter(strSql, conn);
27                     DataTable dt = new DataTable();
28                     DataSet ds = new DataSet();
29                     adap.Fill(ds);
30                     dt = ds.Tables[0];
31                     if (dt.Rows.Count>0)
32                     {
33                         total =Convert.ToInt32(dt.Rows[0]["total_count"]);
34                     }                    
35                     return dt;
36                 }
37                 catch (DbException ex)
38                 {
39                     throw new Exception(ex.Message);
40                 }
41                 finally
42                 {
43                     conn.Close();
44                 }
45             }
46         }

View Code

1 SELECT TOP (30) TMP.* FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY ID) rn FROM UserInfo) TMP WHERE rn>30  ORDER BY ID DESC
2 
3 
4 SELECT TOP (前多少行) TMP.* FROM (SELECT *,ROW_NUMBER() OVER(ORDER BY 排序字段) rn FROM 表名) TMP WHERE rn>越过多少行   ORDER BY 排序字段 DESC

微软标准分页查询

1     SELECT TOP(5)* FROM Main WHERE id not in
2     (
3         SELECT TOP(5*2) id FROM Main ORDER BY id
4     )
5     ORDER BY ID

View Code

 1         /// <summary>
 2         /// 分页
 3         /// </summary>
 4         /// <param name="pageSize">一页多少条</param>
 5         /// <param name="currentPageIndex">当前页的索引</param>
 6         /// <param name="totalCount">总条数</param>
 7         /// <returns></returns>
 8         public static string ShowPageNavigate(int pageSize,int currentPageIndex,int totalCount)
 9         {
10             pageSize = pageSize == 0 ? 3 : pageSize;
11             var totalPages = Math.Max((totalCount + pageSize - 1) / pageSize, 1); //总页数
12             var output = new StringBuilder();
13             if (totalPages>0)
14             {
15                 if (currentPageIndex != 1)
16                 { //处理首页链接
17                     output.AppendFormat("<a class='pageLink' href='?pageIndex=1&pageSize={0}'>首页</a>",pageSize);
18                 }
19                 if (currentPageIndex > 1)
20                 {//处理上一页的链接
21                     output.AppendFormat("<a class='pageLink' href='?pageIndex={0}&pageSize={1}'>上一页</a>", currentPageIndex - 1,pageSize);
22                 }
23                 output.Append(" ");
24                 int currint = 5;
25                 for (int i = 0; i <=10; i++)
26                 {//一共最多显示10个页面，前面5个，后面5个
27                     if (currentPageIndex + i - currint > totalPages) //处理总页数少于10页
28                     {
29                         break;
30                     }
31                     if ((currentPageIndex + i-currint)>=1&&(currentPageIndex + i-currint)<=totalCount)
32                     {
33                         if (currint == i)
34                         { //当前页处理
35                             output.AppendFormat("<a class='current' href='?pageIndex={0}&pageSize={1}'>{2}</a>", currentPageIndex, pageSize, currentPageIndex);
36                         }
37                         else
38                         {//一般页处理
39                             output.AppendFormat("<a class='pageLink' href='?pageIndex={0}&pageSize={1}'>{2}</a>", currentPageIndex + i-currint,pageSize, currentPageIndex + i-currint);
40                         }
41                     }
42                     output.Append(" ");
43                 }
44                 if (currentPageIndex < totalPages)
45                 {//处理下一页的链接
46                     output.AppendFormat("<a class='pageLink' href='?pageIndex={0}&pageSize={1}'>下一页</a>", currentPageIndex + 1,pageSize);
47                 }
48                 output.Append(" ");
49                 if (currentPageIndex != totalPages)
50                 {
51                     output.AppendFormat("<a class='pageLink' href='?pageIndex={0}&pageSize={1}'>末页</a>",totalPages,pageSize);
52                 }
53             }
54             output.AppendFormat("<span class="ep - pages - e5e5e5">第{0}页/共{1}页</span>", currentPageIndex, totalPages); //统计页数
55             return output.ToString();
56         }

 1         a {
 2             text-decoration: none;
 3         }
 4 
 5         .ep-pages {
 6             padding: 10px 12px;
 7             clear: both;
 8             font-family: Arial, "5B8B4F53", sans-serif;
 9             font-size: 14px;
10             vertical-align: top;
11         }
12 
13         .ep-pages a, .ep-pages span {
14             display: inline-block;
15             height: 23px;
16             line-height: 23px;
17             padding: 0 8px;
18             margin: 5px 1px 0 0;
19             background: #fff;
20             border: 1px solid #e5e5e5;
21             overflow: hidden;
22             vertical-align: top;
23         }
24 
25         .ep-pages a:hover {
26             background: #cc1b1b;
27             border: 1px solid #cc1b1b;
28             text-decoration: none;
29         }
30 
31         .ep-pages a, .ep-pages a:visited {
32             color: #252525;
33         }
34 
35         .ep-pages a:hover, .ep-pages a:active {
36             color: #ffffff;
37         }
38 
39         .ep-pages .current {
40             background: #cc1b1b;
41             border: 1px solid #cc1b1b;
42             color: #fff;
43         }
44 
45         .ep-pages a.current, .ep-pages a.current:visited {
46             color: #ffffff;
47         }
48 
49         .ep-pages a.current:hover, .ep-pages a.current:active {
50             color: #ffffff;
51         }
52 
53         .ep-pages-ctrl {
54             font-family: "5B8B4F53", sans-serif;
55             font-weight: bold;
56             font-size: 16px;
57         }
58 
59         .ep-pages-e5e5e5 {
60             color: #e5e5e5;
61         }
62 
63         .ep-pages-all {
64             font-size: 12px;
65             vertical-align: top;
66         }

分页CSS样式

使用示例

效果图:

分页SQL语句

1 SELECT * FROM (select ROW_NUMBER() over (order by id) as row,TT.* from Main TT) TTT
2 WHERE TTT.row BETWEEN 5 AND 10
3 
4 模板：
5 SELECT * FROM (select ROW_NUMBER() over (order by 排序字段) as row,TT.* from 表 TT) TTT
6 WHERE TTT.row BETWEEN 第几条 AND 第几条

效果图

存储过程分页

创建存储过程

 1 create proc P_LoadPageData
 2     @pageIndex int,
 3     @pageSize int,
 4     @total int out
 5 as
 6 begin
 7 --分页原理：越过多少条。取多少条
 8     SELECT TOP(@pageIndex)* FROM Main WHERE id not in
 9     (
10         SELECT TOP((@pageIndex-1)*@pageSize) id FROM Main ORDER BY id
11     )
12     ORDER BY ID
13     SELECT @total=COUNT(1) FROM Main
14     SELECT @total
15 end

测试刚才写的存储过程

 1 create proc P_LoadPageData
 2     @pageIndex int,
 3     @pageSize int,
 4     @total int out
 5 as
 6 begin
 7 --分页原理：越过多少条。取多少条
 8     SELECT TOP(@pageIndex)* FROM Main WHERE id not in
 9     (
10         SELECT TOP((@pageIndex-1)*@pageSize) id FROM Main ORDER BY id
11     )
12     ORDER BY ID
13     SELECT @total=COUNT(1) FROM Main
14     SELECT @total
15 end
16 
17 --存储过程测试
18 declare @total int
19 exec P_LoadPageData 3,5,@total
20 print @total

效果图

程序调用

 1         public List<Model.MainModel> LoadPageData(int pageIndex, int pageSize, out int total)
 2         {
 3             SqlParameter tal = new SqlParameter("@total", SqlDbType.Int);
 4             tal.Direction = ParameterDirection.Output; //设置为输出参数
 5             SqlParameter[] pms = new SqlParameter[] {
 6                 new SqlParameter("@pageIndex",SqlDbType.Int) {Value=pageIndex },
 7                 new SqlParameter("@pageSize",SqlDbType.Int) {Value=pageSize },
 8                 tal
 9             };
10             
11             DataSet ds= SqlHelper.GetList("P_LoadPageData", CommandType.StoredProcedure, pms);
12             total = (int)tal.Value; //拿到输出参数的值
13             DataTable dt = ds.Tables[0];
14             return Common.ToEntity.DtConvertToModel<MainModel>(dt);
15         }

 1         /// <summary>
 2         /// 执行sql语句或存储过程，返回DataSet
 3         /// </summary>
 4         /// <param name="procNameOrStrSql">存储过程名称/sql语句</param>
 5         /// <param name="cmdStoredProcedure">执行类型</param>
 6         /// <param name="pms">可变参数</param>
 7         /// <returns></returns>
 8         public static DataSet GetList(string procNameOrStrSql,CommandType cmdStoredProcedure, SqlParameter[] pms)
 9         {
10             try
11             {
12                 
13                 using (SqlConnection conn=new SqlConnection(connStr))
14                 {
15                     using (SqlDataAdapter adap = new SqlDataAdapter(procNameOrStrSql, conn))
16                     {
17                         DataSet ds = new DataSet();
18                         //添加参数
19                         if (pms != null)
20                         {
21                             adap.SelectCommand.Parameters.AddRange(pms);
22                         }
23                         adap.SelectCommand.CommandType = cmdStoredProcedure;
24                         adap.Fill(ds);
25                         return ds;
26                     }
27                 }
28             }
29             catch (Exception ex)
30             {
31                 WriteLog(procNameOrStrSql, ex);
32                 throw new Exception("错误内容:" + ex.Message.ToString());
33             }
34         }

sqlHelper类

 1         /// <summary>
 2         /// 将DataTable转换成实体类
 3         /// </summary>
 4         /// <typeparam name="T">实体类</typeparam>
 5         /// <param name="dt">DataTable</param>
 6         /// <returns></returns>
 7         public static List<T> DtConvertToModel<T>(DataTable dt) where T:new()
 8         {
 9             List<T> ts = new List<T>();
10             foreach (DataRow dr in dt.Rows)
11             {
12                 T t = new T();
13                 foreach (PropertyInfo pi in t.GetType().GetProperties())
14                 {
15                     if (dt.Columns.Contains(pi.Name))
16                     {
17                         if (!pi.CanWrite) continue;
18                         var value = dr[pi.Name];
19                         if (value!= DBNull.Value)
20                         {
21                             switch (pi.PropertyType.FullName)
22                             {
23                                 case "System.Decimal":
24                                     pi.SetValue(t, decimal.Parse(value.ToString()), null);
25                                     break;
26                                 case "System.String":
27                                     pi.SetValue(t, value.ToString(), null);
28                                     break;
29                                 case "System.Int32":
30                                     pi.SetValue(t, int.Parse(value.ToString()), null);
31                                     break;
32                                 default:
33                                     pi.SetValue(t, value, null);
34                                     break;
35                             }
36                         }
37                     }                    
38                 }
39                 ts.Add(t);
40             }
41             return ts;
42         }

DataTable反射实体类