programmatically create querystring and update sitemap node


D

Dwayne

I've tried getting an answer from the asp.net website, but haven't had any
luck so I'm posting here in the hopes someone might provide some help.

I am using a SqlSiteMapProvider that creates a sitemap dynamically from a
sql server database.

I also have a table in the database where I store page content that can be
displayed dynamically based on the page that is being requested. Some pages
have dynamic content and other pages just have static content.

here is my sitemap table:

CREATE TABLE [dbo].[sitemap](
[ID] [int] NOT NULL,
[Title] [varchar](32) NULL,
[Description] [varchar](512) NULL,
[varchar](512) NULL,
[Roles] [varchar](512) NULL,
[Parent] [int] NULL,
CONSTRAINT [PK_SiteMap] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO

Here is my web_item table that contains the data for pages with dynamic
content:

CREATE TABLE [dbo].[web_item](
[item_id] [int] IDENTITY(1,1) NOT NULL,
[sitemap_id] [int] NOT NULL,
[item_sitekey] [varchar](50) NOT NULL,
[item_title] [varchar](255)NULL,
[item_body] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF


web_item table has a foreign key relationship to sitemap table.

What I'd like to be able to do is programmatically generate a querystring
and update the sitemap nodes for pages that have dynamic content.

For example, in my sitemap table, I have a record for the home page, about
page and contact page...i.e. ~/default.aspx, ~/about.aspx and ~/contact.aspx
respectively.

The default and contact pages simply contain static content. The about page
contains dyanamic content.

So, when the about page is requested, I'm trying to figure out how I can
programmatically generate a querystring and update the sitemap node so I can
pass in a querystring value to a method that retrieves the content for that
page.

I need to dynamically generate the querystring. So, the about page url in
the database is:

~/about.aspx

but, when the page is requested, I want to add the querystring
programmatically so the url becomes:

~/about.aspx?sitekey=1.0 ("1.0" is the item_sitekey value stored in the
web_item table for the about page record).

Currently, I can hardcode the url in the sitemap table, but I want to
dynamically do this so as my sitemap becomes larger and more complex, the
generation of the url is automatic.

How can I accomplish this?

Any help is appreciated.

Thank you
 
Ad

Advertisements

A

Allen Chen [MSFT]

Hi,
Currently, I can hardcode the url in the sitemap table, but I want to
dynamically do this so as my sitemap becomes larger and more complex, the
generation of the url is automatic.
How can I accomplish this?

Could you tell me what do you mean by "as my sitemap becomes larger and
more complex, the
generation of the url is automatic"?

If you want to add a new node in sitemap you can insert a new record in
sitemap table. The URL can be controlled by you.

You can also change the stored procedure proc_GetSiteMap used by
SqlSiteMapProvider to return custom URL. For instance, in that stored
procedure you can append the ID (if you like you can set this field as self
increased identity) to the end of URL field and return as URL. You can even
customize SqlSiteMapProvider to meet your requirement since it's open
source.

If you could provide detailed scenario we can work together to see how to
resolve this issue.

Regards,
Allen Chen
Microsoft Online Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
D

Dwayne

Hi Allen,

As you suggested, I've modified the sql sitemap provider to use custom
attributes.

I've also modified the SQL that returns the menu data (from the sitemap
table) to also include the item_sitekey from the web_item table. Here is
the stored procedure that the sql sitemap provider class file calls:

CREATE PROCEDURE [dbo].[spGetSiteMap] AS
SELECT [sitemap].[ID], [Title], [Description], != null && reader["item_sitekey"] !=
DBNull.Value)
attributes.Add("SiteKey", reader["item_sitekey"].ToString());

SiteMapNode node = new SiteMapNode(this, id.ToString(), url, title,
description, rolelist, attributes, null, null);

So, in my webform page code-behind, I tried the following to
programmatically modify the sitemap node:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class About : System.Web.UI.Page
{

private SiteMapNode UpdateUrl(Object sender, SiteMapResolveEventArgs e)
{
SiteMapNode currentNode = SiteMap.CurrentNode.Clone(true);
SiteMapNode tempNode = currentNode;

string sitekey = GetSiteKey();
if (null != sitekey)
{
tempNode.Url = tempNode.Url + "?sitekey=" + sitekey.ToString();
}

return currentNode;
}

protected string GetSiteKey()
{
string sitekey = SiteMap.CurrentNode["SiteKey"].ToString();
return sitekey;
}

protected void Page_Load(object sender, EventArgs e)
{
SiteMap.SiteMapResolve += new
SiteMapResolveEventHandler(this.UpdateUrl);

}
}

I did a response write to verify the Sitemap.CurrentNode["SiteKey"] was
returning the correct value, which it is. But, the above is not updating
the url as I would expect. I'm getting object not set to instance of object
error on this line:

string sitekey = SiteMap.CurrentNode["SiteKey"].ToString();

What am I missing?

Below is the entire code to my sqlsitemap provider class file:

using System;
using System.Web;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Configuration;
using System.Web.Configuration;
using System.Collections.Generic;
using System.Configuration.Provider;
using System.Security.Permissions;
using System.Data.Common;
using System.Data;
using System.Web.Caching;

/// <summary>
/// Summary description for SqlSiteMapProvider
/// </summary>
///
namespace Library.Providers
{
[SqlClientPermission(SecurityAction.Demand, Unrestricted = true)]
public class SqlSiteMapProvider : StaticSiteMapProvider
{
#region Fields...

private const string _errmsg1 = "Missing node ID";
private const string _errmsg2 = "Duplicate node ID";
private const string _errmsg3 = "Missing parent ID";
private const string _errmsg4 = "Invalid parent ID";
private const string _errmsg5 = "Empty or missing
connectionStringName";
private const string _errmsg6 = "Missing connection string";
private const string _errmsg7 = "Empty connection string";
private const string _errmsg8 = "Invalid sqlCacheDependency";
private const string _cacheDependencyName =
"__SiteMapCacheDependency";

private string _connect; // Database connection string
private string _database, _table; // Database info for SQL
Server 7/2000 cache dependency
private bool _2005dependency = false; // Database info for SQL
Server 2005 cache dependency
private int _indexID, _indexTitle, _indexUrl, _indexDesc,
_indexRoles, _indexParent;
private Dictionary<int, SiteMapNode> _nodes = new Dictionary<int,
SiteMapNode>(16);
private readonly object _lock = new object();
private SiteMapNode _root;

#endregion

#region Methods...

public override void Initialize(string name, NameValueCollection
config)
{
// Verify that config isn't null
if (config == null)
throw new ArgumentNullException("config");

// Assign the provider a default name if it doesn't have one
if (String.IsNullOrEmpty(name))
name = "SqlSiteMapProvider";

// Add a default "description" attribute to config if the
// attribute doesn't exist or is empty
if (string.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "SQL site map provider");
}

// Call the base class's Initialize method
base.Initialize(name, config);

// Initialize _connect
string connect = config["connectionStringName"];

if (String.IsNullOrEmpty(connect))
throw new ProviderException(_errmsg5);

config.Remove("connectionStringName");

if (WebConfigurationManager.ConnectionStrings[connect] == null)
throw new ProviderException(_errmsg6);

_connect =
WebConfigurationManager.ConnectionStrings[connect].ConnectionString;

if (String.IsNullOrEmpty(_connect))
throw new ProviderException(_errmsg7);

// Initialize SQL cache dependency info
string dependency = config["sqlCacheDependency"];

if (!String.IsNullOrEmpty(dependency))
{
if (String.Equals(dependency, "CommandNotification",
StringComparison.InvariantCultureIgnoreCase))
{
SqlDependency.Start(_connect);
_2005dependency = true;
}
else
{
// If not "CommandNotification", then extract database
and table names
string[] info = dependency.Split(new char[] { ':' });
if (info.Length != 2)
throw new ProviderException(_errmsg8);

_database = info[0];
_table = info[1];
}

config.Remove("sqlCacheDependency");
}

// SiteMapProvider processes the securityTrimmingEnabled
// attribute but fails to remove it. Remove it now so we can
// check for unrecognized configuration attributes.

if (config["securityTrimmingEnabled"] != null)
config.Remove("securityTrimmingEnabled");

// Throw an exception if unrecognized attributes remain
if (config.Count > 0)
{
string attr = config.GetKey(0);
if (!String.IsNullOrEmpty(attr))
throw new ProviderException("Unrecognized attribute: " +
attr);
}
}

public override SiteMapNode BuildSiteMap()
{
lock (_lock)
{
// Return immediately if this method has been called before
if (_root != null)
return _root;

// Query the database for site map nodes
SqlConnection connection = new SqlConnection(_connect);

try
{
SqlCommand command = new SqlCommand("spGetSiteMap",
connection);
command.CommandType = CommandType.StoredProcedure;

// Create a SQL cache dependency if requested
SqlCacheDependency dependency = null;

if (_2005dependency)
dependency = new SqlCacheDependency(command);
else if (!String.IsNullOrEmpty(_database) &&
!string.IsNullOrEmpty(_table))
dependency = new SqlCacheDependency(_database,
_table);

connection.Open();
SqlDataReader reader = command.ExecuteReader();
_indexID = reader.GetOrdinal("ID");
_indexUrl = reader.GetOrdinal("Url");
_indexTitle = reader.GetOrdinal("Title");
_indexDesc = reader.GetOrdinal("Description");
_indexRoles = reader.GetOrdinal("Roles");
_indexParent = reader.GetOrdinal("Parent");

if (reader.Read())
{
// Create the root SiteMapNode and add it to the
site map
_root = CreateSiteMapNodeFromDataReader(reader);
AddNode(_root, null);

// Build a tree of SiteMapNodes underneath the root
node
while (reader.Read())
{
// Create another site map node and add it to
the site map
SiteMapNode node =
CreateSiteMapNodeFromDataReader(reader);
AddNode(node,
GetParentNodeFromDataReader(reader));
}

// Use the SQL cache dependency
if (dependency != null)
{
HttpRuntime.Cache.Insert(_cacheDependencyName,
new object(), dependency,
Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable,
new
CacheItemRemovedCallback(OnSiteMapChanged));
}
}
}
finally
{
connection.Close();
}

// Return the root SiteMapNode
return _root;
}
}

protected override SiteMapNode GetRootNodeCore()
{
lock (_lock)
{
BuildSiteMap();
return _root;
}
}

// Helper methods
private SiteMapNode CreateSiteMapNodeFromDataReader(DbDataReader
reader)
{
// Make sure the node ID is present
if (reader.IsDBNull(_indexID))
throw new ProviderException(_errmsg1);

// Get the node ID from the DataReader
int id = reader.GetInt32(_indexID);

// Make sure the node ID is unique
if (_nodes.ContainsKey(id))
throw new ProviderException(_errmsg2);

// Get title, URL, description, and roles from the DataReader
string title = reader.IsDBNull(_indexTitle) ? null :
reader.GetString(_indexTitle).Trim();
string url = reader.IsDBNull(_indexUrl) ? null :
reader.GetString(_indexUrl).Trim();
string description = reader.IsDBNull(_indexDesc) ? null :
reader.GetString(_indexDesc).Trim();
string roles = reader.IsDBNull(_indexRoles) ? null :
reader.GetString(_indexRoles).Trim();

// If roles were specified, turn the list into a string array
string[] rolelist = null;
if (!String.IsNullOrEmpty(roles))
rolelist = roles.Split(new char[] { ',', ';' }, 512);

NameValueCollection attributes = new NameValueCollection();
if (reader["item_sitekey"] != null && reader["item_sitekey"] !=
DBNull.Value)
attributes.Add("SiteKey",
reader["item_sitekey"].ToString());

// Create a SiteMapNode
SiteMapNode node = new SiteMapNode(this, id.ToString(), url,
title, description, rolelist, attributes, null, null);

// Record the node in the _nodes dictionary
_nodes.Add(id, node);

// Return the node
return node;
}

private SiteMapNode GetParentNodeFromDataReader(DbDataReader reader)
{
// Make sure the parent ID is present
if (reader.IsDBNull(_indexParent))
throw new ProviderException(_errmsg3);

// Get the parent ID from the DataReader
int pid = reader.GetInt32(_indexParent);

// Make sure the parent ID is valid
if (!_nodes.ContainsKey(pid))
throw new ProviderException(_errmsg4);

// Return the parent SiteMapNode
return _nodes[pid];
}

void OnSiteMapChanged(string key, object item,
CacheItemRemovedReason reason)
{
lock (_lock)
{
if (key == _cacheDependencyName && reason ==
CacheItemRemovedReason.DependencyChanged)
{
// Refresh the site map
Clear();
_nodes.Clear();
_root = null;
}
}
}

#endregion
}
}


