Life of a geek and a part-time poet

Contents

• Introduction
• Database Site Navigation and Caching
• Source Code
• Resources

Introduction

Quite a while ago I internally blogged about creating a custom Sitemap provider in ASP.NET.  Even though its been a while I believe its still relevant, especially the SQL caching example I added.  Hopefully you can still get something out of it.

With ASP.NET 2: Microsoft introduced a few services which are built on top of the provider model.
Services such as the membership or sitemap rely on providers to supply them with the actual information. All the providers are derived from the ProviderBase class.

The default Sitemap provider is the XmlSiteMapProvider which is used by ASP.NET to retrieve the website’s navigation structure stored in the Web.sitemap file.

The cool thing about this is that we can easily extend these providers if we need different functionality than what Microsoft provides. So As long as we keep inline with the providers’ interfaces the services will work with our own customizations just fine.

A benefit of creating a sitemap provider that reads from a database should be obvious: Its pretty easy to add custom navigation where needed to new or existing sites by reading ready information from our application data store. The information in many cases will already be structured in a hierarchy so its easier to retrieve it for a navigation structure. This allows us to create the main site’s navigation, breadcrumbs, or specific structures for different sections of our site in a pretty easy way to implement, no need to write additional files on to the webserver’s file-system and the caching mechanism shown in this article gives a boost for performance.

Database Site Navigation and Caching

For this tutorial I decided to build a small dummy website which uses both web.sitemap for the main navigation and a sub navigation using my custom SiteMapProvider for a news articles section.

I dont want to elaborate too much on the actual code example so if you’re interested in seeing it work just download the website code I include in this article and explore the files in it. I actually wanted to focus more on the caching and caching dependency being used.

I used a sqlCacheDependency in order to notify my web application on changes made to the database table holding the information for the SiteMap. The first thing to do in order to get it working is to set up caching by adding the following elements to the Web.Config file:

                  .
                  .
                  .
                  <caching>
                       <sqlCacheDependency enabled="true" />
                  </caching>
                </system.web>

Nothing else is needed when using SQL 2005 (not tested on 2008) which has the Broker service that is used for the notifications about changes.
For MSSQL 7/2000 there are more parameters involved and then it works by polling the table.

Second thing is to tell the database that we’ll be passing dependency information, we do this by calling the static (shared) Start method: SqlDependency.Start(_ConnectionString) in the provider’s Initialize method.

In the overridden BuildSiteMap i used the SqlCommand object to execute my select query in the stored procedure: SProc_NEWSMAP_CTE
which looks like this:

WITH NodeTree (NewsID, NewsType, NewsTitle, NewsParentID,
                              Roles, NewsDescription, URL) AS
   (
      SELECT NewsID, NewsType, NewsTitle, NewsParentID, Roles,
      NewsDescription, URL
      FROM dbo.NEWS_MAP
      WHERE (NewsParentID IS NULL OR NewsParentID = 0)

     UNION ALL

      SELECT dbo.NEWS_MAP.NewsID, dbo.NEWS_MAP.NewsType,
      dbo.NEWS_MAP.NewsTitle, dbo.NEWS_MAP.NewsParentID,
      dbo.NEWS_MAP.Roles, dbo.NEWS_MAP.NewsDescription,
      dbo.NEWS_MAP.URL
      FROM dbo.NEWS_MAP
      INNER JOIN NodeTree ON NodeTree.NewsID = dbo.NEWS_MAP.NewsParentID
   )
   SELECT NewsID, NewsType, NewsTitle, NewsParentID, Roles,
    NewsDescription, URL
   FROM NodeTree 

This query allows to get all the nodes already ordered in the hierarchy at one go.

The problem is, though, that there are quite a few (allot actually!) of limitations on the kind of select query you can use for the sqlCacheDependency and my CTE query does not conform to those limitations which include: no subqueries, no computed fields and no fields or tables aliases. For the complete list check out:  http://msdn2.microsoft.com/en-US/library/ms181122.aspx.

What I did then was to create a second simple stored procedure that selects everything from the table(over simplified of course),  no “Select *” though, because its also not allowed.

I used another SqlCommand object to execute the select but before calling Execute and before actually opening the connection we need to pass the command to the SqlCacheDependency object like so:

Dim CommDep As New SqlCommand(“SProc_NEWSMAP_SELECT”, objConn)

CommDep.CommandType = CommandType.StoredProcedure

 Dim SMDependency As New SqlCacheDependency(CommDep)

After opening the connection and executing the command we can insert the dependency object to the HttpRunTime.Cache:

HttpRuntime.Cache.Insert(_cacheDependencyName, New Object(), SMDependency, Cache.NoAbsoluteExpiration, _
Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable, _
New CacheItemRemovedCallback(AddressOf OnSiteMapChanged))

The interesting part in this Insert call is the last parameter, by giving the address of the OnSiteMapChanged method we tell ASP.NET to call it when the item is removed from the cache, as we dont set any expiration time limit this should happen when the data in the table is changed. When OnSiteMapChanged is called we can clear the information we’re holding in the provider and next time the provider is called we will reload the information from the database.

Private Sub OnSiteMapChanged(ByVal key As String, ByVal item As Object, ByVal reason As CacheItemRemovedReason)
If key = _cacheDependencyName And reason = CacheItemRemovedReason.DependencyChanged Then Clear()
End Sub

That basically is all there is to it, to use the database in the downloadable code you can attach it to a 2005 SQLServer.  Dont forget to enable the Broker service for this DB with:  ALTER DATABASE NewsSite SET ENABLE_BROKER
If you do decide to try it you may notice that the NewsUser i created in the database is defined as db_owner, this is because I was lazy, In a real website this wont be the case but then there are some permissions to set in order for the cache dependency to work, I’ve added a script example in the download as well.

An important point when debugging SQL dependencies issues is first make sure your notification request has been successfully registered in the DB server using the following command: select * from sys.dm_qn_subscriptions

Source Code

Can be downloaded here.

Resources

1. Introduction to Microsoft ASP.NET 2.0
2. Creating a Query for Notification
3. Custom Site Map Providers in ASP.NET 2.0