存储过程|分页|速度存储过程:

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