From: Dave on
I'm using the below code with no problem except when trying to use a
parameter. The Parameter is supposed to be passed in the BuildSiteMap
method....but it's not. I've ran it through SQL Profiler and the
parameter isn't even showing up. Can anyone tell me what I may be
doing wrong?

Thanks


/////////////////////////////////////////////////////////////////////////////
Web.Config
/////////////////////////////////////////////////////////////////////////////
<siteMap defaultProvider="SqlSiteMapProvider">
<providers>
<add name="SqlSiteMapProvider" type="Web.SqlSiteMapProvider"
providerName="System.Data.SqlClient"
connectionStringName="PLIConnectionString"/>
</providers>
</siteMap>

/////////////////////////////////////////////////////////////////////////////
SQLSiteMapProvider.cs
/////////////////////////////////////////////////////////////////////////////
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.CompilerServices;
using System.Web;

namespace Web
{
public class SqlSiteMapProvider : StaticSiteMapProvider
{
static readonly string _errmsg1 =
"Missing connectionStringName attribute";
static readonly string _errmsg2 = "Duplicate node ID";
SiteMapNode _root = null;
string _connect;

public override void Initialize(string name,
NameValueCollection attributes)
{
base.Initialize(name, attributes);

if (attributes == null)
throw new ConfigurationException(_errmsg1);

_connect = attributes["connectionStringName"];
if (String.IsNullOrEmpty(_connect))
throw new ConfigurationException(_errmsg1);
}

[MethodImpl(MethodImplOptions.Synchronized)]
public override SiteMapNode BuildSiteMap()
{
// Return immediately if this method has been called
before
if (_root != null) return _root;

// Create a dictionary for temporary node storage and
lookup
Dictionary<int, SiteMapNode> nodes = new Dictionary<int,
SiteMapNode>(16);

// Query the database for site map nodes


string strConnection =
ConfigurationManager.ConnectionStrings[_connect].ConnectionString;
using (SqlConnection connection = new
SqlConnection(strConnection))
{
try
{
connection.Open();
SqlCommand command = new SqlCommand();

command.CommandText = "GetSiteMap";

command.Parameters.Add("@App", SqlDbType.Int);
command.Parameters["@App"].Value =
Convert.ToInt16(HttpContext.Current.Profile["App"].ToString());

command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;

SqlDataReader reader = command.ExecuteReader();

int id = reader.GetOrdinal("ID");
int url = reader.GetOrdinal("Url");
int title = reader.GetOrdinal("Title");
int desc = reader.GetOrdinal("Description");
int roles = reader.GetOrdinal("Roles");
int parent = reader.GetOrdinal("Parent");

if (reader.Read())
{
// Create the root SiteMapNode
_root = new SiteMapNode(this,
reader.GetInt32(id).ToString(),
reader.IsDBNull(url) ? null :
reader.GetString(url),
reader.GetString(title),
reader.IsDBNull(desc) ? null :
reader.GetString(desc));

if (!reader.IsDBNull(roles))
{
string rolenames =
reader.GetString(roles).Trim();
if (!String.IsNullOrEmpty(rolenames))
{
string[] rolelist = rolenames.Split(
new char[] { ',', ';' }, 512);
_root.Roles = rolelist;
}
}

// Add "*" to the roles list if no roles are
specified
if (_root.Roles == null) _root.Roles = new
string[] { "*" };

// Record the root node in the dictionary
if (nodes.ContainsKey(reader.GetInt32(id)))
throw new
ConfigurationException(_errmsg2);
nodes.Add(reader.GetInt32(id), _root);

// Add the node to the site map
AddNode(_root, null);

// Build a tree of SiteMapNodes underneath the
root
while (reader.Read())
{
SiteMapNode node = new SiteMapNode(this,
reader.GetInt32(id).ToString(),
reader.IsDBNull(url) ? null :
reader.GetString(url),
reader.GetString(title),
reader.IsDBNull(desc) ?
null : reader.GetString(desc));

if (!reader.IsDBNull(roles))
{
string rolenames =
reader.GetString(roles).Trim();
if (!String.IsNullOrEmpty(rolenames))
{
string[] rolelist =
rolenames.Split
(new char[] { ',', ';' },
512);
node.Roles = rolelist;
}
}

// If the node lacks roles information,
// "inherit" that information from its
parent
SiteMapNode parentnode =
nodes[reader.GetInt32(parent)];
if (node.Roles == null)
node.Roles = parentnode.Roles;

// Record the node in the dictionary
if
(nodes.ContainsKey(reader.GetInt32(id)))
throw new
ConfigurationException(_errmsg2);
nodes.Add(reader.GetInt32(id), node);

// Add the node to the site map
AddNode(node, parentnode);
}
}
}
finally
{
connection.Close();

}
// Return the root SiteMapNode
return _root;
}
}
protected override SiteMapNode GetRootNodeCore()
{
BuildSiteMap();
return _root;
}
}
}