在經典 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)
%>
[
{"Id":1,"Name":"Alice","Email":"alice@example.com"},
{"Id":2,"Name":"Bob","Email":"bob@example.com"}
]
{
"total": 150,
"data": [
{"Id":1,"Name":"Alice","Email":"alice@example.com"},
{"Id":2,"Name":"Bob","Email":"bob@example.com"}
]
}
ROW_NUMBER()、OFFSET/FETCH)。\",防止 JSON 解析錯誤。null 而非空字串。COUNT(*),確保分頁元件正確顯示總頁數。conn 需在全域或父頁面已建立並開啟(建議使用連線池)。新專案請優先考慮:
include/json_util.asp,統一管理。Command.Parameters 防注入。