存储过程:

alter procedure sppagination

    @fieldlist nvarchar(200),–字段列表
    @tablename nvarchar(20), –表名
    @wherestr nvarchar(500),–条件语句(须写完整,如"where name=sea and image=0",如果使用or语句,须用():如:"where (name=sea or image=0)"
    @primarykey nvarchar(20),–主键
    @sortstr nvarchar(100),–排序语句(须写完整,如"order by id,nname")
    @sortstrdesc nvarchar(100), –倒序语句(须写完整,如"order by id desc,nname desc")
    @pagesize int,–页记录数
    @pageno int,–页码
    @recordcount int output,–返回记录总数    
    @pagecount int output–返回页总数    

as
    /*定义局部变量*/
    declare @intbeginid         nvarchar(20)
    declare @intendid           nvarchar(20)
    declare @intrecordcount     int
    declare @introwcount        int
    declare @tmpselect          nvarchar(600)
    /*关闭计数*/
    set nocount on
   
   /*
   set @pageno=7
   set @pagesize=2
   set @sortstr=order by subproclassid, productid
   set @sortstrdesc=order by subproclassid desc, productid desc
   */
    /*求总记录数*/
    set @tmpselect = set nocount on;select @spintrootrecordcount = count(*) from +@tablename+ +@wherestr
    execute sp_executesql
            @tmpselect,
            n@spintrootrecordcount int output,
            @spintrootrecordcount=@intrecordcount output
             
    /*返回总记录数*/             
    set @recordcount = @intrecordcount
    
    if @intrecordcount=0
        –没有记录则返回一个空记录集
        begin
            set @tmpselect=select + @fieldlist + from +@tablename+ +@wherestr    
            execute sp_executesql @tmpselect
            set @recordcount=0
            set @pagecount=1
        end
    else
        –有记录则返回记录集
        begin    
    /*返回总页数*/
    if @intrecordcount <> 0
        begin
            set @pagecount=floor((@intrecordcount+1.0-1.0) / @pagesize)
            if @pagecount<(@intrecordcount+1.0-1.0) / @pagesize
            set @pagecount=@pagecount+1
        end
    else
       set @pagecount=0
                
    /*判断页码是否正确
    如果页码小于1,设置页码为1,如果页码大于总页数,设置页码为总页数*/
    if @pageno<1
        set @pageno=1
    else
        if @pageno>@pagecount
            set @pageno=@pagecount
        
    /*求结束记录位置*/
    set @introwcount = @pageno * @pagesize
    
            
    /*如果是最后页则返回余下的记录*/
   if @pageno=@pagecount
        set @pagesize=@recordcount – (@pageno-1) * @pagesize
    
    /* 开始分页 */    
   set @tmpselect= select * from + @tablename + where + @primarykey + = any (
   set @tmpselect=@tmpselect + select top + str(@pagesize) + + @primarykey + from + @tablename + where + @primarykey + in (select top + str(@introwcount) + + @primarykey + from + @tablename
   set @tmpselect=@tmpselect + + @wherestr + + @sortstr + ) + @sortstrdesc
   set @tmpselect=@tmpselect + ) + @sortstr

   execute sp_executesql @tmpselect
   end
   /*返回受上一行影响的行数*/
   return @@rowcount

vb类:
imports system
imports system.data
imports system.data.sqlclient
imports system.data.sqltypes
imports system.configuration

namespace gyone.dataaccess
    public class pagination

        private connstr as string = configurationsettings.appsettings("connstr")
        private dscommand as new sqldataadapter()

        ————————————————————————————————
        private _fieldlist as string = "*"
        private _tablename as string
        private _wherestr as string = ""
        private _primarykey as string
        private _sortstr as string = ""
        private _sortstrdesc as string
        private _pagesize as integer = 15
        private _pageno as integer = 1
        private _recordcount as integer
        private _pagecount as integer

        ————————————————————————————————-
        定义字段列表属性
        public property fieldlist() as string
            get
                return _fieldlist
            end get
            set(byval value as string)
                _fieldlist = value
            end set
        end property

        ——————————————————————————————————-
        定义表名属性
        public property tablename() as string
            get
                return _tablename
            end get
            set(byval value as string)
                _tablename = value
            end set
        end property

        ——————————————————————————————————-
        定义条件语句属性,须写完整,如"where id=5 and name=sea",如使用了"or"语句,则须用()括住如:"where (id=5 or name=sea)"
        public property wherestr() as string
            get
                return _wherestr
            end get
            set(byval value as string)
                _wherestr = "where " & value
            end set
        end property

        —————————————————————————————————-
        定义主键
        public property primarykey() as string
            get
                return _primarykey
            end get
            set(byval value as string)
                _primarykey = value
            end set
        end property

        ——————————————————————————————————–
        定义排序语句属性,须写完整,如"order by id desc,name"
        public property sortstr() as string
            get
                return _sortstr
            end get
            set(byval value as string)
                _sortstr = "order by " & value
                dim s() as string = value.split(",")
                dim i as string
                _sortstrdesc = nothing
                for each i in s
                    if _sortstrdesc = nothing then
                        if instr(i.toupper, "desc") > 0 then
                            _sortstrdesc = "order by " & i.toupper.replace("desc", "")
                        else
                            _sortstrdesc = "order by " & i & " desc"
                        end if
                    else
                        if instr(i, "desc") > 0 then
                            _sortstrdesc += "," & i.toupper.replace("desc", "")
                        else
                            _sortstrdesc += "," & i & " desc"
                        end if
                    end if
                next
            end set
        end property

        ——————————————————————————————————-
        定义页记录数属性
        public property pagesize() as integer
            get
                return _pagesize
            end get
            set(byval value as integer)
                _pagesize = value
            end set
        end property

        ——————————————————————————————————–
        定义页码属性
        public property pageno() as integer
            get
                return _pageno
            end get
            set(byval value as integer)
                _pageno = value
            end set
        end property

        ———————————————————————————————————–
        定义总记录数属性(只读)
        public readonly property recordcount() as integer
            get
                return _recordcount
            end get
        end property

        ———————————————————————————————————
        定义页总数属性(只读)
        public readonly property pagecount() as integer
            get
                return _pagecount
            end get
        end property

        ———————————————————————————————————-
        定义分页方法
        public function pagination() as dataset

            dim data as new dataset(tablename)
            dim objcmd as new sqlcommand("sppagination", new sqlconnection(connstr))
            objcmd.commandtype = commandtype.storedprocedure

            with objcmd.parameters

                .add(new sqlparameter("@fieldlist", sqldbtype.nvarchar, 200))
                .add(new sqlparameter("@tablename", sqldbtype.nvarchar, 20))
                .add(new sqlparameter("@wherestr", sqldbtype.nvarchar, 500))
                .add(new sqlparameter("@primarykey", sqldbtype.nvarchar, 20))
                .add(new sqlparameter("@sortstr", sqldbtype.nvarchar, 100))
                .add(new sqlparameter("@sortstrdesc", sqldbtype.nvarchar, 100))
                .add(new sqlparameter("@pagesize", sqldbtype.int))
                .add(new sqlparameter("@pageno", sqldbtype.int))
                .add(new sqlparameter("@recordcount", sqldbtype.int))
                .add(new sqlparameter("@pagecount", sqldbtype.int))

                .item("@fieldlist").value = _fieldlist
                .item("@tablename").value = _tablename
                .item("@wherestr").value = _wherestr
                .item("@primarykey").value = _primarykey
                .item("@sortstr").value = _sortstr
                .item("@sortstrdesc").value = _sortstrdesc
                .item("@pagesize").value = _pagesize
                .item("@pageno").value = _pageno

                .item("@recordcount").direction = parameterdirection.output
                .item("@pagecount").direction = parameterdirection.output

            end with

            dscommand.selectcommand = objcmd
            dscommand.fill(data, tablename)

            _recordcount = dscommand.selectcommand.parameters("@recordcount").value
            _pagecount = dscommand.selectcommand.parameters("@pagecount").value
            return data

        end function
    end class
end namespace