The Will Will Web

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

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

📅 2009/08/25 23:14 📁 LINQ, .NET Framework, SQL Server

我們都知道 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) 絕對沒錯的啦，這顆甜美的果實我們已經吃好久啦，至今還是依然香甜可口！:-)

相關連結

留言評論