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:
- Master
- 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);
SqlConnection destination = new SqlConnection(_sqlconnString);
source.Open();
destination.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM Master", source);
SqlCommand cmd1 = new SqlCommand("SELECT * FROM Child", destination);
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:
="1.0" ="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:
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();
objXMLTW.WriteStartElement("siteMap");
objXMLTW.WriteStartElement("siteMapNode");
objXMLTW.WriteAttributeString("title",
"Home");
objXMLTW.WriteAttributeString("description",
"This is home");
objXMLTW.WriteAttributeString("url",
"http://www.home.com");
The above code will generate the root node and the first node for the menu, as shown below:
="1.0" ="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)
{
objXMLTW.WriteStartElement("siteMapNode");
objXMLTW.WriteAttributeString("title",
reader2.GetString(2));
objXMLTW.WriteAttributeString("description",
reader2.GetString(3));
objXMLTW.WriteAttributeString("url",
reader2.GetString(4));
objXMLTW.WriteEndElement();
}
}
reader2.Close();
objXMLTW.WriteEndElement();
After this, we just need to close the first siteMapNode
and close the creation of the XML document:
}
objXMLTW.WriteEndElement();
objXMLTW.WriteEndDocument();
}
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);
SqlConnection destination = new SqlConnection(_destsqlconnString);
source.Open();
destination.Open();
SqlCommand cmd = new
SqlCommand("SELECT * FROM Master", source);
SqlCommand cmd1 = new
SqlCommand("SELECT * FROM Child", destination);
SqlDataReader reader1 = cmd.ExecuteReader();
Encoding enc = Encoding.UTF8;
XmlTextWriter objXMLTW = new XmlTextWriter(sFileName, enc);
try
{
objXMLTW.WriteStartDocument();
objXMLTW.WriteStartElement("siteMap");
objXMLTW.WriteStartElement("siteMapNode");
objXMLTW.WriteAttributeString("title", "Home");
objXMLTW.WriteAttributeString("description",
"This is home");
objXMLTW.WriteAttributeString("url",
"http://www.home.com");
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));
i.ToString());
SqlDataReader reader2 = cmd1.ExecuteReader();
while (reader2.Read())
{
int ChildMasterID = reader2.GetInt32(1);
if (MasterID == ChildMasterID)
{
objXMLTW.WriteStartElement("siteMapNode");
objXMLTW.WriteAttributeString("title",
reader2.GetString(2));
objXMLTW.WriteAttributeString("description",
reader2.GetString(3));
objXMLTW.WriteAttributeString("url",
reader2.GetString(4));
objXMLTW.WriteEndElement();
}
}
reader2.Close();
objXMLTW.WriteEndElement();
}
objXMLTW.WriteEndElement();
objXMLTW.WriteEndDocument();
}
finally
{
objXMLTW.Flush();
objXMLTW.Close();
}
destination.Close();
source.Close();
}