Creating an RSS feed for your website from your SQL database

Introduction

Some time ago, I was thinking about starting my own business and registered a domain etc., but then I was made an offer I couldn't refuse, and so I put these plans on hold. I still had my domain (http://www.bid4binary.com) and was paying for hosting, so I thought I would do something, and blogging seemed to be the natural choice. I therefore wrote myself a blog system and started using it. Shortly thereafter, one of my readers asked me where my RSS feed was, and I realised that while I had heard of RSS, I had no idea exactly what it was...

Background

And so, I did a bit of research on RSS. RSS is generally considered to stand for Real Simple Syndication, and in a nutshell, is a way to "push" content from your website to users who have chosen to subscribe to this content. You can read more about blogs and blogging over here: http://www.bid4binary.com/Item.aspx?iID=72. I thought that it might be complicated, but was pleasantly surprised at how easy it was to create an RSS feed from my blog.

Using the code

In your ASP.NET project, add a new Web Form called MyFeed.aspx. Open the source of this page and delete everything but the first line, which should read something like:

<%@ Page Language="VB" AutoEventWireup="false" 
         CodeFile="MyFeed.aspx.vb" Inherits="MyFeed" %>

Below this, add the following line:

<%@ OutputCache Duration="300" VaryByParam="Category" %>

OutputCache is the amount of time in seconds that the ASP.NET server will keep the page in its cache so that the database is not interrogated every time someone opens your RSS feed. You can decrease this depending on how often your feed is updated.

The optional VaryByParam allows you to pass a parameter to your SQL query that will change the content of your RSS feed.

That's it for the ASPX page. Make sure you save it, and let's move on to the code-behind. Open the code-behind page and import the following namespaces:

Imports System.Data.SqlClient
Imports System.Text
Imports System.Xml

Now, add the following to the Page_Load event:

Response.Clear()
Response.ContentType "application/rss+xml;"

Dim xrFeed As XmlTextWriter = New XmlTextWriter
Response.OutputStream, Encoding.UTF8)
xrFeed.WriteStartDocument()

There are a few elements we need to include in all our RSS feeds: a title, a link, a ttl, and a description.

• title - the name of your channel and how people will refer to it.
• link - the URL of the website from which the feed originates.
• ttl - "time to live" - the number of minutes a feed can be cached before refreshing from the source.
• description - a phrase describing the RSS feed.

More information on these elements can be found here.

xrFeed.WriteStartElement("rss")
xrFeed.WriteAttributeString("version", "2.0")
xrFeed.WriteStartElement("channel")
xrFeed.WriteElementString("title", "Latest Blog Items")
xrFeed.WriteElementString("link", "http://www.mysite.com")
xrFeed.WriteElementString("ttl", "10")
xrFeed.WriteElementString("description", "The latest items from my blog.")

Now we connect to our database to retrieve our blog items for our feed. I first select the email address of the website owner from my "settings" table, and then I select the items from my blog.

Dim clsUtils As New Utils
Dim intBlogID As Integer = clsUtils.intBlogID
Dim strConnection As String = clsUtils.strConnection

Dim connSql As SqlConnection = New SqlConnection(strConnection)
Dim cmdSql As SqlCommand = New SqlCommand

cmdSql.Connection = connSql
cmdSql.CommandText = "SELECT strOwnerEmail From " & _ 
                     "tblSiteSettings WHERE intUniqueID = " & intBlogID
connSql.Open() 
Dim strOwnerEmail As String = cmdSql.ExecuteScalar
connSql.Close()

cmdSql = New SqlCommand
cmdSql.Connection = connSql
Dim strSql as String = ""
If Trim(Request.QueryString("cID")) <> "" Then
    strSql = "SELECT * FROM tblItems WHERE I.intFKBlogItemCategoryID = " 
    strSql += Request.QueryString("cID") & " AND I.bitActive = 1 "
    strSql += "AND I.intFKBlogID = " & intBlogID.ToString & _
              " ORDER BY dtPosted DESC"
Else
    strSql = "SELECT * FROM tblItems WHERE I.bitActive = 1 AND "
    strSql += "I.intFKBlogID = " & intBlogID.ToString & _
              " ORDER BY dtPosted DESC"
End If
cmdSql.CommandText = strSql

connSql.Open()
Dim drSql As SqlDataReader = cmdSql.ExecuteReader
Do While drSql.Read

For each item I want in my feed, I create several elements:

• title - the title of this particular item
• description - the content of this particular item
• category - the category to which this particular item belongs
• link - the URL to this particular item
• guid - a string that uniquely identifies this particular item
• pubDate - the date that this particular item was published (watch out for the formatting on this one)

xrFeed.WriteStartElement("item")
xrFeed.WriteElementString("title", drSql("strTitle").ToString)
xrFeed.WriteElementString("description", Replace(drSql("strBody").ToString(), "", vbCrLf))
xrFeed.WriteElementString("category", drSql("strCategory").ToString)
xrFeed.WriteElementString("author", strOwnerEmail & " (" & _
                          drSql("strPostedBy").ToString & ")")
xrFeed.WriteElementString("link", _
                          "http://www.mysite.com/Item.aspx?iID=" & _
                          drSql("intUniqueID"))
xrFeed.WriteElementString("guid", _
                          "http://www.mysite.com/Item.aspx?iID=" & _
                          drSql("intUniqueID"))
xrFeed.WriteElementString("pubDate", drSql.GetDateTime(4).ToString("R"))

Now I just close everything off.

xrFeed.WriteEndElement() 
Loop
drSql.Close()
connSql.Close()

xrFeed.WriteEndElement()
xrFeed.WriteEndElement() 
xrFeed.WriteEndDocument()
xrFeed.Flush() 
xrFeed.Close()

Response.End()

And that's it!

Now, if you want your visitors to know that your site has an active RSS feed, add the following link into the <head> section of your site's pages:

<link rel="alternate" type="application/atom+xml" 
      title="Latest Items" href="http://www.mysite.com/MyFeed.aspx/" />

When they visit any of your pages with this link, their browser will know that you have an RSS feed available. Also, if your page has the above link in it, then an RSS reader pointed at the page will automatically pick up your feed.

Points of Interest

RSS feeds have the added bonus of being very search engine friendly, and may contribute to your site ranking higher in search results.