Summer Street Systems

Dynamic Sitemaps …what a concept!  When I first learned of the Sitemap features in ASP.NET 2.0 I couldn’t wait to try it out.  I had struggled with menus for years and had written my own xml based menu control for my old 1.1 web site.  It took weeks before I finally had something that worked reasonably well.  Of coarse I learned a lot about xml and JavaScript in the process but that wasn’t my goal. I just wanted some decent menus without forking out a lot of money for something I was only going to use once or twice.  So I was over the moon when I saw how easy it was to build decent looking menus using the Sitemap control in ASP.NET 2.0.  Just like my old menus it’s pretty easy to update the new Sitemap menus by simply editing the xml file. 

But of coarse we always want more.  One of the big problems with web development is content management.  Wouldn’t it be nice to be able to build self managing web sites?  Imagine being able to log in as an administrator with menu items that only exist for you.  They give you access to a content management web page containing an HTML editor that lets you control the content of any page on the site.  This site uses the FreeTextBox control for just that purpose.  But what to do when you want to add new content? …a new case study or a new download item for example.  The simple (and old answer) is that you would update the xml file adding a new menu item and an url pointing to the content management page containing a query string parameter referencing the new menu item name.  There are plenty of solutions that don’t involve using the menu structure at all but that’s not the point.  If you do want to use menus then you need to be able to update them dynamically.

What a pain! But if the menus are in the database I can use a textbox and other controls on the content management page that lets me provide the new menu item and include some information about where it fits in the menu hierarchy. When the new content is saved some code updates the Sitemap table and the next time a page is loaded the new menu item appears and …Voila! My new content is now available to the world.  Of coarse I could also do this using the xml file but then you get into security issues, modifying files in the root directory of my site …yada, yada, yada.  No!  The database is definitely a better place for this.

So naturally I asked myself: How does this work?  How will my web site know when the Sitemap table in the database has changed?  It turns out that “as advertised” there are some new features in SQL server 2005 that work with ASP.NET 2.0 making this possible with a few fairly simple (but hard to find) changes to the configuration of SQL Server and the config.sys file of your web site.  It also turns out that this is not possible without some additional code that is not part of the currently released version of ASP.NET.  This article provides the detailed instructions required to implement Dynamic Sitemaps using a SQL Server 2005 database.  This can also be accomplished using SQL Server 2000 with a little extra work.  I’ll provide some pointers and reference some articles that should point you in the right direction.

Overview

Implementing Dynamic Sitemaps using SQL Server 2005 as the repository for the menu items requires the well coordinated interaction of several new technologies.  These can logically be broken down between the Sql Server and the Web Server.  On the Sql Server side you have to perform the following steps:

1. Create A Service Broker Endpoint
2. Enable the Service Broker on your database
3. Create the SiteMap table
4. Create the Stored Procedure
5. Assign the user that will access these db objects the appropriate permissions

On the Web Server side the following steps are required:

1. Implement a SqlSiteMapProvider class that use the SqlCacheDependency class
2. Add an entry in the <connectionStrings> section of the Web.config file to be used by the SqlSiteMapProvider
3. Modify the SiteMap section in the Web.config file to use the SqlSiteMapProvider and the connection string specified.
4. Add a <caching> section to the Web.config file to tell the application to use SqlCacheDependency.
5. Add code to the Applicaton_Start method in the Global.asax to start the SqlDependency.

The following instructions provide more background and detail for these steps:

Sql Server 2005

• Service Broker Endpoint – Sql Server 2005 needs to be configured as an endpoint for Broker Services.  This is a new feature in Sql Server 2005 providing asynchronous notification services to clients that subscribe to them. Check out this link to learn more about the Service Broker Endpoint.  The Service Broker is required to support the SqlCacheDependency used to let you web application know that the underlying data has changed and the cache needs to be refreshed.  The basic idea is that you’re going to create a procedure that is going to retrieve the Sitemap node data whenever the data changes.  Otherwise the application will use the data in the Cache. 

You can use the following TSQL code to create a Service Broker Endpoint:

USE master;
GO

CREATE ENDPOINT BrokerEndpoint
    STATE = STARTED
    AS TCP (LISTENER_PORT = 4037)
    FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS );
GO

Once the endpoint is created you need to enable it for the database that will contain the Sitemap table:

ALTER DATABASE MyDatabase SET ENABLE_BROKER;
GO

You can verify that your database has been enabled to use the broker service by executing the following:

select databasepropertyex('MyDatabase', 'IsBrokerEnabled')

1 indicates enabled, 0 means it is not enabled

• The SiteMap table