Thanks for your help.
Regards"], [Roles], [Parent],
[web_item].[item_sitekey]
FROM [SiteMap]
LEFT OUTER JOIN [web_item] ON [sitemap].[id] = [web_item].[sitemap_id]
ORDER BY [ID]

In the sqlsitemapprovider class file I added the attributes, which is in
CreateSiteMapNodeFromDataReader method. The attributes are a parameter after
the rolelist argument. So I added:

NameValueCollection attributes = new NameValueColelction();
if (reader["item_sitekey"] != null && reader["item_sitekey"] !=
DBNull.Value)
attributes.Add("SiteKey", reader["item_sitekey"].ToString());

SiteMapNode node = new SiteMapNode(this, id.ToString(), url, title,
description, rolelist, attributes, null, null);

So, in my webform page code-behind, I tried the following to
programmatically modify the sitemap node:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class About : System.Web.UI.Page
{

private SiteMapNode UpdateUrl(Object sender, SiteMapResolveEventArgs e)
{
SiteMapNode currentNode = SiteMap.CurrentNode.Clone(true);
SiteMapNode tempNode = currentNode;

string sitekey = GetSiteKey();
if (null != sitekey)
{
tempNode.Url = tempNode.Url + "?sitekey=" + sitekey.ToString();
}

return currentNode;
}

protected string GetSiteKey()
{
string sitekey = SiteMap.CurrentNode["SiteKey"].ToString();
return sitekey;
}

protected void Page_Load(object sender, EventArgs e)
{
SiteMap.SiteMapResolve += new
SiteMapResolveEventHandler(this.UpdateUrl);

}
}

I did a response write to verify the Sitemap.CurrentNode["SiteKey"] was
returning the correct value, which it is. But, the above is not updating
the url as I would expect. I'm getting object not set to instance of object
error on this line:

string sitekey = SiteMap.CurrentNode["SiteKey"].ToString();

What am I missing?

Below is the entire code to my sqlsitemap provider class file:

using System;
using System.Web;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Configuration;
using System.Web.Configuration;
using System.Collections.Generic;
using System.Configuration.Provider;
using System.Security.Permissions;
using System.Data.Common;
using System.Data;
using System.Web.Caching;

/// <summary>
/// Summary description for SqlSiteMapProvider
/// </summary>
///
namespace Library.Providers
{
[SqlClientPermission(SecurityAction.Demand, Unrestricted = true)]
public class SqlSiteMapProvider : StaticSiteMapProvider
{
#region Fields...

private const string _errmsg1 = "Missing node ID";
private const string _errmsg2 = "Duplicate node ID";
private const string _errmsg3 = "Missing parent ID";
private const string _errmsg4 = "Invalid parent ID";
private const string _errmsg5 = "Empty or missing
connectionStringName";
private const string _errmsg6 = "Missing connection string";
private const string _errmsg7 = "Empty connection string";
private const string _errmsg8 = "Invalid sqlCacheDependency";
private const string _cacheDependencyName =
"__SiteMapCacheDependency";

private string _connect; // Database connection string
private string _database, _table; // Database info for SQL
Server 7/2000 cache dependency
private bool _2005dependency = false; // Database info for SQL
Server 2005 cache dependency
private int _indexID, _indexTitle, _indexUrl, _indexDesc,
_indexRoles, _indexParent;
private Dictionary<int, SiteMapNode> _nodes = new Dictionary<int,
SiteMapNode>(16);
private readonly object _lock = new object();
private SiteMapNode _root;

#endregion

#region Methods...

public override void Initialize(string name, NameValueCollection
config)
{
// Verify that config isn't null
if (config == null)
throw new ArgumentNullException("config");

// Assign the provider a default name if it doesn't have one
if (String.IsNullOrEmpty(name))
name = "SqlSiteMapProvider";

// Add a default "description" attribute to config if the
// attribute doesn't exist or is empty
if (string.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "SQL site map provider");
}

// Call the base class's Initialize method
base.Initialize(name, config);

// Initialize _connect
string connect = config["connectionStringName"];

if (String.IsNullOrEmpty(connect))
throw new ProviderException(_errmsg5);

config.Remove("connectionStringName");

if (WebConfigurationManager.ConnectionStrings[connect] == null)
throw new ProviderException(_errmsg6);

_connect =
WebConfigurationManager.ConnectionStrings[connect].ConnectionString;

if (String.IsNullOrEmpty(_connect))
throw new ProviderException(_errmsg7);

// Initialize SQL cache dependency info
string dependency = config["sqlCacheDependency"];

if (!String.IsNullOrEmpty(dependency))
{
if (String.Equals(dependency, "CommandNotification",
StringComparison.InvariantCultureIgnoreCase))
{
SqlDependency.Start(_connect);
_2005dependency = true;
}
else
{
// If not "CommandNotification", then extract database
and table names
string[] info = dependency.Split(new char[] { ':' });
if (info.Length != 2)
throw new ProviderException(_errmsg8);

_database = info[0];
_table = info[1];
}

config.Remove("sqlCacheDependency");
}

// SiteMapProvider processes the securityTrimmingEnabled
// attribute but fails to remove it. Remove it now so we can
// check for unrecognized configuration attributes.

if (config["securityTrimmingEnabled"] != null)
config.Remove("securityTrimmingEnabled");

// Throw an exception if unrecognized attributes remain
if (config.Count > 0)
{
string attr = config.GetKey(0);
if (!String.IsNullOrEmpty(attr))
throw new ProviderException("Unrecognized attribute: " +
attr);
}
}

public override SiteMapNode BuildSiteMap()
{
lock (_lock)
{
// Return immediately if this method has been called before
if (_root != null)
return _root;

// Query the database for site map nodes
SqlConnection connection = new SqlConnection(_connect);

try
{
SqlCommand command = new SqlCommand("spGetSiteMap",
connection);
command.CommandType = CommandType.StoredProcedure;

// Create a SQL cache dependency if requested
SqlCacheDependency dependency = null;

if (_2005dependency)
dependency = new SqlCacheDependency(command);
else if (!String.IsNullOrEmpty(_database) &&
!string.IsNullOrEmpty(_table))
dependency = new SqlCacheDependency(_database,
_table);

connection.Open();
SqlDataReader reader = command.ExecuteReader();
_indexID = reader.GetOrdinal("ID");
_indexUrl = reader.GetOrdinal("Url");
_indexTitle = reader.GetOrdinal("Title");
_indexDesc = reader.GetOrdinal("Description");
_indexRoles = reader.GetOrdinal("Roles");
_indexParent = reader.GetOrdinal("Parent");

if (reader.Read())
{
// Create the root SiteMapNode and add it to the
site map
_root = CreateSiteMapNodeFromDataReader(reader);
AddNode(_root, null);

// Build a tree of SiteMapNodes underneath the root
node
while (reader.Read())
{
// Create another site map node and add it to
the site map
SiteMapNode node =
CreateSiteMapNodeFromDataReader(reader);
AddNode(node,
GetParentNodeFromDataReader(reader));
}

// Use the SQL cache dependency
if (dependency != null)
{
HttpRuntime.Cache.Insert(_cacheDependencyName,
new object(), dependency,
Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable,
new
CacheItemRemovedCallback(OnSiteMapChanged));
}
}
}
finally
{
connection.Close();
}

// Return the root SiteMapNode
return _root;
}
}

protected override SiteMapNode GetRootNodeCore()
{
lock (_lock)
{
BuildSiteMap();
return _root;
}
}

// Helper methods
private SiteMapNode CreateSiteMapNodeFromDataReader(DbDataReader
reader)
{
// Make sure the node ID is present
if (reader.IsDBNull(_indexID))
throw new ProviderException(_errmsg1);

// Get the node ID from the DataReader
int id = reader.GetInt32(_indexID);

// Make sure the node ID is unique
if (_nodes.ContainsKey(id))
throw new ProviderException(_errmsg2);

// Get title, URL, description, and roles from the DataReader
string title = reader.IsDBNull(_indexTitle) ? null :
reader.GetString(_indexTitle).Trim();
string url = reader.IsDBNull(_indexUrl) ? null :
reader.GetString(_indexUrl).Trim();
string description = reader.IsDBNull(_indexDesc) ? null :
reader.GetString(_indexDesc).Trim();
string roles = reader.IsDBNull(_indexRoles) ? null :
reader.GetString(_indexRoles).Trim();

// If roles were specified, turn the list into a string array
string[] rolelist = null;
if (!String.IsNullOrEmpty(roles))
rolelist = roles.Split(new char[] { ',', ';' }, 512);

NameValueCollection attributes = new NameValueCollection();
if (reader["item_sitekey"] != null && reader["item_sitekey"] !=
DBNull.Value)
attributes.Add("SiteKey",
reader["item_sitekey"].ToString());

// Create a SiteMapNode
SiteMapNode node = new SiteMapNode(this, id.ToString(), url,
title, description, rolelist, attributes, null, null);

// Record the node in the _nodes dictionary
_nodes.Add(id, node);

// Return the node
return node;
}

private SiteMapNode GetParentNodeFromDataReader(DbDataReader reader)
{
// Make sure the parent ID is present
if (reader.IsDBNull(_indexParent))
throw new ProviderException(_errmsg3);

// Get the parent ID from the DataReader
int pid = reader.GetInt32(_indexParent);

// Make sure the parent ID is valid
if (!_nodes.ContainsKey(pid))
throw new ProviderException(_errmsg4);

// Return the parent SiteMapNode
return _nodes[pid];
}

void OnSiteMapChanged(string key, object item,
CacheItemRemovedReason reason)
{
lock (_lock)
{
if (key == _cacheDependencyName && reason ==
CacheItemRemovedReason.DependencyChanged)
{
// Refresh the site map
Clear();
_nodes.Clear();
_root = null;
}
}
}

#endregion
}
}


Thanks for your help.
Regards.
 
A

Allen Chen [MSFT]

Hi,

Thanks for your update. Could you use the default SqlSiteMapProvider and
select in this way?

CREATE PROCEDURE [dbo].[spGetSiteMap] AS
SELECT [sitemap].[ID], [Title], [Description], ( + '?sitekey=' +
[web_item].[item_sitekey]) as [Url], [Roles], [Parent]
FROM [SiteMap]
LEFT OUTER JOIN [web_item] ON [sitemap].[id] = [web_item].[sitemap_id]
ORDER BY [ID]

Can it work?

Regards,
Allen Chen
Microsoft Online Support
 
D

Dwayne

Allen,

I updated the stored procedure as you indicated and uncommented the
attributes code in the SqlSiteMapProvider using the default code and
recompiled and updated the assembly in my web project. When I run the
default page, the menu doesn't display at all. None of the menu items show
up. It seems like that should work, but the menu isn't displaying? I'm not
sure what I'm missing.

Any ideas?

-Dwayne
 
A

Allen Chen [MSFT]

Hi Dwayne,
I updated the stored procedure as you indicated and uncommented the
attributes code in the SqlSiteMapProvider using the default code and
recompiled and updated the assembly in my web project. When I run the
default page, the menu doesn't display at all. None of the menu items show
up. It seems like that should work, but the menu isn't displaying? I'm not
sure what I'm missing.

Thanks for you update. I've tested it on my side. It works fine. The tables
and stored procedure are the same as yours.

Most likely it's caused by remained cache. You can restart ASP.NET
Development to see if it works. To do this, find the ASP.NET Development
Server icon on the right bottom of the windows toolbar if it's been
started. Right click the icon and select "Stop". Then start debugging in
Visual Studio again.

Regards,
Allen Chen
Microsoft Online Support
 
Ad

Advertisements

D

Dwayne

Hi Allen,

I just tested again after being out of town this weekend and still the menu
doesn't display. There should be no cache left as Visual Studio has been
shut down all weekend and the asp.net development server isn't running.

When I execute the stored procedure manually in Sql Server Management Studio
Express 2005, the url field correctly contains the querystring. So, the
stored procedure is returning the correct values.

But, when the page renders, the menu does not display. I did a view page
source and this is what is coming up for the menu:

<div class="AspNet-Menu-Horizontal" id="Menu1">

</div>

No menu items?

What should I check to see what is going on?

This seems very strange. I must be missing something really simple and
obvious, but I don't see it.

Thanks,
-Dwayne
 
A

Allen Chen [MSFT]

Hi Dwayne,
What should I check to see what is going on?
This seems very strange. I must be missing something really simple and
obvious, but I don't see it.

Thanks for your update.

Are you using the following SqlSiteMapProvider? (change proc_GetSiteMap to
the name of your stored procedure)

