Navigation using dynamic SiteMapPath file

Introduction

This article demonstrates the ASP.NET 2.0 feature to extend menu generation using SiteMapPath. Here, I will be showing how to generate the Web.Sitemap file using the XmlTextWriter at runtime, retrieving the data from SQL Server.

The code

First, I would like to show you the database structure, I have two tables in the database:

1. Master
2. Child

Following are the screenshots of the tables:

Master

Child

To fetch data from the database, I have created two connections:

string _sqlconnString = 
       "server=yoursever;User ID=sa;Password=sa;database=test";
 
SqlConnection source = new SqlConnection(_sqlconnString);
// Create destination connection
SqlConnection destination = new SqlConnection(_sqlconnString);
//Open connections
source.Open();
destination.Open();
// Select data from Products table
SqlCommand cmd = new SqlCommand("SELECT * FROM Master", source);
SqlCommand cmd1 = new SqlCommand("SELECT * FROM Child", destination);
// Execute reader
SqlDataReader reader1 = cmd.ExecuteReader();

Once I get the data from the database, I look into the format of the Web.Sitemap file which has to follow certain formats. The root node must be <siteMap> and that should be followed by <siteMapNode>. The first SiteMapNode refers to the root level of the menu so you can't afford to have multiple <SiteMapNode> under the <SiteMap> tag. So, a well formed XML for a site map would look like the following:

<?xml version="1.0" encoding="utf-8"?>
<siteMap>
 <siteMapNode title="hello" description="hello" url="hello">
 <siteMapNode title="Himanshu" 
      description="Himanshu ALive" url="http://www.yah1oo.com">
 <siteMapNode title="Himanshu1" 
      description="Himanshu1 Alive" url="http://www.yah3oo.com" />
 <siteMapNode title="Himanshu2" 
      description="Himanshu2 Alive" url="http://www.ya4hoo.com" />
 </siteMapNode><siteMapNode title="Dhami" 
      description="Dhami Alive" url="http://www.ya2hoo.com">
 <siteMapNode title="Dhami1" 
      description="Dhami1 Alive" url="http://www.ya5hoo.com" />
 <siteMapNode title="Dhami2" 
      description="Dhami2 Alive" url="http://www.ya6hoo.com" />
 </siteMapNode><siteMapNode title="Dell" 
      description="Dell Alive" url="http://www.dell.com" />
 </siteMapNode>
</siteMap>

Now comes the interesting part, we need to generate the above XML file based on the data retrieved from the SQL Server. As stated above, we have two connections open to the database for retrieving data from the Master and the Child tables.

The first step is to create the XML file, which we will do using the XMLTextWriter as shown below:

//Create XML
Encoding enc = Encoding.UTF8;
XmlTextWriter objXMLTW = new XmlTextWriter(sFileName, enc);

where sFileName is the path of the root directory of your application where you want to generate the Web.SiteMap file. For me, it is something like this:

sFileName=D:\VS2005PROJECTS\TESTASPNET\Web.SiteMap

Now we have created the XML file, so we need to create the required structure based on the fetched data.

<siteMapNode> requires three attributes:

• Title: Displayed in the menu.
• Description: This can be anything.
• URL: Link to some other website when clicked.

Now, I am pasting the code to generate the <siteMap> and the first <siteMapNode>. You can follow them with the comments specified:

try
{
    objXMLTW.WriteStartDocument();//xml document open
    //'Top level (Parent element)
    //root node open
    objXMLTW.WriteStartElement("siteMap");
    //first Node of the Menu open
    objXMLTW.WriteStartElement("siteMapNode");
    objXMLTW.WriteAttributeString("title", 
        "Home");//Title attribute set
    objXMLTW.WriteAttributeString("description", 
        "This is home");//Description attribute set
    objXMLTW.WriteAttributeString("url", 
        "http://www.home.com");//URL attribute set
    //Loop and create nodes

The above code will generate the root node and the first node for the menu, as shown below:

<?xml version="1.0" encoding="utf-8"?>
 <siteMap>
 <siteMapNode title="hello" description="hello" url="hello">

Once we are done with this, we will be looping through the master DataReader and creating the master node elements:

while (reader1.Read())
{
    int MasterID = reader1.GetInt32(0);
    objXMLTW.WriteStartElement("siteMapNode");
    objXMLTW.WriteAttributeString("title",
                    reader1.GetString(1));
    objXMLTW.WriteAttributeString("description",
                          reader1.GetString(2));
    objXMLTW.WriteAttributeString("url",
                  reader1.GetString(3));

Inside the same loop, we will loop through the child DataReader, and if the masterId for the master table matches the master ID for the child table then, we will generate the <SiteMapNode> for the child elements also. This can go down deeper on the basis of the master and the child table structure.

To retrieve the desired data, we will open the child DataReader as shown below:

SqlDataReader reader2 = cmd1.ExecuteReader();
while (reader2.Read())
{
    int ChildMasterID = reader2.GetInt32(1);
    if (MasterID == ChildMasterID)
    {
        //Open Child Element
        objXMLTW.WriteStartElement("siteMapNode");
        objXMLTW.WriteAttributeString("title",
                        reader2.GetString(2));
        objXMLTW.WriteAttributeString("description",
                              reader2.GetString(3));
        objXMLTW.WriteAttributeString("url",
                      reader2.GetString(4));
        objXMLTW.WriteEndElement();//Close Child Element
    }
}
reader2.Close();//Close the child datareader
objXMLTW.WriteEndElement();//Close the siteMapNode

After this, we just need to close the first siteMapNode and close the creation of the XML document:

    }
    objXMLTW.WriteEndElement();//Close the first siteMapNode
    objXMLTW.WriteEndDocument();//xml document closed
}
finally
{
    objXMLTW.Flush();
    objXMLTW.Close();
}
destination.Close();
source.Close();

The above code generates the required Web.SiteMap file in the root directory of the application, and you need to write a function using the code shown above. Just call the function in the Page_Load and it will update the Web.SiteMap file every time you run it. The sample menu looks like this:

This is how the entire code looks like:

private void GenerateXMLFile(string sFileName)
{
    string _sqlconnString = 
           "server=nouswk124;User ID=sa;Password=sa;database=test";
    string _destsqlconnString = 
           "server=nouswk124;User ID=sa;Password=sa;database=test";
    SqlConnection source = new SqlConnection(_sqlconnString);
    // Create destination connection
    SqlConnection destination = new SqlConnection(_destsqlconnString);
    //Open connections
    source.Open();
    destination.Open();
    // Select data from Products table
    SqlCommand cmd = new 
               SqlCommand("SELECT * FROM Master", source);
    SqlCommand cmd1 = new 
               SqlCommand("SELECT * FROM Child", destination);
    // Execute reader
    SqlDataReader reader1 = cmd.ExecuteReader();
  
    //Create XML
    Encoding enc = Encoding.UTF8;
    XmlTextWriter objXMLTW = new XmlTextWriter(sFileName, enc);
    try
    {          
        objXMLTW.WriteStartDocument();//xml document open
        //'Top level (Parent element)
        //root node open
        objXMLTW.WriteStartElement("siteMap");
        //first Node of the Menu open
        objXMLTW.WriteStartElement("siteMapNode");
        //Title attribute set
        objXMLTW.WriteAttributeString("title", "Home");
        objXMLTW.WriteAttributeString("description", 
                 "This is home");//Description attribute set
        objXMLTW.WriteAttributeString("url", 
                 "http://www.home.com");//URL attribute set
        //Loop and create nodes
        while (reader1.Read())
        {
            int MasterID = reader1.GetInt32(0);
            objXMLTW.WriteStartElement("siteMapNode");
            objXMLTW.WriteAttributeString("title", 
                            reader1.GetString(1));
            objXMLTW.WriteAttributeString("description", 
                                  reader1.GetString(2));
            objXMLTW.WriteAttributeString("url", 
                          reader1.GetString(3));

            //objXMLTW.WriteString("No Nonsense" + 
                                    i.ToString());
            SqlDataReader reader2 = cmd1.ExecuteReader();
            while (reader2.Read())
            {
                int ChildMasterID = reader2.GetInt32(1);
                if (MasterID == ChildMasterID)
                {
                    //Open Child Element
                    objXMLTW.WriteStartElement("siteMapNode");
                    objXMLTW.WriteAttributeString("title", 
                                    reader2.GetString(2));
                    objXMLTW.WriteAttributeString("description", 
                                          reader2.GetString(3));
                    objXMLTW.WriteAttributeString("url", 
                                  reader2.GetString(4));
                    objXMLTW.WriteEndElement();
                    //Close Child Element
                }
            }
            reader2.Close();//Close the child datareader
            objXMLTW.WriteEndElement();//Close the siteMapNode
        }
        objXMLTW.WriteEndElement();//Close the first siteMapNode
        objXMLTW.WriteEndDocument();//xml document closed
    }
    finally
    {
        objXMLTW.Flush();
        objXMLTW.Close();
    }

    destination.Close();
    source.Close();
}