再来一个分页的存储过程,这可比别的好多了,就是不知速度如何!
点击次数：23 次 发布日期：2008-11-27 01:11:30 作者：源代码网
|
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  源代码网供稿. |
