ASP.NET

SQLCacheDependency not notifying

•

Join Date: Feb 2009

Posts: 8

Reputation: yisman is an unknown quantity at this point

Solved Threads: 0

yisman

Offline

Newbie Poster

SQLCacheDependency not notifying

Jun 23rd, 2009

Hi everybody
I have coded a LINQ SiteMapProvider for my ASP.NET site. The site pages info is stored in a MSSQL database.
When I make changes to the SitePAges table, the SiteMap does not reset. Here is my code

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
Public Overrides Function BuildSiteMap() As System.Web.SiteMapNode
        If RootMapNode IsNot Nothing Then Return RootMapNode
        dcx = New GrabdayDataContext
        RootMapNode = Nothing
        Dim SitePages = From q In dcx.SitePages Where q.ParentID Is Nothing
        Dim cmd As New SqlCommand("select sitepageid, parentid from SitePages", New SqlConnection(ConfigurationManager.ConnectionStrings("Grabday").ConnectionString))
        Dim dependency = New SqlCacheDependency(cmd)
        RootMapNode = SitePageNode(SitePages.FirstOrDefault)
        AddNode(RootMapNode, Nothing)
        AddChildNodes(RootMapNode, SitePages.FirstOrDefault)
        HttpRuntime.Cache.Insert(_cacheDependencyName, New Object(), dependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable, New CacheItemRemovedCallback(AddressOf OnSiteMapChanged))
        Return RootMapNode
    End Function
 
    Sub OnSiteMapChanged()
        Clear()
    End Sub
Public Overrides Function BuildSiteMap() As System.Web.SiteMapNode
        If RootMapNode IsNot Nothing Then Return RootMapNode
        dcx = New GrabdayDataContext
        RootMapNode = Nothing
        Dim SitePages = From q In dcx.SitePages Where q.ParentID Is Nothing
        Dim cmd As New SqlCommand("select sitepageid, parentid from SitePages", New SqlConnection(ConfigurationManager.ConnectionStrings("Grabday").ConnectionString))
        Dim dependency = New SqlCacheDependency(cmd)
        RootMapNode = SitePageNode(SitePages.FirstOrDefault)
        AddNode(RootMapNode, Nothing)
        AddChildNodes(RootMapNode, SitePages.FirstOrDefault)
        HttpRuntime.Cache.Insert(_cacheDependencyName, New Object(), dependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable, New CacheItemRemovedCallback(AddressOf OnSiteMapChanged))
        Return RootMapNode
    End Function

    Sub OnSiteMapChanged()
        Clear()
    End Sub

If i understand correctly, the OnSiteMapChanged sub is supposed to run as soon as the tableset becomes invalidated, which somehow never occurs. I put dependenct.start and dependency.stop in my global.asax file in app_start and app_end respectively

My debugging shows that the "Insert" code to the cache does occur, though i dont understand why it is needed (the code was ported from a sqlsitemapprovider). doesnt asp.net cache the data on its own? it definetly does.

Can someone help me get my data invalidated or whatever?
my db show that Is_Broker_Enabled is on

•

Join Date: Oct 2008

Posts: 3,100

Reputation: adatapost has much to be proud of

Solved Threads: 570

adatapost

Offline

Posting Sensei

Re: SQLCacheDependency not notifying

Jun 23rd, 2009

Welcome.

Your code is incomplete for any suggestion. Please read this Article.

•

Join Date: Feb 2009

Posts: 8

Reputation: yisman is an unknown quantity at this point

Solved Threads: 0

yisman

Offline

Newbie Poster

Re: SQLCacheDependency not notifying

Jun 23rd, 2009

hi
soory, i havent meant to skimp. I just thought to keep the code to only waht is necessary.
I am going over the article you sent me, meanwhile, here is the complete Provider Class
Please point out where im misatking
thanks

 Help with Code Tags 
 ASP.NET Syntax (Toggle Plain Text) 
Imports System.Collections.Specialized
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Caching
Imports System.Collections.Generic
 
Public Class LINQSiteMapProvider
    Inherits StaticSiteMapProvider
    Dim dcx As GrabdayDataContext
    Dim RootMapNode As SiteMapNode
    Dim UserID As Integer
    Const _cacheDependencyName As String = "__SiteMapCacheDependency"
 
 
    Public Overrides Sub Initialize(ByVal name As String, ByVal attributes As System.Collections.Specialized.NameValueCollection)
        MyBase.Initialize(name, attributes)
        dcx = New GrabdayDataContext
    End Sub
 
    Public Overrides Function BuildSiteMap() As System.Web.SiteMapNode
        If RootMapNode IsNot Nothing Then Return RootMapNode
        dcx = New GrabdayDataContext
        RootMapNode = Nothing
        Dim SitePages = From q In dcx.SitePages Where q.ParentID Is Nothing
        Dim cmd As New SqlCommand("select sitepageid, parentid from SitePages", New SqlConnection(ConfigurationManager.ConnectionStrings("Grabday").ConnectionString))
        Dim dependency = New SqlCacheDependency(cmd)
        RootMapNode = SitePageNode(SitePages.FirstOrDefault)
        AddNode(RootMapNode, Nothing)
        AddChildNodes(RootMapNode, SitePages.FirstOrDefault)
        HttpRuntime.Cache.Insert(_cacheDependencyName, New Object(), dependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable, New CacheItemRemovedCallback(AddressOf OnSiteMapChanged))
        Return RootMapNode
    End Function
 
    Protected Overrides Function GetRootNodeCore() As System.Web.SiteMapNode
        BuildSiteMap()
        Return RootMapNode
    End Function
 
    Sub AddChildNodes(ByVal ParentNode As SiteMapNode, ByVal ParentPage As SitePage)
        Dim childs = From q In dcx.SitePages Where q.ParentID = ParentPage.SitePageID
        Dim NewNode As SiteMapNode
        For Each sp In childs
            NewNode = SitePageNode(sp)
            AddNode(NewNode, ParentNode)
            AddChildNodes(NewNode, sp)
        Next
        If ParentPage.PageName = "OrdersByStatus" Then
            Dim statusses = From q In dcx.OrderEvents Select q.Brief Distinct
            Dim roles As String() = {}
            Dim nvc As New NameValueCollection
            nvc.Item("PageName") = "Orders"
            For Each os As String In statusses
                NewNode = New SiteMapNode(Me, os & "Orders", "~\Admin\Orders.aspx?Status=" & os, os & " Orders", os & " Orders", roles, nvc, New NameValueCollection, "")
                AddNode(NewNode, ParentNode)
            Next
        End If
    End Sub
 
    Function SitePageNode(ByVal SitePage As SitePage) As SiteMapNode
        Dim nvc As New NameValueCollection
        nvc.Item("PageName") = SitePage.PageName
        Dim roles As String() = {}
        Return New SiteMapNode(Me, SitePage.SitePageID, If(SitePage.URL, "~\Admin\Menu.aspx" & "?ID=" & SitePage.SitePageID), SitePage.Title, SitePage.Description, roles, nvc, New NameValueCollection, "")
    End Function
 
    Sub OnSiteMapChanged()
        Clear()
    End Sub
 
End Class
Imports System.Collections.Specialized
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Web.Caching
Imports System.Collections.Generic

Public Class LINQSiteMapProvider
    Inherits StaticSiteMapProvider
    Dim dcx As GrabdayDataContext
    Dim RootMapNode As SiteMapNode
    Dim UserID As Integer
    Const _cacheDependencyName As String = "__SiteMapCacheDependency"


    Public Overrides Sub Initialize(ByVal name As String, ByVal attributes As System.Collections.Specialized.NameValueCollection)
        MyBase.Initialize(name, attributes)
        dcx = New GrabdayDataContext
    End Sub

    Public Overrides Function BuildSiteMap() As System.Web.SiteMapNode
        If RootMapNode IsNot Nothing Then Return RootMapNode
        dcx = New GrabdayDataContext
        RootMapNode = Nothing
        Dim SitePages = From q In dcx.SitePages Where q.ParentID Is Nothing
        Dim cmd As New SqlCommand("select sitepageid, parentid from SitePages", New SqlConnection(ConfigurationManager.ConnectionStrings("Grabday").ConnectionString))
        Dim dependency = New SqlCacheDependency(cmd)
        RootMapNode = SitePageNode(SitePages.FirstOrDefault)
        AddNode(RootMapNode, Nothing)
        AddChildNodes(RootMapNode, SitePages.FirstOrDefault)
        HttpRuntime.Cache.Insert(_cacheDependencyName, New Object(), dependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable, New CacheItemRemovedCallback(AddressOf OnSiteMapChanged))
        Return RootMapNode
    End Function

    Protected Overrides Function GetRootNodeCore() As System.Web.SiteMapNode
        BuildSiteMap()
        Return RootMapNode
    End Function

    Sub AddChildNodes(ByVal ParentNode As SiteMapNode, ByVal ParentPage As SitePage)
        Dim childs = From q In dcx.SitePages Where q.ParentID = ParentPage.SitePageID
        Dim NewNode As SiteMapNode
        For Each sp In childs
            NewNode = SitePageNode(sp)
            AddNode(NewNode, ParentNode)
            AddChildNodes(NewNode, sp)
        Next
        If ParentPage.PageName = "OrdersByStatus" Then
            Dim statusses = From q In dcx.OrderEvents Select q.Brief Distinct
            Dim roles As String() = {}
            Dim nvc As New NameValueCollection
            nvc.Item("PageName") = "Orders"
            For Each os As String In statusses
                NewNode = New SiteMapNode(Me, os & "Orders", "~\Admin\Orders.aspx?Status=" & os, os & " Orders", os & " Orders", roles, nvc, New NameValueCollection, "")
                AddNode(NewNode, ParentNode)
            Next
        End If
    End Sub

    Function SitePageNode(ByVal SitePage As SitePage) As SiteMapNode
        Dim nvc As New NameValueCollection
        nvc.Item("PageName") = SitePage.PageName
        Dim roles As String() = {}
        Return New SiteMapNode(Me, SitePage.SitePageID, If(SitePage.URL, "~\Admin\Menu.aspx" & "?ID=" & SitePage.SitePageID), SitePage.Title, SitePage.Description, roles, nvc, New NameValueCollection, "")
    End Function

    Sub OnSiteMapChanged()
        Clear()
    End Sub

End Class