A Dynamic Sql-Based Self-Caching RSS Feed Generator
by Peter A. Bromberg, Ph.D.

Peter Bromberg

"Never confuse movement with action." - - Ernest Hemingway

RSS has become so ubiquitous that it's pretty much expected for virtually any kind of information site to offer at least one feed. What I present here is a slightly different approach to this expectation that conforms to some design goals that I set out for myself:

1) The generator should work based on the contents of a specified database table.

2) The generator should be as efficient as possible and offer caching based on a specified expiration

3) The generator should operate as an HTTPHandler only, without the baggage of the ASP.NET Page class.

4) Most or all of the parameters should be configurable so that any database table that contains suitable data can be used.

5) The generator should be able to generate and cache multiple "categories" of feeds.

There are now a significant number of RSS Generator class libraries in the open - source space. For this one I chose to use RSS.NET. RSS.NET is an open-source .NET class library for RSS feeds. It provides a reusable object model for parsing and writing RSS feeds. It is fully compatible with RSS versions 0.90, 0.91, 0.92, and 2.0.1, implementing all constructs. They haven't made a release yet (it's still Beta) but I haven't been able to find anything wrong with it, so as far as I'm concerned, it works for me.



Writing an HttpHandler is not a difficult task. You can think of an HttpHandler as a way to hook into the current HttpContext without all the baggage of the ASP.NET Page class. You can do pretty much anything you want to, just remember that your output goes directly to the Response stream. Most of the examples of HttpHandlers (ASHX) that you may have seen have their code portion inline inside the .ASHX "page". However, this makes debugging nearly impossible, and there is no reason why a handler can't use the codebehind model. For codebehind, all you need to do is specify the class name (Namespace.ClassName) in the WebHandler directive. The result is that you will have an XXX.ASHX file that consists of a single line, like this.

<%@ WebHandler class="rss" %>

Then all you need to do is create a class file with the name XXX.ASHX.cs and you are on your way. This handler basically accepts a querystring parameter of "feed" and an integer value and can use this to determine the category column to use in your database table as a SQL WHERE clause. The "feed" querystring item is optional.

ASHX WebHandlers cannot be requested directly from within the debugging environment of the IDE, you will need an HTML "Launcher" page with a simple hyperlink to your ASHX as the defined "Start Page".

I've set this up so that all the parameters are specified in the web.config in an appSettings section. That's what makes the arrangement so flexible. In fact, it is so flexible that in the downloadable example, I have used it to generate an RSS 2.0 feed from the Northwind Employees table! So let's take a look at the settings first:

<appSettings>
<add key="connectionString" value="server=localhost;database=Northwind;uid=sa;pwd=;" />
<add key="rssTableName" value="Employees" />
<add key="channelTitle" value="Test Title" />
<add key="channelDescription" value="This is the channel Description heah!" />
<add key="channelLink" value="http://www.mysite.com" />
<add key="itemTitleColumnName" value="LastName" />
<add key="itemLinkColumnName" value="city" />
<add key="itemDescriptionColumnName" value="Notes" />
<add key="itemPubDateColumnName" value="BirthDate" />
<add key="linkUrlPrefix" value="http://www.mysite.com?categoryId=" />
<add key="maxItems" value="100" />
<add key="categoryColumnName" value="" />
<add key="categoryId" value="0" />
<add key="cacheExpirationSeconds" value="3600" />
</appSettings>

Explanation:

rssTableName is the name of the table in your database from which to take the data items for your feed.
channelTitle is the Channel Title value.
channelDescription is the Channel description element value.
channelLink is the Channel link (url) to your site.
itemTitleColumnName is the table column to use for the title element of each item node.
itemLinkColumnName is for the link element.
itemDescriptionColumnName is for the description element.
itemPubDateColumnName is for the pubDate element.
linkUrlPrefix would be the "http://yoursite/yourfeeds/showfeed.aspx?id=" portion of the link element.
maxItems is the maximum number of item elements for the feed.
categoryColumnName is the name of the table column to use for the "WHERE" clause - it is optional.
categoryId is an integer that specifies the value of a column used for the "WHERE" Clause.
cacheExpirationSeconds is the absolute expiration value of the generated DataSet in Cache.

As can be seen, we have pretty much covered all the bases. What happens next is that all these items are used as SqlParameters in the stored procedure that generates our DataSet, through the GetFeed method. The DataSet has two tables, one basically to regurgitate the Channel Information we have provided, and the other to hold a table of standard RSS - named items comprising a subset of the most commonly used RSS 2.0 elements that you find in a typical feed.

