|
| ASP.net的ACCESS数据分页方案 |
| 来源：转载 人气：1809 录入时间：2007-11-8 |
常用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(@# @#+RecordCount+@# @#);
document.write(@# @#+requestpage+@#/@#+MaxPage+@# Pages @#);
if(requestpage==1)
document.write(@#<@#);
else
document.write(@#<a href=?@#+PageName+@#=1 title=首页><</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(@#>@#);
else
document.write(@#<a href=?@#+PageName+@#=@#+MaxPage+@# title=尾页>></a>@#);
}
document.write(@#</font> <input class="sBut" type="text" size="1" name="page" value="@#+requestpage+@#" class="PageInput"> <input class="sBut" type="button" value="GO" onclick="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))
|
| |
|
