Need lowdown on my custom CacheDependency implementation [modified]
-
:sigh: I've been using Data CacheDependency for a while now for my XML File based data. But then I suddenly had the need to use a data cache for my SQL Server based Products table and started looking around for some help. Then I found this: http://www.asp.net/data-access/tutorials/using-sql-cache-dependencies-vb After taking a look at this tutorial, I said "Wow, there goes my sunday afternoon!". But being lazy as I am, I started wondering if there wouldn't be an easier way for achieving what I wanted, and this is what I came up with. 1. I created a Table named TableChanged with 2 columns: - TableName varchar(20) - Changed bit The idea is to track changes made to other tables; for the time being there is only 1 row in it: TableName Changed Products True 2. I created a trigger in the Products table:
CREATE TRIGGER Changed
ON dbo.Products
AFTER INSERT, UPDATE, DELETE
AS
Update TableChanged Set Changed = 1 WHERE TableName='Products'
So everytime something changes in Products, the value of Changed becomes True, in TableChanged. I created the same trigger in table Categories, since it's related to Products. 3. In PageLoad I did:
If Not IsPostBack Then
Dim dv As DataView
Dim changed As Boolean = Global.CheckChanged("Products")
If changed Then
dv = LoadDV()
Else
dv = Cache("cachedDV")
If dv Is Nothing Then 'in case application restarts, changed will be false and dv null
LoadDV()
dv = Cache("cachedDV")
End If
End IfProtected Function LoadDV() As DataView
Dim dt As DataTable = Product.GetDiscounted
Dim dv As DataView = New DataView(dt)
Global.UpdateChanged("Products")
Cache("cachedDV") = dv
Return dv
End FunctionGlobal and Product are classes in my BOL which handles the dataconnection thru my DAL. The sprocs used for CheckChanged and UpdateChanged are the following:
ALTER PROCEDURE dbo.TableChangedCheck
@sTable varchar(20)
AS
SET NOCOUNT ON
Select Changed From TableChanged WHERE TableName=@sTable
RETURNALTER PROCEDURE dbo.TableChangedUpdate
@sTable varchar(20)
AS
SET NOCOUNT ON
UPDATE TableChanged SET Changed = 0 WHERE TableName=@sTable
RETURNSo, on Pageload, if changed = true the dataview gets populated from the db and
-
:sigh: I've been using Data CacheDependency for a while now for my XML File based data. But then I suddenly had the need to use a data cache for my SQL Server based Products table and started looking around for some help. Then I found this: http://www.asp.net/data-access/tutorials/using-sql-cache-dependencies-vb After taking a look at this tutorial, I said "Wow, there goes my sunday afternoon!". But being lazy as I am, I started wondering if there wouldn't be an easier way for achieving what I wanted, and this is what I came up with. 1. I created a Table named TableChanged with 2 columns: - TableName varchar(20) - Changed bit The idea is to track changes made to other tables; for the time being there is only 1 row in it: TableName Changed Products True 2. I created a trigger in the Products table:
CREATE TRIGGER Changed
ON dbo.Products
AFTER INSERT, UPDATE, DELETE
AS
Update TableChanged Set Changed = 1 WHERE TableName='Products'
So everytime something changes in Products, the value of Changed becomes True, in TableChanged. I created the same trigger in table Categories, since it's related to Products. 3. In PageLoad I did:
If Not IsPostBack Then
Dim dv As DataView
Dim changed As Boolean = Global.CheckChanged("Products")
If changed Then
dv = LoadDV()
Else
dv = Cache("cachedDV")
If dv Is Nothing Then 'in case application restarts, changed will be false and dv null
LoadDV()
dv = Cache("cachedDV")
End If
End IfProtected Function LoadDV() As DataView
Dim dt As DataTable = Product.GetDiscounted
Dim dv As DataView = New DataView(dt)
Global.UpdateChanged("Products")
Cache("cachedDV") = dv
Return dv
End FunctionGlobal and Product are classes in my BOL which handles the dataconnection thru my DAL. The sprocs used for CheckChanged and UpdateChanged are the following:
ALTER PROCEDURE dbo.TableChangedCheck
@sTable varchar(20)
AS
SET NOCOUNT ON
Select Changed From TableChanged WHERE TableName=@sTable
RETURNALTER PROCEDURE dbo.TableChangedUpdate
@sTable varchar(20)
AS
SET NOCOUNT ON
UPDATE TableChanged SET Changed = 0 WHERE TableName=@sTable
RETURNSo, on Pageload, if changed = true the dataview gets populated from the db and
As per my understanding and knowledge, your solution is working for one table but if you need same thing in future for different table then you have to manage same thing same for other table. So Sql server provide itself feature called Cache Callback inbuilt feature and easy to maintainable and there less cost is associated. Please read more about notification: http://rusanu.com/2006/06/17/the-mysterious-notification/[^]
Parwej Ahamad