Developing a custom data provider for an ASP.Net sitemap
I recently was tasked with creating a sitemap provider that uses a database as a datasource. However, we weren’t sure ahead of time which database we would be using, so I had to ensure that the implementation would be flexible enough to be driven by SQL Server, Oracle, or even MySQL.
My sitemap provider is based heavily on Jeff Prosie’s article The SQL Site Map Provider You've Been Waiting For. I highly recommend you read it before continuing here.
Since he had bound his sitemap provider directly to SQL Server, I had to find an alternative way to drive my provider. I settled on the DataTable. I chose it because it’s a (relatively) simple object, and works well with the DataReader objects provided by any database vendor. While my example uses SQL Server, I’ll point out where you would need to make a trivial change to use another database.
Prosie's implementation also forbids a node being the parent to a node with a lesser ID. Due to the volatile nature of the website for which this provider was intended, my implementation now allows for any node to be a parent to any other node, with the exception of the root node which never has a parent.
Prosie’s SqlSiteMapProvider has a BuildSiteMap() function that builds the sitemap from a SQL Server stored procedure. In this function, he also implements caching using SQL Server’s custom SQLCacheDependency object. We’ll be building the sitemap differently, but it is very important to cache the sitemap because it is called so frequently. We’ll create our own caching mechanism that will support any data store.
1: public override SiteMapNode BuildSiteMap()
2: {
3:
4: // Return immediately if the sitemap is in the cache
5: if (HttpContext.Current.Cache[getCacheKey()] != null)
6: {
7: SiteMapNode mySn = (SiteMapNode) HttpContext.Current.Cache[getCacheKey()];
8: return mySn;
9: }
10:
11: //Lock this part to prevent race conditions
12: lock (_lock)
13: {
14:
15: dt = getSiteMapTable();
16: _indexID = dt.Columns["ID"].Ordinal;
17: _indexUrl = dt.Columns["Url"].Ordinal;
18: _indexTitle = dt.Columns["Title"].Ordinal;
19: _indexDesc = dt.Columns["Description"].Ordinal;
20: //Not using roles
21: //_indexRoles = dt.Columns("Roles").Ordinal
22: _indexParent = dt.Columns["Parent"].Ordinal;
23:
24: //We know our home section must always have an ID of 1
25: DataRow[] rootRow = dt.Select("ID = 1");
26: _root = CreateSiteMapNodeFromDataRow(rootRow[0]);
27: AddNode(_root, null);
28:
29: //I could order by a menuOrder field here. Currently menu items are ordered
30: //as they are ordered in the DB.
31: DataRow[] underRootRows = dt.Select("[Parent] = 1");
32:
33: //We have just grabbed all first-level links, now lets drill down further levels
34: if (underRootRows.Length > 0)
35: {
36: foreach (DataRow dr in underRootRows)
37: {
38: SiteMapNode node = CreateSiteMapNodeFromDataRow(dr);
39: AddNode(node, GetParentNodeFromDataRow(dr));
40: IterateThroughChildrenNodes(node);
41: }
42: }
43:
44: addNodeToCache(_root);
45: return (SiteMapNode) HttpContext.Current.Cache[getCacheKey()];
46: }
47: }
The first part of the function checks to see if we have a cached version of the sitemap we can use. If not, we’ll build another one. Building a new sitemap is done by calling getSiteMapTable(), which returns a DataTable.
getSiteMapTable() calls our very simple BLL/DAL object, which will talk to our database for us. Our sitemap provider doesn’t care which database, it simply expects a DataTable object from our business or data layer.
Our Sections object has a single GetListForSiteMap() function which returns the DataTable containing all the data we need to build a sitemap. I used SQL Server, but it would be trivial to change the data retrieval implementation to support any data store.
1: public static DataTable GetListForSiteMap()
2: {
3: using (SqlConnection conn =
4: new SqlConnection(
5: ConfigurationManager.ConnectionStrings["sectionConnection"].ConnectionString))
6: {
7: conn.Open();
8:
9: using (SqlCommand cmd = new SqlCommand("Select * from Section", conn))
10: {
11: SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
12: DataTable dt = new DataTable();
13: dt.Load(myReader);
14:
15: return dt;
16: }
17: }
18: }
Since we know our sitemap begins with the root node, and the root node must always have an ID of 1, we extract it from our DataTable so that we may begin building our sitemap.
1: DataRow[] rootRow = dt.Select("ID = 1");
2: _root = CreateSiteMapNodeFromDataRow(rootRow[0]);
3: AddNode(_root, null);
Now that we have our first node, we can start to examine its children.
1: if (underRootRows.Length > 0)
2: {
3: foreach (DataRow dr in underRootRows)
4: {
5: SiteMapNode node = CreateSiteMapNodeFromDataRow(dr);
6: AddNode(node, GetParentNodeFromDataRow(dr));
7: IterateThroughChildrenNodes(node);
8: }
9: }
For each of the root’s children, we then use the IterateThroughChildrenNodes() function to determine if our current node has any children we should examine and add to our sitemap.
1: private void IterateThroughChildrenNodes(SiteMapNode parentNode)
2: {
3: lock (_lock)
4: {
5: DataRow[] rootRows = dt.Select("[Parent] = " + parentNode.Key);
6: foreach (DataRow dr in rootRows)
7: {
8: SiteMapNode node = CreateSiteMapNodeFromDataRow(dr);
9: AddNode(node, GetParentNodeFromDataRow(dr));
10: IterateThroughChildrenNodes(node);
11: }
12: }
13: }
Note that this is a recursive function, so that each child that is examined also checks for its own children to add.
Once the sitemap is built, we must add it to the cache, where it will be served from after the initial request. It’s a one-liner:
1: protected void addNodeToCache(SiteMapNode node)
2: {
3: HttpContext.Current.Cache.Insert(getCacheKey(), node, null);
4: }
Our sitemap provider object now needs to be registered in the web.config. Paste the following into <system.web>.
1: <siteMap defaultProvider="MySiteMapProvider">
2: <providers>
3: <add name="MySiteMapProvider" type="nd.DataTableSitemapProvider"
4: securityTrimmingEnabled="false"></add>
5: </providers>
6: </siteMap>
Adding a SiteMapDataSource and a TreeView to a webpage, we can see our sitemap provider in action.
1: <asp:SiteMapDataSource ID="SiteMapDataSource1" runat="server" />
2: <asp:TreeView ID="TreeView1" runat="server" DataSourceID="SiteMapDataSource1" ShowLines="True">
3: </asp:TreeView>
One last thing to talk about is invalidating the cache. Obviously when data changes, your sitemap should be updated automatically. Prosie used the SQLCacheDependency object, but because we want something that will work with any data store, we’ll build our own caching and purging mechanism in our sitemap provider object.
1: protected virtual void purgeNodesFromCache()
2: {
3: List<string> itemsToRemove = new List<string>();
4: IDictionaryEnumerator enumerator = HttpContext.Current.Cache.GetEnumerator();
5:
6: while (enumerator.MoveNext())
7: {
8: if (enumerator.Key.ToString().StartsWith(getCacheKey()))
9: {
10: itemsToRemove.Add(enumerator.Key.ToString());
11: }
12: }
13:
14: foreach (string itemToRemove in itemsToRemove)
15: {
16: HttpContext.Current.Cache.Remove(itemToRemove);
17: }
18: }
To properly purge and refresh our sitemap, we should do it right after we make an update to our section data. I haven’t built a GUI to manage sections, so I’ve included that bit of code in the Default.aspx.vb. That way, if you edit the data directly within the database, the cache will be purged when the page is loaded and the new data read in. It’s a two-liner.
1: nd.DataTableSitemapProvider mysp =
2: (nd.DataTableSitemapProvider)SiteMap.Providers["MySiteMapProvider"];
3: mysp.OnSiteMapChanged();
Finally, be aware to avoid circular relationships among your nodes. If an ancestor node is updated to become a child of one of its descendents, you will need to create some mechanism to ensure your node structure remains a top-down hierarchy.
Sound off