Share on Facebook
I needed a way to generate a Web Sitemap file from a Google Sitemap file. There's probably a bunch of those around, if you look long enough, you can find anything on the web these days. Ain't life grand? But I wanted my own for a change and it's sometimes just faster to write your own than to learn someone else's style and work within it.
So, since I need to keep the blog active and I needed this tool for a web site I'm managing - I thought I'd just get two things accomplished at once. Here it is. The source for this Web Sitemap to Google Sitemap tool is available here.
I started by grabbing this GoogleSitemaps for ASPNET 2.0 program put out by Bertrand Leroy. To this gift of code, I added a new Handler, called WSiteMap.ashx. I created a stored procedure that returns from the product, category, manufacturer tables the following three fields: Title, Description, and url. And I added a class to the AppCode folder of the GoogleSitemaps1.1 program, called GenerateSiteMap.cs
If you use the Commerce Starter Kit or a derivative of it, this Stored Procedure may work for you, otherwise, maybe it'll give you a sample to work off of
create PROCEDURE [dbo].[GoogleSiteMapPagesList]
@baseUrl varchar(255)
as
set nocount on
select [categoryName] as 'title' ,
IsNull([shortDescription],'') as [Description],
@baseUrl +
'catalog.aspx?guid=' + convert(varchar(40),categoryGUID)
as [url]
from CSK_Store_Category
UNION
select [productName] as 'title' ,
IsNull([shortDescription],'') as [Description],
@baseUrl + 'product.aspx?guid=' + convert(varchar(40),productGUID) as 'url'
from CSK_Store_Product p where p.isDeleted = 0
Here's the code for GenerateSiteMap.cs
public class GenerateSiteMap
{
public GenerateSiteMap()
{
}
public DataTable createUpdatedList()
{
SqlConnection sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["WebStoreDB_Production"].ConnectionString);
DataTable dt = executeTableSp(sqlconn, "GoogleSiteMapPagesList");
return dt;
}
private DataTable executeTableSp(SqlConnection connection, string commandText, params SqlParameter[] parameters)
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText = commandText;
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
if (!Microsoft.VisualBasic.Information.IsNumeric(parameter.Value))
{
if (parameter.Value is DBNull)
{
parameter.Value = (object)DBNull.Value;
}
else
parameter.Value = parameter.Value.ToString();
}
command.Parameters.Add(parameter);
}
try
{
connection.Open();
DataSet oDataSet = new DataSet();
SqlDataAdapter oAdapter = new SqlDataAdapter(command);
oAdapter.SelectCommand.CommandTimeout = 1000;
oAdapter.Fill(oDataSet, "Return");
return oDataSet.Tables["Return"];
}
finally
{
connection.Close();
}
}
}
}
And the other piece of code is in WSiteMap.ashx class, it's here:
<%@ WebHandler Language="C#" Class="WSiteMap" %>
using System;
using System.Web;
using System.Data;
using System.Text;
using System.IO;
public class WSiteMap : IHttpHandler {
public void ProcessRequest (HttpContext context) {
context.Response.ContentType = "text/plain";
StringBuilder sb = new StringBuilder();
sb.Append("<?xml version=\"1.0\" encoding=\"utf-8\" ?>\r\n");
sb.Append("<siteMap xmlns=\"http://schemas.microsoft.com/AspNet/SiteMap-File-1.0\" >\r\n<siteMapNode>");
GenerateSiteMap gsm = new GenerateSiteMap();
DataTable dt = gsm.createUpdatedList();
foreach (DataRow drow in dt.Rows)
{
sb.Append("<siteMapNode ");
for (int i = 0; i < dt.Columns.Count; i++)
{
sb.Append(dt.Columns[i]).Append("=\"").Append( removeBreaks( drow[i].ToString() )).Append("\" ");
}
sb.Append("/>\r\n");
}
sb.Append("</siteMapNode></siteMap>");
string savepath = context.Server.MapPath(".") + "\\Web.sitemap";
File.Delete(savepath);
File.WriteAllText( savepath, sb.ToString());
context.Response.Write("web.sitemap has been refreshed from the database");
}
private string removeBreaks(string cleanit)
{
return cleanit.Replace("<BR>", " ").Replace("<Br>","").Replace("<br />", " ").Replace("<br>", " ").Replace("<br >","").Replace("<br/>", " ").Replace("\"", "").Replace("&","&") ;
}
public bool IsReusable {
get {
return false;
}
}
}
The process isn't fully automated. I like to be able to checkover what's going out for Google to index, so I am saving the sitemap manually. Here are the steps I use when refreshing my sitemap file:
1. Open Default.aspx in your browser, with the connection strings pointed to your database.
2. Click the link to refresh your web sitemap from the databases.
3. Click the link to view your Google sitemap - it will be displayed onscreen.
4. Save the file as "sitemap.xml".
5. Submit the file to Google using Webmaster Tools.
Subscribe