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

Well it's been a while since I've had something decent to blog about, however I warn you what I'm about to blog about isn't really decent, its more of a side comment / observation. A while a go I posted about using ASP.NET 2's site navigation control with SQL, I have been playing with my modified of this for a while, and all seemed to be working fine in demo's and general development. However last week an interesting bug appeared. When the site's application is restarted, i.e cache cleared and the site navigation is rebuilt, the site would crash out the first two times the navigation tries to rebuild. The third time however results in the site loading perfectly fine. I have to say this behaviour baffled me. The first thing I did was to download the site's live database and run against a more debug friendly version of the site's code. Now the error message being thrown up was that the current node / page had an invalid parent. So of course I then checked the database only to find it did have a valid parent and all was ship shape data wise. I decided to deactive this page only to find another page casued the error, so I disabled the next suspect along with 2 others. Now this all seemed very odd, why would some pages error and others not? I couldn't figure it out, however I did realise one situation where this bug appeared for a genuine reason. I decided to add an "active" field next to each page so you could turn off pages until they were ready to go live. Now you can do this for a parent page, however all the child pages are left active. This would then oviously cause the script to chuck out errors as our SQL that returns all the pages for the navigation doesn't check to see if a pages parent is active. So how do you cater for this situation? Well I thought of a couple of ways, you could make your SQL, be it a stored procedure or plain T-SQL statement, join the parent Id's using an Inner Join and ensure the parents are active, this way any inactive parents are left off. However if you have a lot of pages in your database and the site has a steady flow of traffic, you could end up with a potential bottleneck. You could ensure that when a page is deactivated that all it's child pages and then their child pages are deactivaed, again this would be crazy as if you have lots of child pages which than have further child pages you would end up with unrelistic amount of update statements to perform. So what is the answer? Well i decided to do the required work at the business logic layer of my system. We have our SQLSiteMapProvider and it checks to see that the Parent ID of a node is valid. At present this churns out a nice provider exception error message so I decided to update this to handle things differently. I decided to go down the root of instead of causing a provider error if the parent id is invalid then simply return null instead of a sitemap node for the current page. Then where we call the check if parent is valid function, check if null is returned. If it isn't add the node to our sitemap, if not then don't worry carry on. This way any orphaned children are just ignored and the end user knows no difference. Now before anyone else comments, I know this isn't a grand solution and I'm sure that there will be pros and con's for a SQL solution and this solution or maybe even a better way, please comment if you have a better way, however for the hour I had to look at this I felt this was the best solution. It even masked the random page error that was occuring at the start, hopefully when I get chance to rebuild my main site using this code I will be able to take some time to relook at this provider and come up with a better solution. Until then if you want a copy of the SQLSiteMapProvider I am using then grab this zip file.

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.