troubleshooting Question

Problem with SiteMap using SQL

Avatar of rock815
rock815 asked on
.NET ProgrammingASP.NETWeb Applications
4 Comments1 Solution451 ViewsLast Modified:
Good day!  Below is the class I'm using to read a SiteMap from SQL Server into the Site Map Control on ASP.NET 2.0.  The problem I have is it returns the results in order by ID.  I need it to display the results in alphabetical order via the "Title" column in SQL.  You can not change the ORDER clause in the pasted code because it will error citing no parent Id.  Can you help?  Thanks!!!!

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

/// <summary>SqlSiteMapProvider</summary>
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 ConfigurationErrorsException(_errmsg1);

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

    [MethodImpl(MethodImplOptions.Synchronized)]
    public override SiteMapNode BuildSiteMap()
    {
       // _root = null;
        // Return immediately if this method has been called before
        if (_root != null)
            return _root;
          //  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
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[_connect].ConnectionString))
        {
            connection.Open();
            SqlCommand command = new SqlCommand("SELECT ID, Title, Description, Url, Roles, Parent FROM SiteMap ORDER BY ID", connection);
            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 ConfigurationErrorsException(_errmsg2); // ConfigurationException pre-Beta 2
                nodes.Add(reader.GetInt32(id), _root);

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

                // Build a tree of SiteMapNodes underneath the root node
                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 ConfigurationErrorsException(_errmsg2);
                    nodes.Add(reader.GetInt32(id), node);

                    // Add the node to the site map
                    AddNode(node, parentnode);
                }
            }
        }

        // Return the root SiteMapNode
        return _root;
    }

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

ASKER CERTIFIED SOLUTION
CmdoProg2

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros