將常用資料快取於 Application 可大幅減少資料庫存取,但資料更新時如何讓快取同步?本文提供一個封裝類別,實現「快取為空才重查、更新時清除快取」的自動化流程。
<%
Class wawa_app_getrows
Public Function wawa_Get_List(strapp, strconn, strsql)
Dim wawa
wawa = Application(strapp)
If IsEmpty(wawa) Then
wawa = wawa_Get_Rows(strconn, strsql)
Application(strapp) = wawa
End If
wawa_Get_List = wawa
End Function
Private Function wawa_Get_Rows(strconn, strsql)
Dim rs_wawa
Set rs_wawa = CreateObject("ADODB.Recordset")
rs_wawa.Open strsql, strconn, , 1, 1 ' 唯讀、順向游標
wawa_Get_Rows = rs_wawa.GetRows() ' 直接轉為二維陣列
rs_wawa.Close
Set rs_wawa = Nothing
End Function
End Class
%>
<%
Dim strapp, strsql, strconn
strapp = "employee"
strsql = "SELECT EmployeeID, TitleOfCourtesy, FirstName, LastName, Title, City FROM Employees"
strconn = "Driver={SQL Server};server=192.168.0.110;database=northwind;uid=sa;pwd=sa;"
Set wawa_temp = New wawa_app_getrows
arr_wawa = wawa_temp.wawa_Get_List(strapp, strconn, strsql)
%>
<table width="100%" border="0" cellspacing="1">
<%
Dim i, j, Rows, Flds
Rows = UBound(arr_wawa, 2)
Flds = UBound(arr_wawa, 1)
If Rows >= 0 Then
For i = 0 To Rows
Response.Write "<tr>"
For j = 0 To Flds
Response.Write "<td>" & arr_wawa(j, i) & "</td>"
Next
Response.Write "</tr>"
Next
Else
Response.Write "<tr><td>無資料</td></tr>"
End If
%>
</table>
當資料庫異動(新增/修改/刪除)時,僅需執行:
Application.Contents.Remove("employee")
下次請求時 IsEmpty 判斷為 True,自動重新查詢並重建快取,零程式碼維護。