分页原理：越过多少条。取多少条
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 }
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
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 }
使用示例
效果图:
分页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 }
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 }