[SqlClientPermission(SecurityAction.Demand, Unrestricted = true)]
public class SqlSiteMapProvider : StaticSiteMapProvider
{
private const string _errmsg1 = "Missing node ID";
private const string _errmsg2 = "Duplicate node ID";
private const string _errmsg3 = "Missing parent ID";
private const string _errmsg4 = "Invalid parent ID";
private const string _errmsg5 = "Empty or missing
connectionStringName";
private const string _errmsg6 = "Missing connection string";
private const string _errmsg7 = "Empty connection string";
private const string _errmsg8 = "Invalid sqlCacheDependency";
private const string _cacheDependencyName =
"__SiteMapCacheDependency";

private string _connect; // Database connection string
private string _database, _table; // Database info for SQL
Server 7/2000 cache dependency
private bool _2005dependency = false; // Database info for SQL
Server 2005 cache dependency
private int _indexID, _indexTitle, _indexUrl, _indexDesc,
_indexRoles, _indexParent;
private Dictionary<int, SiteMapNode> _nodes = new Dictionary<int,
SiteMapNode>(16);
private readonly object _lock = new object();
private SiteMapNode _root;

public override void Initialize(string name, NameValueCollection
config)
{
// Verify that config isn't null
if (config == null)
throw new ArgumentNullException("config");

// Assign the provider a default name if it doesn't have one
if (String.IsNullOrEmpty(name))
name = "SqlSiteMapProvider";

// Add a default "description" attribute to config if the
// attribute doesn't exist or is empty
if (string.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "SQL site map provider");
}

// Call the base class's Initialize method
base.Initialize(name, config);

// Initialize _connect
string connect = config["connectionStringName"];

if (String.IsNullOrEmpty(connect))
throw new ProviderException(_errmsg5);

config.Remove("connectionStringName");

if (WebConfigurationManager.ConnectionStrings[connect] == null)
throw new ProviderException(_errmsg6);

_connect =
WebConfigurationManager.ConnectionStrings[connect].ConnectionString;

if (String.IsNullOrEmpty(_connect))
throw new ProviderException(_errmsg7);

// Initialize SQL cache dependency info
string dependency = config["sqlCacheDependency"];

if (!String.IsNullOrEmpty(dependency))
{
if (String.Equals(dependency, "CommandNotification",
StringComparison.InvariantCultureIgnoreCase))
{
SqlDependency.Start(_connect);
_2005dependency = true;
}
else
{
// If not "CommandNotification", then extract database
and table names
string[] info = dependency.Split(new char[] { ':' });
if (info.Length != 2)
throw new ProviderException(_errmsg8);

_database = info[0];
_table = info[1];
}

config.Remove("sqlCacheDependency");
}

// SiteMapProvider processes the securityTrimmingEnabled
// attribute but fails to remove it. Remove it now so we can
// check for unrecognized configuration attributes.

if (config["securityTrimmingEnabled"] != null)
config.Remove("securityTrimmingEnabled");

// Throw an exception if unrecognized attributes remain
if (config.Count > 0)
{
string attr = config.GetKey(0);
if (!String.IsNullOrEmpty(attr))
throw new ProviderException("Unrecognized attribute: "
+ attr);
}
}

public override SiteMapNode BuildSiteMap()
{
lock (_lock)
{
// Return immediately if this method has been called before
if (_root != null)
return _root;

// Query the database for site map nodes
SqlConnection connection = new SqlConnection(_connect);

try
{
SqlCommand command = new SqlCommand("proc_GetSiteMap",
connection);
command.CommandType = CommandType.StoredProcedure;

// Create a SQL cache dependency if requested
SqlCacheDependency dependency = null;

if (_2005dependency)
dependency = new SqlCacheDependency(command);
else if (!String.IsNullOrEmpty(_database) &&
!string.IsNullOrEmpty(_table))
dependency = new SqlCacheDependency(_database,
_table);

connection.Open();
SqlDataReader reader = command.ExecuteReader();
_indexID = reader.GetOrdinal("ID");
_indexUrl = reader.GetOrdinal("Url");
_indexTitle = reader.GetOrdinal("Title");
_indexDesc = reader.GetOrdinal("Description");
_indexRoles = reader.GetOrdinal("Roles");
_indexParent = reader.GetOrdinal("Parent");

if (reader.Read())
{
// Create the root SiteMapNode and add it to the
site map
_root = CreateSiteMapNodeFromDataReader(reader);
AddNode(_root, null);

// Build a tree of SiteMapNodes underneath the root
node
while (reader.Read())
{
// Create another site map node and add it to
the site map
SiteMapNode node =
CreateSiteMapNodeFromDataReader(reader);
AddNode(node,
GetParentNodeFromDataReader(reader));
}

// Use the SQL cache dependency
if (dependency != null)
{
HttpRuntime.Cache.Insert(_cacheDependencyName,
new object(), dependency,
Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable,
new
CacheItemRemovedCallback(OnSiteMapChanged));
}
}
}
catch (Exception ex) {
}
finally
{
connection.Close();
}

// Return the root SiteMapNode
return _root;
}
}

protected override SiteMapNode GetRootNodeCore()
{
lock (_lock)
{
BuildSiteMap();
return _root;
}
}

// Helper methods
private SiteMapNode CreateSiteMapNodeFromDataReader(DbDataReader
reader)
{
// Make sure the node ID is present
if (reader.IsDBNull(_indexID))
throw new ProviderException(_errmsg1);

// Get the node ID from the DataReader
int id = reader.GetInt32(_indexID);

// Make sure the node ID is unique
if (_nodes.ContainsKey(id))
throw new ProviderException(_errmsg2);

// Get title, URL, description, and roles from the DataReader
string title = reader.IsDBNull(_indexTitle) ? null :
reader.GetString(_indexTitle).Trim();
string url = reader.IsDBNull(_indexUrl) ? null :
reader.GetString(_indexUrl).Trim();
string description = reader.IsDBNull(_indexDesc) ? null :
reader.GetString(_indexDesc).Trim();
string roles = reader.IsDBNull(_indexRoles) ? null :
reader.GetString(_indexRoles).Trim();

// If roles were specified, turn the list into a string array
string[] rolelist = null;
if (!String.IsNullOrEmpty(roles))
rolelist = roles.Split(new char[] { ',', ';' }, 512);

// Create a SiteMapNode
SiteMapNode node = new SiteMapNode(this, id.ToString(), url,
title, description, rolelist, null, null, null);

// Record the node in the _nodes dictionary
_nodes.Add(id, node);

// Return the node
return node;
}

private SiteMapNode GetParentNodeFromDataReader(DbDataReader reader)
{
// Make sure the parent ID is present
if (reader.IsDBNull(_indexParent))
throw new ProviderException(_errmsg3);

// Get the parent ID from the DataReader
int pid = reader.GetInt32(_indexParent);

// Make sure the parent ID is valid
if (!_nodes.ContainsKey(pid))
throw new ProviderException(_errmsg4);

// Return the parent SiteMapNode
return _nodes[pid];
}

void OnSiteMapChanged(string key, object item,
CacheItemRemovedReason reason)
{
lock (_lock)
{
if (key == _cacheDependencyName && reason ==
CacheItemRemovedReason.DependencyChanged)
{
// Refresh the site map
Clear();
_nodes.Clear();
_root = null;
}
}
}
}

If you're using the above code but it still doesn't work, please set
breakpoints in BuildSiteMap method to debug step by step. Is there any
exception can be caught? What's the data read from SqlDataReader? What's
the returned SiteMapNode?

Regards,
Allen Chen
Microsoft Online Support
 
D

Dwayne

Hi Allen,

The SqlSiteMapProvider class file looks the same (see code below). I set
breakpoints in the BuildSiteMap method and debugged.

I'm noticing some irregularities, the sql data reader is not returning the
url values, but is returning the titles and the _root SiteMapNode is
returning null.

Here is my SqlSiteMapProvider class file I"m using. This is the correct?

using System;
using System.Web;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Configuration;
using System.Web.Configuration;
using System.Collections.Generic;
using System.Configuration.Provider;
using System.Security.Permissions;
using System.Data.Common;
using System.Data;
using System.Web.Caching;

