dougv.com « Doug Vanderweide

Creating An ASP.NET RSS Feed, Using Data From SQL Server And HTTP WebHandler

There are a couple of ways to create an RSS feed from a SQL database store. Over at 4GuysFromRolla.com, there’s a post explaining how to create an RSS feed using a regular old ASP.NET Web Form.

Another option would be to write a script that creates an actual XML file on some periodic basis (probably just before the recommended “time to live” setting of the feed). The benefit of that is, one taxes the database server a little every now and then, and a “real” XML file does the work.

But as a rule, for ASP.NET applications, Microsoft recommends using HTTP handlers or modules whenever one wants to present data other than HTML.

(Aside on handlers vs. modules: A handler is a special ASP.NET Web page; a module is a plug-in one can install in Internet Information Server. If you’ve got a lot of different, special-case Web processing, or one need that is near-constant — such as processing images stored in a database every time a specific page is called — then you’ll want to consider a module. For occasional or lightweight processing, such as serving up a low-use RSS stream, a handler will do fine. End of aside.)

So that’s what we’ll use here. Let’s begin by creating an ASP.NET HTTP handler, which is written in the same way one would write the code behind for an ASP.NET page, but uses the file extension .ashx.

Creating An ASP.NET HTTP Handler

Fortunately, handlers are very easy to create, especially if you’re using Visual Studio / Visual Web Developer Express as your IDE. You can select it (“Generic Handler”) right from the Add New Item menu:

<%@ WebHandler Language="VB" Class="Handler" %>

Imports System
Imports System.Web

Public Class Handler : Implements IHttpHandler

    Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
        context.Response.ContentType = "text/plain"
        context.Response.Write("Hello World")
    End Sub

    Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable
        Get
            Return False
        End Get
    End Property

End Class

What this really does is create a special class for handling our request. And since we may well have several different kinds of handlers in our Web application, we ought to change the name of the class to be more meaningful — that is, to better reflect what the class is actually doing.

In the case of our handler, we’re creating an RSS feed. So, let’s rename the handler to RSSFeed.ashx. If we do that at the time we create the handler, Visual Studio will make the necessary changes for us. However, if we’re making this file by hand, we need to declare the class name in our WebHander directive declaration:

<%@ WebHandler Language="VB" Class="RSSFeed" %>

We also want to change the class declaration:

Public Class RSSFeed : Implements IHttpHandler

Add Required Namespaces

With our HTTP handler created, we can move on to programming the specific solution. Let’s add the namespaces we need. I’m going to use an XMLTextWriter to create the feed, and SQL Server to provide the data. Therefore, I need the System.Text, System.Xml, System.Data and System.Data.SqlClient namespaces:

<%@ WebHandler Language="VB" Class="RSSFeed" %>

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

Public Class RSSFeed : Implements IHttpHandler

Create Function To Retrieve Records From Database

To help abstract this solution, I’m going to break out the process of getting the records from the database into a function. This function will return a DataSet, which I will consume in the ProcessRequest subroutine.

Private Function GetArticles() As DataSet
	Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("my_connection_string").ConnectionString)
	Dim cmd As New SqlCommand("my_stored_procedure", conn)
	cmd.CommandType = CommandType.StoredProcedure

	Dim ds As New DataSet

	conn.Open()
	Dim da As New SqlDataAdapter(cmd)
	da.Fill(ds)
	conn.Close()
	cmd.Dispose()
	conn.Dispose()

	Return ds
End Function

Note that I am not checking for errors here. That’s because I don’t know what might be an acceptable error-catching scheme for you, and I don’t want to complicate this post with error-trapping methodologies you cannot use. But you should definitely check for errors.

If anything goes wrong, the DataSet as written won’t have any DataTable objects; you could always test for that, but I agree with John Sonmez at ElegantCode.com that throwing a null instead of an exception is a form of laziness.

If I were writing this handler for production, I would add a Try-Catch block. How you trap DB errors is up to you, but again, you should definitely trap them.

Prepare The Handler To Cache Results And Respond With The Proper Type

I need to instruct the handler as to what content type it’s creating. And I also want to cache these results, so that I don’t call upon the database every time someone wants to access the feed.

I’ll set the handler to produce XML, with UTF-8 encoding, and to cache results for one hour.

Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest
	'send document as response
	context.Response.ContentType = "text/xml"
	context.Response.ContentEncoding = System.Text.Encoding.UTF8
	context.Response.Cache.SetExpires(DateTime.Now.AddSeconds(3600))
	context.Response.Cache.SetCacheability(HttpCacheability.Public)

Sure, I could have used AddHours instead of AddSeconds here. That would have been better, but I’m a bit too lazy to change it now. I program a lot in PHP / JavaScript, too, so when working with time, I tend to go the Unix timestamp route by default, which generally means working in seconds.

