sql server分页

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[Pg_Paging]
    @Tables varchar(500), --表名,多红表是请使用 tA a inner join tB b On a.AID = b.AID
    @PK varchar(100),    --主键，可以带表头 a.AID
    @Sort varchar(200) = '', --排序字段
    @PageNumber int = 1,    --开始页码
    @PageSize int = 10,        --页大小
    @Fields varchar(1000) = '*',--读取字段
    @Filter varchar(1000) = NULL,--Where条件
    @Group varchar(1000) = NULL  --分组
AS
--
--select * from GL_NEWS order by GN_UPDATE_DATE DESC
--exec Pg_Paging @Tables = 'tb_NewsInfo', @PK = 'News_ID', @Sort = 'News_ID DESC', @PageNumber = 2, @PageSize = 15,@Fields = '*', @Group = '', @isCount = 0
DECLARE @strFilter varchar(2000)
declare @sql varchar(8000)
declare @sqlCount varchar(4000)
IF @Filter IS NOT NULL AND @Filter != ''
  BEGIN
   SET @strFilter = ' WHERE ' + @Filter + ' '
  END
ELSE
  BEGIN
   SET @strFilter = ''
  END
begin
if @Sort = ''
  set @Sort = @PK + ' DESC '

IF @PageNumber < 1
  SET @PageNumber = 1

if @PageNumber = 1 --第一页提高性能
begin 
  set @sql = 'select top ' + str(@PageSize) +' '+@Fields+ '  from ' + @Tables + ' ' + @strFilter + ' ORDER BY  '+ @Sort
end 
else
  begin   
   DECLARE @START_ID varchar(50)
DECLARE @END_ID varchar(50)
SET @START_ID = convert(varchar(50),(@PageNumber - 1) * @PageSize + 1)
SET @END_ID = convert(varchar(50),@PageNumber * @PageSize)
    set @sql =  ' SELECT '+@Fields+ '
   FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum, 
     '+@Fields+ '
      FROM '+@Tables+' ' +@strFilter+') AS D
   WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY '+@Sort
  END

END
set @sqlCount = 'select count(0) from '+@Tables + ' '+@strFilter
set @sql = @sqlCount +'; '+ @sql+';'
--print @sql
EXEC(@sql)

 此分页从网上找来的。

c#：

        public static DataSet GetList(int pageNumber,int pageSize,string areaName)
        {
            string wheres = string.Empty;
            if (!string.IsNullOrEmpty(areaName))
            {
                wheres = " AreaName like '%" +areaName +"%'";
            }
            string files = "ID,AreaName,AreaCode,SimpleCode,AreaType";
            return GetListPage("AreaDivide", "ID", "ID desc", pageNumber, pageSize,files ,wheres); 
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="pk">主键</param>
        /// <param name="sort">排序字段</param>
        /// <param name="pageNumber">开始页码</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="files">读取字段</param>
        /// <param name="wheres">Where条件</param>
        /// <returns></returns>
        public static DataSet GetListPage(string tableName, string pk, string sort, int pageNumber, int pageSize, string files, string wheres)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            SqlParameter sqlparam;

            sqlparam = new SqlParameter("@Tables", SqlDbType.VarChar, 500);
            sqlparam.Value = tableName;
            sqlParams.Add(sqlparam);

            sqlparam = new SqlParameter("@PK", SqlDbType.VarChar, 100);
            sqlparam.Value = pk;
            sqlParams.Add(sqlparam);

            sqlparam = new SqlParameter("@Sort", SqlDbType.VarChar, 200);
            sqlparam.Value = sort;
            sqlParams.Add(sqlparam);

            sqlparam = new SqlParameter("@PageNumber", SqlDbType.Int);
            sqlparam.Value = pageNumber;
            sqlParams.Add(sqlparam);

            sqlparam = new SqlParameter("@PageSize", SqlDbType.Int);
            sqlparam.Value = pageSize;
            sqlParams.Add(sqlparam);

            sqlparam = new SqlParameter("@Fields", SqlDbType.VarChar, 1000);
            sqlparam.Value = files; 
            sqlParams.Add(sqlparam);

            if (!string.IsNullOrEmpty(wheres))
            {
                sqlparam = new SqlParameter("@Filter", SqlDbType.VarChar, 1000);
                sqlparam.Value = wheres;
                sqlParams.Add(sqlparam);
            }
            DataSet ds = new DataSet();
            try
            {
                 ds = SqlHelp.ExecuteDataSet(CommandType.StoredProcedure, "Pg_Paging", sqlParams.ToArray());
            }
            catch(Exception ex)
            {
                var exstr = ex.ToString();
            }
            return ds;
        }

 

/// <summary>  
    /// 把DataTable转换为List<Model>形式  
    /// </summary>  
    public class ModelConvertHelper<T> where T : new() //were T : new()代表作为泛型的类型T，必须是具有公共的无参数构造函数  
    {
        public static IList<T> ConvertToModel(DataTable dt)
        {
            if (dt != null || dt.Rows.Count > 0)
            {
                // 定义集合  
                IList<T> ts = new List<T>();
                // 获得此模型的类型  
                Type type = typeof(T);
                string tempName = "";
                foreach (DataRow dr in dt.Rows)
                {
                    T t = new T();
                    // 获得此模型的公共属性  
                    PropertyInfo[] propertys = t.GetType().GetProperties();
                    foreach (PropertyInfo pi in propertys)
                    {
                        tempName = pi.Name;
                        // 检查DataTable是否包含此列  
                        if (dt.Columns.Contains(tempName))
                        {
                            // 判断此属性是否有Setter  
                            if (!pi.CanWrite)
                                continue;
                            object value = dr[tempName];
                            if (value != DBNull.Value)
                            {
                                object obj = Convert.ChangeType(value, pi.PropertyType, CultureInfo.CurrentCulture);
                                pi.SetValue(t, obj, null);
                            }
                        }
                    }
                    ts.Add(t);
                }
                return ts;
            }

            return null;
        }



    }