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

80酷酷网    80kuku.com

　　asp.net|分页|控件|数据

存储过程 为 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

分享到
  • 微信分享
  • 新浪微博
  • QQ好友
  • QQ空间

上一篇ASP.net 2.0 自定义控件的开发之数据分页 第五章

下一篇ASP.NET 2.0 遍历配置文件的数据库连接字符串

相关文章

点击：