SQL存储过程: USE [K2_Workflow_wuhan] GO /**//****** 对象: StoredProcedure [dbo].[TablePager] 脚本日期: 11/05/2007 15:44:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[TablePager] ( @serTable nvarchar(200), --要显示的表 @serColumns nvarchar(500), --要显示的字段 @strWhere nvarchar(500), --查询条件,不需where @serPK nvarchar(500),--表主键 @pagesize int, --每页显示的记录个数 @pageindex int, --要显示那一页的记录,以开始 @docount bit) --查询到的记录数 asDeclare @strSQL nvarchar(3500) Declare @betweenS INT Declare @betweenE INT PRINT @strWhere if(@docount=1) BEGIN Set @strSQL='select count(*) from '+Cast(@serTable as nvarchar(200)) IF (@strWhere!='') Set @strSQL=(@strSQL+' where ' + Cast(@strWhere as nvarchar(1000))) END else BEGIN Set @betweenS =(@pageindex-1)*@pagesize+1 Set @betweenE =(@pageindex-1)*@pagesize+@pagesize --Set @strSQL='with temptbl as (SELECT ROW_NUMBER() OVER (ORDER BY Comicid desc)AS Row, '+Cast(@serColumns as nvarchar(200))+' from '+Cast(@serTable as nvarchar(200)) Set @strSQL='with temptbl as (SELECT ROW_NUMBER() OVER (ORDER BY '+@serPK+' desc)AS Row, '+Cast(@serColumns as nvarchar(200))+' from '+Cast(@serTable as nvarchar(200)) --PRINT @strSQL IF (@strWhere!='') BEGIN Set @strSQL=(@strSQL+' where (1=1) and ' + Convert(Varchar,@strWhere,500)) END --PRINT @strSQL Set @strSQL=(@strSQL+' ) SELECT * FROM temptbl where Row between '+Cast(@betweenS AS nvarchar(200))+' and '+Cast(@betweenE AS nvarchar(200))) END --PRINT @strSQL execute sp_executesql @strSQL思路: (1)创建SQL存储过程 (2)写ASP。NET分页页面 (3)写ASP。NET分页类,这个类用来计算分页索引的。 (4)接受每次传进来的页索引参数,从数据库中获取分页数据,绑定到Repeater控件。
关于源码,你搜一下XproerBBS源码,上面有一个完整的分页源码。
去吴旗娃的网站上去看www.webdiyer.com
CREATE PROCEDURE [dbo].[UP_CASE_GetPagedCaseInfoList] @pagesize int, @pageindex int, @docount bit ASset nocount on if(@docount=1) Select Count(*) as RecordCount From CM_CaseData else begin declare @indextable table(id int identity(1,1),nid int) declare @PageLowerBound int declare @PageUpperBound int set @PageLowerBound=(@pageindex-1)*@pagesize set @PageUpperBound=@PageLowerBound+@pagesize set rowcount @PageUpperBound insert into @indextable(nid) select CaseID from CM_CaseData order by CaseID asc select a.*,b.ClassName From CM_CaseData a,CM_CaseClass b,@indextable t where a.ClassID=b.ClassID AND a.CaseID=t.nid and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id asc end set nocount off GO
USE [K2_Workflow_wuhan]
GO
/**//****** 对象: StoredProcedure [dbo].[TablePager] 脚本日期: 11/05/2007 15:44:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[TablePager]
(
@serTable nvarchar(200), --要显示的表
@serColumns nvarchar(500), --要显示的字段
@strWhere nvarchar(500), --查询条件,不需where
@serPK nvarchar(500),--表主键
@pagesize int, --每页显示的记录个数
@pageindex int, --要显示那一页的记录,以开始
@docount bit) --查询到的记录数
asDeclare @strSQL nvarchar(3500)
Declare @betweenS INT
Declare @betweenE INT
PRINT @strWhere
if(@docount=1)
BEGIN
Set @strSQL='select count(*) from '+Cast(@serTable as nvarchar(200))
IF (@strWhere!='')
Set @strSQL=(@strSQL+' where ' + Cast(@strWhere as nvarchar(1000)))
END
else
BEGIN
Set @betweenS =(@pageindex-1)*@pagesize+1
Set @betweenE =(@pageindex-1)*@pagesize+@pagesize --Set @strSQL='with temptbl as (SELECT ROW_NUMBER() OVER (ORDER BY Comicid desc)AS Row, '+Cast(@serColumns as nvarchar(200))+' from '+Cast(@serTable as nvarchar(200))
Set @strSQL='with temptbl as (SELECT ROW_NUMBER() OVER (ORDER BY '+@serPK+' desc)AS Row, '+Cast(@serColumns as nvarchar(200))+' from '+Cast(@serTable as nvarchar(200))
--PRINT @strSQL IF (@strWhere!='')
BEGIN
Set @strSQL=(@strSQL+' where (1=1) and ' + Convert(Varchar,@strWhere,500))
END
--PRINT @strSQL
Set @strSQL=(@strSQL+' ) SELECT * FROM temptbl where Row between '+Cast(@betweenS AS nvarchar(200))+' and '+Cast(@betweenE AS nvarchar(200)))
END
--PRINT @strSQL
execute sp_executesql @strSQL思路:
(1)创建SQL存储过程
(2)写ASP。NET分页页面
(3)写ASP。NET分页类,这个类用来计算分页索引的。
(4)接受每次传进来的页索引参数,从数据库中获取分页数据,绑定到Repeater控件。
@pagesize int,
@pageindex int,
@docount bit
ASset nocount on
if(@docount=1)
Select Count(*) as RecordCount From CM_CaseData
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select CaseID from CM_CaseData order by CaseID asc
select a.*,b.ClassName From CM_CaseData a,CM_CaseClass b,@indextable t where a.ClassID=b.ClassID AND a.CaseID=t.nid and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id asc
end
set nocount off
GO