ASP.net的ACCESS数据分页方案

常用ASP.net分页为 DataGrid 控件 和 ADO分页

本分页为缓存唯一标识字段 只选出分页后的记录

对大量数据库分页提高了效率

NetPage.vb

Imports System.Web
Imports System.Data
Imports System.Data.OleDb

Namespace RyNetPage
    Public Class NetPage
        Inherits System.Web.UI.Page
        Private Conn As OleDbConnection
        Private DS As DataSet
        Private DT As DataTable
        Private SQLstr, ScriptName As String
        Private GetPage, PageCount, ListCount As Integer
        Private OrderStr, TableStr, IndexStr, ColumnStr, JscriptStr As String
        Private PageSizeNum As Integer
        Private PageIndex As String
        Public Sub New()
            ScriptName = "RyNetPage"
            PageSizeNum = 10
        End Sub
        Public WriteOnly Property ConnStr()
            Set(ByVal Value)
                Try
                    Conn = New OleDbConnection(Value)
                    Conn.Open()
                Catch ex As Exception
                    Echo(".New" & ex.Message.ToString)
                End Try
            End Set
        End Property
        Public WriteOnly Property Jscript() As String
            Set(ByVal Value As String)
                JscriptStr = Value
            End Set
        End Property
        Public WriteOnly Property PageSize() As String
            Set(ByVal Value As String)
                PageSizeNum = Convert.ToInt32(Value)
            End Set
        End Property
        Public WriteOnly Property Order() As String
            Set(ByVal Value As String)
                If AppCache("Order") <> Value Then
                    OrderStr = "Order " & Value
                    AppCache("Order") = OrderStr
                    WriteIndex()
                End If
            End Set
        End Property
        Public WriteOnly Property Table() As String
            Set(ByVal Value As String)
                TableStr = "[" & Value & "]"
            End Set
        End Property
        Public WriteOnly Property Index() As String
            Set(ByVal Value As String)
                IndexStr = Value
            End Set
        End Property
        Public WriteOnly Property Column() As String
            Set(ByVal Value As String)
                ColumnStr = Value
            End Set
        End Property
        Public ReadOnly Property RecordsCount() As Integer
            Get
                Try
                    WriteIndex()
                    DT = AppCache("Index")
                    Return DT.Rows.Count
                Catch ex As Exception
                    Echo(".RecordsCount " & ex.Message.ToString)
                End Try
            End Get
        End Property
        Public Function ShowRecords() As DataTable
            Try
                SQLstr = "Select " & ColumnStr & " From " & TableStr & PageWhere()
                WriteIndex()
                Dim Adapt As OleDbDataAdapter
                Adapt = New OleDbDataAdapter(SQLstr, Conn)
                DS = New DataSet
                DS.Clear()
                Adapt.Fill(DS)
                Return DS.Tables(0)
            Catch ex As Exception
                Echo(".ShowRecords " & ex.Message.ToString)
            End Try
        End Function
        Public Function ShowPage(Optional ByVal GetStr As String = "") As String
            Dim Temp As String
            Temp = "<Script Language=""Jscript"" Src=""" & JscriptStr & """></Script>" & Chr(13)
            Temp += "<Script Language=""Jscript"">Pages(" & GetPage + 1 & "," & PageCount & "," & ListCount & ",'" & GetStr & "Page')</Script>"
            Return Temp
        End Function
        Public Sub Clear()
            AppCache("Index") = Nothing
        End Sub
        Private Function PageWhere() As String
            Try
                Dim EPage As Integer
                Dim i As Integer
                DT = AppCache("Index")
                GetPage = Convert.ToInt32(HttpContext.Current.Request.QueryString("Page"))
                ListCount = RecordsCount()
                PageCount = CInt(Fix(ListCount / PageSizeNum) + 1)
                If ListCount Mod PageSizeNum = 0 Then
                    PageCount = PageCount - 1
                End If
                If GetPage = 0 Or PageCount < GetPage Then
                    GetPage = 1
                End If
                GetPage = GetPage - 1
                If ListCount < PageSizeNum Or GetPage = PageCount Then
                    EPage = ListCount - 1
                Else
                    EPage = (GetPage * PageSizeNum + PageSizeNum) - 1
                End If
                For i = (GetPage * PageSizeNum) To EPage
                    PageIndex += DT.Rows(i)(0) & ","
                    If i = ListCount - 1 Then Exit For
                Next
                PageIndex = Left(PageIndex, Len(PageIndex) - 1)
                Return " Where " & IndexStr & " In(" & PageIndex & ")"
            Catch ex As Exception
                Echo(".PageWhere " & ex.Message.ToString)
            End Try
        End Function
        Private Sub WriteIndex()
            Try
                If (AppCache("Index") Is Nothing) Then
                    Dim Adapt As OleDbDataAdapter
                    Adapt = New OleDbDataAdapter("Select " & IndexStr & " From " & TableStr & OrderStr, Conn)
                    DS = New DataSet
                    DS.Clear()
                    Adapt.Fill(DS)
                    AppCache("Index") = DS.Tables(0)
                End If
            Catch ex As Exception
                Echo(".WriteIndex " & ex.Message.ToString)
            End Try
        End Sub
        Private Property AppCache(ByVal SetName As String)
            Get
                Return HttpContext.Current.Application.Get(ScriptName & "_" & TableStr & "_" & SetName)
            End Get
            Set(ByVal Value)
                HttpContext.Current.Application.Lock()
                HttpContext.Current.Application.Set(ScriptName & "_" & TableStr & "_" & SetName, Value)
                HttpContext.Current.Application.UnLock()
            End Set
        End Property
        Private Sub Echo(ByVal Value As String)
            Dispose()
            HttpContext.Current.Response.Write(Value)
            HttpContext.Current.Response.End()
        End Sub
        Protected Overrides Sub Finalize()
            MyBase.Finalize()
            Dispose()
        End Sub
        Public Overrides Sub Dispose()
            If Not (Conn Is Nothing) Then
                Conn.Dispose()
            End If
        End Sub
    End Class
End Namespace

Pages.js

function Pages(requestpage,MaxPage,RecordCount,PageName){
var p,ii;
if((requestpage-1)%10==0)
 p=(requestpage-1)/10;
else
 p=parseInt((requestpage-1)/10);
 document.write('<font class="smalltxt">');
 document.write('&nbsp;'+RecordCount+'&nbsp;');
 document.write('&nbsp;'+requestpage+'/'+MaxPage+'&nbsp;&nbsp;Pages&nbsp;');
if(requestpage==1)
 document.write('&lt;');
else
 document.write('<a href=?'+PageName+'=1 title=首页>&lt;</a>');
 if(p*10>0)
 {
  document.write(' <a href=?'+PageName+'='+p*10+' title=上十页>..</a>');
 }
 for(ii=p*10+1;ii<=p*10+10;ii++)
 {
  if(ii==requestpage)
  {
   document.write (' <u><font color="#CC0033">'+ii+'</font></u> ');
  }
  else
  {
   document.write (' <a href=?'+PageName+'='+ii+'>'+ii+'</a> ');
  }
 if(ii==MaxPage)
  break;
 }

if(ii<MaxPage)
{
 document.write('<a href=?'+PageName+'='+ii+' title=下十页>..</a> ');
 if(requestpage==MaxPage)
  document.write('&gt;');
 else
  document.write('<a href=?'+PageName+'='+MaxPage+' title=尾页>&gt;</a>');
}
 document.write('</font>&nbsp;<input class="sBut" type="text" size="1" name="page" value="'+requestpage+'" class="PageInput">&nbsp;<input class="sBut" type="button" value="GO"  οnclick="window.location=/'?'+PageName+'=/'+page.value">');
}

调用示范

Dim Net = New NetPage

        ConnStr = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" & Server.MapPath("DataBase/RYBBS.mdb")
        Dim i As Integer
        Net.Jscript = Server.MapPath("Jscript/Pages.js") 'JS分页函数地址
        Net.ConnStr = ConnStr '连接数据库
        Net.PageSize = 20 '每页显示数
        Net.Table = "Test" '表名
        Net.Index = "ID" '唯一标识字段名
        Net.Column = "ID,Title,Content" '要显示的字段名
        Net.Order = "By ID Desc" '排序
        Dim RS As DataTable
        RS = Net.ShowRecords '调出分页后的记录
        Response.Write("<table>")
        For i = 0 To RS.Rows.Count - 1
            Response.Write("<tr>" & Chr(13))
            Response.Write("<td>" & RS.Rows(i)(0) & "</td><td>" & RS.Rows(i)(1) & "</td><td>" & RS.Rows(i)(2) & "</td>" & Chr(13))
            Response.Write("</tr>" & Chr(13))
        Next
        Response.Write("<table>" & Chr(13))
        Response.Write(Net.ShowPage("ID=20&") & "<br>") 'ID=20&为一同传递的其它分页参数
        Response.Write(FormatNumber(Timer - Run, 6))