The SQL Site Map Provider You've Been Waiting For

Now that ASP.NET 2.0 is a shipping product, it seems appropriate to revisit an issue that tops the new features wish lists of many developers: a SQL Server^™ site map provider.

As you probably know, ASP.NET 2.0 vastly simplifies the process of building data-driven site navigation interfaces. You build a site map, slap a SiteMapDataSource control onto the page, and bind a Menu or TreeView control to the SiteMapDataSource. The SiteMapDataSource uses the default site map provider (typically XmlSiteMapProvider) to read the site map, and then passes site map nodes to the Menu or TreeView, which renders the nodes into HTML. For good measure, you can add a SiteMapPath control to the page, too. SiteMapPath displays the familiar breadcrumb element showing the path to the current page. Figure1 shows the components of the site navigation subsystem and illustrates how they fit together.

Figure 1Navigation System

The one drawback to site navigation is that XmlSiteMapProvider is the one and only site map provider included in the box with ASP.NET 2.0, which means that site maps must be stored in XML files. Even before ASP.NET 2.0 shipped, developers were clamoring for a means to store site maps in databases.

The June 2005 installment of Wicked Code presented one solution in the form of a custom site map provider named SqlSiteMapProvider. Unlike XmlSiteMapProvider, SqlSiteMapProvider reads site maps from SQL Server databases. And it takes advantage of the ASP.NET 2.0 provider architecture to integrate seamlessly with the site navigation subsystem. Just create the site map database and register SqlSiteMapProvider as the default provider, and then, like magic, everything else just works.

So, why do I want to revisit an issue that has been addressed before? Three reasons. First, after spending most of the summer digging deep into the provider architecture, I realized that my original SqlSiteMapProvider implementation needed a few improvements in order to be more consistent with the built-in XmlSiteMapProvider. Second, ASP.NET 2.0 saw some significant changes between Beta 2 and RTM, and I wanted to update SqlSiteMapProvider for the shipping platform. Finally, and most importantly, I wanted to add a feature that several readers e-mailed me about: automatic reloading of the site map following a change to the site map database. Without this feature, it seems that many readers consider a site map stored in SQL Server to be about as useful as an airplane without wings—something I learned about the hard way this summer when my favorite radio-control airplane clipped a fence post. But that's a story for another day.

The New and Improved SQL Site Map Provider

The upshot of this new and improved version of SqlSiteMapProvider is that it meets all my objectives and more. Its architecture is consistent with that of the built-in providers; it compiles and runs on the retail release of ASP.NET 2.0; and it uses the ASP.NET 2.0 SqlCacheDependency class to monitor the site map database and refresh the site map if changes occur. XmlSiteMapProvider has a similar feature that reloads the site map if the underlying XML site map file changes.

Figure2 lists the source code for the new SqlSiteMapProvider. The Initialize method, which is present in all providers, is a special one that ASP.NET calls after loading the provider. ASP.NET passes Initialize a NameValueCollection named config that contains all the configuration attributes (and their values) found in the configuration element that registered the provider. The Initialize method's job is to apply configuration settings and do anything else required to initialize the provider. SqlSiteMapProvider's Initialize method performs the following tasks:

1. It demands SqlClientPermission to make sure it has permission to access databases. Without that permission, SqlSiteMapProvider is powerless to operate.
2. It calls the base class's Initialize method, which, among other things, processes the securityTrimmingEnabled configuration attribute, if present.
3. It processes the connectionStringName and sqlCacheDependency configuration attributes, if present.
4. It throws an exception if the element that registers the provider contains unrecognized configuration attributes.

The sqlCacheDependency attribute is the one that allows you to take advantage of SqlSiteMapProvider's ability to refresh the site map if the underlying database changes. Setting sqlCacheDependency to "SiteMapDatabase:SiteMap" instructs the provider to refresh the site map if a table named SiteMap in a SQL Server 7.0 or a SQL Server 2000 database changes. ("SiteMapDatabase" indirectly specifies the database name by referring to an entry in the <databases> section of the <sqlCacheDependency> configuration section.) If the site map lives in a SQL Server 2005 database, you set sqlCacheDependency equal to "CommandNotification" instead. That's the high-level view; the details will come shortly.

