ASP.net 2.0 自定义控件的开发之数据分页 第四章
存储过程 为 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
免责声明：本文为网络用户发布，其观点仅代表作者个人观点，与本站无关，本站仅提供信息存储服务。文中陈述内容未经本站证实，其真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
