存储过程:
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