Wicked Code
The SQL Site Map Provider You've Been Waiting For
Code download available at: Wicked Code 0602.exe(117 KB)
Contents
The New and Improved SQL Site Map Provider
Using SqlSiteMapProvider
Creating the Site Map Database
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. Figure 1 shows the components of the site navigation subsystem and illustrates how they fit together.
Figure 1** Navigation 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.
Figure 2 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:
- It demands SqlClientPermission to make sure it has permission to access databases. Without that permission, SqlSiteMapProvider is powerless to operate.
- It calls the base class's Initialize method, which, among other things, processes the securityTrimmingEnabled configuration attribute, if present.
- It processes the connectionStringName and sqlCacheDependency configuration attributes, if present.
- It throws an exception if the element that registers the provider contains unrecognized configuration attributes.
Figure 2 SqlSiteMapProvider.cs
[SqlClientPermission(SecurityAction.Demand, Unrestricted=true)]
public class SqlSiteMapProvider : StaticSiteMapProvider
{
... // static error messages omitted
const string _cacheDependencyName = "__SiteMapCacheDependency";
private string _connect;
private string _database, _table;
private bool _2005dependency = false;
private int _indexID, _indexTitle, _indexUrl, _indexDesc,
indexRoles, _indexParent;
private Dictionary<int, SiteMapNode> _nodes =
new Dictionary<int, SiteMapNode>(16);
private SiteMapNode _root;
private readonly object _lock = new object();
public override void Initialize (
string name, NameValueCollection config)
{
// Verify parameters
if (config == null) throw new ArgumentNullException("config");
if (String.IsNullOrEmpty(name)) name = "SqlSiteMapProvider";
// Add a default "description" attribute to config if the
// attribute doesn't exist or is empty
if (string.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "SQL site map provider");
}
// Call the base class's Initialize method
base.Initialize(name, config);
// Initialize _connect
string connect = config["connectionStringName"];
if (String.IsNullOrEmpty(connect))
throw new ProviderException(_errmsg5);
config.Remove("connectionStringName");
if (WebConfigurationManager.ConnectionStrings[connect] == null)
throw new ProviderException(_errmsg6);
_connect = WebConfigurationManager.ConnectionStrings[
connect].ConnectionString;
if (String.IsNullOrEmpty(_connect))
throw new ProviderException(_errmsg7);
// Initialize SQL cache dependency info
string dependency = config["sqlCacheDependency"];
if (!String.IsNullOrEmpty(dependency))
{
if (String.Equals(dependency, "CommandNotification",
StringComparison.InvariantCultureIgnoreCase))
{
SqlDependency.Start(_connect);
_2005dependency = true;
}
else
{
// If not "CommandNotification", then extract
// database and table names
string[] info = dependency.Split(new char[] { ':' });
if (info.Length != 2)
throw new ProviderException(_errmsg8);
_database = info[0];
_table = info[1];
}
config.Remove("sqlCacheDependency");
}
// Throw an exception if unrecognized attributes remain
if (config.Count > 0)
{
string attr = config.GetKey(0);
if (!String.IsNullOrEmpty(attr))
throw new ProviderException(
"Unrecognized attribute: " + attr);
}
}
public override SiteMapNode BuildSiteMap()
{
lock (_lock)
{
// Return immediately if this method has been called before
if (_root != null) return _root;
// Query the database for site map nodes
using(SqlConnection connection = new SqlConnection(_connect))
{
SqlCommand command = new SqlCommand(
"proc_GetSiteMap", connection);
command.CommandType = CommandType.StoredProcedure;
// Create a SQL cache dependency if requested
SqlCacheDependency dependency = null;
if (_2005dependency)
dependency = new SqlCacheDependency(command);
else if (!String.IsNullOrEmpty(_database) &&
!String.IsNullOrEmpty(_table))
dependency = new SqlCacheDependency(_database,
_table);
connection.Open();
SqlDataReader reader = 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())
{
// Create the root SiteMapNode and add it to site map
_root = CreateSiteMapNodeFromDataReader(reader);
AddNode(_root, null);
// Build a tree of SiteMapNodes under the root node
while (reader.Read())
{
// Create another site map node and add it
AddNode(CreateSiteMapNodeFromDataReader(reader),
GetParentNodeFromDataReader(reader));
}
// Use the SQL cache dependency
if (dependency != null)
{
HttpRuntime.Cache.Insert(_cacheDependencyName,
new object(), dependency,
Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration,
CacheItemPriority.NotRemovable,
new CacheItemRemovedCallback(
OnSiteMapChanged));
}
}
}
// Return the root SiteMapNode
return _root;
}
}
protected override SiteMapNode GetRootNodeCore ()
{
return BuildSiteMap();
}
... // Helper methods CreateSiteMapNodeFromDataReader and
// GetParentNodeFromDataReader
}
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:
// In Initialize
SqlDependency.Start(_connect);
// In BuildSiteMap
dependency = new SqlCacheDependency(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 = new SqlCacheDependency(_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,
new object(), dependency,
Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration,
CacheItemPriority.NotRemovable,
new CacheItemRemovedCallback(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 Figure 2).
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 Figure 3 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.
Figure 3 Cache Dependencies (SQL Server 7.0 and 2000)
<configuration>
<connectionStrings>
<add name="SiteMapConnectionString"
connectionString="..."
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<siteMap enabled="true" defaultProvider="AspNetSqlSiteMapProvider">
<providers>
<add name="AspNetSqlSiteMapProvider"
type="SqlSiteMapProvider"
securityTrimmingEnabled="true"
connectionStringName="SiteMapConnectionString"
sqlCacheDependency="SiteMapDatabase:SiteMap" />
</providers>
</siteMap>
<caching>
<sqlCacheDependency enabled="true" pollTime="5000">
<databases>
<add name="SiteMapDatabase"
connectionStringName="SiteMapConnectionString" />
</databases>
</sqlCacheDependency>
</caching>
</system.web>
</configuration>
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 Figure 4. (You also need to run the ASP.NET worker process with dbo privileges for SQL Server 2005 cache dependencies to work automatically.)
Figure 4 Cache Dependencies (SQL Server 2005)
<configuration>
<connectionStrings>
<add name="SiteMapConnectionString"
connectionString="..."
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<siteMap enabled="true" defaultProvider="AspNetSqlSiteMapProvider">
<providers>
<add name="AspNetSqlSiteMapProvider"
type="SqlSiteMapProvider"
securityTrimmingEnabled="true"
connectionStringName="SiteMapConnectionString"
sqlCacheDependency="CommandNotification" />
</providers>
</siteMap>
<caching>
<sqlCacheDependency enabled="true" />
</caching>
</system.web>
</configuration>
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 Figure 5 creates one such table named SiteMap and seeds it with sample site map nodes.
Figure 5 SQL Script for Creating a SiteMap Table
-- Create the site map node table
CREATE TABLE [dbo].[SiteMap] (
[ID] [int] NOT NULL,
[Title] [varchar] (32),
[Description] [varchar] (512),
[Url] [varchar] (512),
[Roles] [varchar] (512),
[Parent] [int]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[SiteMap] ADD
CONSTRAINT [PK_SiteMap] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
-- Add site map nodes
INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent)
VALUES (1, 'Home', NULL, '~/Default.aspx', NULL, NULL)
INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent)
VALUES (10, 'News', NULL, NULL, '*', 1)
INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent)
VALUES (11, 'Local', 'News from greater Seattle', '~/Summary.aspx?CategoryID=0', NULL, 10)
INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent)
VALUES (12, 'World', 'News from around the world', '~/Summary.aspx?CategoryID=2', NULL, 10)
INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent)
VALUES (20, 'Sports', NULL, NULL, '*', 1)
INSERT INTO SiteMap (ID, Title, Description, Url, Roles, Parent)
VALUES (21, 'Baseball', 'What''s happening in baseball', '~/Summary.aspx?CategoryID=3', NULL, 20)
...
-- Create the stored proc used to query site map nodes
CREATE PROCEDURE proc_GetSiteMap AS
SELECT [ID], [Title], [Description], [Url], [Roles], [Parent]
FROM [SiteMap] ORDER BY [ID]
GO
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.
Jeff Prosiseis a contributing editor to MSDN Magazine and the author of several books, including Programming Microsoft .NET (Microsoft Press, 2002). He's also a cofounder of Wintellect, a software consulting and education firm.