More ASP.Net Antics - using the Site Navigation with SQL

Now recently I have been building a website for a mate's mum's church. Mainly just in my spare time and have used it to learn .Net. Initially I was going to knock something together using an old PHP 5 CMS I knocked together at uni. But me being me wanted to learn .Net as it was about time I stated. Anyway I have been working on it for many a week now and it's coming along nicely, the point of this post is to discuss and share a development I made.

.Net 2's Site Map Provider

As standard ASP.Net 2's site navigation controls all use XML as there base. Now my basic CMS I have developed obviously stored everything in SQL. Which obviously isn't a simple XML file. My problem then was how do I use the navigation controls. My intital development, which left me learning a valued lesson I might add, lead me to start writing some that generated a new XML file for the site navigation as soon as the admin tool added / removed a page. As great as this was and it allowed me to learn to use XMLWriter it was a bit rubbish. Fancy having to write an XML file to the server all the time. Not an ideal solution. However shortly after this I was on the MSDN Magazines site and I found the following article this was a far better way. So I used it as it said to and low and behold the site navigation tool worked. Breadcrumbs, Nav all good. That was until I started looking at the code. Tables, no provision for selecting which pages would show up for nav customisation, no access keys for accessibility and must I say it again TABLES. So time for a MJJames customisation. The navigation control was easiest to solve as it involved making a custom control that used the SQLSiteMap datasource and then repeaters to render the navigation. My code for this will be available shortly here.

Modifying the SQLSiteMap code

The next step then adapting the SQLSiteMap datasource code. These changes involved 1) altering the SQL Stored Procedure so that instead of the SiteMap table the stored procedure used my page table and also returned the access key stored in the DB. This SP will also be found here in the code file. With the SP returning the right information it was time to customize the SQLSiteMap code to allow access key's to be inserted into the SiteMap XML being returned for use in the SiteNavigation Control. The first change was made to the initialization method. Within here I needed to add code to pull out which filter the navigation should, if any, use. The simplest way to store these would be in the web.config as this is customisable on a site per site basis. So:

sqlFilter = "";

    if (config["filter"] != null)
    {
        sqlFilter = config["filter"];
        config.Remove("filter");
    }

allows us so first set the sqlFilter to an empty string, so we don't get any nasty errors. We then query the config for the filter property. If the property isn't null then lets use it and then remove it from our config object so that we know it's used and it's not an unrecognised attribute and therefore can't break anything. With this done i decided to update

 public override SiteMapNode BuildSiteMap() 

within here the various indexes are set up for the siteMap, as I needed to another attribute another index needed to be added. So I added

                 _indexAccessKey = reader.GetOrdinal("AccessKey"); 

This then gets our AccessKey out of our recordset and were hunky dorey. Now we have the AccessKey in our index we need to use it. The method used to render / build our sitemap

    private SiteMapNode CreateSiteMapNodeFromDataReader (DbDataReader reader) 

To understand what we need to achieve in this method its worth getting to grip with what the method does. The method basically uses the indexes that were set in the BuildSiteMap method sees if there is any data in the datareader contained at he index location. If there is then we use it, if not we don't. Within here we need to pull out the data contained within the datareader that relates to our access key and then store it some how in the xml sitemap node. So the code for this little section takes on the form

 string accesskey = reader.IsDBNull(_indexAccessKey) ? null : reader.GetString(_indexAccessKey).Trim(); 

The next issue, as I hinted upon, was how do we get this data into our sitemap node. When I looked at the schema for the sitemap navigation, Web.sitemap XML schema, Microsoft talk about being able to store custom attributes as Named Value Pairs. This is exactly what I needed to do for accesskey's so i then created a namedvaluecolelction and added the accesskey to it.

        NameValueCollection attributes = new NameValueCollection();
    attributes.Add("AccessKey", accesskey); 

It's worth noting here that you could potentially add any other name value attributes here, so if you need to include any extra information into your navigation simply add it to this attributes collection using

 attributes.add()

The final step I took was to then update the insert sitemap node command to include the attributes collection, thus giving:

 SiteMapNode node = new SiteMapNode(this, id.ToString(), url, title, description, rolelist, attributes, null, null); 

. And that was it, the SQLSiteMapProvider now included accesskeys. And this process can be used to add any amount of attributes as you need. Just remember to work your way through the code and take time to understand what is being done, and why. My modified SQLSiteMapProvider, stored procedure and my simple navigation control using repeaters is all bundled together here, it's released here and this work is licensed under a Creative Commons Attribution-Share Alike 3.0 License.