所属类别：.NET

文章作者：佚名

特别推荐：免费发布信息 承包关键词~~抢爆了！^HOT!

存储过程: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 @intBeginIDnvarchar(20)declare @intEndIDnvarchar(20)declare @intRecordCountintdeclare @intRowCountintdeclare @TmpSelectNVarchar(600)/*关闭计数*/set nocount on/*set @PageNo=7set @PageSize=2set @SortStr='order by subproclassid, ProductID'set @SortStrDesc='order by subproclassid desc, ProductID desc'*//*求总记录数*/Set @TmpSelect = 'set nocount on;select @SPintRootRecordCount = count(*) from '+@TableName+' '+@WhereStrexecute sp_executesql@TmpSelect,N'@SPintRootRecordCount int OUTPUT',@SPintRootRecordCount=@intRecordCount OUTPUT/*返回总记录数*/set @RecordCount = @intRecordCountif @intRecordCount=0--没有记录则返回一个空记录集BeginSet @TmpSelect='Select ' + @FieldList + ' from '+@TableName+' '+@WhereStrExecute sp_executesql @TmpSelectset @RecordCount=0set @PageCount=1Endelse--有记录则返回记录集begin/*返回总页数*/if @intRecordCount <> 0beginset @PageCount=floor((@intRecordCount+1.0-1.0) / @PageSize)if @PageCount<(@intRecordCount+1.0-1.0) / @PageSizeset @PageCount=@PageCount+1endelseset @PageCount=0/*判断页码是否正确如果页码小于1,设置页码为1,如果页码大于总页数,设置页码为总页数*/if @PageNo<1set @PageNo=1elseif @PageNo>@PageCountset @PageNo=@PageCount/*求结束记录位置*/set @intRowCount = @PageNo * @PageSize/*如果是最后页则返回余下的记录*/if @PageNo=@PageCountset @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 ' + @TableNameset @TmpSelect=@TmpSelect + ' ' + @WhereStr + ' ' + @SortStr + ') ' + @SortStrDescset @TmpSelect=@TmpSelect + ') ' + @SortStrexecute sp_executesql @TmpSelectend/*返回受上一行影响的行数*/return @@rowcountVB类:Imports SystemImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports System.ConfigurationNamespace Gyone.DataAccessPublic Class PaginationPrivate connStr As String = ConfigurationSettings.AppSettings("connStr")Private dsCommand As New SqlDataAdapter()'------------------------------------------------------------------------------------------------Private _FieldList As String = "*"Private _TableName As StringPrivate _WhereStr As String = ""Private _PrimaryKey As StringPrivate _SortStr As String = ""Private _SortStrDesc As StringPrivate _PageSize As Integer = 15Private _PageNo As Integer = 1Private _RecordCount As IntegerPrivate _PageCount As Integer'-------------------------------------------------------------------------------------------------'定义字段列表属性Public Property FieldList() As StringGetReturn _FieldListEnd GetSet(ByVal Value As String)_FieldList = ValueEnd SetEnd Property'-------------------------------------------------------------------------------------------------------'定义表名属性Public Property TableName() As StringGetReturn _TableNameEnd GetSet(ByVal Value As String)_TableName = ValueEnd SetEnd Property'-------------------------------------------------------------------------------------------------------'定义条件语句属性,须写完整,如"Where Id=5 And Name='sea'",如使用了"Or"语句,则须用()括住如:"Where (Id=5 Or Name='sea')"Public Property WhereStr() As StringGetReturn _WhereStrEnd GetSet(ByVal Value As String)_WhereStr = "Where " & ValueEnd SetEnd Property'----------------------------------------------------------------------------------------------------'定义主键Public Property PrimaryKey() As StringGetReturn _PrimaryKeyEnd GetSet(ByVal Value As String)_PrimaryKey = ValueEnd SetEnd Property'--------------------------------------------------------------------------------------------------------'定义排序语句属性,须写完整,如"Order By Id Desc,Name"Public Property SortStr() As StringGetReturn _SortStrEnd GetSet(ByVal Value As String)_SortStr = "Order By " & ValueDim s() As String = Value.Split(",")Dim i As String_SortStrDesc = NothingFor Each i In sIf _SortStrDesc = Nothing ThenIf InStr(i.ToUpper, "DESC") > 0 Then_SortStrDesc = "Order By " & i.ToUpper.Replace("DESC", "")Else_SortStrDesc = "Order By " & i & " DESC"End IfElseIf InStr(i, "desc") > 0 Then_SortStrDesc += "," & i.ToUpper.Replace("DESC", "")Else_SortStrDesc += "," & i & " DESC"End IfEnd IfNextEnd SetEnd Property'-------------------------------------------------------------------------------------------------------'定义页记录数属性Public Property PageSize() As IntegerGetReturn _PageSizeEnd GetSet(ByVal Value As Integer)_PageSize = ValueEnd SetEnd Property'--------------------------------------------------------------------------------------------------------'定义页码属性Public Property PageNo() As IntegerGetReturn _PageNoEnd GetSet(ByVal Value As Integer)_PageNo = ValueEnd SetEnd Property'-----------------------------------------------------------------------------------------------------------'定义总记录数属性(只读)Public ReadOnly Property RecordCount() As IntegerGetReturn _RecordCountEnd GetEnd Property'---------------------------------------------------------------------------------------------------------'定义页总数属性(只读)Public ReadOnly Property PageCount() As IntegerGetReturn _PageCountEnd GetEnd Property'----------------------------------------------------------------------------------------------------------'定义分页方法Public Function Pagination() As DataSetDim Data As New DataSet(TableName)Dim objCmd As New SqlCommand("spPagination", New SqlConnection(connStr))objCmd.CommandType = CommandType.StoredProcedureWith 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.OutputEnd WithdsCommand.SelectCommand = objCmddsCommand.Fill(Data, TableName)_RecordCount = dsCommand.SelectCommand.Parameters("@RecordCount").Value_PageCount = dsCommand.SelectCommand.Parameters("@PageCount").ValueReturn DataEnd FunctionEnd ClassEnd Namespace关闭本页

....