Giving back: An application to write sitemaps for dynamic
database drivin sites.

So, you have a dynamic website that uses querystrings to produce content. I have
come into a problem with my site to produce good results telling google what
pages to index, and what pages not to. 

Thinking about many ways to do this, I decided to write a VB .net windows
application to look at the active listings in the site and make a XML standard
SiteMap file. This application will run on the server on a schedule, 3 times a
day, and produce any updated pages into the sitemap file, as well as removing
the listings that no longer are active. 

This is acheived by having a column in your database table that is a bit (true
or false) and your website will update it when a page is active or not. The
application will then look at the database, preform a query on the page id, and
if it is active, and then write the XML header, the content, and then the
footer, and write the file. 

Sample code: 

Imports System
Imports System.IO
Imports System.Data.SqlClient



Private Sub Listings() 

Dim header As String header = "<?xml version=""1.0""
encoding=""UTF-8""?>" & vbCrLf & "
<urlset
xmlns=""http://www.sitemaps.org/schemas/sitemap/0.9"">&qu
ot; & vbCrLf 

Dim xmlcontent As String
Dim footer As String = " </urlset>"
Dim SQLStr As String
Dim ConnString As String


ConnString = "Data Source=xxx.xxx.xxx.xxx;Initial Catalog=yourdb;Persist
Security Info=True;User ID=username;Password=password"
SQLStr = "Select PageID, ActiveListing from db_Pages where
ActiveListing=1"


Dim SQLConn As New SqlConnection() 'The SQL Connection 
Dim SQLCmd As New SqlCommand() 'The SQL Command 
Dim SQLdr As SqlDataReader 'The Local Data Store 


SQLConn.ConnectionString = ConnString 

SQLConn.Open() 

SQLCmd.Connection = SQLConn

SQLCmd.CommandText = SQLStr 
SQLdr = SQLCmd.ExecuteReader

While SQLdr.Read() 

xmlcontent += "- <url>" & vbCrLf & "
<loc>http://www.yoursite.com/PageView.aspx?pageid=" &
SQLdr("PageID") & "</loc> " & vbCrLf &
" <priority>0.5</priority> " & vbCrLf & "
<changefreq>always</changefreq>" & vbCrLf & "
</url>" & vbCrLf End While


SQLdr.Close()
SQLConn.Close()


Dim path As String = "c:\Inetpub\Yoursite\SiteMap.xml"
Dim sw As StreamWriter
Dim createText As String = header + xmlcontent + footer
File.WriteAllText(path, createText)
Dim readText As String = File.ReadAllText(path)
Console.WriteLine(readText) 

Me.Close()


End Sub



This will effectivly get all your dynamic pages listed in the major search
engines by telling it where to look. 

Hopefully this will help someone, as it has certainly helped me.