The heart of SqlSiteMapProvider is its BuildSiteMap method. This method is called by ASP.NET sometime after the provider is loaded to build the site map, which is simply a collection of SiteMapNodes linked together to form a tree. Each SiteMapNode represents one node in the site map and is distinguished by the following properties: Title, which specifies the text that a navigation control displays for the node; Url, which specifies the URL the user is sent to when the node is clicked; Description, which specifies the descriptive text that's displayed if the cursor hovers over the node; and Roles, which specifies the role or roles that are permitted to view the node if security trimming is enabled ("*" if anyone can view it). Multiple roles can be specified using commas or semicolons as separators.

SqlSiteMapProvider's implementation of BuildSiteMap queries the site map database. Then it iterates over the records one by one, transforming them into SiteMapNodes. At the end, it hands the site map over to ASP.NET by returning a reference to the root site map node. And because all provider code outside the Initialize method must be thread-safe, SqlSiteMapProvider wraps everything in BuildSiteMap in a lock statement in order to serialize concurrent thread accesses.

In addition to querying the database and building the site map, BuildSiteMap also creates the basic infrastructure that enables SqlSiteMapProvider to refresh the site map if the site map database changes. If the configuration element that registered the provider contains a sqlCacheDependency="CommandNotification" attribute, BuildSiteMap creates a SQL Server 2005-compatible SqlCacheDependency object that wraps the SqlCommand used to query the site map database:

//InInitialize
SqlDependency.Start(_connect);

//InBuildSiteMap
dependency=newSqlCacheDependency(command);

If, on the other hand, the configuration element contains the kind of sqlCacheDependency configuration string used in SQL Server 7.0 or SQL Server 2000, (for example, "SiteMapDatabase:SiteMap"), BuildSiteMap creates a SqlCacheDependency object that wraps the supplied database name and table name:

//Initialize
_database=info[0];
_table=info[1];

//BuildSiteMap
dependency=newSqlCacheDependency(_database,_table);

Regardless of which type of SqlCacheDependency it created, BuildSiteMap later inserts a trivial object into the ASP.NET application cache and creates a dependency between that object and the database by including the SqlCacheDependency in the call to Cache.Insert:

if(dependency!=null)
...{
HttpRuntime.Cache.Insert(_cacheDependencyName,
newobject(),dependency,
Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration,
CacheItemPriority.NotRemovable,
newCacheItemRemovedCallback(OnSiteMapChanged));
}

The final parameter to Cache.Insert instructs ASP.NET to call the provider's OnSiteMapChanged method if the SqlCacheDependency triggers a cache removal—that is, if the site map database changes. OnSiteMapChanged clears out the old site map and calls BuildSiteMap to build a new one.

It may seem odd that SqlSiteMapProvider uses the ASP.NET application cache when there's really nothing for it to cache (after all, the object it inserts into the cache is simply a marker that contains no meaningful data), but doing so enables SqlSiteMapProvider to capitalize on a key feature of ASP.NET 2.0.

ADO.NET 2.0 has a SqlDependency class that enables application code to query SQL Server 2005 databases and receive callbacks if the underlying data changes, but it has no comparable feature for SQL Server 7.0 or SQL Server 2000. The ASP.NET 2.0 SqlCacheDependency class, by contrast, works with SQL Server 7.0, SQL Server 2000, and SQL Server 2005. Placing a marker object accompanied by a SqlCacheDependency in the cache and registering for cache removal callbacks is a convenient way to take advantage of the extra smarts built into SqlCacheDependency. If the underlying database changes, the marker object is removed from the cache and the callback method is called and can take whatever action it deems appropriate—in this case, refreshing the site map (see Figure2).

You can deploy SqlSiteMapProvider by copying SqlSiteMapProvider.cs to your Web site's App_Code folder. (In ASP.NET 2.0, source code files in that directory are automatically compiled.) Once the provider is deployed, you need to register it and make it the default site map provider. If you want to use its SQL cache dependency feature, you have to configure that, too. How you do it depends on whether the site map is stored in a SQL Server 7.0 or SQL Server 2000 database or a SQL Server 2005 database.

Using SqlSiteMapProvider

