分页排序通用存储过程带C#代码

分享一个通用的带分页、带排序、带条件、带分组的全功能的存储过程，而且返回总页数，总记录、另外附上C#调用代码

存储过程:
   

[SQL] 纯文本查看 复制代码

USE [ATDBLot]
GO
/****** Object:  StoredProcedure [dbo].[Com_GroupPagination]    Script Date: 07/15/2013 11:48:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Com_GroupPagination] --分页通用存储过程支持分组、排序
@TotalCount INT OUTPUT,						--总记录数
@TotalPage INT OUTPUT,						--总页数
@Table NVARCHAR(1000),						--查询的表名（可多表，例如：Person p LEFT JOIN TE a ON a.PID=p.Id ）
@Column NVARCHAR(1000),						--查询的字段，可多列或者为*
@OrderColumn NVARCHAR(100),					--排序字段
@GroupColumn NVARCHAR(150),					--分组字段
@PageSize INT,								--每页记录数
@CurrentPage INT,							--当前页数
@Group TINYINT,								--是否使用分组，0否1是
@Condition NVARCHAR(4000),					--查询条件
@OrderType INT								--排序方式  1降序 0升序
AS
DECLARE @PageCount     INT,					--总页数
        @strSql        NVARCHAR(4000),		--主查询语句
        @strTemp       NVARCHAR(2000),		--临时变量
        @strCount      NVARCHAR(1000),		--统计语句
        @strOrderType  NVARCHAR(1000)		--排序语句
BEGIN
SET @PageCount = @PageSize * (@CurrentPage -1)
IF @OrderType=1
	BEGIN
		SET @strOrderType = ' ORDER BY ' + @OrderColumn + ' desc '	
	END
ELSE
	BEGIN
		SET @strOrderType = ' ORDER BY ' + @OrderColumn + ' asc '
	END

IF @Condition != ''
BEGIN
    IF @CurrentPage = 1
    BEGIN
        IF @GROUP = 1
        BEGIN
            SET @strCount = 'SELECT  @TotalCount=COUNT(*) FROM ' + @Table
                + ' WHERE ' + @Condition + ' GROUP BY ' + @GroupColumn
            
            SET @strCount = @strCount + ' SET @TotalCount=@@ROWCOUNT'
            SET @strSql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Column 
                + ' FROM ' + @Table + ' WHERE ' + @Condition + 
                ' GROUP BY ' + @GroupColumn + ' ' + @strOrderType
        END
        ELSE
        BEGIN
            SET @strCount = 'SELECT  @TotalCount=COUNT(*) FROM ' + @Table
                + ' WHERE ' + @Condition
            
            SET @strSql = 'SELECT TOP ' + STR(@PageSize) + ' ' + @Column 
                + ' FROM ' + @Table + ' WHERE ' + @Condition + ' ' + @strOrderType
        END
    END
    ELSE
    BEGIN
        IF @GROUP = 1
        BEGIN
            SET @strCount = 'SELECT  @TotalCount=COUNT(*) FROM ' + @Table
                + ' WHERE ' + @Condition + ' GROUP BY ' + @GroupColumn
            
            SET @strCount = @strCount + ' SET @TotalCount=@@ROWCOUNT'
            SET @strSql = 'SELECT  * FROM (SELECT ' + @Column 
                + ',ROW_NUMBER() OVER(' + @strOrderType + 
                ') AS NUM FROM ' + @Table + ' WHERE ' + @Condition + 
                ' GROUP BY ' + @GroupColumn + 
                ') AS T WHERE NUM BETWEEN ' + STR(@PageCount + 1) + 
                ' AND ' + STR(@PageCount + @PageSize)
        END
        ELSE
        BEGIN
            SET @strCount = 'SELECT  @TotalCount=COUNT(*) FROM ' + @Table
                + ' WHERE ' + @Condition
            
            SET @strSql = 'SELECT  * FROM (SELECT ' + @Column 
                + ',ROW_NUMBER() OVER(' + @strOrderType + 
                ') AS NUM FROM ' + @Table + ' WHERE ' + @Condition + 
                ') AS T WHERE NUM BETWEEN ' + STR(@PageCount + 1) + 
                ' AND ' + STR(@PageCount + @PageSize)
        END
    END
END
print @strSql
EXEC sp_executesql @strCount,
     N'@TotalCount INT OUTPUT',
     @TotalCount OUTPUT
IF @TotalCount%@PageSize = 0
BEGIN
    SET @TotalPage = @TotalCount / @PageSize
END
ELSE
BEGIN
    SET @TotalPage = @TotalCount / @PageSize + 1
END
SET NOCOUNT ON
EXEC (@strSql)
END
SET NOCOUNT OFF

C#调用代码分享：

返回DataTable类型的

[C#] 纯文本查看 复制代码

        /// <summary>
        /// 通用分页结果存储过程
        /// </summary>
        public DataTable GetPageExecuteProcedureQuery(string sp, SqlParameter[] parameter, out int count, out int page)
        {
            dbConnection = new SqlConnection(SQLCONNECTSTR);
            SqlCommand command = new SqlCommand(sp, dbConnection);
            count = 0;//数据总数
            page = 0;//总页数
            SqlDataAdapter da = new SqlDataAdapter(command);
            try
            {
                dbConnection.Open();               
                if (parameter != null)
                {
                    command.CommandType = System.Data.CommandType.StoredProcedure;
                    command.Parameters.AddRange(parameter);  
                    command.Parameters.Add("@TotalCount", SqlDbType.Int, 32);
                    command.Parameters.Add("@TotalPage", SqlDbType.Int, 32);
                    command.Parameters["@TotalCount"].Direction = System.Data.ParameterDirection.Output;
                    command.Parameters["@TotalPage"].Direction = System.Data.ParameterDirection.Output;                             
                }
                da.Fill(ds);
                count = Convert.ToInt32(command.Parameters["@TotalCount"].Value);
                page = Convert.ToInt32(command.Parameters["@TotalPage"].Value);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally 
            {
                da.Dispose();
            }
            return ds;
        }

真不错，谢谢分享。。。