建立NHibernateTest的Windows解决方案

花一段时间写了个分页存储过程
可适应90%需求

现已经应用于多个项目

 

代码

/*********************************************************************************
*      Copyright (C) 2010 www.cnblogs.com/immensity,All Rights Reserved             *
*      Function:  PROC_Pagination                                                 *
*      Description:                                                              *
*             高效率通用分页存储过程                                             *
*      Author:                                                                   *
*             张盼(ZP)                                                           *
*             [e-mail]                                               *
*             http://www.cnblogs.com/immensity                                   *
*      Finish DateTime:                                                          *
*             2010/04/08                                                         *
*      History:                                                                     *
*             2008/02/07                                                         *         
*       Example:                                                                     *
*              EXEC PROC_Pagination @TableName='表名',@Orderfld='排序列名'        *           
*********************************************************************************/
CREATE PROCEDURE [dbo].[PROC_Pagination]
(
    @TableName        VARCHAR(50),            --表名
    @Where            VARCHAR(1000) = '',        --查询条件
    @ReturnFields    VARCHAR(500) = '*',        --返回的列
    @Orderfld        VARCHAR(100),            --排序的列(多页以,分隔)
    @OrderType        BIT = true,                --排序模式(默认为true,表示降序)
    @FKColumn        VARCHAR(50) = '',        --主键列
    @PageIndex        INT = 1,                --行索引
    @PageSize        INT    = 10                --页大小
)
AS
BEGIN
    --当前页索引处理(争对第二和第三种情况,前台使用时@PageIndex起始值为1)  开始
    IF(@PageIndex<1)
        SET @PageIndex=1
    ELSE
        SET @PageIndex=@PageIndex-1
    --当前页索引处理(争对第二和第三种情况,前台使用时@PageIndex起始值为1)  结束
    DECLARE  @SQL NVARCHAR(2000)--动态SQL语句
    DECLARE @OrderColumnCrux VARCHAR(50)--排序核心  值如下:>(SELECT MAX( 或 <(SELECT MIN(
    SET @OrderColumnCrux='<(SELECT MIN('--默认为降序模式
    SET @ReturnFields=' '+@ReturnFields+' '--避免错误  在查询的列的前后加一个空格
    DECLARE @OrderBy NVARCHAR(255)            --排序
    DECLARE @RowCount NVARCHAR(1000)--拼接查询行数的SQL语句

    --处理排序开始
    IF(@Orderfld IS NOT NULL AND @Orderfld<>'')
    BEGIN

        --降序
        IF(@OrderType=1)
        BEGIN
            SET @OrderBy=' ORDER BY ' + REPLACE(@Orderfld,',',' DESC,') + ' DESC '
            SET @OrderColumnCrux='<(SELECT MIN('
        END
        ELSE--否则为降序
        BEGIN
            SET @OrderBy = ' ORDER BY ' + REPLACE(@Orderfld,',',' ASC,') + ' ASC '
            SET @OrderColumnCrux='>(SELECT MAX('
        END
    END    

    --当无主键时候(情况一)
    IF(@FKColumn IS NULL OR @FKColumn = '')
    BEGIN
        --当前页索引处理(争对当前情况,前台使用时@PageIndex起始值为1)  开始
        IF(@PageIndex<=0)
            SET @PageIndex=1
        ELSE
            SET @PageIndex=@PageIndex+1
        --当前页索引处理(争对当前情况,前台使用时@PageIndex起始值为1)  结束
        DECLARE @TotalRecord int                --记录总数
        DECLARE @TotalPage int                    --页总数
        DECLARE @CurrentPageSize int            --当前页面数据数量
        DECLARE @TotalRecordForPageIndex int     
        DECLARE @CutOrderBy nvarchar(255)    
        DECLARE @CurrentWhere nvarchar(1000)--当前情况下的where条件
        --降序
        IF @OrderType = 1
        BEGIN
            SET @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' asc,') + ' asc '
        END
        ELSE
        BEGIN
            SET @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' desc,') + ' desc '    
        END
        --计算
        SET @TotalPage=(@TotalRecord-1)/@PageSize+1
        SET @CurrentPageSize=@PageSize
            IF(@TotalPage=@PageIndex)
            BEGIN
                SET @CurrentPageSize=@TotalRecord%@PageSize
                IF(@CurrentPageSize=0)
                    SET @CurrentPageSize=@PageSize
            END
        -- 返回记录
        set @TotalRecordForPageIndex=@PageIndex*@PageSize
        --查询条件
        SET @CurrentWhere=''
        IF(@Where IS NOT NULL AND @Where <>'')
        BEGIN
            SET @CurrentWhere=' WHERE 1=1 AND '+@Where
        END
        SET @SQL='SELECT * FROM
        (SELECT TOP '+STR(@CurrentPageSize)+' * FROM
            (SELECT TOP '+STR(@TotalRecordForPageIndex)+' '+@ReturnFields+'
            FROM '+@TableName+' '+@CurrentWhere+' '+@OrderBy+') TB2
        '+@CutOrderBy+') TB3
              '+@OrderBy
        --执行查询
        EXEC SP_EXECUTESQL @SQL
        --为查询表行数准备数据
        IF(@Where IS NOT NULL AND @Where <>'')
        BEGIN
            SET @RowCount='SELECT COUNT(*) FROM '+@TableName+' WHERE 1=1 AND '+@Where
        END
        ELSE
        BEGIN
            SET @RowCount='SELECT COUNT(*) FROM '+@TableName
        END
        EXEC SP_EXECUTESQL @RowCount--执行查询   得到表行总数
    END
    --主键与排序字段相同(情况二)
    ELSE IF(@Orderfld=@FKColumn)
    BEGIN        
        IF(@PageIndex IS NOT NULL AND @PageIndex<>0)
        BEGIN
            --当查询条件不为空
            IF(@Where IS NOT NULL AND @Where <>'')
            BEGIN
                --处理SQL语句
                SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
                +' WHERE('+@FKColumn+@OrderColumnCrux+@FKColumn+') FROM (SELECT TOP '+Convert(varchar(5),(@PageIndex*@PageSize))
                +' '+@FKColumn+' FROM '+@TableName+' WHERE 1=1 AND '+@Where+' '+@OrderBy+') AS T)) AND '+@Where+' '+@OrderBy
            END
            ELSE
            BEGIN
                --处理SQL语句
                SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
                +' WHERE('+@FKColumn+@OrderColumnCrux+@FKColumn+') FROM (SELECT TOP '+Convert(varchar(5),(@PageIndex*@PageSize))
                +' '+@FKColumn+' FROM '+@TableName+' '+@OrderBy+') AS T)) '+@OrderBy
            END    
        END
        ELSE
        BEGIN
            IF(@Where IS NOT NULL AND @Where <>'')--当查询条件不为空
            BEGIN
                --处理SQL语句
                SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
                +' WHERE 1=1 AND '+@Where+' '+@OrderBy
            END
            ELSE
            BEGIN
                --处理SQL语句
                SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
                +' '+@OrderBy
            END
        END
        EXEC SP_EXECUTESQL @SQL--执行查询

        --为查询表行数准备数据
        IF(@Where IS NOT NULL AND @Where <>'')
        BEGIN
            SET @RowCount='SELECT COUNT(*) FROM '+@TableName+' WHERE 1=1 AND '+@Where
        END
        ELSE
        BEGIN
            SET @RowCount='SELECT COUNT(*) FROM '+@TableName
        END
        EXEC SP_EXECUTESQL @RowCount--执行查询   得到表行总数
    END
    --主键与排序字段不同(情况三)
    ELSE
    BEGIN
        IF(@Where IS NOT NULL AND @Where <>'')--当查询条件不为空
                BEGIN
                SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
                +' WHERE '+@FKColumn+' NOT IN'
                +' (SELECT TOP '+Convert(varchar(5),(@PageIndex*@PageSize))+' '+@FKColumn+' FROM '+@TableName
                +' WHERE 1=1 AND '+@Where+' '+@OrderBy+') AND '+@Where+' '+@OrderBy
                EXEC SP_EXECUTESQL @SQL--执行查询
                SET @RowCount='SELECT COUNT(*) FROM '+@TableName+' WHERE 1=1 AND '+@Where
                EXEC SP_EXECUTESQL @RowCount--执行查询   得到表行总数
        END
        ELSE
        BEGIN
                SET @SQL='SELECT TOP '+Convert(varchar(5),@PageSize)+' '+@ReturnFields+' FROM '+@TableName
                +' WHERE '+@FKColumn+' NOT IN'
                +' (SELECT TOP '+Convert(varchar(5),(@PageIndex*@PageSize))+' '+@FKColumn+' FROM '+@TableName
                +' '+@OrderBy+') '+@OrderBy
                EXEC SP_EXECUTESQL @SQL--执行查询
                SET @RowCount='SELECT COUNT(*) FROM '+@TableName
                EXEC SP_EXECUTESQL @RowCount--执行查询   得到表行总数
        END
    END
END

 

 

此存储过程可产生3形式中SQL语句

形式一(主要实现对无主键表的分页,可以任意字段排序,但是效率很低)

exec [PROC_Pagination] 'articles',@Orderfld='articleid',@PageIndex=2

SELECT * FROM (SELECT TOP 10 * FROM
(SELECT TOP 20  * FROM articles ORDER BY articleid ASC ) TB2
Order by articleid desc ) TB3
ORDER BY articleid ASC 

 

 

形式二(适应大部分分页操作,可以任意字段排序,但是当数据量很大时,达到百万级时,效率会有所影响)

exec [PROC_Pagination] 'articles',@Orderfld='Title',@PageIndex=2,@FKColumn='articleid'

SELECT TOP 10  *  FROM articles WHERE articleid NOT IN 
(SELECT TOP 10 articleid FROM articles  ORDER BY articleid ASC )  
ORDER BY articleid ASC 

 

 

形式三(适合以唯一主键排序且数据量很大时)

exec [PROC_Pagination] 'articles',@Orderfld='articleid',@PageIndex=2,@FKColumn='articleid'

SELECT TOP 10  *  FROM articles WHERE(articleid>(SELECT MAX(articleid) 
FROM (SELECT TOP 10 articleid FROM articles  ORDER BY articleid ASC ) AS T))  
ORDER BY articleid ASC 

 

 

点击下载