The web.config file in Figure3 shows how to configure SqlSiteMapProvider to use SQL cache dependencies if the site map is stored in a table named SiteMap in a SQL Server 7.0 or SQL Server 2000 database. The <connectionStrings> section defines a connection string named SiteMapConnectionString that identifies the database. The provider uses this to query the database and monitor the site map portion of it for changes. Obviously, you'll need to replace the ellipsis ("…") with a real connection string.

The <siteMap> section registers SqlSiteMapProvider and makes it the default site map provider. It also includes a sqlCacheDependency attribute that identifies the database and table where site map information is stored. The presence of this attribute tells SqlSiteMapProvider to create a SQLCacheDependency to monitor the site map database for changes; if you want to use SqlSiteMapProvider without cache dependencies, simply omit the sqlCacheDependency attribute.

The <sqlCacheDependency> section enables SQL cache dependencies in ASP.NET and supplies needed configuration information, including the polling interval which specifies how often ASP.NET checks the database for changes (in this example, every five seconds). The database name in this section maps to the database name specified in the provider's sqlCacheDependency attribute; the connection string name maps to the connection string in the <connectionStrings> section.

In order for these configuration settings to work, you must first prepare the site map database and the table containing site map data to support SQL cache dependencies. Two minutes with the aspnet_regsql.exe utility that comes with ASP.NET 2.0 is all it takes. If the database is a local one named SiteMapDatabase, first run the following command to prepare the database:

aspnet_regsql –S localhost –E –d SiteMapDatabase -ed

Then, if site map data is stored in the database's SiteMap table, run this command to prepare the table:

aspnet_regsql –S localhost –E –d SiteMapDatabase –t SiteMap -et

The first command adds a change notification table to the database, as well as stored procedures for accessing the table. The second adds an insert/update/delete trigger to the SiteMap table. When fired, the trigger inserts an entry into the change notification table, indicating that the contents of the SiteMap table have changed. ASP.NET 2.0 polls the change notification table at preprogrammed intervals to detect changes to the SiteMap table and to any other tables being monitored with SQL cache dependencies.

If you use SqlSiteMapProvider with a SQL Server 2005 database, you don't have to prepare the database to use SQL cache dependencies. You don't even have to identify the database and table containing the site map data; you just enable SQL cache dependencies with a <sqlCacheDependency> element and set SqlSiteMapProvider's sqlCacheDependency attribute to "CommandNotification," as shown in Figure4. (You also need to run the ASP.NET worker process with dbo privileges for SQL Server 2005 cache dependencies to work automatically.)

Configured thusly, SqlSiteMapProvider takes advantage of the SQL Server 2005 support built into ASP.NET by wrapping a SqlCacheDependency object around the SqlCommand object used to query the database for site map data. SqlCacheDependency, in turn, uses SQL Server 2005 query notifications to receive asynchronous callbacks indicating that data returned by the query has changed. No polling occurs and no special tables, stored procedures, or triggers are required. If you're looking for an excuse to upgrade your current database to SQL Server 2005, this feature alone is worth the price of admission for data-driven ASP.NET applications.

Creating the Site Map Database

A site map table created for SqlSiteMapProvider must conform to a predefined schema that lends itself to the representation of hierarchical data in a relational database. The SQL script in Figure5 creates one such table named SiteMap and seeds it with sample site map nodes.

Each record added to the table represents one site map node, and each has fields that map to SiteMapNode properties of the same name as well as fields that denote relationships between nodes. Each node must have a unique ID, which is stored in the ID field. To parent one node to another, you set the child node's Parent field equal to the ID of the parent. All nodes except the root node must have a parent. In addition, because of the way BuildSiteMap is implemented, a node can only be parented to a node with a lesser ID. (Note the ORDER BY clause in the database query.) For example, a node with an ID of 100 can be the child of a node with an ID of 99, but it can't be the child of a node with an ID of 101.

By default, SqlSiteMapProvider assumes that the table where site map data is stored is named SiteMap. SqlSiteMapProvider uses the stored procedure named proc_GetSiteMap to query the database for site map nodes, and this targets the SiteMap table. If you want to change the name of the site map table, simply change the table name in the database and in the stored procedure.

