MSSQL数据分页(通用)

CREATE PROCEDURE [dbo].[sp_GetListByPageAndFileds]
( 
	@pageSize int,	/** 每页数据量 **/ 
	@currentPage int = 1,	/** 当前页，默认为为1 **/ 
	@fields varchar(2000),	/** 查询字段，可以用 * 表示所有 **/ 
	@tablename varchar(max), /** 表名，或者为查询得出的 子表 ，子表查询sql需要括号括起来，并指定新表名 **/ 
	@orderString varchar(1000), /** 排序字段 + asc/desc **/ 
	@whereString varchar(1000) /** 不包含‘where’的字符串 **/ 
)
	AS
	BEGIN
	 DECLARE @sql varchar(2000)
	 DECLARE @strOrder varchar(2000)
	 DECLARE @strWhere varchar(2000)
	 declare @recordcount int 
	 declare @convertorderstr varchar(2000)
	declare @countsql nvarchar(4000)
	declare @totalpage int
	 set @strOrder = REPLACE(RTRIM(LTRIM(@orderString)),'order by','')
	 if @strOrder != ''
	 set @strOrder = ' order by ' + @strOrder
	 else
	 set @strOrder = ' order by ID DESC'
	set @strOrder=lower(@strOrder)
	set @convertorderstr=replace(@strOrder,'desc','d_e_s_c')
	set @convertorderstr=replace(@convertorderstr,'asc','desc')
	set @convertorderstr=replace(@convertorderstr,'d_e_s_c','asc')
	 set @strWhere = REPLACE(RTRIM(LTRIM(@whereString)),'where','')
	 if @strWhere != ''
	 set @strWhere = ' where ' + @strWhere

	set @countsql='select @a=count(*) from ' + @tablename + @strWhere
	exec sp_executesql @countsql,N'@a int output',@recordcount output 
	 if @pageSize = 0
	 set @sql = 'select ' + @fields + ' from ' + @tablename + @strWhere + @strOrder
	 else
	 begin
	if @recordcount%@pageSize=0
	set @totalpage=@recordcount/@pageSize
	else
	set @totalpage=@recordcount/@pageSize+1
	if @totalpage <=1
	 set @currentPage=1 
	 if @totalpage <@currentPage 
	 set @currentPage=@totalpage
	 if @currentPage = 1
	 set @sql = 'select top ' + Str(@pageSize)+' '+ @fields + ' from ' + @tablename + @strWhere + @strOrder
	 else
	if (@currentPage - 1) * @pageSize > @recordcount / 2
	set @sql = 'select top ' + str(@pageSize) + ' * from (select top ' + str((@recordcount - (@currentPage - 1) * @pageSize)) + ' ' + @fields + ' from ' + @tablename + @strWhere + @convertorderstr + ') as t1 ' + @strOrder
	else
	set @sql = 'select * from(select top ' + str(@pageSize) + ' * from (select top ' + str(@pageSize * @currentPage) + ' ' + @fields + ' from ' + @tablename + @strWhere + @strOrder + ') as t1 ' + @convertorderstr + ') as t2 ' + @strOrder
	end
	set @sql = @sql + '; select '+str(@recordcount)+' as cnt'
	 exec(@sql)
	END

 public static DataTable GetDataTableByPager(int pageSize, int currentPage, string fields, string orderString, string whereString, string tablename, out int count)
 {
 if (fields == String.Empty)
 {
 fields = "*";
 }
 count = 0;
 DataTable table = new DataTable();
 SqlParameter[] param ={ new SqlParameter("@pageSize",pageSize) ,
 new SqlParameter("@currentPage",currentPage) ,
 new SqlParameter("@fields",fields) ,
 new SqlParameter("@orderString",orderString) ,
 new SqlParameter("@tablename",tablename) ,
 new SqlParameter("@whereString",whereString) ,
 };

 DataSet ds = new DataSet();
 ds = RunProcedure("sp_GetListByPageAndFileds", param);
 table = ds.Tables[0];
 count = Convert.ToInt32(ds.Tables[1].Rows[0]["cnt"].ToString());
 return table;
 }
 public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters)
 {
 using (SqlConnection connection = new SqlConnection(Maticsoft.DBUtility.PubConstant.ConnectionString))
 {
 DataSet dataSet = new DataSet();
 connection.Open();
 SqlDataAdapter sqlDA = new SqlDataAdapter();
 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
 sqlDA.Fill(dataSet);
 connection.Close();
 return dataSet;
 }
 }
 private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
 {
 SqlCommand command = new SqlCommand(storedProcName, connection);
 command.CommandType = CommandType.StoredProcedure;
 foreach (SqlParameter parameter in parameters)
 {
 if (parameter != null)
 {
 // 检查未分配值的