分页原理：越过多少条。取多少条
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, "\5B8B\4F53", 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: "\5B8B\4F53", 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反射实体类