萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> ASP 程式設計

ADO Recordset 分頁顯示完整教學:PageSize、PageCount、AbsolutePage 實戰

電子佈告欄 (BBS)、搜尋結果、商品清單等常需「分頁顯示」。ADO `Recordset` 內建分頁屬性 (`PageSize`、`PageCount`、`AbsolutePage`),配合客戶端游標即可快速實作。本文以經典 ASP+BBS 為例,完整說明兩種分頁策略與程式碼。

兩種分頁策略比較

策略做法優點缺點適用場景
全量讀入 (Client-side Cursor)一次 `SELECT *` 所有符合條件記錄 → `Recordset` 存記憶體 → 用 `AbsolutePage` 切頁程式簡單;翻頁快 (無再查 DB)記憶體暴增;資料過舊;不適合大資料/高併發資料量 < 5,000 筆、低併發、內部管理系統
分頁查詢 (Server-side Paging)每次只 `SELECT TOP N ... WHERE id > last_id` 或 `OFFSET/FETCH`記憶體穩定;資料即時;支援海量資料SQL 較複雜;需處理排序/游標一致性公開網站、大資料量、高併發、即時性要求高

Recordset 分頁關鍵屬性

  • PageSize:每頁筆數 (如 20)
  • PageCount:總頁數 (自動計算)
  • AbsolutePage:目前頁碼 (1-based),設定可跳頁
  • AbsolutePosition:目前記錄在 Recordset 中位置 (1-based)
  • RecordCount:總筆數 (需 `CursorLocation = adUseClient` 才準確)
  • EOF/BOF:判斷是否超出範圍

完整 BBS 分頁範例 (全量讀入法)

<%@ Language=VBScript CodePage=65001 %><%
Option Explicit
Response.Buffer = True

Dim conn, rs, sql, pageSize, curPage, totalRecords, totalPages, startRec, endRec
Dim keyword, orderBy

'--- 參數 ---
pageSize = 20
curPage = CInt(Request("page"))
If curPage < 1 Then curPage = 1
keyword = Trim(Request("kw"))
orderBy = "postdate DESC"

'--- 連線 ---
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("bbs.mdb")

'--- 建立 Recordset (必須用 Client Cursor 才支援分頁屬性) ---
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3  ' adUseClient
rs.CursorType = 3      ' adOpenStatic (支援 AbsolutePage)
rs.LockType = 1        ' adLockReadOnly

'--- 動態 SQL ---
sql = "SELECT id, subject, name, email, postdate FROM bbs_table"
If keyword <> "" Then
    sql = sql & " WHERE subject LIKE '%" & Replace(keyword, "'", "''") & "%' OR name LIKE '%" & Replace(keyword, "'", "''") & "%'"
End If
sql = sql & " ORDER BY " & orderBy

rs.Open sql, conn

'--- 分頁屬性 ---
rs.PageSize = pageSize
totalRecords = rs.RecordCount
totalPages = rs.PageCount
If curPage > totalPages Then curPage = totalPages
If curPage < 1 Then curPage = 1

rs.AbsolutePage = curPage

'--- 顯示當頁資料 ---
Dim i, recCount
recCount = 0
Response.Write ""
Do While Not rs.EOF And recCount < pageSize
    Response.Write "" & _
        "" & _
        "" & _
        "" & _
        ""
    rs.MoveNext
    recCount = recCount + 1
Loop
Response.Write "
主題作者日期
" & Server.HTMLEncode(rs("subject")) & "" & Server.HTMLEncode(rs("name")) & "" & FormatDateTime(rs("postdate"), 2) & "
" '--- 分頁導覽列 --- Response.Write "
" If curPage > 1 Then Response.Write "首頁 " Response.Write "上一頁 " End If Response.Write " 第 " & curPage & " / " & totalPages & " 頁 (共 " & totalRecords & " 筆) " If curPage < totalPages Then Response.Write "下一頁 " Response.Write "尾頁" End If Response.Write "
" '--- 清理 --- rs.Close : Set rs = Nothing conn.Close : Set conn = Nothing %>

SQL Server / Oracle 真正的分頁查詢 (Server-side)

'--- SQL Server 2012+ (OFFSET/FETCH) ---
sql = "SELECT id, subject, name, postdate FROM bbs_table " & _
      "ORDER BY postdate DESC " & _
      "OFFSET " & ((curPage-1)*pageSize) & " ROWS FETCH NEXT " & pageSize & " ROWS ONLY;"

'--- Oracle 12c+ (OFFSET/FETCH) ---
sql = "SELECT id, subject, name, postdate FROM bbs_table " & _
      "ORDER BY postdate DESC " & _
      "OFFSET " & ((curPage-1)*pageSize) & " ROWS FETCH NEXT " & pageSize & " ROWS ONLY;"

'--- MySQL / PostgreSQL / SQLite ---
sql = "SELECT id, subject, name, postdate FROM bbs_table " & _
      "ORDER BY postdate DESC " & _
      "LIMIT " & pageSize & " OFFSET " & ((curPage-1)*pageSize) & ";"

'--- SQL Server 2000/2005/2008 (ROW_NUMBER) ---
sql = "WITH Paged AS (" & _
      "  SELECT id, subject, name, postdate, ROW_NUMBER() OVER (ORDER BY postdate DESC) AS rn " & _
      "  FROM bbs_table" & _
      ") SELECT * FROM Paged WHERE rn BETWEEN " & ((curPage-1)*pageSize+1) & " AND " & (curPage*pageSize) & ";"

效能優化關鍵

  1. 索引:`ORDER BY` 欄位 (如 `postdate`) 必須有索引。
  2. 避免 SELECT *:只取需要欄位,減少網路/記憶體傳輸。
  3. 快取總筆數:`COUNT(*)` 昂貴時,可快取 5~10 分鐘或用近似值。
  4. 游標選擇:`adOpenForwardOnly` + `adUseServer` 最省資源,但不支援 `AbsolutePage`;需分頁時改 `adOpenStatic` + `adUseClient`。
  5. 連線池:確保連線字串相同以重用連線。
結論:舊專案維護可用 Recordset 分頁;新專案務必採用資料庫原生 `OFFSET/FETCH` 或 `ROW_NUMBER` 分頁,並配合快取策略,才能支撐高併發與大資料量。
copyright © 萬盛學電腦網 all rights reserved