ALTER proc [dbo].[pageing] ( @tableName varchar(255), --表名 @showField varchar(1000), --显示的字段 @orderField varchar(255), --排序的字段 @pageSize int, --页尺寸 @pageIndex int, --页码 @orderType bit, --排序类型,1是升序,0是降序 @strWhere varchar(3000), --查询条件 @total int output --返回总记录数 ) as begin declare @strSql varchar(4000) --主语句 declare @strOrder varchar(200) --排序 declare @strSqlCount nvarchar(500) --查询记录总数主语句 if(@orderType != 0) begin set @strOrder=' order by '+@orderField+' asc' end else begin set @strOrder=' order by '+@orderField+' desc' end if(len(@strWhere) > 0) begin set @strSqlCount='select @totalCout=count(1) from '+@tableName+' where 1=1 '+@strWhere end else begin set @strSqlCount='select @totalCout=count(1) from '+@tableName end exec sp_executesql @strSqlCount,N'@totalCout int output',@total output if(@pageIndex <= 0) begin set @pageIndex=1 end if(len(@strWhere) > 0) begin set @strSql='select * from (select top '+str(@total)+' row_number() over('+@strOrder+') rowId,'+@showField +' from '+@tableName+' where 1=1 '+@strWhere+@strOrder+') tb where tb.rowId>'+str((@pageIndex-1)*@pageSize) +' and tb.rowId<='+str(@pageIndex*@pageSize) end else begin set @strSql='select * from (select top '+str(@total)+' row_number() over('+@strOrder+') rowId,'+@showField +' from '+ @tableName+' where 1=1 '+@strWhere+@strOrder+') tb where tb.rowId>'+str((@pageIndex-1)*@pageSize) +' and tb.rowId<='+str(@pageIndex*@pageSize) end print @strSql exec(@strSql) end using System; using System.Data; using System.Configuration; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Text; /// <summary> ///MyPageing 的摘要说明 /// </summary> public class MyPageing : Page { public MyPageing() { // //TODO: 在此处添加构造函数逻辑 // } public static string GetPageNum(int total, int pagesize) { string[] canshu = HttpContext.Current.Request.QueryString.ToString().Split('&'); StringBuilder sb = new StringBuilder(); foreach (string cs in canshu) { if (cs.IndexOf("PageNo") > -1) continue; sb.Append("&" + cs); } int page; if (HttpContext.Current.Request.QueryString["PageNo"] != null) page = Convert.ToInt32(HttpContext.Current.Request.QueryString["PageNo"]); else page = 1; int allpage = 0; int next = 0; int pre = 0; int startcount = 0; int endcount = 0; StringBuilder pagestr = new StringBuilder(); pagestr.Append("<style type=\"text/css\">"); pagestr.Append("* { margin:0; padding:0;}"); pagestr.Append("a{ color:#333; text-decoration:none;}"); pagestr.Append("ul{ list-style:none;}"); pagestr.Append("#pagelist {padding:6px 0px; height:20px; float:right;}"); pagestr.Append("#pagelist ul li { float:left; height:20px; line-height:20px; margin:0px 2px;}"); pagestr.Append(".hrjaa{border:1px solid #d0d0d0; }"); pagestr.Append(".hrjaa a{ display:block; padding:0px 6px; background:#f6f6f6;}"); pagestr.Append(".current { background:#fdf3f3; display:block; padding:0px 6px; font-weight:bold;border:1px solid #d00202; color:#d00202;}"); pagestr.Append("</style>"); if (page < 1) { page = 1; } //计算总页数 if (pagesize != 0) { allpage = (total / pagesize); allpage = ((total % pagesize) != 0 ? allpage + 1 : allpage); allpage = (allpage == 0 ? 1 : allpage); } next = page + 1; pre = page - 1; startcount = (page + 5) > allpage ? allpage - 9 : page - 4;//中间页起始序号 //中间页终止序号 endcount = page < 5 ? 10 : page + 5; if (startcount < 1) { startcount = 1; } //为了避免输出的时候产生负数，设置如果小于1就从序号1开始 if (allpage < endcount) { endcount = allpage; } //页码+5的可能性就会产生最终输出序号大于总页码，那么就要将其控制在页码数之内 //pagestr.Append("共<font color='red'>" + total + "</font>条记录 每页<font color='red'>" + pagesize + "</font>条 共<font color='red'>" + allpage + "</font>页"); pagestr.Append("<div id=\"pagelist\">"); pagestr.Append("<ul>"); if (page > 1) pagestr.Append("<li class=\"hrjaa\"><a href=\"" + HttpContext.Current.Request.CurrentExecutionFilePath + "?PageNo=" + pre + sb.ToString() + "\">上一页</a></li>"); else pagestr.Append("<li>上一页</li>"); //中间页处理，这个增加时间复杂度，减小空间复杂度 for (int i = startcount; i <= endcount; i++) { if (page == i) pagestr.Append("<li class=\"current\">" + i + "</li>"); else pagestr.Append("<li class=\"hrjaa\"><a href=\"" + HttpContext.Current.Request.CurrentExecutionFilePath + "?PageNo=" + i + sb.ToString() + "\">" + i + "</a></li>"); } if (page != allpage) pagestr.Append("<li class=\"hrjaa\"><a href=\"" + HttpContext.Current.Request.CurrentExecutionFilePath + "?PageNo=" + next + sb.ToString() + "\">下一页</a></li>"); else pagestr.Append(" <li>下一页</li>"); pagestr.Append("</ul>"); pagestr.Append("</div>"); return pagestr.ToString(); } }
--第一页数据 select top 2 id,SerialNumber,AddTime from TB_Voucher --第n页数据(n>1) 2*1 2*2 SELECT TOP 2 id,SerialNumber,AddTime from TB_Voucher where (id > (SELECT MAX(id) FROM (SELECT TOP 6 id FROM TB_Voucher ORDER BY id) AS T)) ORDER BY ID
/// <summary> /// 分页使用 /// </summary> /// <param name="query"></param> /// <param name="passCount"></param> /// <returns></returns> private static string recordID(string query, int passCount) { using (OleDbConnection m_Conn = new OleDbConnection(ConnectionString)) { m_Conn.Open(); OleDbCommand cmd = new OleDbCommand(query, m_Conn); string result = string.Empty; using (OleDbDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { if (passCount < 1) { result += "," + dr.GetInt32(0); } passCount--; } } m_Conn.Close(); m_Conn.Dispose(); return result.Substring(1); } } /// <summary> /// ACCESS高效分页 /// </summary> /// <param name="pageIndex">当前页码</param> /// <param name="pageSize">分页容量</param> /// <param name="strKey">主键</param> /// <param name="showString">显示的字段</param> /// <param name="queryString">查询字符串，支持联合查询</param> /// <param name="whereString">查询条件，若有条件限制则必须以where 开头</param> /// <param name="orderString">排序规则</param> /// <param name="pageCount">传出参数：总页数统计</param> /// <param name="recordCount">传出参数：总记录统计</param> /// <returns>装载记录的DataTable</returns> public static DataTable ExecutePager(int pageIndex, int pageSize, string strKey,string showString, string queryString, string whereString, string orderString, out int pageCount, out int recordCount) { if (pageIndex < 1) pageIndex = 1; if (pageSize < 1) pageSize = 10; if (string.IsNullOrEmpty(showString)) showString = "*"; if (string.IsNullOrEmpty(orderString)) orderString = strKey+" asc "; using (OleDbConnection m_Conn = new OleDbConnection(ConnectionString)) { m_Conn.Open(); string myVw = string.Format(" ( {0} ) tempVw ", queryString); OleDbCommand cmdCount = new OleDbCommand(string.Format(" select count(*) as recordCount from {0} {1}", myVw, whereString), m_Conn); recordCount = Convert.ToInt32(cmdCount.ExecuteScalar()); if ((recordCount % pageSize) > 0) pageCount = recordCount / pageSize + 1; else pageCount = recordCount / pageSize; OleDbCommand cmdRecord; if (pageIndex == 1)//第一页 { cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, whereString, orderString), m_Conn); } else if (pageIndex > pageCount)//超出总页数 { cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, "where 1=2", orderString), m_Conn); } else { int pageLowerBound = pageSize * pageIndex; int pageUpperBound = pageLowerBound - pageSize; string recordIDs = recordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, strKey, myVw, whereString, orderString), pageUpperBound); cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {2} in ({3}) order by {4} ", showString, myVw,strKey, recordIDs, orderString), m_Conn); } OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord); DataTable dt = new DataTable(); dataAdapter.Fill(dt); m_Conn.Close(); m_Conn.Dispose(); return dt; } }
public static System.Data.DataSet getPageTable(int pageSize, int pageIndex, string tab, string show, string where, string order, bool mulit) { if (pageIndex < 1) { pageIndex = 1; }; if (pageSize < 1) { pageSize = 1; }; StringBuilder sb = new StringBuilder(); if (pageIndex == 1) { if (string.IsNullOrEmpty(where)) { sb.AppendFormat("select COUNT(0) from {0};select top {1} {2} from {0} order by {3}", tab, pageSize, show, order); } else { sb.AppendFormat("select COUNT(0) from {0} where {4};select top {1} {2} from {0} where {4} order by {3}", tab, pageSize, show, order, where); } } else { int start = pageIndex * pageSize; int end = start - pageSize + 1; string pagestr = start == end ? string.Format("={0}", start) : string.Format(" between {0} and {1}", end, start); if (mulit) { int kgIndex = show.IndexOf(' '); if (string.IsNullOrEmpty(where)) { sb.AppendFormat("select COUNT(0) from {0};select top {5} {1} from (select ROW_NUMBER() over(order by {2}) rowid,{4} from {0}) tb0 where rowid{3}", tab, show.Substring(kgIndex), order, pagestr, show, pageSize); } else { sb.AppendFormat("select COUNT(0) from {0} where {1};select top {6} {2} from (select ROW_NUMBER() over(order by {3}) rowid,{5} from {0} where {1}) tb0 where rowid{4}", tab, where, show.Substring(kgIndex), order, pagestr, show, pageSize); } } else { if (string.IsNullOrEmpty(where)) { sb.AppendFormat("select COUNT(0) from {0};select top {4} {1} from (select ROW_NUMBER() over(order by {2}) rowid,{1} from {0}) tb0 where rowid{3}", tab, show, order, pagestr, pageSize); } else { sb.AppendFormat("select COUNT(0) from {0} where {1};select top {5} {2} from (select ROW_NUMBER() over(order by {3}) rowid,{2} from {0} where {1}) tb0 where rowid{4}", tab, where, show, order, pagestr, pageSize); } } } return DbHelperSQL.Query(sb.ToString()); }