/// <summary>
/// Summary description for SqlSiteMapProvider
/// </summary>
///
namespace Library.Providers
{
[SqlClientPermission(SecurityAction.Demand, Unrestricted = true)]
public class SqlSiteMapProvider : StaticSiteMapProvider
{
#region Fields...

private const string _errmsg1 = "Missing node ID";
private const string _errmsg2 = "Duplicate node ID";
private const string _errmsg3 = "Missing parent ID";
private const string _errmsg4 = "Invalid parent ID";
private const string _errmsg5 = "Empty or missing
connectionStringName";
private const string _errmsg6 = "Missing connection string";
private const string _errmsg7 = "Empty connection string";
private const string _errmsg8 = "Invalid sqlCacheDependency";
private const string _cacheDependencyName =
"__SiteMapCacheDependency";

private string _connect; // Database connection string
private string _database, _table; // Database info for SQL
Server 7/2000 cache dependency
private bool _2005dependency = false; // Database info for SQL
Server 2005 cache dependency
private int _indexID, _indexTitle, _indexUrl, _indexDesc,
_indexRoles, _indexParent;
private Dictionary<int, SiteMapNode> _nodes = new Dictionary<int,
SiteMapNode>(16);
private readonly object _lock = new object();
private SiteMapNode _root;

#endregion

#region Methods...

public override void Initialize(string name, NameValueCollection
config)
{
// Verify that config isn't null
if (config == null)
throw new ArgumentNullException("config");

// Assign the provider a default name if it doesn't have one
if (String.IsNullOrEmpty(name))
name = "SqlSiteMapProvider";

// Add a default "description" attribute to config if the
// attribute doesn't exist or is empty
if (string.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "SQL site map provider");
}

// Call the base class's Initialize method
base.Initialize(name, config);

// Initialize _connect
string connect = config["connectionStringName"];

if (String.IsNullOrEmpty(connect))
throw new ProviderException(_errmsg5);

config.Remove("connectionStringName");

if (WebConfigurationManager.ConnectionStrings[connect] == null)
throw new ProviderException(_errmsg6);

_connect =
WebConfigurationManager.ConnectionStrings[connect].ConnectionString;

if (String.IsNullOrEmpty(_connect))
throw new ProviderException(_errmsg7);

// Initialize SQL cache dependency info
string dependency = config["sqlCacheDependency"];

if (!String.IsNullOrEmpty(dependency))
{
if (String.Equals(dependency, "CommandNotification",
StringComparison.InvariantCultureIgnoreCase))
{
SqlDependency.Start(_connect);
_2005dependency = true;
}
else
{
// If not "CommandNotification", then extract database
and table names
string[] info = dependency.Split(new char[] { ':' });
if (info.Length != 2)
throw new ProviderException(_errmsg8);

_database = info[0];
_table = info[1];
}

config.Remove("sqlCacheDependency");
}

// SiteMapProvider processes the securityTrimmingEnabled
// attribute but fails to remove it. Remove it now so we can
// check for unrecognized configuration attributes.

if (config["securityTrimmingEnabled"] != null)
config.Remove("securityTrimmingEnabled");

// Throw an exception if unrecognized attributes remain
if (config.Count > 0)
{
string attr = config.GetKey(0);
if (!String.IsNullOrEmpty(attr))
throw new ProviderException("Unrecognized attribute: " +
attr);
}
}

public override SiteMapNode BuildSiteMap()
{
lock (_lock)
{
// Return immediately if this method has been called before
if (_root != null)
return _root;

// Query the database for site map nodes
SqlConnection connection = new SqlConnection(_connect);

try
{
SqlCommand command = new SqlCommand("spGetSiteMap",
connection);
command.CommandType = CommandType.StoredProcedure;

// Create a SQL cache dependency if requested
SqlCacheDependency dependency = null;

if (_2005dependency)
dependency = new SqlCacheDependency(command);
else if (!String.IsNullOrEmpty(_database) &&
!string.IsNullOrEmpty(_table))
dependency = new SqlCacheDependency(_database,
_table);

connection.Open();
SqlDataReader reader = command.ExecuteReader();
_indexID = reader.GetOrdinal("ID");
_indexUrl = reader.GetOrdinal("Url");
_indexTitle = reader.GetOrdinal("Title");
_indexDesc = reader.GetOrdinal("Description");
_indexRoles = reader.GetOrdinal("Roles");
_indexParent = reader.GetOrdinal("Parent");

if (reader.Read())
{
// Create the root SiteMapNode and add it to the
site map
_root = CreateSiteMapNodeFromDataReader(reader);
AddNode(_root, null);

// Build a tree of SiteMapNodes underneath the root
node
while (reader.Read())
{
// Create another site map node and add it to
the site map
SiteMapNode node =
CreateSiteMapNodeFromDataReader(reader);
AddNode(node,
GetParentNodeFromDataReader(reader));
}

// Use the SQL cache dependency
if (dependency != null)
{
HttpRuntime.Cache.Insert(_cacheDependencyName,
new object(), dependency,
Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable,
new
CacheItemRemovedCallback(OnSiteMapChanged));
}
}
}
finally
{
connection.Close();
}

// Return the root SiteMapNode
return _root;
}
}

protected override SiteMapNode GetRootNodeCore()
{
lock (_lock)
{
BuildSiteMap();
return _root;
}
}

// Helper methods
private SiteMapNode CreateSiteMapNodeFromDataReader(DbDataReader
reader)
{
// Make sure the node ID is present
if (reader.IsDBNull(_indexID))
throw new ProviderException(_errmsg1);

// Get the node ID from the DataReader
int id = reader.GetInt32(_indexID);

// Make sure the node ID is unique
if (_nodes.ContainsKey(id))
throw new ProviderException(_errmsg2);

// Get title, URL, description, and roles from the DataReader
string title = reader.IsDBNull(_indexTitle) ? null :
reader.GetString(_indexTitle).Trim();
string url = reader.IsDBNull(_indexUrl) ? null :
reader.GetString(_indexUrl).Trim();
string description = reader.IsDBNull(_indexDesc) ? null :
reader.GetString(_indexDesc).Trim();
string roles = reader.IsDBNull(_indexRoles) ? null :
reader.GetString(_indexRoles).Trim();

// If roles were specified, turn the list into a string array
string[] rolelist = null;
if (!String.IsNullOrEmpty(roles))
rolelist = roles.Split(new char[] { ',', ';' }, 512);

//NameValueCollection attributes = new NameValueCollection();
//if (reader["item_sitekey"] != null && reader["item_sitekey"]
!= DBNull.Value)
// attributes.Add("SiteKey",
reader["item_sitekey"].ToString());

// Create a SiteMapNode
SiteMapNode node = new SiteMapNode(this, id.ToString(), url,
title, description, rolelist, null, null, null);

// Record the node in the _nodes dictionary
_nodes.Add(id, node);

// Return the node
return node;
}

private SiteMapNode GetParentNodeFromDataReader(DbDataReader reader)
{
// Make sure the parent ID is present
if (reader.IsDBNull(_indexParent))
throw new ProviderException(_errmsg3);

// Get the parent ID from the DataReader
int pid = reader.GetInt32(_indexParent);

// Make sure the parent ID is valid
if (!_nodes.ContainsKey(pid))
throw new ProviderException(_errmsg4);

// Return the parent SiteMapNode
return _nodes[pid];
}

void OnSiteMapChanged(string key, object item,
CacheItemRemovedReason reason)
{
lock (_lock)
{
if (key == _cacheDependencyName && reason ==
CacheItemRemovedReason.DependencyChanged)
{
// Refresh the site map
Clear();
_nodes.Clear();
_root = null;
}
}
}

#endregion
}
}


Allen Chen said:
Hi Dwayne,
What should I check to see what is going on?
This seems very strange. I must be missing something really simple and
obvious, but I don't see it.

Thanks for your update.

Are you using the following SqlSiteMapProvider? (change proc_GetSiteMap to
the name of your stored procedure)

[SqlClientPermission(SecurityAction.Demand, Unrestricted = true)]
public class SqlSiteMapProvider : StaticSiteMapProvider
{
private const string _errmsg1 = "Missing node ID";
private const string _errmsg2 = "Duplicate node ID";
private const string _errmsg3 = "Missing parent ID";
private const string _errmsg4 = "Invalid parent ID";
private const string _errmsg5 = "Empty or missing
connectionStringName";
private const string _errmsg6 = "Missing connection string";
private const string _errmsg7 = "Empty connection string";
private const string _errmsg8 = "Invalid sqlCacheDependency";
private const string _cacheDependencyName =
"__SiteMapCacheDependency";

private string _connect; // Database connection string
private string _database, _table; // Database info for SQL
Server 7/2000 cache dependency
private bool _2005dependency = false; // Database info for SQL
Server 2005 cache dependency
private int _indexID, _indexTitle, _indexUrl, _indexDesc,
_indexRoles, _indexParent;
private Dictionary<int, SiteMapNode> _nodes = new Dictionary<int,
SiteMapNode>(16);
private readonly object _lock = new object();
private SiteMapNode _root;

public override void Initialize(string name, NameValueCollection
config)
{
// Verify that config isn't null
if (config == null)
throw new ArgumentNullException("config");

// Assign the provider a default name if it doesn't have one
if (String.IsNullOrEmpty(name))
name = "SqlSiteMapProvider";

// Add a default "description" attribute to config if the
// attribute doesn't exist or is empty
if (string.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "SQL site map provider");
}

// Call the base class's Initialize method
base.Initialize(name, config);

// Initialize _connect
string connect = config["connectionStringName"];

if (String.IsNullOrEmpty(connect))
throw new ProviderException(_errmsg5);

config.Remove("connectionStringName");

if (WebConfigurationManager.ConnectionStrings[connect] == null)
throw new ProviderException(_errmsg6);

_connect =
WebConfigurationManager.ConnectionStrings[connect].ConnectionString;

if (String.IsNullOrEmpty(_connect))
throw new ProviderException(_errmsg7);

// Initialize SQL cache dependency info
string dependency = config["sqlCacheDependency"];

if (!String.IsNullOrEmpty(dependency))
{
if (String.Equals(dependency, "CommandNotification",
StringComparison.InvariantCultureIgnoreCase))
{
SqlDependency.Start(_connect);
_2005dependency = true;
}
else
{
// If not "CommandNotification", then extract database
and table names
string[] info = dependency.Split(new char[] { ':' });
if (info.Length != 2)
throw new ProviderException(_errmsg8);

_database = info[0];
_table = info[1];
}

config.Remove("sqlCacheDependency");
}

// SiteMapProvider processes the securityTrimmingEnabled
// attribute but fails to remove it. Remove it now so we can
// check for unrecognized configuration attributes.

if (config["securityTrimmingEnabled"] != null)
config.Remove("securityTrimmingEnabled");

// Throw an exception if unrecognized attributes remain
if (config.Count > 0)
{
string attr = config.GetKey(0);
if (!String.IsNullOrEmpty(attr))
throw new ProviderException("Unrecognized attribute: "
+ attr);
}
}

