分享
 
 
 

ASP.net 2.0 自定义控件的开发之数据分页 第四章

王朝asp·作者佚名  2006-11-16
窄屏简体版  字體: |||超大  

存储过程 为 SQL Server 2000版本,请打开SQL server 2000 的查询分析器执行下面的SQL 语句。

程序用到的存储过程(仅支持主键排序)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sys_QuickSortPaging]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sys_QuickSortPaging]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

CREATE PROCEDURE sys_QuickSortPaging

(

@Table nvarchar(4000), --表名(必须)

@PrimaryKeyField nvarchar(50), --表的主键字段

@Field nvarchar (4000)='*', --需要返回字段名(必须)

@Where nvarchar(1000)=NULL, --Where 条件(可选)

@GroupBy nvarchar(1000) = NULL, --分组

@OrderBy nvarchar(1000)=NULL, --排序用到的字段()

@PageNumber int = 1, --要返回的页(第X页) (默认为第一页)

@PageSize int = 10, --每页大小(默认为5)

@RecordCount int output --返回记录总数

)

AS

SET NOCOUNT ON

DECLARE @SortTable nvarchar(100)

DECLARE @SortName nvarchar(100)

DECLARE @strSortColumn nvarchar(200)

DECLARE @Operator nvarchar(50)

DECLARE @Type varchar(100)

DECLARE @Prec int

IF @OrderBy IS NULL OR @OrderBy = ''

SET @OrderBy = @PrimaryKeyField

/**//* 获取用于定位的字段*/

IF CHARINDEX('DESC',@OrderBy)>0

BEGIN

SET @strSortColumn = REPLACE(@OrderBy, 'DESC', '')

SET @Operator = '<='

END

ELSE

BEGIN

IF CHARINDEX('ASC', @OrderBy) = 0

SET @strSortColumn = REPLACE(@OrderBy, 'ASC', '')

SET @Operator = '>='

END

IF CHARINDEX('.', @strSortColumn) > 0

BEGIN

SET @SortTable = SUBSTRING(@strSortColumn, 0, CHARINDEX('.',@strSortColumn))

SET @SortName = SUBSTRING(@strSortColumn, CHARINDEX('.',@strSortColumn) + 1, LEN(@strSortColumn))

END

ELSE

BEGIN

SET @SortTable = @Table

SET @SortName = @strSortColumn

END

SELECT @Type=t.name, @Prec=c.prec

FROM sysobjects o

JOIN syscolumns c on o.id=c.id

JOIN systypes t on c.xusertype=t.xusertype

WHERE o.name = @SortTable AND c.name = @SortName

IF CHARINDEX('char', @Type) > 0

SET @Type = @Type + '(' + CAST(@Prec AS nvarchar) + ')'

DECLARE @strStartRow nvarchar(50)

DECLARE @strPageSize nvarchar(50)

DECLARE @strWhere nvarchar(1000)

DECLARE @strWhereAnd nvarchar(1000)

DECLARE @strGroupBy nvarchar(1000)

IF @PageNumber < 1

SET @PageNumber = 1

SET @strPageSize = CONVERT (nvarchar(50), @PageSize)

SET @strStartRow = CONVERT ( nvarchar(50), (@PageNumber - 1)*@PageSize + 1)

IF @Where IS NOT NULL AND @Where !=''

BEGIN

SET @strWhere = ' WHERE '+ @Where

SET @strWhereAnd= ' AND ' + @Where

END

ELSE

BEGIN

SET @strWhere = ''

SET @strWhereAnd=''

END

IF @GroupBy IS NOT NULL AND @GroupBy != ''

BEGIN

SET @strGroupBy = ' GROUP BY ' + @GroupBy

END

ELSE

BEGIN

SET @strGroupBy = ''

END

DECLARE @strSQL nvarchar(4000)

SET @strSql= ' SELECT @RecordCount = Count (*) FROM ' + @Table + @strWhere + ' ' + @strGroupBy

EXEC sp_executesql @strSql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT--计算总页数

EXEC

(

'

DECLARE @Sort ' + @Type + '

SET ROWCOUNT ' + @strStartRow + '

SELECT @Sort = ' + @strSortColumn + ' FROM ' + @Table + @strWhere + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy + '

SET ROWCOUNT ' + @strPageSize + '

SELECT '+@Field+' FROM ' + @Table + ' WHERE ' + @strSortColumn + @Operator + ' @Sort ' + @strWhereAnd + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy

)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

支持任意字段排序的存储过程

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sys_SortDataPager]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sys_SortDataPager]

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE PROCEDURE sys_SortDataPager (

@Table nvarchar(4000), --表名(必须)

@PrimaryKeyField nvarchar(50), --表的主键字段

@Field nvarchar (4000)='*', --需要返回字段名(必须)

@Where nvarchar(1000)=NULL, --Where 条件(可选)

@GroupBy nvarchar(1000) = NULL, --分组

@OrderBy nvarchar(1000)=NULL, --排序用到的字段()

@PageNumber int = 1, --要返回的页(第X页) (默认为第一页)

@PageSize int = 10, --每页大小(默认为5)

@RecordCount int out --返回记录总数

)