(Aside on caching DB results: If I wanted to get all fancy, I could set the handler’s cache expiration to whenever there are changes to the underlying SQL Server data, via the SqlCacheDependency class. But that requires extra code and confuses the process to some extent, so I’ve simply fixed the cache time to one hour, which is the same as my feed’s TTL. End of aside.)

Use XmlTextWriter To Create The RSS Feed

With our records on hand, we can go ahead and create the feed. I do that with an XmlTextWriter that is set to become the output stream for the handler. (That is, I tell the handler, “Take a look at this here XmlTextWriter I’m making. You and it are the effectively same thing. Whatever it does, that’s what you’re doing.”)

I set the XmlTextWriter to encode its XML file in UTF-8, and then I create the opening <channel> data:

'create RSS xml document
Dim xml As New XmlTextWriter(context.Response.OutputStream, Encoding.UTF8)
xml.Formatting = Formatting.Indented

xml.WriteStartDocument()
xml.WriteStartElement("rss")
xml.WriteAttributeString("version", "2.0")

'create feed header section
xml.WriteStartElement("channel")
xml.WriteElementString("title", "ASP.NET RSS Feed - Web Handler Demo")
xml.WriteElementString("link", "http://www.dougv.net")
xml.WriteElementString("description", "A demonstration of using an ASP.NET Web Handler to create an RSS feed of SQL Server data")
xml.WriteElementString("language", "en-us")
xml.WriteElementString("pubDate", DateTime.UtcNow.ToString("r"))
xml.WriteElementString("lastBuildDate", DateTime.UtcNow.ToString("r"))
xml.WriteElementString("managingEditor", "dougvanderweide@gmail.com")
xml.WriteElementString("webMaster", "dougvanderweide@gmail.com")
xml.WriteElementString("ttl", "60")

Note that, at Line 34, I use UTC time and output it in RFC1123 format. That’s the RSS standard.

Because I want to ensure as many RSS readers as possible access this file as little as necessary, I ensure that the last build date is easily understood, and provide my time-to-live value at Line 37. That way, a well-made RSS reader should locally cache the RSS feed until the last build date / TTL values indicate it’s time to get a fresh copy.

Iterate Through The DataSet’s Records And Add Them As Items

With that out of the way, we can now focus on the ProcessRequest subroutine. First, we create a local DataSet that calls the GetArticles() function, previously described. Once that’s loaded, we go ahead and iterate the results, adding each as an <item> of the feed:

'get top stories
Dim ds As DataSet = GetArticles()

'add stories to feed
Dim row As DataRow
For Each row In ds.Tables(0).Rows
	xml.WriteStartElement("item")
	xml.WriteElementString("title", row("article_title"))
	xml.WriteElementString("link", row("article_link"))

	'output story as cdata
	xml.WriteStartElement("description")
	xml.WriteCData(row("article_text"))
	xml.WriteEndElement()

	xml.WriteElementString("pubDate", CType(row("article_date"), DateTime).ToString("r"))
	xml.WriteEndElement()
Next

Some of the data I intend to output contains HTML entities (i.e., HTML markup). When you create an XML document that contains entities, you must output that data as CDATA. So where before, I was using the WriteElementString method to output quick-and-dirty child tags, Lines 50-52 use the necessarily much more verbose methods WriteStartElement, WriteCdata and WriteEndElement.

Lastly, note that at Line 54, I intentionally cast the database’s date value to DateTime, so that I can be absolutely certain that when I use ToString to format it, I get the result I want.

Close Tags, Flush The Buffer And Close The XmlTextWriter

Now we can go ahead and close all open XML tags, flush the XmlTextWriter’s buffer (that is, go ahead and output the document), and close the XmlTextWriter. We can also declare the end of the ProcessRequest subroutine:

	'close channel
	xml.WriteEndElement()
	'close rss
	xml.WriteEndElement()
	'close document
	xml.WriteEndDocument()
	xml.Flush()
	xml.Close()

End Sub

I use both the Flush and Close methods on the XmlTextWriter to ensure that the handler is entirely clear that it can go ahead and send its response.

This is almost certainly overkill; I could probably get away with just calling Close. However, I don’t like chancing that the buffer will output completely to the handler’s response stream without an explicit instruction to do so, and that’s the primary purpose of Flush. So that’s why I call both.

Demo, Download And Links

And just like that, we have an RSS feed. You can see a working demo at http://www.dougv.net/demos/rss_web_handler/

I distribute this code under the GNU GPL version 3. Download the ASHX file: Creating An ASP.NET RSS Feed, Using Data From SQL Server And HTTP WebHandler Sample Code

All links in this post on delicious: http://www.delicious.com/dougvdotcom/creating-an-asp-net-rss-feed-using-data-from-sql-server-and-http-webhandler