public override SiteMapNode BuildSiteMap()
{
lock (_lock)
{
// Return immediately if this method has been called before
if (_root != null)
return _root;

// Query the database for site map nodes
SqlConnection connection = new SqlConnection(_connect);

try
{
SqlCommand command = new SqlCommand("proc_GetSiteMap",
connection);
command.CommandType = CommandType.StoredProcedure;

// Create a SQL cache dependency if requested
SqlCacheDependency dependency = null;

if (_2005dependency)
dependency = new SqlCacheDependency(command);
else if (!String.IsNullOrEmpty(_database) &&
!string.IsNullOrEmpty(_table))
dependency = new SqlCacheDependency(_database,
_table);

connection.Open();
SqlDataReader reader = command.ExecuteReader();
_indexID = reader.GetOrdinal("ID");
_indexUrl = reader.GetOrdinal("Url");
_indexTitle = reader.GetOrdinal("Title");
_indexDesc = reader.GetOrdinal("Description");
_indexRoles = reader.GetOrdinal("Roles");
_indexParent = reader.GetOrdinal("Parent");

if (reader.Read())
{
// Create the root SiteMapNode and add it to the
site map
_root = CreateSiteMapNodeFromDataReader(reader);
AddNode(_root, null);

// Build a tree of SiteMapNodes underneath the root
node
while (reader.Read())
{
// Create another site map node and add it to
the site map
SiteMapNode node =
CreateSiteMapNodeFromDataReader(reader);
AddNode(node,
GetParentNodeFromDataReader(reader));
}

// Use the SQL cache dependency
if (dependency != null)
{
HttpRuntime.Cache.Insert(_cacheDependencyName,
new object(), dependency,
Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable,
new
CacheItemRemovedCallback(OnSiteMapChanged));
}
}
}
catch (Exception ex) {
}
finally
{
connection.Close();
}

// Return the root SiteMapNode
return _root;
}
}

protected override SiteMapNode GetRootNodeCore()
{
lock (_lock)
{
BuildSiteMap();
return _root;
}
}

// Helper methods
private SiteMapNode CreateSiteMapNodeFromDataReader(DbDataReader
reader)
{
// Make sure the node ID is present
if (reader.IsDBNull(_indexID))
throw new ProviderException(_errmsg1);

// Get the node ID from the DataReader
int id = reader.GetInt32(_indexID);

// Make sure the node ID is unique
if (_nodes.ContainsKey(id))
throw new ProviderException(_errmsg2);

// Get title, URL, description, and roles from the DataReader
string title = reader.IsDBNull(_indexTitle) ? null :
reader.GetString(_indexTitle).Trim();
string url = reader.IsDBNull(_indexUrl) ? null :
reader.GetString(_indexUrl).Trim();
string description = reader.IsDBNull(_indexDesc) ? null :
reader.GetString(_indexDesc).Trim();
string roles = reader.IsDBNull(_indexRoles) ? null :
reader.GetString(_indexRoles).Trim();

// If roles were specified, turn the list into a string array
string[] rolelist = null;
if (!String.IsNullOrEmpty(roles))
rolelist = roles.Split(new char[] { ',', ';' }, 512);

// Create a SiteMapNode
SiteMapNode node = new SiteMapNode(this, id.ToString(), url,
title, description, rolelist, null, null, null);

// Record the node in the _nodes dictionary
_nodes.Add(id, node);

// Return the node
return node;
}

private SiteMapNode GetParentNodeFromDataReader(DbDataReader
reader)
{
// Make sure the parent ID is present
if (reader.IsDBNull(_indexParent))
throw new ProviderException(_errmsg3);

// Get the parent ID from the DataReader
int pid = reader.GetInt32(_indexParent);

// Make sure the parent ID is valid
if (!_nodes.ContainsKey(pid))
throw new ProviderException(_errmsg4);

// Return the parent SiteMapNode
return _nodes[pid];
}

void OnSiteMapChanged(string key, object item,
CacheItemRemovedReason reason)
{
lock (_lock)
{
if (key == _cacheDependencyName && reason ==
CacheItemRemovedReason.DependencyChanged)
{
// Refresh the site map
Clear();
_nodes.Clear();
_root = null;
}
}
}
}

If you're using the above code but it still doesn't work, please set
breakpoints in BuildSiteMap method to debug step by step. Is there any
exception can be caught? What's the data read from SqlDataReader? What's
the returned SiteMapNode?

Regards,
Allen Chen
Microsoft Online Support
 
D

Dwayne

Hi Allen,

I'm not sure what the problem was, but I re-built my spGetSiteMap stored
procedure and now it is working. I'm not sure what I might have been
missing, but the menu is rendering now.

I did notice one issue. If I open a page directly, the url querystring does
not initially display. So, if someone bookmarks a page that has dynamic
content they will encounter a page exception.

For example, the 'about.aspx' page has dynamic content and the url that is
built from the database is: 'about.aspx?sitekey=1.0'

However, if I open 'about.aspx' directly, the url is just: 'about.aspx'
without the querystring, which causes a page exception. How can I trap this
exception and make sure the querystring is appended?

Thanks for your help.
Regards,
-Dwayne

Dwayne said:
Hi Allen,

The SqlSiteMapProvider class file looks the same (see code below). I set
breakpoints in the BuildSiteMap method and debugged.

I'm noticing some irregularities, the sql data reader is not returning the
url values, but is returning the titles and the _root SiteMapNode is
returning null.

Here is my SqlSiteMapProvider class file I"m using. This is the correct?

using System;
using System.Web;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Configuration;
using System.Web.Configuration;
using System.Collections.Generic;
using System.Configuration.Provider;
using System.Security.Permissions;
using System.Data.Common;
using System.Data;
using System.Web.Caching;

/// <summary>
/// Summary description for SqlSiteMapProvider
/// </summary>
///
namespace Library.Providers
{
[SqlClientPermission(SecurityAction.Demand, Unrestricted = true)]
public class SqlSiteMapProvider : StaticSiteMapProvider
{
#region Fields...

private const string _errmsg1 = "Missing node ID";
private const string _errmsg2 = "Duplicate node ID";
private const string _errmsg3 = "Missing parent ID";
private const string _errmsg4 = "Invalid parent ID";
private const string _errmsg5 = "Empty or missing
connectionStringName";
private const string _errmsg6 = "Missing connection string";
private const string _errmsg7 = "Empty connection string";
private const string _errmsg8 = "Invalid sqlCacheDependency";
private const string _cacheDependencyName =
"__SiteMapCacheDependency";

private string _connect; // Database connection string
private string _database, _table; // Database info for SQL
Server 7/2000 cache dependency
private bool _2005dependency = false; // Database info for SQL
Server 2005 cache dependency
private int _indexID, _indexTitle, _indexUrl, _indexDesc,
_indexRoles, _indexParent;
private Dictionary<int, SiteMapNode> _nodes = new Dictionary<int,
SiteMapNode>(16);
private readonly object _lock = new object();
private SiteMapNode _root;

#endregion

#region Methods...

public override void Initialize(string name, NameValueCollection
config)
{
// Verify that config isn't null
if (config == null)
throw new ArgumentNullException("config");

// Assign the provider a default name if it doesn't have one
if (String.IsNullOrEmpty(name))
name = "SqlSiteMapProvider";

// Add a default "description" attribute to config if the
// attribute doesn't exist or is empty
if (string.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "SQL site map provider");
}

// Call the base class's Initialize method
base.Initialize(name, config);

// Initialize _connect
string connect = config["connectionStringName"];

if (String.IsNullOrEmpty(connect))
throw new ProviderException(_errmsg5);

config.Remove("connectionStringName");

if (WebConfigurationManager.ConnectionStrings[connect] == null)
throw new ProviderException(_errmsg6);

_connect =
WebConfigurationManager.ConnectionStrings[connect].ConnectionString;

if (String.IsNullOrEmpty(_connect))
throw new ProviderException(_errmsg7);

// Initialize SQL cache dependency info
string dependency = config["sqlCacheDependency"];

if (!String.IsNullOrEmpty(dependency))
{
if (String.Equals(dependency, "CommandNotification",
StringComparison.InvariantCultureIgnoreCase))
{
SqlDependency.Start(_connect);
_2005dependency = true;
}
else
{
// If not "CommandNotification", then extract database
and table names
string[] info = dependency.Split(new char[] { ':' });
if (info.Length != 2)
throw new ProviderException(_errmsg8);

_database = info[0];
_table = info[1];
}

config.Remove("sqlCacheDependency");
}

// SiteMapProvider processes the securityTrimmingEnabled
// attribute but fails to remove it. Remove it now so we can
// check for unrecognized configuration attributes.

if (config["securityTrimmingEnabled"] != null)
config.Remove("securityTrimmingEnabled");

// Throw an exception if unrecognized attributes remain
if (config.Count > 0)
{
string attr = config.GetKey(0);
if (!String.IsNullOrEmpty(attr))
throw new ProviderException("Unrecognized attribute: "
+ attr);
}
}

public override SiteMapNode BuildSiteMap()
{
lock (_lock)
{
// Return immediately if this method has been called before
if (_root != null)
return _root;

// Query the database for site map nodes
SqlConnection connection = new SqlConnection(_connect);

try
{
SqlCommand command = new SqlCommand("spGetSiteMap",
connection);
command.CommandType = CommandType.StoredProcedure;

// Create a SQL cache dependency if requested
SqlCacheDependency dependency = null;

if (_2005dependency)
dependency = new SqlCacheDependency(command);
else if (!String.IsNullOrEmpty(_database) &&
!string.IsNullOrEmpty(_table))
dependency = new SqlCacheDependency(_database,
_table);

connection.Open();
SqlDataReader reader = command.ExecuteReader();
_indexID = reader.GetOrdinal("ID");
_indexUrl = reader.GetOrdinal("Url");
_indexTitle = reader.GetOrdinal("Title");
_indexDesc = reader.GetOrdinal("Description");
_indexRoles = reader.GetOrdinal("Roles");
_indexParent = reader.GetOrdinal("Parent");

if (reader.Read())
{
// Create the root SiteMapNode and add it to the
site map
_root = CreateSiteMapNodeFromDataReader(reader);
AddNode(_root, null);

// Build a tree of SiteMapNodes underneath the root
node
while (reader.Read())
{
// Create another site map node and add it to
the site map
SiteMapNode node =
CreateSiteMapNodeFromDataReader(reader);
AddNode(node,
GetParentNodeFromDataReader(reader));
}

// Use the SQL cache dependency
if (dependency != null)
{
HttpRuntime.Cache.Insert(_cacheDependencyName,
new object(), dependency,
Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable,
new
CacheItemRemovedCallback(OnSiteMapChanged));
}
}
}
finally
{
connection.Close();
}

// Return the root SiteMapNode
return _root;
}
}

protected override SiteMapNode GetRootNodeCore()
{
lock (_lock)
{
BuildSiteMap();
return _root;
}
}

// Helper methods
private SiteMapNode CreateSiteMapNodeFromDataReader(DbDataReader
reader)
{
// Make sure the node ID is present
if (reader.IsDBNull(_indexID))
throw new ProviderException(_errmsg1);

// Get the node ID from the DataReader
int id = reader.GetInt32(_indexID);

// Make sure the node ID is unique
if (_nodes.ContainsKey(id))
throw new ProviderException(_errmsg2);

// Get title, URL, description, and roles from the DataReader
string title = reader.IsDBNull(_indexTitle) ? null :
reader.GetString(_indexTitle).Trim();
string url = reader.IsDBNull(_indexUrl) ? null :
reader.GetString(_indexUrl).Trim();
string description = reader.IsDBNull(_indexDesc) ? null :
reader.GetString(_indexDesc).Trim();
string roles = reader.IsDBNull(_indexRoles) ? null :
reader.GetString(_indexRoles).Trim();

// If roles were specified, turn the list into a string array
string[] rolelist = null;
if (!String.IsNullOrEmpty(roles))
rolelist = roles.Split(new char[] { ',', ';' }, 512);

//NameValueCollection attributes = new NameValueCollection();
//if (reader["item_sitekey"] != null && reader["item_sitekey"]
!= DBNull.Value)
// attributes.Add("SiteKey",
reader["item_sitekey"].ToString());