AS

/**//*Find the @PrimaryKeyField type*/

DECLARE @PKTable varchar(1000)

DECLARE @PKName varchar(1000)

DECLARE @type varchar(1000)

DECLARE @prec int

IF CHARINDEX('.', @PrimaryKeyField) > 0

BEGIN

SET @PKTable = SUBSTRING(@PrimaryKeyField, 0, CHARINDEX('.',@PrimaryKeyField))

SET @PKName = SUBSTRING(@PrimaryKeyField, CHARINDEX('.',@PrimaryKeyField) + 1, LEN(@PrimaryKeyField))

END

ELSE

BEGIN

SET @PKTable = @Table

SET @PKName = @PrimaryKeyField

END

SELECT @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype

WHERE o.name = @PKTable AND c.name = @PKName

IF CHARINDEX('char', @type) > 0

SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

DECLARE @strPageSize varchar(50)

DECLARE @strStartRow varchar(50)

DECLARE @strWhere varchar(1000)

DECLARE @strGroupBy varchar(1000)

/**//*Default Sorting*/

IF @OrderBy IS NULL OR @OrderBy = ''

SET @OrderBy = @PrimaryKeyField

/**//*Default Page Number*/

IF @PageNumber < 1

SET @PageNumber = 1

/**//*Set paging variables.*/

SET @strPageSize = CAST(@PageSize AS varchar(50))

SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))

/**//*Set filter & group variables.*/

IF @Where IS NOT NULL AND @Where != ''

SET @strWhere = ' WHERE ' + @Where + ' '

ELSE

SET @strWhere = ''

IF @GroupBy IS NOT NULL AND @GroupBy != ''

SET @strGroupBy = ' GROUP BY ' + @GroupBy + ' '

ELSE

SET @strGroupBy = ''

/**//*Execute dynamic query*/

DECLARE @strSQL nvarchar(4000)

SET @strSql= ' SELECT @RecordCount = Count (*) FROM ' + @Table + @strWhere + ' ' + @strGroupBy

EXEC sp_executesql @strSql,N'@RecordCount int OUTPUT',@RecordCount OUTPUT--计算总页数

EXEC(

'DECLARE @PageSize int

SET @PageSize = ' + @strPageSize + '

DECLARE @PrimaryKeyField ' + @type + '

DECLARE @tblPK TABLE (

PK ' + @type + ' NOT NULL PRIMARY KEY

)

DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR

SELECT ' + @PrimaryKeyField + ' FROM ' + @Table + @strWhere + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy + '

OPEN PagingCursor

FETCH RELATIVE ' + @strStartRow + ' FROM PagingCursor INTO @PrimaryKeyField

SET NOCOUNT ON

WHILE @PageSize > 0 AND @@FETCH_STATUS = 0

BEGIN

INSERT @tblPK (PK) VALUES (@PrimaryKeyField)

FETCH NEXT FROM PagingCursor INTO @PrimaryKeyField

SET @PageSize = @PageSize - 1

END

CLOSE PagingCursor

DEALLOCATE PagingCursor

SELECT ' + @Field + ' FROM ' + @Table + ' JOIN @tblPK tblPK ON ' + @PrimaryKeyField + ' = tblPK.PK ' + @strWhere + ' ' + @strGroupBy + ' ORDER BY ' + @OrderBy

)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

创建一个用于测试的表

CREATE TABLE [dbo].[Employees] (

[EmployeesID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,

[LastName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[FirstName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

[BirthDate] [datetime] NULL ,

[Address] [nvarchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,

[City] [nvarchar] (18) COLLATE Chinese_PRC_CI_AS NULL ,

[HomePhone] [nvarchar] (24) COLLATE Chinese_PRC_CI_AS NULL ,

[Extension] [nvarchar] (4) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

GO

生成 1000000 条测试数据

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertTableData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[InsertTableData]

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE InsertTableData

AS

DECLARE @cnt bigint

SET @cnt = 0

WHILE @cnt < 1000000

BEGIN

INSERT INTO Employees(LastName,FirstName,BirthDate,Address,City,HomePhone,Extension)

Values

(

CAST('LastName ' +CONVERT(nvarchar(10), @cnt) as nvarchar(30)),

CAST('FirstName '+ CONVERT(nvarchar(10), @cnt) as nvarchar(30)),

GETDATE(),

CAST('Address IS No.'+ CONVERT(nvarchar(10), @cnt) as nvarchar(30)),

CAST('City '+ CONVERT(nvarchar(10), @cnt) as nvarchar(30)),

CAST('021-0000'+ LEFT(CONVERT(nvarchar(10), @cnt),4) as nvarchar(30)),

CAST('00' + LEFT(CONVERT(nvarchar(10), @cnt) ,1) as nvarchar(30))

)

SET @cnt = @cnt + 1

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有