通用分页存储过程的使用

看着同事写的通用分页存储过程觉得还不错，分享给大家

1、存储过程

USE [test]

GO

/****** Object:  StoredProcedure [dbo].[ads_GetAll]    Script Date: 07/04/2013 16:00:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo]. [ads_GetAll]   -- 通用分页存储过程

@TableName varchar (50),          --表名

@Fields varchar (5000),      --字段名 (全部字段为*)

@OrderField varchar (5000),          --排序字段( 必须!支持多字段 )

@sqlWhere varchar (5000), --条件语句( 不用加where)

@pageSize int ,                     --每页多少条记录

@pageIndex int = 1 ,             --指定当前为第几页

@COUNT int output             --返回总页数

as

begin

     Begin Tran --开始事务

     Declare @sql nvarchar( 4000);

     Declare @totalRecord int;    

     --计算总记录数

        

     if (@SqlWhere ='' or @sqlWhere =NULL)

         set @sql = 'select @totalRecord = count(*) from ' + @TableName

     else

         set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere

     EXEC sp_executesql @sql, N'@totalRecord int OUTPUT',@totalRecord OUTPUT--计算总记录数       

   

     --计算总页数

     select @COUNT =CEILING(( @totalRecord+0.0 )/@PageSize)

     if (@SqlWhere ='' or @sqlWhere =NULL)

         set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName

     else

         set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere   

       

   

     --处理页数超出范围情况

     if @PageIndex <=0

         Set @pageIndex = 1

   

     if @pageIndex >@COUNT

         Set @pageIndex = @COUNT

      --处理开始点和结束点

     Declare @StartRecord int

     Declare @EndRecord int

   

     set @StartRecord = (@pageIndex- 1)*@PageSize + 1

     set @EndRecord = @StartRecord + @pageSize - 1

     --继续合成sql 语句

     set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar (50), @StartRecord) + ' and ' +   Convert(varchar (50), @EndRecord)

   

     Exec(@Sql )

     ---------------------------------------------------

     If @@Error <> 0

       Begin

         RollBack Tran

         Return - 1

       End

      Else

       Begin

         Commit Tran

         Return @totalRecord ---返回记录总数

       End   

end

2、数据处理层中：

     /// <summary>

        /// 获取分析师信息并分页

        /// </summary>

        /// <param name="pageSize">每页页数</param>

        /// <param name="count">总个数</param>

        /// <returns></returns>

        public List<Ads_Analyst> ads_Analyst_GetAll(int pageIndex, int pageSize, string where, out int count)

        {

            List<Ads_Analyst> list = new List<Ads_Analyst>();

            IDataReader reader = null;

            IDataParameter[] parameters =  

            { 

               Helper.GetParameter("@COUNT", DbType.Int32, ParameterDirection.Output),

               Helper.GetParameter("@ReturnValue",DbType.Int32,ParameterDirection.ReturnValue),

               Helper.GetParameter("@PAGESIZE", DbType.Int16,pageSize),

               Helper.GetParameter("@PAGEINDEX", DbType.Int16,pageIndex),

               Helper.GetParameter("@sqlWhere", DbType.String,where),

               Helper.GetParameter("@TableName", DbType.String,"ads_Analyst"),

               Helper.GetParameter("@Fields", DbType.String,"[ID],[AnalystName],[Picture],[JobTitle],[Trade],[Introduction],[Status],[AddTime],[EditTime]"),

               Helper.GetParameter("@OrderField", DbType.String,"AddTime desc"),

            };

            try

            {

                reader = Helper.ExecuteReader

                (

                    ConnectToADS,

                    CommandType.StoredProcedure,

                    "ads_GetAll",

                    parameters

                );

                while (reader.Read())

                {

                    Ads_Analyst m = new Ads_Analyst();

                    m.ID = Field.GetInt32(reader, "ID");

                    m.AnalystName = Field.GetString(reader, "AnalystName");

                    m.Picture = Field.GetString(reader, "Picture");

                    m.JobTitle = Field.GetString(reader, "JobTitle");

                    m.Trade = Field.GetString(reader, "Trade");

                    m.Introduction = Field.GetString(reader, "Introduction");

                    m.AddTime = Field.GetDateTime(reader, "AddTime");

                    m.EditTime = Field.GetDateTime(reader, "EditTime");

                    m.Status = Field.GetInt32(reader, "Status");

                    list.Add(m);

                }

                reader.Close();

                count = (int)parameters[1].Value;

                return list;

            }

            catch (System.Exception ex)

            {

                Logger.Error("获取分析师信息并分页出错", ex);

                throw new DataAccessException("获取分析师信息并分页出错", ex);

            }

            finally

            {

                if (reader != null)

                {

                    reader.Close();

                }

            }

        }