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());
}
