How to create dynamic sitemap xml file from database in ASP.Net

 

Introduction

Today i will explain how to create dynamic sitemap.xml file which is very useful for search engines to find all of your urls. One way to create sitemap file is manually, but doing so means that I would have to update it every time I added an article, or even deleted one. So, much like my current RSS feed, I decided to generate the sitemap dynamically on demand.

Google publish extremely helpful guidelines for site owners, including details on the specification of the sitemap format that they are happy to accept. The current standard is SiteMap Protocol 0.9, and is the definition of a fairly simple xml file. One way to create is manually, but doing so means that I would have to update it every time I added an article, or even deleted one. So, much like my current RSS feed, I decided to generate the sitemap dynamically on demand.

History

In the Previous articles i explained how to integrate ckeditor with asp.nethow to upload image to the server or application using Ckeditor , how to change or set width of ckeditor textbox area using asp.nethow to set or change height of Ckeditor textbox area using ASP.Netintegrate custom image uploader control with ckeditor using asp.nethow to add asp.net membership tables to the existing databasewhere to add connection string in web .config file of asp.net, how to get connection string from web .config file  and many more about Asp.Net.

The xml format is simply this:


For each <url> tag, there must be a <loc> tag pointing to the URL of the page. <lastmod>, <changefreq> and <priority> are optional. More details can be found at the link above for the specification.

Step 1.

Create a Blank Sitemap.xml file to the root of your web folder. And add content type like showing below.

Step 2.

Add new .aspx page to your application and name it as "sitemap.aspx".

Step 3.

Write the below code on load event of the "sitemap.aspx" page. Here is the full code-behind of sitemap.aspx.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Xml;
using System.Text;
using System.Data.SqlClient;
using System.Data;

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

    protected void Page_Load(object sender, EventArgs e)

    {

        Response.Clear();

        Response.ContentType = "text/xml";

        using (XmlTextWriter writer = new XmlTextWriter(Server.MapPath("sitemap.xml"), Encoding.UTF8))

        {

            writer.WriteStartDocument();

            writer.WriteStartElement("urlset");

            writer.WriteAttributeString("xmlns", "http://www.google.com/schemas/sitemap/0.84");

            writer.WriteAttributeString("xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance");

            writer.WriteAttributeString("xsi:schemaLocation", "http://www.google.com/schemas/sitemap/0.84 http://www.google.com/schemas/sitemap/0.84/sitemap.xsd");

            string dbstring = System.Configuration.ConfigurationManager.ConnectionStrings["database_con"].ConnectionString;

            SqlConnection connection = new SqlConnection(dbstring);

            connection.Open();

            SqlDataAdapter adp1 = new SqlDataAdapter("Select title,id from articles", connection);

            DataSet ds = new DataSet();

            adp1.Fill(ds);

            if (ds.Tables[0].Rows.Count > 0)

            {

                for (int LIntCtr = 0; LIntCtr <= ds.Tables[0].Rows.Count - 1; LIntCtr++)

                {

                    writer.WriteStartElement("url");

                    writer.WriteElementString("loc", "http://www.mysite.com/articles/" + ds.Tables[0].Rows[LIntCtr]["title"].ToString()+ "");

                    writer.WriteElementString("lastmod", String.Format("{0:yyyy-MM-dd}", DateTime.Now));

                    writer.WriteElementString("changefreq", "daily");

                    writer.WriteElementString("priority", "1.00");

                    writer.WriteEndElement();

                }

            }

            writer.WriteEndElement();

            writer.Flush();

        }

    }

}

Screenshot

Step 4.

Run your "sitemap.aspx" page.

Step 5.

Check your "sitemap.xml" file. It is ready for you.

Give your Valuable Comments.
comments powered by Disqus
 
Follow me on twitter
About Me
Harpreet Singh
I began programming with C++ when i was 17. Then at the middle of my study .NET came. Then I began to read C# and VB.NET. By the time i learnt SQL and ASP.NET, and developed some websites such as news portals that are active now. Currently i am running my own Software company.
More Articles