存储过程分页

CREATE PROCEDURE AspNetPager 
( 
 @TableName  nvarchar(50),   -- 表名 
 @ReturnFields nvarchar(2000) = '*', -- 需要返回的列  
 @PageSize  int = 10,    -- 每页记录数 
 @PageIndex  int = 1,    -- 当前页码 
 @Where   nvarchar(2000) = '',  -- 查询条件 
 @Orderfld  nvarchar(2000),   -- 排序字段名 最好为唯一主键 
 @OrderType  int = 1     -- 排序类型 1:降序 其它为升序 
  
) 
AS 
    DECLARE @TotalRecord int 
 DECLARE @TotalPage int 
 DECLARE @CurrentPageSize int 
    DECLARE @TotalRecordForPageIndex int 
    DECLARE @OrderBy nvarchar(255) 
    DECLARE @CutOrderBy nvarchar(255) 
  
 if @OrderType = 1 
  BEGIN 
   set @OrderBy = ' Order by ' + REPLACE(@Orderfld,',',' desc,') + ' desc ' 
   set @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' asc,') + ' asc ' 
  END 
 else 
  BEGIN 
   set @OrderBy = ' Order by ' +  REPLACE(@Orderfld,',',' asc,') + ' asc ' 
   set @CutOrderBy = ' Order by '+ REPLACE(@Orderfld,',',' desc,') + ' desc '    
  END 
  
  
        -- 记录总数 
 declare @countSql nvarchar(4000)   
 set @countSql='SELECT @TotalRecord=Count(*) From '+@TableName+' '+@Where 
 execute sp_executesql @countSql,N'@TotalRecord int out',@TotalRecord out 
  
 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 
 exec('SELECT * FROM 
  (SELECT TOP '+@CurrentPageSize+' * FROM 
   (SELECT TOP '+@TotalRecordForPageIndex+' '+@ReturnFields+' 
   FROM '+@TableName+' '+@Where+' '+@OrderBy+') TB2 
  '+@CutOrderBy+') TB3 
              '+@OrderBy) 
 -- 返回总页数和总记录数 
 SELECT @TotalPage as PageCount,@TotalRecord as RecordCount 

 

 

 public static DataTable GetPagerData(AspNetPager PagerControl,string Sql,string OrderField,OrderType Type)
    {
        DataTable dt = new DataTable();
        int RecordCount = 0;
        int TempIndex = 0;
        int TempEndIndex = 0;
        string TableName = String.Empty;
        string Where = String.Empty;
        string Cells = String.Empty;
        Sql = Sql.ToLower();
        using(SqlConnection conn=GetSqlConnection())
        {
            try
            {
                
                TempIndex = Sql.IndexOf("select") + 6;
                TempEndIndex = Sql.IndexOf("from");
                Cells = Sql.Substring(TempIndex, TempEndIndex - TempIndex);
                TempIndex = Sql.IndexOf("where");
                if (TempIndex != -1)
                {
                    TableName = Sql.Substring(TempEndIndex + 4, TempIndex - (TempEndIndex + 4)).Trim();
                    Where = Sql.Substring(TempIndex);
                }
                else
                {
                    TableName = Sql.Substring(TempEndIndex + 4).Trim();
                }
                SqlCommand cmd = new SqlCommand("AspNetPager", conn);
                cmd.CommandType=CommandType.StoredProcedure;
                // 设置参数
                cmd.Parameters.Add("@TableName", SqlDbType.NVarChar, 500).Value = TableName;
                cmd.Parameters.Add("@ReturnFields", SqlDbType.NVarChar, 500).Value = "*";
                cmd.Parameters.Add("@Where", SqlDbType.NVarChar, 500).Value = Where;
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = PagerControl.CurrentPageIndex;
                cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = PagerControl.PageSize;
                cmd.Parameters.Add("@Orderfld", SqlDbType.NVarChar, 200).Value = OrderField;
                cmd.Parameters.Add("@OrderType", SqlDbType.Int).Value = Type;
               
                // 执行
                conn.Open();
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                //dt.Load(dr);
                for (int i = 0; i < dr.FieldCount; i++)
                {
                    dt.Columns.Add(dr.GetName(i));
                }
                while (dr.Read())
                {
                    DataRow Row = dt.NewRow();
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        Row[i] = dr[i].ToString();
                    }
                    dt.Rows.Add(Row);
                }
                // 取记录总数 及页数
                if (dr.NextResult())
                {
                    if (dr.Read())
                    {
                        RecordCount = Convert.ToInt32(dr["RecordCount"]);
                    }
                }
                PagerControl.RecordCount = RecordCount;
                cmd.Dispose();
            }
            catch (Exception)
            {
                throw;
            }
        }
        return dt;
    }