Create procedure jxytext_pagination
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as TotalPages,@rowcount as TotalRows,@currentpage as CurPage
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
GO
demo:
declare @sql varchar(1000) set @sql='select * from cxloanbal' exec jxytext_pagination @sql,1,10
其中 @sql 是你想执行的sql语句,那个1是 从起始记录的index,那个10是 你想每页显示的记录数
返回三个结果集 第一个是空的 第二个返回的分别是 总页数,总行数,起始记录的index,第三个结果集是 你想要的结果集
.多个数据集的提取和应用主要是Dr.NextResult();
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; using DAL; using System.Data.SqlClient; public partial class Default6 : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { SqlDataReader Dr ; Dr = SqlHelper.ExecuteReader(SqlHelper.CONNECTION_STRING, CommandType.StoredProcedure, "Pr_jxytest2"); Dr.NextResult(); while (Dr.Read()) { Response.Write(Dr["Totalpages"]+"--"+Dr["Totalrows"]+"--"+Dr["Curpage"]); } Dr.NextResult(); while (Dr.Read()) { Response.Write(Dr["roomname"]+"<br>"); } } }