MS SQL 数据分页(通用)

1.支持简单查询分页。 2.支持子表查询分页。 3.分页返回DataTable及cnt总记录数。 无 CREATE PROCEDURE [dbo].[sp_GetListByPageAndFileds]( @pageSize int,/** 每页数据量 **/ @currentPage int = 1,/** 当前页，默认为为1 **/ @fields varchar(2000),/** 查

1.支持简单查询分页。
2.支持子表查询分页。
3.分页返回DataTable及cnt 总记录数。

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)
                {
                    // 检查未分配值的输出参数,将其分配以DBNull.Value.
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }

            return command;
        }