The DataSet is stored in Cache, and the cache is checked every time a feed is requested. If the data in cache is live, no database call is necessary. I cannot stress how important this technique is in a high-volume web application. In ASP.NET, if you aren't caching data, even if only for one second, then your app is pretty much a non-performer, in my opinion.

This is then passed into the RenderFeed method which uses the aforementioned RSS.NET Library to create the full feed, and finally the feed is streamed to the Response Output stream.

I won't bore you with the SQL For the Stored procedure or the accompanying RFC822Date user defined SQL function, since they are just plain SQL and I've provided them for your hacking pleasure in the download below. In the feed generating sproc, I use a table variable to hold the rows of items to be returned, which is much more efficient than the use of a temporary table. I also use the MS Data Access Application Block V2 code, which automatically caches SqlParameters and cuts way down on the amount of code needed to make a stored proc call. Let's take a look at the HttpHandler code:

using System;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

using System.IO;

using System.Configuration;

using Rss;

using Microsoft.ApplicationBlocks.Data;

///

/// Rss Handler class provides the methods to get, render and stream the Feed(s)

/// using a Generic Stored Procedure that can use any specified database table

/// and caches the DataSet of results for the specified time for optimal performance

///

public class rss :IHttpHandler

{

 

 /// <summary>

 /// Creates a new <see cref="rss"/> instance.

 /// </summary>

 public rss()

 {

 }

 

 #region IHttpHandler Implementation

 

 /// <summary>

 /// Processes the request.

 /// </summary>

 /// <param name="context">Context.</param>

 public void ProcessRequest(System.Web.HttpContext context)

 {

//feedCode is mapped with a CategoryId eg. 1,2

int feedCode;

if (context.Request.QueryString["feed"]!=null)

{

//This will make sure the page output will be text/XML and not HTML

context.Response.ContentType = "text/xml";

feedCode = int.Parse(context.Request.QueryString["feed"]);

RenderFeed(context.Response.OutputStream, feedCode);

}

else

{

//If not specified, get the "Main" feed with code Id =0

RenderFeed(context.Response.OutputStream,0);

}

 }

 

 /// <summary>

 /// Gets a value indicating whether this instance is reusable.

 /// </summary>

 /// <value>

 /// <c>true</c> if this instance is reusable; otherwise, <c>false</c>.

 /// </value>

 public bool IsReusable

 {

get{return true;}

 }

 #endregion

 

 

 /// <summary>

 /// Creates the RSS feed.

 /// </summary>

 /// <param name="feedCode">Feed code</param>

 /// <returns>RSSFeed instance</returns>

 private RssFeed CreateRssFeed(int feedCode)

 {

DataSet ds;

int cacheExpirationSeconds = Convert.ToInt32(ConfigurationSettings.AppSettings["cacheExpirationSeconds"]);

RssFeed rssFeed = new RssFeed();

if(HttpContext.Current.Cache["feed"+feedCode.ToString()]!=null)

{

ds=(DataSet)HttpContext.Current.Cache["feed"+feedCode.ToString()];

}

else

{

ds = GetFeed(feedCode); // calls the generic stored proc with params from web.config

DateTime expireTime = DateTime.Now.AddSeconds(cacheExpirationSeconds);

HttpContext.Current.Cache.Insert("feed"+feedCode.ToString(),ds,null,
expireTime,System.Web.Caching.Cache.NoSlidingExpiration,
System.Web.Caching.CacheItemPriority.Normal,null);

 

}

 

// Fill in channel information:

RssChannel channel = new RssChannel();

channel.Title = ds.Tables[0].Rows[0]["ChannelTitle"].ToString();

System.Uri uri = new System.Uri( ds.Tables[0].Rows[0]["ChannelLink"].ToString() );

channel.Link = uri;

channel.Description = ds.Tables[0].Rows[0]["ChannelDescription"].ToString();

channel.Generator ="RSSAshxGen 1.0.0.0";

channel.Language = "en";

channel.Copyright = "Copyright © " + System.DateTime.Now.Year + ", "+channel.Title;

channel.ManagingEditor = "Managing.Editor@Domain.com";

channel.TimeToLive = 2;

channel.PubDate = System.DateTime.Now;

// Add items.

RssItem item;

RssGuid rssGuid;

RssSource rssSource;

foreach (DataRow drow in ds.Tables[1].Rows)

{

item = new RssItem();

item.Title = drow["title"].ToString();

item.Description = drow["description"].ToString();

uri = new System.Uri( drow["link"].ToString());

item.Link = uri;

rssGuid = new RssGuid();

rssGuid.Name = drow["title"].ToString();

rssGuid.PermaLink = true;

rssSource = new RssSource();

rssSource.Name = channel.Title;

uri = null;

rssSource.Url = uri;

 

item.PubDate = System.DateTime.Now;

//Add Item to channel

channel.Items.Add(item);

}

rssFeed.Channels.Add(channel);

return rssFeed;

 }

 

