//这里是前台显示页面的后台cs代码:
public partial class newslist : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
type=Request.QueryString["type"].ToString();
sid=Convert.ToInt32(Request.QueryString["sid"].ToString());
try
{
pageindex = Convert.ToInt32(Request.QueryString["pageindex"].ToString());
}
catch
{
pageindex = 1;
url= HttpContext.Current.Request.Url.PathAndQuery.ToString();
}
pagecount = 4;
Information info = new Information();
info.GetPageInfoListBytype(type, sid,pagecount,pageindex,url);
Inforlist = info.Infolist;
}
}
protected int pageindex;
protected int pagecount;
protected static string url;
protected string type;
protected string Inforlist;
protected int sid;
}
//上面方法的Information类
public void GetPageInfoListBytype(string type, int sid,int pagecount,int pageindex,string url)
{
DataAcess dac = new DataAcess();
SqlDataReader sdr = dac.GetPageInfoReaderBytype(type, sid,pagecount,pageindex);
int allcount=0;
while (sdr.Read())
{
allcount = sdr.GetInt32(0);
}
sdr.NextResult();
while (sdr.Read())
{
_InfoList += "<tr><td><a href='infoshow.aspx?nid=" + int.Parse(sdr["infoid"].ToString()) + "'>" + sdr["infotitle"] + "</a></td></a><td>" + System.DateTime.Parse(sdr["pubtime"].ToString()).ToString("yyyy-MM-dd") + "</td></tr>";
}
sdr.Close();
_InfoList += "<tr><td>"+this.Pager(pageindex, pagecount, allcount, url)+"</td></tr>";
}
//分页的关键地方
protected string Pager(int pageindex,int pagecout,int allcount,string url)
{
int allpagecount =Convert.ToInt32(Math.Ceiling((double)allcount/(double)pagecout));
string fh;
string strHtml;
if (url.IndexOf("?") > -1)
{
fh = "&";
}
else
{
fh = "?";
}
strHtml = "共:" + allcount + " 条记录,每页显示:" + pagecout + "条,当前" + pageindex + "/" + allpagecount + "页";
if (pageindex == 1&pageindex!=allpagecount)
{
strHtml += "首页<span style='margin-left:2em'></span>上一页<span style='margin-left:2em'></span><a href='" + url + fh + "pageindex=" + (pageindex+1) + "'>下一页</a><span style='margin-left:2em'></span><a href='" + url + fh + "pageindex=" + allpagecount + "'>末页</a>"; }
else if (pageindex==1&pageindex==allpagecount)
{
strHtml += "首页<span style='margin-left:2em'></span>上一页<span style='margin-left:2em'></span>下一页<span style='margin-left:2em'></span>末页";
}
else if (pageindex != 1 & pageindex != allpagecount)
{
strHtml += "<a href='" + url + fh + "pageindex=1'>首页</a><span style='margin-left:2em'></span><a href='" + url + fh + "pageindex=" + (pageindex - 1) + "'>上一页</a><span style='margin-left:2em'></span><a href='" + url + fh + "pageindex=" + (pageindex + 1) + "'>下一页</a><span style='margin-left:2em'></span><a href='" + url + fh + "pageindex=" + allpagecount + "'>末页</a>";
}
else
{
strHtml += "<a href='" + url + fh + "pageindex=1'>首页</a><span style='margin-left:2em'></span><a href='" + url + fh + "pageindex=" + (pageindex - 1) + "'>上一页</a><span style='margin-left:2em'></span>下一页<span style='margin-left:2em'></span>末页";
}
return strHtml; }
//SQL语句
select count(*) from Information inner join category on Information.cateid=category.cateid and category.catename=@type and sortid=@sid;
select top (@pagecount) infoid,infotitle,pubtime from Information inner join category on Information.cateid=category.cateid and category.catename=@type and sortid=@sid and infoid <=(select min(infoid) from (select top (@pagecount*(@pageindex-1)+1) infoid from Information inner join category on Information.cateid=category.cateid and category.catename=@type and sortid=@sid order by infoid desc) as t) order by infoid desc
public partial class newslist : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
type=Request.QueryString["type"].ToString();
sid=Convert.ToInt32(Request.QueryString["sid"].ToString());
try
{
pageindex = Convert.ToInt32(Request.QueryString["pageindex"].ToString());
}
catch
{
pageindex = 1;
url= HttpContext.Current.Request.Url.PathAndQuery.ToString();
}
pagecount = 4;
Information info = new Information();
info.GetPageInfoListBytype(type, sid,pagecount,pageindex,url);
Inforlist = info.Infolist;
}
}
protected int pageindex;
protected int pagecount;
protected static string url;
protected string type;
protected string Inforlist;
protected int sid;
}
//上面方法的Information类
public void GetPageInfoListBytype(string type, int sid,int pagecount,int pageindex,string url)
{
DataAcess dac = new DataAcess();
SqlDataReader sdr = dac.GetPageInfoReaderBytype(type, sid,pagecount,pageindex);
int allcount=0;
while (sdr.Read())
{
allcount = sdr.GetInt32(0);
}
sdr.NextResult();
while (sdr.Read())
{
_InfoList += "<tr><td><a href='infoshow.aspx?nid=" + int.Parse(sdr["infoid"].ToString()) + "'>" + sdr["infotitle"] + "</a></td></a><td>" + System.DateTime.Parse(sdr["pubtime"].ToString()).ToString("yyyy-MM-dd") + "</td></tr>";
}
sdr.Close();
_InfoList += "<tr><td>"+this.Pager(pageindex, pagecount, allcount, url)+"</td></tr>";
}
//分页的关键地方
protected string Pager(int pageindex,int pagecout,int allcount,string url)
{
int allpagecount =Convert.ToInt32(Math.Ceiling((double)allcount/(double)pagecout));
string fh;
string strHtml;
if (url.IndexOf("?") > -1)
{
fh = "&";
}
else
{
fh = "?";
}
strHtml = "共:" + allcount + " 条记录,每页显示:" + pagecout + "条,当前" + pageindex + "/" + allpagecount + "页";
if (pageindex == 1&pageindex!=allpagecount)
{
strHtml += "首页<span style='margin-left:2em'></span>上一页<span style='margin-left:2em'></span><a href='" + url + fh + "pageindex=" + (pageindex+1) + "'>下一页</a><span style='margin-left:2em'></span><a href='" + url + fh + "pageindex=" + allpagecount + "'>末页</a>"; }
else if (pageindex==1&pageindex==allpagecount)
{
strHtml += "首页<span style='margin-left:2em'></span>上一页<span style='margin-left:2em'></span>下一页<span style='margin-left:2em'></span>末页";
}
else if (pageindex != 1 & pageindex != allpagecount)
{
strHtml += "<a href='" + url + fh + "pageindex=1'>首页</a><span style='margin-left:2em'></span><a href='" + url + fh + "pageindex=" + (pageindex - 1) + "'>上一页</a><span style='margin-left:2em'></span><a href='" + url + fh + "pageindex=" + (pageindex + 1) + "'>下一页</a><span style='margin-left:2em'></span><a href='" + url + fh + "pageindex=" + allpagecount + "'>末页</a>";
}
else
{
strHtml += "<a href='" + url + fh + "pageindex=1'>首页</a><span style='margin-left:2em'></span><a href='" + url + fh + "pageindex=" + (pageindex - 1) + "'>上一页</a><span style='margin-left:2em'></span>下一页<span style='margin-left:2em'></span>末页";
}
return strHtml; }
//SQL语句
select count(*) from Information inner join category on Information.cateid=category.cateid and category.catename=@type and sortid=@sid;
select top (@pagecount) infoid,infotitle,pubtime from Information inner join category on Information.cateid=category.cateid and category.catename=@type and sortid=@sid and infoid <=(select min(infoid) from (select top (@pagecount*(@pageindex-1)+1) infoid from Information inner join category on Information.cateid=category.cateid and category.catename=@type and sortid=@sid order by infoid desc) as t) order by infoid desc
/// 取得总数
/// </summary>
/// <returns></returns>
public string getTotal()
{
StringBuilder sb = new StringBuilder();
sb.Append("select count(*) total from Test");
DataTable dt = DBHelper.ExecuteDt(sb.ToString());
return dt.Rows[0][0].ToString();
}
/// <summary>
/// 根据当前页码,每页条数,取得相应数据。
/// </summary>
/// <param name="pageNum">每页显示条数</param>
/// <param name="currentPage">当前页码</param>
/// <returns></returns>
public DataTable getPagesData(int pageNum, int currentPage)
{
StringBuilder sb = new StringBuilder();
sb.Append("select top " + pageNum + " * from Test where ");
sb.Append("ID not in (select top " + pageNum * currentPage + " ID from Test)");
return DBHelper.ExecuteDt(sb.ToString());
}
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="sqlPager_Default" %>
<%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>不用存储过程的分页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:gridview ID="gvSql" runat="server">
</asp:gridview>
</div>
<div>
<webdiyer:aspnetpager ID="AspNetPager1" runat="server" OnPageChanged="AspNetPager1_PageChanged" PageSize="3">
</webdiyer:aspnetpager>
</div>
</form>
</body>
</html>using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class sqlPager_Default : System.Web.UI.Page
{
BLL.Test test = new BLL.Test();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
AspNetPager1.RecordCount = Convert.ToInt32(test.getTotal());//此属性保存总记录数..
Bind();
}
}
private void Bind()
{
this.gvSql.DataSource = test.getPagesData(Convert.ToInt32(AspNetPager1.PageSize), AspNetPager1.CurrentPageIndex - 1);
this.gvSql.DataBind();
}
protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
Bind();
}
}
SELECT TOP 20 * FROM a
第二页,id为主键
SELECT TOP 20 * FROM a WHERE id NOT IN (SELECT TOP 40 id FROM a)
第三页
SELECT TOP 20 * FROM a WHERE id NOT IN (SELECT TOP 60 id FROM a)
public static DataTable getPageService(int start, int end,string conStr,string selectStr)
{
string sql;
SqlConnection con = new SqlConnection(conStr);
if (selectStr.IndexOf("order by") <= -1)
{
sql = "select * from (select row_number() over(order by (select 0)) rownum,* from (" + selectStr + ") as a) as b where rownum between " + start + " and " + end;
}
else
{
string[] temp = Regex.Split(selectStr, @"[ ]order[ ]by[ ]", RegexOptions.IgnoreCase);
string newStr = temp[0];
string orderValue = temp[1];
sql = "select * from (select row_number() over(order by "+temp[1]+") rownum,* from (" + temp[0] + ") as a) as b where rownum between " + start + " and " + end; }
SqlCommand com = new SqlCommand(sql, con);
con.Open(); DataSet da = new DataSet();
SqlDataAdapter adapt = new SqlDataAdapter(com);
adapt.Fill(da);
con.Close();
return da.Tables[0];
}
开始行号,结束行号,连接字符串,SELECT查询语句我这个是根据一个已经有的SELECT查询语句得到一个分页的DATATABLE对象
这就是一分页SQL语句
存储过程写还是很简单的。
给你个例子:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <hoover.huang>
-- Create date: < 2010-10-28>
-- Description: <查询订单详细信息列表,根据起止时间>
-- =============================================
ALTER PROCEDURE [dbo].[spGetOrderListInfoByTime] -- Add the parameters for the stored procedure here
@startIndex INT, --起始行数
@endindex INT, --结束行数
--@userEmail nvarchar(100), --用户名
--@prBrand nvarchar(100), --品牌
@beginTime datetime, --起始时间
@endTime datetime --结束时间
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
WITH orderList AS (
SELECT ROW_NUMBER() OVER (ORDER BY ord.ORD_DATE DESC)AS rownum,
--ord.ORD_ID as ordID, --订单主键Id
ord.ORD_NO as OrdNo, --订单号
ord.ORD_DATE as OrdDate, --订单时间
ISNULL(ord.ORG_PROMOTION_CODE,'') as PromotionCode, --促销代码
ISNULL(sta.OST_STATUS,'') as OstStatus, --订单状态
ite.OIT_BRAND AS Brand , --品牌
ite.OIT_NAME AS PName, --产品名称
ite.PRD_ID AS PrDId, --产品编号
ISNULL(ite.OIT_QUANTITY,0.00) AS Quantity, --数量
ISNULL(ite.OIT_UPRICE,0.00) AS Price, --单价
ISNULL(ite.OIT_SUBTOTAL,0.00) AS ProductSubtotal , --小计
ISNULL(ord.ORD_SHIPPING_TOTAL,0) AS ShippingTotal, --运费
(ISNULL(ord.ORD_SUB_TOTAL,0.00) + ISNULL(ord.ORD_SHIPPING_TOTAL,0)) AS SubTotal, --总计
addr.CAD_PROVINCE AS Province, --送货省份
addr.CAD_CITY AS City , --送货城市
PType = CASE ord.ORD_PTYPE WHEN 0 THEN N'货到付款' WHEN 1 THEN N'银联支付' WHEN 2 THEN N'支付宝' ELSE N'货到付款' END,
cu.CU_EMAIL as email --用户名from Orders ord
left join OrderStatus sta on ord.ORD_ID=sta.ORD_ID
left join OrderItem ite on ord.ORD_ID=ite.ORD_ID
left join OrderAddress addr on ord.CAD_ID=addr.CAD_ID
left join CustUser cu on ord.CU_ID =cu.CU_ID
where
ord.ORD_DATE>=@beginTime and ord.ORD_DATE<@endTime
)SELECT * FROM orderlist
WHERE
rownum between @startIndex and @endIndex
--and ordDate>= @beginTime and ordDate<= @endTime
order by ordDate desc ;END每次把查询条件和起止行数作为参数传进去就ok了,效率也还可以。
页面上的页码,你查询出总行数,然后循环输出就可以了,下一页和上一页就更简单了
{
/// <summary>
/// 返回第几页的数据
/// ex: DataSet ds = getCurrentPageData("product", "productid",3,2,"productname like '圣%'");
/// </summary>
/// <param name="v_tableName"></param>
/// <param name="v_orderByID"></param>
/// <param name="v_pagesize"></param>
/// <param name="v_currentPage"></param>
/// <param name="v_expression">条件</param>
/// <returns></returns>
public static DataSet getCurrentPageData( string v_tableName, string v_orderByID, int v_pagesize, int v_currentPage, string v_expression, string orderBy)
{
////判断是否降序排列
//string newOrder = v_orderByID.Substring(v_orderByID.LastIndexOf(' ') + 1);
//if ( newOrder.ToUpper() == "DESC" )
// newOrder = v_orderByID.Substring(0,v_orderByID.LastIndexOf(' ')); string strsql = "";
//第一页
if (v_currentPage == 1)
{
strsql = "select top " + v_pagesize + " * from " + v_tableName + " where " + v_expression + " order by " + orderBy;
}
else
{
strsql = "select top " + v_pagesize + " * from " + v_tableName + " where " + v_expression + " and " + v_orderByID + " not in ";
strsql = strsql + " (select top " + (v_currentPage - 1) * v_pagesize + " " + v_orderByID + " from " + v_tableName + " where " + v_expression;
strsql = strsql + " order by " + orderBy + ") order by " + orderBy;
} //改
return DbHelperSQL.ExecuteDataSet(strsql);
}
//改
/// <summary>
/// 返回表中的总页数
/// </summary>
/// <param name="v_tableName"></param>
/// <returns></returns>
public static int getTotalPage(string v_tableName, int v_pagesize, string v_expression)
{ string strsql = "select * from " + v_tableName + " where " + v_expression;
DataSet ds = DbHelperSQL.ExecuteDataSet(strsql);
int totalRow = ds.Tables[0].Rows.Count;// SQLDBHelper.ExecuteScalar(SQLDBHelper.ConnectionString, strsql); int maxpage = 1;
if (totalRow % v_pagesize == 0)
{
maxpage = totalRow / v_pagesize;
}
else
{
maxpage = totalRow / v_pagesize + 1;
}
return maxpage;
}
}