萬盛學電腦網

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

ASP 經典:從 Recordset 產生 JSON 輸出的通用函數

前言

在經典 ASP (VBScript) 環境下,若需將資料庫查詢結果直接輸出為 JSON 供前端 AJAX 使用,可封裝通用函數 GetTable,支援分頁、排序、MiniUI 格式輸出。

完整函數代碼

<%
'===============================================================
' GetTable - 通用資料表 JSON 輸出函數
' 參數:
'   Table      - 資料表名稱
'   Where      - WHERE 條件子句(不含 WHERE 關鍵字)
'   Order      - 排序欄位(必填,作為分頁游標)
'   OrderSort  - 排序方向 asc/desc
'   curpage    - 當前頁碼 (1-based)
'   pagesize   - 每頁筆數 (0 表不分頁)
'   MiniJson   - 1=輸出 MiniUI 格式 {total:, data:[]}, 0=純陣列
'===============================================================
Function GetTable(Table, Where, Order, OrderSort, curpage, pagesize, MiniJson)
    Dim i, j, rs, TmpSql, TmpJson, RealSize, CountSql, Counts
    
    If OrderSort = "" Then OrderSort = "asc"
    If Where = "" Then Where = "1=1"  ' 避免 WHERE 子句為空導致語法錯誤
    
    Set rs = Server.CreateObject("ADODB.Recordset")
    
    ' 建構分頁 SQL (SQL Server 2000/2005 相容寫法:TOP 巢狀查詢)
    If curpage > 1 Then
        TmpSql = "SELECT a.* FROM ( " & _
            "SELECT TOP " & (curpage * pagesize) & " * FROM " & Table & " WHERE " & Where & " ORDER BY " & Order & " " & OrderSort & _
            ") a LEFT JOIN ( " & _
            "SELECT TOP " & ((curpage - 1) * pagesize) & " * FROM " & Table & " WHERE " & Where & " ORDER BY " & Order & " " & OrderSort & _
            ") b ON a." & Order & " = b." & Order & " " & _
            "WHERE IIF(b." & Order & " IS NULL, 1, 0) = 1"
    Else
        TmpSql = "SELECT a.* FROM ( " & _
            "SELECT TOP " & (curpage * pagesize) & " * FROM " & Table & " WHERE " & Where & " ORDER BY " & Order & " " & OrderSort & _
            ") a"
    End If
    
    If pagesize = 0 Then TmpSql = "SELECT * FROM " & Table & " WHERE " & Where
    
    rs.Open TmpSql, conn, 1, 1  ' adOpenKeyset, adLockReadOnly
    RealSize = rs.RecordCount
    
    ' 遍歷 Recordset 產生 JSON
    For i = 0 To rs.RecordCount - 1
        TmpJson = TmpJson & "{"
        For j = 0 To rs.Fields.Count - 1
            TmpJson = TmpJson & """" & rs.Fields(j).Name & """:"
            ' 處理 NULL、字串逸出
            Dim val : val = rs(j)
            If IsNull(val) Then
                TmpJson = TmpJson & "null"
            ElseIf VarType(val) = vbString Then
                TmpJson = TmpJson & """" & Replace(val, """", "\""") & """"
            Else
                TmpJson = TmpJson & val
            End If
            If j < rs.Fields.Count - 1 Then TmpJson = TmpJson & ","
        Next
        TmpJson = TmpJson & "}"
        If i < rs.RecordCount - 1 Then TmpJson = TmpJson & ","
        TmpJson = TmpJson & vbCrLf
        rs.MoveNext
    Next
    rs.Close
    
    If MiniJson = 1 Then
        ' 取得總筆數
        CountSql = "SELECT COUNT(" & Order & ") FROM " & Table & " WHERE " & Where
        rs.Open CountSql, conn, 1, 1
        Counts = rs(0)
        rs.Close
        GetTable = ToMiniUi(TmpJson, Counts)
    Else
        GetTable = ToJson(TmpJson)
    End If
    
    Set rs = Nothing
End Function

Function ToJson(JsonStr)
    ToJson = "[" & vbCrLf & JsonStr & vbCrLf & "]"
End Function

Function ToMiniUi(JsonStr, total)
    ToMiniUi = "{" & vbCrLf & _
        """total"": " & total & "," & vbCrLf & _
        """data"": [" & JsonStr & vbCrLf & "]}"
End Function
%>

使用範例

<%
' 頁面:api_users.asp
' 輸出第 1 頁,每頁 20 筆,依 id 遞增,MiniUI 格式
Response.ContentType = "application/json; charset=utf-8"
Response.Write GetTable("Users", "Status=1", "Id", "asc", 1, 20, 1)
%>

輸出範例

標準 JSON 陣列

[
{"Id":1,"Name":"Alice","Email":"alice@example.com"},
{"Id":2,"Name":"Bob","Email":"bob@example.com"}
]

MiniUI 格式

{
"total": 150,
"data": [
{"Id":1,"Name":"Alice","Email":"alice@example.com"},
{"Id":2,"Name":"Bob","Email":"bob@example.com"}
]
}

關鍵細節說明

  1. 分頁邏輯:採用「雙 TOP 巢狀查詢 + LEFT JOIN 排除」模式,相容 SQL Server 2000/2005(無 ROW_NUMBER()、OFFSET/FETCH)。
  2. 字串逸出:雙引號替換為 \",防止 JSON 解析錯誤。
  3. NULL 處理:輸出 null 而非空字串。
  4. 總筆數查詢:MiniUI 模式額外執行 COUNT(*),確保分頁元件正確顯示總頁數。
  5. 連線物件:conn 需在全域或父頁面已建立並開啟(建議使用連線池)。

現代化建議

新專案請優先考慮:

  • ASP.NET Core / Node.js / Python 等現代框架,內建 JSON 序列化與參數化查詢。
  • 若必須維護舊 ASP 系統,請將此函數放入共用 include/json_util.asp,統一管理。
  • 嚴禁在 SQL 字串中直接拼接參數,生產環境請改用 Command.Parameters 防注入。
copyright © 萬盛學電腦網 all rights reserved