The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

SqlCacheDependency 如何套用到有多重表格關聯的情況

我們都知道 SqlCacheDependency 有個很大的限制,就是一次只能用「單一表格」做判斷,如果你有個檢視表(View)或使用的 SQL 語法有 JOIN 兩個以上的表格,就無法利用 SqlCacheDependency 幫你達成快取相依(CacheDependency)的設計,但我們大多的案子很少有「單一表格查詢」的狀況,以導致很多情境下無法使用 SqlCacheDependency 感覺十分懊惱,但我們最近想出了新方法!

假設我們有兩個表格彼此相依,「父表格」為 NewsCategory,「子表格」為 NewsContent,而我們通常只會用 LINQ 取出 NewsCategory 的資料,至於 NewsContent 的資料真正要使用時可以直接用「點表示法」( dot notation ) 將資料取出,所以套用在 LINQ ��環境下,我們就可以很輕易的將 NewsCategory 註冊到 SQL Server 中(利用 SqlCacheDependency )。

而我們遇到主要的問題是,當 NewsContent 內容變更時,我們儲存在 Cache 中的 NewsCategory 資料並不會被 SQL Server 主動通知,所以被快取的資料永遠不會被更新。

要解決這個問題,就必須讓 NewsContent 更新資料時立即更新「父表格」NewsCategory 表格關連的那筆資料,至於更新什麼資料並不重要,只要有 UPDATE 過即可,例如:

UPDATE dbo.NewsCategory SET [ID]=[ID] WHERE ID=@ID

當啟用 SqlCacheDependency 機制時,ASP.NET 會自動在 SQL Server 的資料庫中建立 一個 AspNet_SqlCacheTablesForChangeNotification 表格,用來紀錄每一註冊表格資料的變化,其中有個欄位叫 changeId,只要註冊表格中的資料被異動就會自動 +1,由此可以看出當 NewsContent 異動時 NewsCategory 也跟著變化了,如下圖例:

AspNet_SqlCacheTablesForChangeNotification

這樣的更新動作只要自己寫一組簡單的觸發程序(TRIGGER)就可以解決,範例程式如下:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[NewsContent_Doggy_SqlCacheNotification_Trigger] 
   ON  [dbo].[NewsContent] 
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

	Declare @ParentID uniqueidentifier

	 --insert
    IF (select count(*) from inserted) <> 0 and (select count(*) from deleted) = 0
	BEGIN
		DECLARE cur_Insert CURSOR FOR SELECT i.CategoryID FROM Inserted i
			
		OPEN cur_Insert
		
		FETCH NEXT FROM cur_Insert INTO @ParentID
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
			Update dbo.NewsCategory
			Set [ID] = [ID]
			Where ID = @ParentID
			
			FETCH NEXT FROM cur_Insert INTO @ParentID
		END
		
		CLOSE cur_Insert

		DEALLOCATE cur_Insert
	END

	--update	
	IF (select count(*) from inserted) <> 0 and (select count(*) from deleted) <> 0
	BEGIN
		DECLARE cur_Update CURSOR FOR SELECT i.CategoryID FROM Inserted i
			
		OPEN cur_Update
		
		FETCH NEXT FROM cur_Update INTO @ParentID
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
			Update dbo.NewsCategory
			Set [ID] = [ID]
			Where ID = @ParentID
			
			FETCH NEXT FROM cur_Update INTO @ParentID
		END
		
		CLOSE cur_Update

		DEALLOCATE cur_Update
	END
	
	--delete
	IF (select count(*) from inserted) = 0 and (select count(*) from deleted) <> 0 
	BEGIN
		DECLARE cur_Delete CURSOR FOR SELECT i.CategoryID FROM deleted i
			
		OPEN cur_Delete
		
		FETCH NEXT FROM cur_Delete INTO @ParentID
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
			Update dbo.NewsCategory
			Set [ID] = [ID]
			Where ID = @ParentID
			
			FETCH NEXT FROM cur_Delete INTO @ParentID
		END
		
		CLOSE cur_Delete

		DEALLOCATE cur_Delete
	END

END

至於透過 LINQ 讀取 NewsCategory 抓取資料並快取後,為什麼連 NewsContent 的資料也會連帶被快取呢?這問題我仔細思考了一下終於理解。

原來是我們將透過 LINQ 讀取 NewsCategory 資料後 ( List<NewsCategory> ) 直接快取到 Cache 中,而這是一個簡單的 POCO 物件,所以當透過 LINQ 的「點表示法」將資料取出後,事實上所存取的物件還是在 Cache 中的物件,資料取回後寫入的地方正好也是 Cache 中的物件,因此資料也一併存在 Cache 中了,這真是一個美麗的意外阿。^_^

這又再次證明採用 ORM 技術 (LINQ) 絕對沒錯的啦,這顆甜美的果實我們已經吃好久啦,至今還是依然香甜可口!:-)

相關連結