存储过程:

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 pF6HTML5中文学习网 - HTML5先行者学习网
pF6HTML5中文学习网 - HTML5先行者学习网