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