Create the SiteMap table in the database used to hold your menus.  Use the following TSQL code to create the SiteMap table;

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[SiteMap](

                [ID] [int] NOT NULL,

                [Title] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

                [Description] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

                [Url] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

                [Roles] [varchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

                [Parent] [int] NULL,

 CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED

(

                [ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

• The Stored Procedure

Once the table is created you can add the stored procedure that will be used to retrieve the sitemap nodes to build the menu on your web site:

CREATE PROCEDURE [dbo].[usp_GetSiteMap]

AS

BEGIN

    SELECT [ID], [Title], [Description], [Url], [Roles], [Parent]

    FROM [SiteMap] ORDER BY [ID]

END

NOTE: The name of the stored procedure is not important except that you must be sure that you are referencing it correctly in the SqlSiteMapProvider.  I have changed the name here slightly from the one in Jeff Prosise’s article on the SqlSiteMapProvider to be consistent with my own naming convention.  If you use his provider be sure that the procedure name created here matches the one used in the provider.

• Permissions

Access to the Service Broker requires special permissions so you need to make sure that the user that is going to be executing the procedure has the correct permissions. Suchil Chordia of the ADO.Net Team at Microsoft provides a good explanation of this in his blog.

ASP.NET 2.0

When the Sql Server has been properly configured and the SiteMap table and stored procedure have been created you will need to configure your web site to use them. 

• SqlSiteMapProvider

The SqlSiteMapProvider replaces the XmlSiteMapProvider used in the default implementation.  For a more complete explanation of the basics of how the Sitemap model is implemented check out this link.

The bad news is that the SqlSiteMapProvider is not available out of the ASP.NET 2.0 box.  The good news is there are a few implementations around that will satisfy most requirements.  If you’re still with me you probably aren’t ready to roll your own.  Even if you’re inclined to write it yourself this will give you a big heads up.  It’s not that difficult to figure out once you know what’s going on.  But since so much of this involves new configurations, features, classes, and methods you can probably use a little bit of help.  There are also a couple of potential gotchas so I would advise caution.  Jeff Prosise provides a good introduction to the SqlSiteMapProvider  in his June, 2005 Wicked Code column for MSDN Magazine.  He has since update it in the February, 2006 column.  This one implements the SqlCacheDependency and so it is the one recommended here. 

• Web.config

There are three areas of the Web.config file that require attention:

1.   Provide a connection string to be used by the SqlSiteMapProvider

<connectionStrings>

<add name="SiteMapConnectionString" connectionString="Data Source=MyServer;Integrated Security=true; Initial Catalog=MyDatabase;"       providerName="System.Data.SqlClient" />

</connectionStrings>

Note:  The database referred to is the one where the SiteMap table is located.

2.  Create the SiteMap

   <siteMap enabled="true" defaultProvider="AspNetSqlSiteMapProvider"  >

<providers>

<add name="AspNetSqlSiteMapProvider"

type="SqlSiteMapProvider"

securityTrimmingEnabled="true"

connectionStringName="SiteMapConnectionString"

sqlCacheDependency="CommandNotification" />

</providers>

   </siteMap>

3.  Specify the use of SqlCacheDependency

   <caching>

<sqlCacheDependency enabled="true" />

   </caching>

• Global.asax

Add the following lines of code to the Application_Start method:

string sqlconn = ConfigurationManager.ConnectionStrings["SiteMapConnectionString"].ToString();

        SqlDependency.Start(sqlconn);

This will ensure that the sqlDependency notification is started when your application starts up.  You can also add a SqlDependency.Stop to yourApplication_End method but it is not required.

Be sure to add the <%@ Import Namespace="System.Data.SqlClient" %> to the top of your Global.asax page or include using System.Data.SqlClient; if you are using code-behind

That’s it!  Crank up your app and change menus on the fly.  The changes will be reflected on the next refresh.  When the menus don’t change they are created from the cache without the need to query the db.  Fast, flexibility and scaleable.  Who could ask for more.  To see this in action you can log in as DemoUser (password – Guest@359),   After a successful login you should see an additional menu item “Sitemap Test”.  Follow these steps to see the dynamic sitemap in action:

1. Select Sitemap Test from the menu.  A test page will be displayed where you can add a content page and associate it with a menu item. 
2. Enter the menu item name and your content.
3. Use the floppy disc icon to save your work. 
4. When you click on the Sitemap Test option you will see a new menu item (the one you just created).
5. Select the new menu item and it will display your new content.

We hope you found this article informative and helpful. 

Thanks to all those on the ASP.NET forum who helped point me in the right direction.  A special thanks to Jeff Prosise for a couple of amazing Wicked Code articles and the excellent SqlSiteMapProvider class that I’m sure will be a very popular download. Also kudos to Sushil Cordia and the ADO.Net team for their implemention of the SqlDependency Class

-ron cicotte