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.
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)
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
'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", "email@example.com") xml.WriteElementString("webMaster", "firstname.lastname@example.org") 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
'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.
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
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.
Download the ASHX file: Creating An ASP.NET RSS Feed, Using Data From SQL Server And HTTP WebHandler demo 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