// Create a SiteMapNode
SiteMapNode node = new SiteMapNode(this, id.ToString(), url,
title, description, rolelist, null, null, null);

// Record the node in the _nodes dictionary
_nodes.Add(id, node);

// Return the node
return node;
}

private SiteMapNode GetParentNodeFromDataReader(DbDataReader
reader)
{
// Make sure the parent ID is present
if (reader.IsDBNull(_indexParent))
throw new ProviderException(_errmsg3);

// Get the parent ID from the DataReader
int pid = reader.GetInt32(_indexParent);

// Make sure the parent ID is valid
if (!_nodes.ContainsKey(pid))
throw new ProviderException(_errmsg4);

// Return the parent SiteMapNode
return _nodes[pid];
}

void OnSiteMapChanged(string key, object item,
CacheItemRemovedReason reason)
{
lock (_lock)
{
if (key == _cacheDependencyName && reason ==
CacheItemRemovedReason.DependencyChanged)
{
// Refresh the site map
Clear();
_nodes.Clear();
_root = null;
}
}
}

#endregion
}
}


Allen Chen said:
Hi Dwayne,
What should I check to see what is going on?
This seems very strange. I must be missing something really simple and
obvious, but I don't see it.

Thanks for your update.

Are you using the following SqlSiteMapProvider? (change proc_GetSiteMap
to
the name of your stored procedure)

[SqlClientPermission(SecurityAction.Demand, Unrestricted = true)]
public class SqlSiteMapProvider : StaticSiteMapProvider
{
private const string _errmsg1 = "Missing node ID";
private const string _errmsg2 = "Duplicate node ID";
private const string _errmsg3 = "Missing parent ID";
private const string _errmsg4 = "Invalid parent ID";
private const string _errmsg5 = "Empty or missing
connectionStringName";
private const string _errmsg6 = "Missing connection string";
private const string _errmsg7 = "Empty connection string";
private const string _errmsg8 = "Invalid sqlCacheDependency";
private const string _cacheDependencyName =
"__SiteMapCacheDependency";

private string _connect; // Database connection
string
private string _database, _table; // Database info for SQL
Server 7/2000 cache dependency
private bool _2005dependency = false; // Database info for SQL
Server 2005 cache dependency
private int _indexID, _indexTitle, _indexUrl, _indexDesc,
_indexRoles, _indexParent;
private Dictionary<int, SiteMapNode> _nodes = new Dictionary<int,
SiteMapNode>(16);
private readonly object _lock = new object();
private SiteMapNode _root;

public override void Initialize(string name, NameValueCollection
config)
{
// Verify that config isn't null
if (config == null)
throw new ArgumentNullException("config");

// Assign the provider a default name if it doesn't have one
if (String.IsNullOrEmpty(name))
name = "SqlSiteMapProvider";

// Add a default "description" attribute to config if the
// attribute doesn't exist or is empty
if (string.IsNullOrEmpty(config["description"]))
{
config.Remove("description");
config.Add("description", "SQL site map provider");
}

// Call the base class's Initialize method
base.Initialize(name, config);

// Initialize _connect
string connect = config["connectionStringName"];

if (String.IsNullOrEmpty(connect))
throw new ProviderException(_errmsg5);

config.Remove("connectionStringName");

if (WebConfigurationManager.ConnectionStrings[connect] ==
null)
throw new ProviderException(_errmsg6);

_connect =
WebConfigurationManager.ConnectionStrings[connect].ConnectionString;

if (String.IsNullOrEmpty(_connect))
throw new ProviderException(_errmsg7);

// Initialize SQL cache dependency info
string dependency = config["sqlCacheDependency"];

if (!String.IsNullOrEmpty(dependency))
{
if (String.Equals(dependency, "CommandNotification",
StringComparison.InvariantCultureIgnoreCase))
{
SqlDependency.Start(_connect);
_2005dependency = true;
}
else
{
// If not "CommandNotification", then extract database
and table names
string[] info = dependency.Split(new char[] { ':' });
if (info.Length != 2)
throw new ProviderException(_errmsg8);

_database = info[0];
_table = info[1];
}

config.Remove("sqlCacheDependency");
}

// SiteMapProvider processes the securityTrimmingEnabled
// attribute but fails to remove it. Remove it now so we can
// check for unrecognized configuration attributes.

if (config["securityTrimmingEnabled"] != null)
config.Remove("securityTrimmingEnabled");

// Throw an exception if unrecognized attributes remain
if (config.Count > 0)
{
string attr = config.GetKey(0);
if (!String.IsNullOrEmpty(attr))
throw new ProviderException("Unrecognized attribute: "
+ attr);
}
}

public override SiteMapNode BuildSiteMap()
{
lock (_lock)
{
// Return immediately if this method has been called
before
if (_root != null)
return _root;

// Query the database for site map nodes
SqlConnection connection = new SqlConnection(_connect);

try
{
SqlCommand command = new SqlCommand("proc_GetSiteMap",
connection);
command.CommandType = CommandType.StoredProcedure;

// Create a SQL cache dependency if requested
SqlCacheDependency dependency = null;

if (_2005dependency)
dependency = new SqlCacheDependency(command);
else if (!String.IsNullOrEmpty(_database) &&
!string.IsNullOrEmpty(_table))
dependency = new SqlCacheDependency(_database,
_table);

connection.Open();
SqlDataReader reader = command.ExecuteReader();
_indexID = reader.GetOrdinal("ID");
_indexUrl = reader.GetOrdinal("Url");
_indexTitle = reader.GetOrdinal("Title");
_indexDesc = reader.GetOrdinal("Description");
_indexRoles = reader.GetOrdinal("Roles");
_indexParent = reader.GetOrdinal("Parent");

if (reader.Read())
{
// Create the root SiteMapNode and add it to the
site map
_root = CreateSiteMapNodeFromDataReader(reader);
AddNode(_root, null);

// Build a tree of SiteMapNodes underneath the
root
node
while (reader.Read())
{
// Create another site map node and add it to
the site map
SiteMapNode node =
CreateSiteMapNodeFromDataReader(reader);
AddNode(node,
GetParentNodeFromDataReader(reader));
}

// Use the SQL cache dependency
if (dependency != null)
{
HttpRuntime.Cache.Insert(_cacheDependencyName,
new object(), dependency,
Cache.NoAbsoluteExpiration,
Cache.NoSlidingExpiration, CacheItemPriority.NotRemovable,
new
CacheItemRemovedCallback(OnSiteMapChanged));
}
}
}
catch (Exception ex) {
}
finally
{
connection.Close();
}

// Return the root SiteMapNode
return _root;
}
}

protected override SiteMapNode GetRootNodeCore()
{
lock (_lock)
{
BuildSiteMap();
return _root;
}
}

// Helper methods
private SiteMapNode CreateSiteMapNodeFromDataReader(DbDataReader
reader)
{
// Make sure the node ID is present
if (reader.IsDBNull(_indexID))
throw new ProviderException(_errmsg1);

// Get the node ID from the DataReader
int id = reader.GetInt32(_indexID);

// Make sure the node ID is unique
if (_nodes.ContainsKey(id))
throw new ProviderException(_errmsg2);

// Get title, URL, description, and roles from the DataReader
string title = reader.IsDBNull(_indexTitle) ? null :
reader.GetString(_indexTitle).Trim();
string url = reader.IsDBNull(_indexUrl) ? null :
reader.GetString(_indexUrl).Trim();
string description = reader.IsDBNull(_indexDesc) ? null :
reader.GetString(_indexDesc).Trim();
string roles = reader.IsDBNull(_indexRoles) ? null :
reader.GetString(_indexRoles).Trim();

// If roles were specified, turn the list into a string array
string[] rolelist = null;
if (!String.IsNullOrEmpty(roles))
rolelist = roles.Split(new char[] { ',', ';' }, 512);

// Create a SiteMapNode
SiteMapNode node = new SiteMapNode(this, id.ToString(), url,
title, description, rolelist, null, null, null);

// Record the node in the _nodes dictionary
_nodes.Add(id, node);

// Return the node
return node;
}

private SiteMapNode GetParentNodeFromDataReader(DbDataReader
reader)
{
// Make sure the parent ID is present
if (reader.IsDBNull(_indexParent))
throw new ProviderException(_errmsg3);

// Get the parent ID from the DataReader
int pid = reader.GetInt32(_indexParent);

// Make sure the parent ID is valid
if (!_nodes.ContainsKey(pid))
throw new ProviderException(_errmsg4);

// Return the parent SiteMapNode
return _nodes[pid];
}

void OnSiteMapChanged(string key, object item,
CacheItemRemovedReason reason)
{
lock (_lock)
{
if (key == _cacheDependencyName && reason ==
CacheItemRemovedReason.DependencyChanged)
{
// Refresh the site map
Clear();
_nodes.Clear();
_root = null;
}
}
}
}

