Treeview Navigation - SqlCacheDependency and SqlSitemapProvider

How to create a Sitemap from a database table with the SqlSitemapProvider and use it to populate your web site's TreeView Navigation Control, with SqlCacheDependency built in.

If you are like me, you are always monkeying around with your web site(s), adding and subtracting or changing features and pages. With the advent of MasterPages in ASP.NET 2.0, it's much easier to have a navigation menu situated on your Master that can be made to appear automatically on every page. However, if you have "monkeyed around", you still need to be able to change the navigation menu to match your tinkering, and do so in an easy and reliable way. Editing HTML by hand to do this every time I change something is not my idea of either "easy" -- or "reliable".

The ASP.NET treeview control, which now comes standard with ASP.NET 2.0, has the ability to use a Web SiteMap (not a Google sitemap - that's a different schema) as its datasource. (By the way - all three "big" search engines are reading sitemaps now. You can even put the url to it in your robots.txt! )  Back in 2005, Jeff Prosise had a nice MSDN magazine article where he created a SqlSiteMap Provider that stored the "tree data"  and hierarchical relations in a self - referencing Sql Server table, and he used the ASP.NET menu control to illustrate it. Then he updated the code in a new article  (http://msdn.microsoft.com/msdnmag/issues/06/02/WickedCode/) and improved it.

Using the SqlSiteMap provider, you can also populate your TreeView navigation menu from the database information and  you can even set a SqlCacheDependency on it so that your pages aren't hitting the database every time somebody requests a page on your site. When you change the data in the database table since you have added or changed a site navigation item row, the Cache is automatically invalidated and the next page request goes and gets the fresh data from the database. Your navigation Treeview is automatically updated to reflect the changes, and your life is made lots easier.

I once had a manager who constantly walked around saying "Simplify, simplify, SIMPLIFY!". Boy, was he ever on to something! How many times have you seen developers (either through lack of knowledge or some other reason) "reinvent the wheel"? And they come up with a "Web 3.0" wheel complete with AJAX, except that it contributes absolutely nothing - other than to make the "wheel" more complicated and harder to use?

What I do is have an "admin" page that is locked down to my Admin role so that I am the only user who can get there. On that page I have a whole bunch of custom "widgets" that I use to check on and administrate my web site. I can view or search the Exception log table, look at traffic patterns, or edit my navigation menu. I can even restart the site or write a google - style sitemap manually (that operation is automated, but sometimes you want to do it again). I even have a page that reads the Sitemap xml file(s) and creates a page of links, just so the bots that miss my sitemap have something nice to chew on.

 

Here is an example of a Master Page's markup that can handle all this:

<%@ Master Language="C#" AutoEventWireup="true" CodeFile="Default.master.cs" Inherits="_DefaultMaster" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<style>
        .toolbar
        {
         font-size: 12px;
         font-family: Verdana;       
         padding: 4px;
         filter: 
         progid:DXImageTransform.Microsoft.Gradient(gradientType=0,startColorStr=#ffffff,endColorStr=lightblue);
        }
</style>
    <title>TEST SITEMAP PROVIDER TREEVIEW MASTER</title>
</head>
<body>
    <form id="form1" runat="server">
    <div style="float:left;">
        
        &nbsp; &nbsp;
        <asp:SiteMapDataSource ID="SiteMapDataSource1" runat="server" SiteMapProvider="SqlSiteMapProvider"  />
 
        <asp:ContentPlaceHolder ID=header runat=server>
        
        <div id=headerDiv align="center" style="margin-top:0px;margin-left:220px;width:80%;height:100px;">
        <h1>Your Page header here</h1>
        </div>
        </asp:ContentPlaceHolder>
        
        
        
        <asp:contentplaceholder id="ContentPlaceHolder1" runat="server">
            <asp:TreeView ID="TreeView1" runat="server" DataSourceID="SiteMapDataSource1" BackColor="Transparent" ExpandDepth="1" MaxDataBindDepth="2" NodeIndent="10" SkipLinkText="skip nav" Target="~/Default.aspx" ToolTip="navigation" Font-Names="Verdana" Font-Size="Smaller" ForeColor="Gray">
                <ParentNodeStyle ImageUrl="~/images/folder.gif" />
                <SelectedNodeStyle ImageUrl="~/images/ittyfolder.gif" />
                <RootNodeStyle ImageUrl="~/images/folder.gif" />
                <NodeStyle ImageUrl="~/images/ittyfolder.gif" />
                <HoverNodeStyle CssClass="toolbar" />
            </asp:TreeView>
        </asp:contentplaceholder>
    </div>
    
    <asp:ContentPlaceHolder ID=ContentMain runat=server>
    
    <div style="float:none;width:80%;margin-top:100px;margin-left:200px;">
    
    <h1>Your other cool page content here.</h1>
    </div>
    
    </asp:ContentPlaceHolder>
    </form>
</body>
</html>

Your web.config will need to sport some relatively uncomplicated extras that look like this:
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
    <appSettings/>
  <connectionStrings>
    <add name="SiteMapConnectionString" connectionString="server=(local);database=test;user id=sa;password=yourpass;"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.web>
    <siteMap defaultProvider="SqlSiteMapProvider" enabled="true">
      <providers>
        <add name="SqlSiteMapProvider" type="SqlSiteMapProvider"
          securityTrimmingEnabled="false"
          connectionStringName="SiteMapConnectionString" />
          <add name="XmlSiteMapProvider" type="System.Web.XmlSiteMapProvider" siteMapFile="web.SiteMap"  />
      </providers>
    </siteMap>
The control, in un-expanded and expanded mode, can look like these:



The ASP.NET Treeview control is highly customizable in many different ways. With some carefully chosen CSS and your own custom images for various states, and an "Admin" page that allows you to edit the database table data, you'll be off to a smashing start with a Treeview navigation menu that really integrates with your site!

You can download the sample Visual Studio 2005 Web Application Project, which includes images and the Sql script that will create the sample "TEST" database, the sitemap table and data, and the stored proc.
By Peter Bromberg   Popularity  (4835 Views)