SqlSiteMapProvider demonstrates how compelling new features can be added to ASP.NET 2.0 through custom providers. To learn more about the ASP.NET 2.0 provider model and how to write custom providers of your own, check out ASP.NET 2.0 Provider Model: Introduction to the Provider Model.

Figure 2 SqlSiteMapProvider.cs

[SqlClientPermission(SecurityAction.Demand,Unrestricted=true)]
publicclassSqlSiteMapProvider:StaticSiteMapProvider
...{
...//staticerrormessagesomitted

conststring_cacheDependencyName="__SiteMapCacheDependency";

privatestring_connect;
privatestring_database,_table;
privatebool_2005dependency=false;
privateint_indexID,_indexTitle,_indexUrl,_indexDesc,
indexRoles,_indexParent;
privateDictionary<int,SiteMapNode>_nodes=
newDictionary<int,SiteMapNode>(16);
privateSiteMapNode_root;
privatereadonlyobject_lock=newobject();

publicoverridevoidInitialize(
stringname,NameValueCollectionconfig)
...{
//Verifyparameters
if(config==null)thrownewArgumentNullException("config");
if(String.IsNullOrEmpty(name))name="SqlSiteMapProvider";

//Addadefault"description"attributetoconfigifthe
//attributedoesn'texistorisempty
if(string.IsNullOrEmpty(config["description"]))
...{
config.Remove("description");
config.Add("description","SQLsitemapprovider");
}

//Callthebaseclass'sInitializemethod
base.Initialize(name,config);

//Initialize_connect
stringconnect=config["connectionStringName"];
if(String.IsNullOrEmpty(connect))
thrownewProviderException(_errmsg5);
config.Remove("connectionStringName");

if(WebConfigurationManager.ConnectionStrings[connect]==null)
thrownewProviderException(_errmsg6);
_connect=WebConfigurationManager.ConnectionStrings[
connect].ConnectionString;
if(String.IsNullOrEmpty(_connect))
thrownewProviderException(_errmsg7);

//InitializeSQLcachedependencyinfo
stringdependency=config["sqlCacheDependency"];

if(!String.IsNullOrEmpty(dependency))
...{
if(String.Equals(dependency,"CommandNotification",
StringComparison.InvariantCultureIgnoreCase))
...{
SqlDependency.Start(_connect);
_2005dependency=true;
}
else
...{
//Ifnot"CommandNotification",thenextract
//databaseandtablenames
string[]info=dependency.Split(newchar[]...{':'});
if(info.Length!=2)
thrownewProviderException(_errmsg8);
_database=info[0];
_table=info[1];
}

config.Remove("sqlCacheDependency");
}

//Throwanexceptionifunrecognizedattributesremain
if(config.Count>0)
...{
stringattr=config.GetKey(0);
if(!String.IsNullOrEmpty(attr))
thrownewProviderException(
"Unrecognizedattribute:"+attr);
}
}

publicoverrideSiteMapNodeBuildSiteMap()
...{
lock(_lock)
...{
//Returnimmediatelyifthismethodhasbeencalledbefore
if(_root!=null)return_root;

//Querythedatabaseforsitemapnodes
using(SqlConnectionconnection=newSqlConnection(_connect))
...{
SqlCommandcommand=newSqlCommand(
"proc_GetSiteMap",connection);
command.CommandType=CommandType.StoredProcedure;

//CreateaSQLcachedependencyifrequested
SqlCacheDependencydependency=null;
if(_2005dependency)
dependency=newSqlCacheDependency(command);
elseif(!String.IsNullOrEmpty(_database)&&
!String.IsNullOrEmpty(_table))
dependency=newSqlCacheDependency(_database,
_table);

connection.Open();
SqlDataReaderreader=command.ExecuteReader();
_indexID=reader.GetOrdinal("ID");
_indexUrl=reader.GetOrdinal("Url");
_indexTitle=reader.GetOrdinal("Title");
_indexDesc=reader.GetOrdinal("Description");
_indexRoles=reader.GetOrdinal("Roles");
_indexParent=reader.GetOrdinal("Parent");

if(reader.Read())
...{
//CreatetherootSiteMapNodeandaddittositemap
_root=CreateSiteMapNodeFromDataReader(reader);
AddNode(_root,null);

//BuildatreeofSiteMapNodesundertherootnode
while(reader.Read())
...{
//Createanothersitemapnodeandaddit
AddNode(CreateSiteMapNodeFromDataReader(reader),
GetParentNodeFromDataReader(reader));
}

//UsetheSQLcachedependency
if(dependency!=null)
...{
HttpRuntime.Cache.Insert(_cacheDependencyName,
newobject(),dependency,
Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration,
CacheItemPriority.NotRemovable,
newCacheItemRemovedCallback(
OnSiteMapChanged));
}
}
}

//ReturntherootSiteMapNode
return_root;
}
}

protectedoverrideSiteMapNodeGetRootNodeCore()
...{
returnBuildSiteMap();
}

...//HelpermethodsCreateSiteMapNodeFromDataReaderand
//GetParentNodeFromDataReader
}

