They Said It Best

Computers in the future may have only 1,000 vacuum tubes and perhapsonly weigh 1 1/2 tons
Popular Mechanics, 1949

Dynamic Sitemap Using ASP.NET

Have you ever wondered why some of your site's pages are indexed by search engines while others are not? Using a sitemap you can tell search engines exactly where to find each of your site's pages. A sitemap is a file using XML that lists URLs for a site so that the site can be more accurately crawled by search engines.

The best news is that now you can specify the sitemap in your robots.txt file and Google's Webmaster Tools, MSN Live, and Yahoo all allow you to submit a site map URL when listing your site.

Here is an example sitemap.


<?xml version="1.0" encoding="UTF-8"?>
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
   <url>
      <loc>http://www.dotnetdesignguide.com/</loc>    
      <changefreq>daily</changefreq>
      <priority>0.5</priority>
   </url>
</urlset>

Unless you have a blog or CMS you have to manually update your sitemap every time you add a new page to your site. Because aspiring web developers never do anything manual we will create a dynamic sitemap using ASP.NET and SQL Server. The urls of the pages on our site will be stored in a SQL Server table and we'll create an ASP.Net page to display the XML syntax. Additionally we could take it a step further and create navigation hyperlinks for our site or even content specific landing pages using the urls from the table.

Step 1. SQL Server table and stored procedure
Step 2. Create Dynamic Sitemap using ASP.NET

Step 1. SQL Server Table and Stored Procedure

First we create a table called "mysitemap". Open SQL Server and open a new query window in your database. Cut and paste the following SQL statement below to create the table. This table will store our data about the urls on our webite that we want search engines to find.

CREATE TABLE [dbo].[MySitemap](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [Location] [varchar](200) NULL,
  [ChangeFreq] [varchar](20) NULL CONSTRAINT [DF_MySitemap_ChangeFreq]  DEFAULT ('daily'),
  [Priority] [varchar](3) NULL CONSTRAINT [DF_MySitemap_Priority]  DEFAULT ((0.5)),
  [Category] [varchar](50) NULL,
 CONSTRAINT [PK_MySitemap] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

After adding some data your table should look similar to this with different urls of course.

SQL Table for storing Sitemap

Next keeping an eye on security and performance we'll create a SQL Server stored procedure called sp_mysitemap_select to query the urls from the table we just created. Be sure to assign execute permissions for this stored procedure to the sql login that your web site uses to query the database.

We are going to add a conditional statement to this stored procedure so that we can use it later on in the example to create navigation menus for the site as well. This way once we have a new page ready on the site all we have to do is add the url to the SQL table and it will be available in the navigation menu as well as the site map automatically.

CREATE  PROCEDURE [dbo].[sp_mysitemap_select] 
(
@category varchar(50)
)
AS

if (@category is null)
	begin
		select Location, ChangeFreq, Priority from mysitemap
		order by Category
	end
else
	begin
		select Location from mysitemap where Category=@category
	end
return

Being good programmers we always test our code on the database before hooking it into a web page so manually execute the store procedure we just created in a query window

Dynamic Sitemap using ASP.NET and SQL Server.

Next Lesson

In Step 2. Create Dynamic Sitemap using ASP.NET we'll create our ASP.NET page to execute the stored procedure we created and then integrate the sitemap protocol XML with our data.