If you're using the above code but it still doesn't work, please set
breakpoints in BuildSiteMap method to debug step by step. Is there any
exception can be caught? What's the data read from SqlDataReader? What's
the returned SiteMapNode?

Regards,
Allen Chen
Microsoft Online Support
 
A

Allen Chen [MSFT]

Hi Dwayne,
However, if I open 'about.aspx' directly, the url is just: 'about.aspx'
without the querystring, which causes a page exception. How can I trap this
exception and make sure the querystring is appended?

Thanks for your update. I guess the exception is something like "Index was
out of range" or "Object reference not set to an instance of an object",
right? If so you can check if there's a querystring in the URL before
accessing it. You can add code below in about.aspx.cs:

if(Request.QueryString.Count>)0{
//your code logic here
}

Your code then will not be executed if there's no querystring.

Please have a try and let me know if it works. If it still doesn't work
please provide exception description and your code.

Regards,
Allen Chen
Microsoft Online Support
 
Ad

Advertisements

D

Dwayne

Hi Allen,

The error I get when I try to access the about.aspx page directly before the
SiteMapNode CurrentNode Url is created is:

Failed to enable constraints. One or more rows contain values violating
non-null, unique, or foreign-key constraints.

Line 1381: }
Line 1382: Dwaynexxxx.WebItemDataTable dataTable = new
Dwaynexxxx.WebItemDataTable();
Line 1383: this.Adapter.Fill(dataTable); // error is thrown on
this line
Line 1384: return dataTable;
Line 1385: }

What I was trying to do is modify the url in memory, but I can't seem to do
that because the SiteMapNode has not been created.

protected void Page_Load(object sender, EventArgs e)
{
if (Request.QueryString.Count == 0)
{
SiteMap.SiteMapResolve += new
SiteMapResolveEventHandler(this.RewriteUrl);
}
}

private SiteMapNode RewriteUrl(Object sender, SiteMapResolveEventArgs e)
{
SiteMapNode currentNode = SiteMap.CurrentNode.Clone(true);
SiteMapNode tempNode = currentNode;

tempNode.Url = tempNode.Url;

return currentNode;
}

So, when someone requests the about.apsx page via the menu control the
querystring is affixed, but someone requests the about.aspx page directly,
the querystring is not added on the url and the exception is thrown.

Thanks for your help.
Regards.
-Dwayne
 
A

Allen Chen [MSFT]

Hi Dwayne,

Line 1381: }
Line 1382: Dwaynexxxx.WebItemDataTable dataTable = new
Dwaynexxxx.WebItemDataTable();
Line 1383: this.Adapter.Fill(dataTable); // error is thrown on
this line
Line 1384: return dataTable;
Line 1385: }

Thanks for your update. I'm not sure why the above exception is related to
querystring. Please check out the following thread to see if it can help:

http://social.msdn.microsoft.com/forums/en-US/Vsexpressvb/thread/27aec612-5c
a4-41ba-80d6-0204893fdcd1/

Could you tell me where you put the above code (do you put the code in
about.aspx.cs? If so in which event handler?) and why missing querystring
will cause this exception (namely what's the difference if querystring
exists or not)? Please show me some code that can clarify the problem.

Regards,
Allen Chen
Microsoft Online Support
 
D

Dwayne

The about.aspx page contains a Formview and OjbectDataSource control that is
configured declaratively. The ObjectDataSource SelectMethod
'GetWebItemBySitekey' expects the parameter 'sitekey' from the querystring.
If the querystring does not exist, the exception is thrown.

The method GetWebItemBySitekey is contained in my Library assembly.

Here is my about.aspx page code:

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.master"
AutoEventWireup="true" CodeFile="About.aspx.cs" Inherits="About" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="topleft" Runat="Server">
<asp:Image ID="Image2" runat="server" ImageUrl="~/images/D2photo.jpg" />
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="topright" Runat="Server">
<asp:FormView ID="FormView1" runat="server"
DataSourceID="ObjectDataSource1">
<ItemTemplate>
<h4><asp:Label ID="item_titleLabel" runat="server" Text='<%#
Bind("item_title") %>' /></h4>
<asp:Label ID="item_bodyLabel" runat="server" Text='<%#
Bind("item_body") %>' />
</ItemTemplate>
</asp:FormView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
OldValuesParameterFormatString="original_{0}"
SelectMethod="GetWebItemBySitekey" TypeName="Library.BLL.WebItemBL">
<SelectParameters>
<asp:QueryStringParameter Name="sitekey"
QueryStringField="sitekey"
Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
</asp:Content>
<asp:Content ID="Content4" ContentPlaceHolderID="bottomleft" Runat="Server">
</asp:Content>
<asp:Content ID="Content5" ContentPlaceHolderID="bottomright"
Runat="Server">
</asp:Content>

Here is the code for the GetWebItemBySitekey method:

public WebItemTableAdapter adapter
{
get
{
if (_adapter == null)
{
_adapter = new WebItemTableAdapter();
}
return _adapter;
}
}

public DwayneEpps.WebItemDataTable GetWebItemBySitekey(string sitekey)
{
return adapter.GetWebItemBySitekey(sitekey);
}

Does this help explain? Let me know if I can provide additional code or
more information.
Thanks again.
Regards,
-Dwayne
 
A

Allen Chen [MSFT]

Hi Dwayne,
Does this help explain? Let me know if I can provide additional code or
more information.

Yes thanks. Now I see what the problem is. You can check null value in this
method:

public DwayneEpps.WebItemDataTable GetWebItemBySitekey(string sitekey)
{
if (!string.IsNullOrEmpty(sitekey))
{
return adapter.GetWebItemBySitekey(sitekey);
}
else{
return null;
//here nothing is returned if there's no querystring sitekey. You can
return anything you like here.
}
}

Please let me know if it works.

Regards,
Allen Chen
Microsoft Online Support
 
D

Dwayne

Hi Allen,

Yes, that prevents the error from getting thrown.

Thank you for your help. I sincerely appreciate it.

Regards,
-Dwayne
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top