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.