分页储存过程

---------------------------------------------------------------------存储过程------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetDatasByPageIndex]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[sp_GetDatasByPageIndex]
    @PageSize    int,        --每页的行数
    @PageIndex   int,        --1 代表第一页
    @Col         varchar(300),    --要显示的字段
    @Table       varchar(500),    --所用到的表,复条的话就写from与where之间的内容
    @Where       varchar(500)='''',    --所用到的条件
    @GroupBy     varchar(500)='''',    --分组字段
    @OrderKey    varchar(100),    --排序字段
    @OrderType   varchar(20)=''ASC'',   --排序方式
    @IsReCount   BIT = 1,            -- 是否返回记录总数, 非 0 值则返回   
    @RecordCount int output       --记录总数
as

set nocount on

 declare @cmdstr varchar(2000) --执行的结果集sql语句
 declare @strSql nvarchar(2000) --执行的结果记录总数sql语句
if @IsReCount != 0
begin
    set @strSql = ''SELECT @RecordCount=COUNT(*) FROM '' + @Table
    if @Where <> ''''
 set @strSql = @strSql +'' where '' + @Where
    print @strSql
    exec sp_executesql @strSql  ,N''@RecordCount varchar(1000) out'',@RecordCount output
end 
        set @cmdstr=''select top ''
        set @cmdstr=@cmdstr+convert(nvarchar,@PageSize)
         if @PageIndex=1
   set @cmdstr=@cmdstr+'' ''+@Col+'' from ''+@Table+'' where ''+@OrderKey+''>=0''
         else  if @PageIndex>1
           begin
            if @OrderType=''DESC''
               set @cmdstr=@cmdstr+'' ''+@Col+'' from ''+@Table+'' where ''+@OrderKey+''<''
                           +''(select min (''+@OrderKey+'') from (select top ''
            if @OrderType=''ASC''
               set @cmdstr=@cmdstr+'' ''+@Col+'' from ''+@Table+'' where ''+@OrderKey+''>''
                           +''(select max (''+@OrderKey+'') from (select top ''
            set @cmdstr=@cmdstr+convert(nvarchar,(@PageIndex-1)*@PageSize)+'' ''+@Col+'' from ''+@Table
   if @Where<>''''
    set @cmdstr=@cmdstr+''  where   ''+@Where
   if @GroupBy<>''''
                set @cmdstr=(@cmdstr+'' group by ''+@GroupBy)
   if @OrderKey<>''''
       set @cmdstr=@cmdstr+''  order by ''+@OrderKey+'' ''+@OrderType
      set @cmdstr=@cmdstr +'') as t) ''
     end
         print @cmdstr
        if @Where<>''''
            set @cmdstr=(@cmdstr+''  and ''+@Where)
        if @GroupBy<>''''
            set @cmdstr=(@cmdstr+'' group by ''+@GroupBy)
        if @OrderKey<>''''
            set @cmdstr=(@cmdstr+'' order by ''+@OrderKey+'' ''+@OrderType)
       print @cmdstr
  
exec(@cmdstr)
set nocount off
   

'
END
GO

----------------------------------------------------------------------后台代码-----------------------------------------------------------------------------------------

 /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pageSize">每页显示数据个数</param>
        /// <param name="pageIndex">页码号</param>
        /// <param name="col">要获取的字段</param>
        /// <param name="table">查询的表格</param>
        /// <param name="where">查询条件</param>
        /// <param name="groupBy">分组条件</param>
        /// <param name="orderKey">排序条件</param>
        /// <param name="orderType">排序方式</param>
        /// <param name="isReCount">是否返回记录总数1.返回，0.不返回</param>
        /// <returns>返回Datatable</returns>
        public DataTable GetValues(int pageSize, int pageIndex, string col, string table, string where, string groupBy, string orderKey, string orderType, int isReCount,out int rNum)
        {
            try
            {
                if (orderKey == null || orderKey == "")
                {
                    rNum = 0;
                    return null;
                }
                int iNum = 0;
                DataTable dt = new DataTable();
                string sProcName = "sp_GetDatasByPageIndex";//存储过程名称
                string strCon = System.Configuration.ConfigurationManager.ConnectionStrings["ScciDBConnectionString"].ConnectionString.ToString();//数据库连接字符串
                SqlConnection con = new SqlConnection(strCon);
                con.Open();
                SqlCommand com = new SqlCommand(sProcName, con);
                com.CommandType = CommandType.StoredProcedure;
                #region  给存储过程参数赋值
                //给输入参数赋值
                com.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;
                com.Parameters.Add("@pageIndex", SqlDbType.Int).Value = pageIndex;
                com.Parameters.Add("@col", SqlDbType.VarChar).Value = col;
                com.Parameters.Add("@table", SqlDbType.VarChar).Value = table;
                com.Parameters.Add("@where", SqlDbType.VarChar).Value = where;
                com.Parameters.Add("@groupBy", SqlDbType.VarChar).Value = groupBy;
                com.Parameters.Add("@orderKey", SqlDbType.VarChar).Value = orderKey;
                com.Parameters.Add("@orderType", SqlDbType.VarChar).Value = orderType;
                com.Parameters.Add("@IsReCount", SqlDbType.Bit).Value = isReCount;
                //定义输出参数
                com.Parameters.Add("@RecordCount", SqlDbType.Int, 10);
                //设置参数的类型为输出参数
                com.Parameters["@RecordCount"].Direction = ParameterDirection.Output;
                #endregion
                SqlDataAdapter sda = new SqlDataAdapter(com);//执行查询
                sda.Fill(dt);
                //得到输出参数的值(记录总数)
                if (isReCount == 1)
                {
                    iNum = int.Parse(com.Parameters["@RecordCount"].Value.ToString());
                }
                //释放资源
                sda.Dispose();
                com.Dispose();
                con.Close();
                rNum = iNum;
                return dt;
            }
            catch (Exception e)
            {
                throw e;
            }
        }