Figure 3 Cache Dependencies (SQL Server 7.0 and 2000)

<configuration>
<connectionStrings>
<addname="SiteMapConnectionString"
connectionString="..."
providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<siteMapenabled="true"defaultProvider="AspNetSqlSiteMapProvider">
<providers>
<addname="AspNetSqlSiteMapProvider"
type="SqlSiteMapProvider"
securityTrimmingEnabled="true"
connectionStringName="SiteMapConnectionString"
sqlCacheDependency="SiteMapDatabase:SiteMap"/>
</providers>
</siteMap>
<caching>
<sqlCacheDependencyenabled="true"pollTime="5000">
<databases>
<addname="SiteMapDatabase"
connectionStringName="SiteMapConnectionString"/>
</databases>
</sqlCacheDependency>
</caching>
</system.web>
</configuration>

---

Figure 4 Cache Dependencies (SQL Server 2005)

<configuration>
<connectionStrings>
<addname="SiteMapConnectionString"
connectionString="..."
providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<siteMapenabled="true"defaultProvider="AspNetSqlSiteMapProvider">
<providers>
<addname="AspNetSqlSiteMapProvider"
type="SqlSiteMapProvider"
securityTrimmingEnabled="true"
connectionStringName="SiteMapConnectionString"
sqlCacheDependency="CommandNotification"/>
</providers>
</siteMap>
<caching>
<sqlCacheDependencyenabled="true"/>
</caching>
</system.web>
</configuration>

---

Figure 5 SQL Script for Creating a SiteMap Table

--Createthesitemapnodetable

CREATETABLE[dbo].[SiteMap](
[ID][int]NOTNULL,
[Title][varchar](32),
[Description][varchar](512),
[Url][varchar](512),
[Roles][varchar](512),
[Parent][int]
)ON[PRIMARY]
GO

ALTERTABLE[dbo].[SiteMap]ADD
CONSTRAINT[PK_SiteMap]PRIMARYKEYCLUSTERED
(
[ID]
)ON[PRIMARY]
GO

--Addsitemapnodes

INSERTINTOSiteMap(ID,Title,Description,Url,Roles,Parent)
VALUES(1,'Home',NULL,'~/Default.aspx',NULL,NULL)

INSERTINTOSiteMap(ID,Title,Description,Url,Roles,Parent)
VALUES(10,'News',NULL,NULL,'*',1)

INSERTINTOSiteMap(ID,Title,Description,Url,Roles,Parent)
VALUES(11,'Local','NewsfromgreaterSeattle','~/Summary.aspx?CategoryID=0',NULL,10)

INSERTINTOSiteMap(ID,Title,Description,Url,Roles,Parent)
VALUES(12,'World','Newsfromaroundtheworld','~/Summary.aspx?CategoryID=2',NULL,10)

INSERTINTOSiteMap(ID,Title,Description,Url,Roles,Parent)
VALUES(20,'Sports',NULL,NULL,'*',1)

INSERTINTOSiteMap(ID,Title,Description,Url,Roles,Parent)
VALUES(21,'Baseball','What''shappeninginbaseball','~/Summary.aspx?CategoryID=3',NULL,20)

...

--Createthestoredprocusedtoquerysitemapnodes

CREATEPROCEDUREproc_GetSiteMapAS
SELECT[ID],[Title],[Description],[Url],[Roles],[Parent]
FROM[SiteMap]ORDERBY[ID]
GO