 /// <summary>

 /// Renders the feed.

 /// </summary>

 /// <param name="stream">Stream.</param>

 /// <param name="feedCode">Feed code.</param>

 private void RenderFeed(Stream stream, int feedCode)

 {

CreateRssFeed(feedCode).Write(stream);

 }

 

 /// <summary>

 /// Gets the feed.

 /// </summary>

 /// <param name="feedCode">Feed code.</param>

 /// <returns>DataSet</returns>

 public DataSet GetFeed(int feedCode)

 {

DataSet ds = new DataSet();

string connectionString =System.Configuration.ConfigurationSettings.AppSettings["connectionString"].ToString();

/* From web.config appSettings section:

<add key="rssTableName" value="Northwind" />

<add key="channelTitle" value="Test Title" />

<add key="channelDescription" value="whatever you want" />

<add key="channelLink" value="http://www.mysite.com" />

<add key="itemTitleColumnName" value="LastName" />

<add key="itemLinkColumnName" value="city" />

<add key="itemDescriptionColumnName" value="Notes" />

<add key="itemPubDateColumnName" value="BirthDate" />

<add key="linkUrlPrefix" value="http://www.mysite.com?categoryId=" />

<add key="maxItems" value="100" />

<add key="categoryColumnName" value="" />

<add key="categoryId" value="0" />

*/

 string rssTableName = (string)System.Configuration.ConfigurationSettings.AppSettings["rssTableName"];

string channelTitle = (string)System.Configuration.ConfigurationSettings.AppSettings["channelTitle"];

string channelLink =(string)System.Configuration.ConfigurationSettings.AppSettings["channelLink"];

string channelDescription = (string)System.Configuration.ConfigurationSettings.AppSettings["channelDescription"];

string itemTitleColumnName =(string)System.Configuration.ConfigurationSettings.AppSettings["itemTitleColumnName"];

string itemLinkColumnName = (string)System.Configuration.ConfigurationSettings.AppSettings["itemLinkColumnName"];

string itemDescriptionColumnName = (string)System.Configuration.ConfigurationSettings.AppSettings["itemDescriptionColumnName"];

string itemPubDateColumnName = (string)System.Configuration.ConfigurationSettings.AppSettings["itemPubDateColumnName"];

string linkUrlPrefix =(string)System.Configuration.ConfigurationSettings.AppSettings["linkUrlPrefix"];

int maxItems= Convert.ToInt32((string)System.Configuration.ConfigurationSettings.AppSettings["maxItems"]);

 

// NOTE: categoryColumnName and categoryId and used to create a WHERE clause on the table if needed

// e.g. ... WHERE [categoryColumnName]=[categoryId]

string categoryColumnName = (string)System.Configuration.ConfigurationSettings.AppSettings["categoryColumnName"];

int categoryId = Convert.ToInt32( (string)System.Configuration.ConfigurationSettings.AppSettings["categoryId"]);

// use SqlHelper (MS Application Block v2) class:

object[] parms=new object[] {rssTableName,channelTitle,channelLink, channelDescription,itemTitleColumnName,itemLinkColumnName,itemDescriptionColumnName,
itemPubDateColumnName,linkUrlPrefix,maxItems,categoryColumnName,categoryId };

ds=SqlHelper.ExecuteDataset(connectionString,"dbo.usp_GenerateRSS2",parms);

return ds;

 }

 

 

}

And that pretty much works for me. I can generate feeds on demand from a messageboard posts table or a table of articles and links, and all I need to do is specify the columns to use along with some metadata in the web.config. My feed gets cached for 60 minutes or whatever I need, and I don't have the overhead of the ASP.NET Page class.

There are several enhancements and / or hacks that can be made using this arrangement as a base to work from. For example, you may have a situation where you need a SQL table join to get all the elements you need for a complete feed. You can easily rearrange the dynamic SQL in the stored proc provided in order to accomodate this. You could also rearrange the code so that the CategoryId is the same as the Querystring item (feedcode).

The download includes the solution, the two SQL Scripts, and a sample web.config, all filled out to get the Northwind Employees table as an RSS Feed.

Download the VS.NET Solution that accompanies this article


Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform.