Custome Site Map to build menus from Database instead of XML

We will use the database as shown in fig.
ID, ParentNodeID are integers
rest of the fields are string.







Make following entry in the web.config file.





Now create following class which implements the StaticSitemapProvider

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Collections;
using System.Collections.Specialized;
using System.Data.SqlClient;
using System.Security.Permissions;

///

/// Summary description for SqlSiteMapProvider
///

public class SqlSiteMapProvider : StaticSiteMapProvider
{

private SiteMapNode rootNode = null;
private SqlConnection sqlConnection = null;
private string sp = string.Empty;

// This string is case sensitive.
private string sqlConnectionStringName = "sqlSiteMapConnectionString";

// Implement a default constructor.
public SqlSiteMapProvider() { }

// Some basic state to help track the initialization state of the provider.
private bool initialized = false;
public virtual bool IsInitialized {
get {
return initialized;
}
}
// Return the root node of the current site map.
public override SiteMapNode RootNode {
get {
SiteMapNode temp = null;
temp = BuildSiteMap();
return temp;
}
}
protected override SiteMapNode GetRootNodeCore() {
return RootNode;
}
// Initialize is used to initialize the properties and any state that the
// SqlProvider holds, but is not used to build the site map.
// The site map is built when the BuildSiteMap method is called, it reads the values from the web.config
// in the attributes namevaluecollection.
public override void Initialize(string name, NameValueCollection attributes) {
if (IsInitialized)
return;

base.Initialize(name, attributes);

// Create and test the connection to the Microsoft Access database.

// Retrieve the Value of the Access connection string from the
// attributes NameValueCollection.
string connectionString = attributes[sqlConnectionStringName];
sp = attributes["storedProcedure"];

if (null == connectionString || connectionString.Length == 0)
throw new Exception("The connection string was not found.");
else
sqlConnection = new SqlConnection(connectionString);

initialized = true;
}

///
/// SiteMapProvider and StaticSiteMapProvider methods that this derived class must override.
///
// Clean up any collections or other state that an instance of this may hold.
protected override void Clear() {
lock (this) {
rootNode = null;
base.Clear();
}
}

// Build an in-memory representation from persistent
// storage, and return the root node of the site map.
public override SiteMapNode BuildSiteMap() {

int parentNodeId = 0;
// Since the SiteMap class is static, make sure that it is
// not modified while the site map is built.
lock(this) {

// If there is no initialization, this method is being
// called out of order.
if (! IsInitialized) {
throw new Exception("BuildSiteMap called incorrectly.");
}

// If there is no root node, then there is no site map.
if (null == rootNode) {
// Start with a clean slate
Clear();

// Select the root node of the site map from Microsoft Access.
int rootNodeId = -1;

if (sqlConnection.State == ConnectionState.Closed)
sqlConnection.Open();
SqlCommand rootNodeCommand = sqlConnection.CreateCommand();
rootNodeCommand.CommandText = sp;
rootNodeCommand.CommandType = CommandType.StoredProcedure;
SqlDataReader rootNodeReader = rootNodeCommand.ExecuteReader();

while( rootNodeReader.Read()) {

if (!rootNodeReader.IsDBNull(0))
{
rootNodeId = rootNodeReader.GetInt32(0);
}
// Create a SiteMapNode that references the current StaticSiteMapProvider.
if (rootNodeReader.IsDBNull(4))
{
rootNode = new SiteMapNode(this,
rootNodeId.ToString(),
rootNodeReader.GetString(1),
rootNodeReader.GetString(2),
rootNodeReader.GetString(3));
parentNodeId = rootNodeId;
}
else if (rootNodeReader.GetInt32(4) == parentNodeId)
{
SiteMapNode childNode = new SiteMapNode(this,
rootNodeId.ToString(),
rootNodeReader.GetString(1),
rootNodeReader.GetString(2),
rootNodeReader.GetString(3));
AddNode(childNode, rootNode);
}
else
{
SiteMapNode ch1 = new SiteMapNode(this,
rootNodeId.ToString(),
rootNodeReader.GetString(1),
rootNodeReader.GetString(2),
rootNodeReader.GetString(3));

SiteMapNode parent = FindSiteMapNodeFromKey(rootNodeReader.GetInt32(4).ToString());
AddNode(ch1,parent);
}
}


rootNodeReader.Close();
// Select the child nodes of the root node.

sqlConnection.Close();
}
return rootNode;
}
}

}