通用分页存储过程

 /// <summary>
        /// 通用分页存储过程
        /// </summary>
        /// <param name="connectionString">连接</param>
        /// <param name="tblName">要显示的表或多个表的连接</param>
        /// <param name="strGetFields"> 需要返回的列</param>
        /// <param name="fldName">排序的字段名</param>
        /// <param name="PageSize">页尺寸</param>
        /// <param name="PageIndex">页码</param>
        /// <param name="doCount">返回记录总数, 非 0 值则返回</param>
        /// <param name="OrderType">1降序 ,0 升序，其他 多字段order 条件</param>
        /// <param name="strWhere">查询条件 (注意: 不要加 WHERE)</param>
        /// <returns>查询当前页的数据集</returns>
        public static DataSet PageList(string connectionString, string tblName, string strGetFields, string fldName
             , int PageSize, int PageIndex, int doCount, int OrderType, string strWhere)
        {
            SqlParameter[] parameters ={ new SqlParameter("@tblName",SqlDbType.NVarChar,255),
                new SqlParameter("@strGetFields",SqlDbType.NVarChar,1000),
                new SqlParameter("@fldName",SqlDbType.NVarChar,255),
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@PageIndex",SqlDbType.Int),
                new SqlParameter("@doCount",SqlDbType.Bit),
                new SqlParameter("@OrderType",SqlDbType.Int),
                //new SqlParameter("@strWhere",SqlDbType.NVarChar,1500)};
                new SqlParameter("@strWhere",SqlDbType.VarChar,8000)};

            parameters[0].Value = tblName;
            parameters[1].Value = (strGetFields == null) ? "*" : strGetFields;
            parameters[2].Value = (fldName == null) ? "" : fldName;
            parameters[3].Value = PageSize;
            parameters[4].Value = PageIndex;
            parameters[5].Value = doCount;
            parameters[6].Value = OrderType;
            parameters[7].Value = (strWhere == null) ? "" : strWhere;

            DataSet ds = RunProcedureDS(connectionString, "PageList", parameters, "PageListTable");
            return ds;
        }

执行存储过程返回dataset方法：

public static DataSet RunProcedureDS(string connectionString, string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

分页存储过程代码如下：

USE [你的数据库名字]
GO
/****** Object:  StoredProcedure [dbo].[PageList]    Script Date: 02/27/2013 14:25:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE  [dbo].[PageList]
    (
        @tblName VARCHAR(255), -- 表名
        @strGetFields VARCHAR(1000) = '*', -- 需要返回的列
        @fldName VARCHAR(255)='', -- 排序的字段名
        @PageSize INT = 10, -- 页尺寸
        @PageIndex INT = 1, -- 页码
        @doCount BIT = 0, -- 返回记录总数, 非 0 值则返回
        @OrderType INT = 0, -- 设置排序类型, 非 0 值则降序
        @strWhere VARCHAR(1500) = '' -- 查询条件 (注意: 不要加 WHERE)
    )
AS

    SET NOCOUNT ON

        DECLARE @strSQL VARCHAR(5000) -- 主语句
        --DECLARE @strTmp VARCHAR(110) -- 临时变量
        DECLARE @strOrder VARCHAR(400) -- 排序类型
       
        IF(@doCount != 0)
            BEGIN
                IF @strWhere !=''
                    SET @strSQL = 'SELECT COUNT(1) AS Total FROM [' + @tblName + '] WHERE '+@strWhere
                ELSE
                    SET @strSQL = 'SELECT COUNT(1) AS Total FROM [' + @tblName + ']'
            END
            --以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况
        ELSE
            BEGIN
				IF @fldName !=''
					BEGIN
						IF @OrderType = 0
							BEGIN                       
								SET @strOrder = ' ORDER BY [' + @fldName +'] ASC'
								--如果@OrderType 是1，就执行降序，这句很重要
							END
						ELSE If @OrderType = 1
							BEGIN
								SET @strOrder = ' ORDER BY [' + @fldName +'] DESC'
							END
						ELSE --如果@OrderType 是其他，就不额外添加后缀，采用默认
							BEGIN
								SET @strOrder = ' ORDER BY ' + @fldName +' '
							END
					END
                
                 IF @strWhere != ''
                    SET @strSQL = 'SELECT '+@strGetFields+ ' FROM [' + @tblName + '] WHERE ' + @strWhere + ' ' + @strOrder
                 ELSE
                    SET @strSQL = 'SELECT '+@strGetFields+ ' FROM ['+ @tblName + '] '+ @strOrder
                        --如果是第一页就执行以上代码，这样会加快执行速度
				
				
            END

		declare @pageCount int --总页数
		declare @recordCount int --总记录数
		declare @SearchTime int   --执行时间

		EXEC Paging @strSQL,@PageIndex,@PageSize,@pageCount output,@recordCount output,@SearchTime output
		select @pageCount as 'pageCount',@recordCount as 'recordCount',@SearchTime as 'SearchTime'
		select @strSQL 
	SET NOCOUNT OFF

------------------------------------------------------------------------另外一种执行sql语句的分页存储过程--------------------------------------------------------------------

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[Paging]    Script Date: 02/27/2013 15:43:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE  [dbo].[Paging] 
@sql nvarchar(4000),--要执行的sql语句
@page int=1, --要显示的页码
@pageSize int,--每页的大小
@pageCount int=0 out,--总页数
@recordCount int=0 out,--总记录数
@SearchTime int=0 out
as
declare @usetime datetime
set @usetime=getdate()
set nocount on
begin
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @recordCount = @pageCount
select @pagecount=ceiling(1.0*@pagecount/@pagesize)
,@page=(@page-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@page,@pagesize 
exec sp_cursorclose @p1
set  @SearchTime=datediff(ms,@usetime,getdate())
print @SearchTime
end

就这样我们就可以利用存储过程实现分页了！