Create an RSS Feed for your Website from a database

Walkthrough: Creating a multi-lingual RSS Feed for your Website in ASP.NET using VB.NET or C#.NET from an SQL Server database

To help you run through this walkthrough, please follow the guide on setting up our test environment, or adapt the code to fit your needs.

Experience Level - Intermediate

About

RSS is a standardised XML based file that provides information on updates made to your site.

These can then be used by various news aggregation applications that go out and check for changes in the users various favourite sites and informs them of anything new.

Due to the nature of the development of the web, there are a couple of slightly different formats, the one below is in Atom 2.0.

Sample RSS File

<rss xmlns:atom="http://www.w3.org/2005/Atom" version="2.0">  <channel>    <atom:link href="" rel="self" type="application/rss+xml"/>    <title/>    <link/>    <copyright>Copyright Claytabase 2012</copyright>    <language/>    <description/>    <item>      <title>Create an RSS Feed for your Website In ASP NET and SQL Server</title>      <description>Easy way to create an RSS Feed for your Website in ASP.NET in VB.NET or C#.NET</description>      <link>https://www.claytabase.co.uk/Academy/Learning-Web-Design/Using-ASP-NET/Create-an-RSS-Feed-for-your-Website-In-ASP-NET-and-SQL-Server</link>      <pubDate>Wed, 15 Dec 2021 09:37:24 GMT</pubDate>      <category>monthly</category>      <guid>https://www.claytabase.co.uk/A7038527-90D0-4214-8C65-3A2BD831F141</guid>    </item>    <item>      <title>CSS Styling for AJAX Accordion Control</title>      <description>Some simple CSS styling rules for an AJAX Accordion Control</description>      <link>https://www.claytabase.co.uk/Academy/Learning-Web-Design/Using-CSS/CSS-Styling-for-AJAX-Accordion-Control</link>      <pubDate>Tue, 14 Dec 2021 07:00:00 GMT</pubDate>      <category>monthly</category>      <guid>https://www.claytabase.co.uk/0DB19797-5B3A-45F0-B3E6-2A8080DA60EE</guid>    </item>  </channel></rss>

We've created a sample table, and run this through in our Academy Database. This table will include a UNIQUEIDENTIFIER (GUID), Title, Description, URL, language and date, this data would be the sort of information captured or required in most CMS's.

USE ClaytabaseAcademyGOCREATE TABLE RSSPages(PageGUID UNIQUEIDENTIFIER CONSTRAINT DF_PageGUID DEFAULT NEWSEQUENTIALID() CONSTRAINT PK_PageGUID PRIMARY KEY,PageTitle NVARCHAR(200),PageDescription NVARCHAR(500),PageURL NVARCHAR(500),PageLanguage NVARCHAR(2),PageDate DATETIME,ChangeFrequency NVARCHAR(20))GOINSERT INTO RSSPages(PageTitle,PageDescription,PageURL,PageDate,PageLanguage,ChangeFrequency)SELECT 'Create an RSS Feed for your Website from a database','Walkthrough: Creating an RSS Feed for your Website in ASP.NET using VB.NET or C#.NET from an SQL Server database','https://www.claytabase.co.uk/Academy/Learning-Web-Design/Using-ASP-NET/Create-an-RSS-Feed-for-your-Website-In-ASP-NET-and-SQL-Server','2021-12-15 10:00:00','en','Weekly'INSERT INTO RSSPages(PageTitle,PageDescription,PageURL,PageDate,PageLanguage,ChangeFrequency)SELECT 'CSS Styling for AJAX Accordion Control','Some simple CSS styling rules for an AJAX Accordion Control','https://www.claytabase.co.uk/Academy/Learning-Web-Design/Using-CSS/CSS-Styling-for-AJAX-Accordion-Control',GETDATE(),'en','Monthly'INSERT INTO RSSPages(PageTitle,PageDescription,PageURL,PageDate,PageLanguage,ChangeFrequency)SELECT 'We''ll take the strain while you do what you are good at','A Multi-National team with over 20 years of experience specialising in Web, Database, Cloud services and bespoke Business Management Software','https://www.claytabase.co.uk/',GETDATE(),'en','Daily'INSERT INTO RSSPages(PageTitle,PageDescription,PageURL,PageDate,PageLanguage,ChangeFrequency)SELECT 'Wir nehmen Ihnen die Anstrengung, während Sie das tun, was Sie gut können','Ein multinationales Team mit über 20 Jahren Erfahrung, das sich auf Web-, Datenbank-, Cloud-Dienste und maßgeschneiderte Business-Management-Software spezialisiert hat','https://de.claytabase.com/',GETDATE(),'de','Daily'INSERT INTO RSSPages(PageTitle,PageDescription,PageURL,PageDate,PageLanguage,ChangeFrequency)SELECT 'Nos esforzaremos mientras haces lo que se te da bien','Un equipo multinacional con más de 20 años de experiencia especializado en Web, bases de datos, servicios en la nube y software de gestión empresarial a medida.','https://de.claytabase.com/',GETDATE(),'es','Daily'GOCREATE PROC GetRSSPages(@Language NVARCHAR(2)) AS BEGINSELECT * FROM RSSPagesWHERE PageLanguage=@LanguageENDGOEXEC GetRSSPages 'de'

Add a new Web Form in Visual Studio

In VS, add a web form by right clicking on the Pages folder, selecting Add and then Web Form.

Now we want to go into the code behind, so right click on your new page and select View Code

The code probably looks a lot more complex than it is, so lets have a look at what it does.

First we import the namespaces for SQL and XML.

Then the connection to the database is set, this is pulling it from the web config in this instance.

VBC#

VB

Imports System.Data.SqlClientImports System.XmlPublic Class RSS    Inherits System.Web.UI.Page    Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("SqlConnection").ConnectionString)    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load        Dim RSSLanguage As String = "en"        Dim BaseURL As String = "https://www.claytabase.co.uk/"        Dim MyTitle As String = "Academy Title"        Dim MyDescr As String = "Academy Description"
        'Clear any previous output from the buffer        Response.ClearContent()        Response.ContentType = "text/xml"        Response.Charset = "Utf-8"        Dim xtwFeed As XmlTextWriter = New XmlTextWriter(Response.OutputStream, Encoding.UTF8)        xtwFeed.WriteStartDocument()        'The mandatory rss tag        xtwFeed.WriteStartElement("rss")        xtwFeed.WriteAttributeString("version", "2.0")        xtwFeed.WriteAttributeString("xmlns:atom", "https://www.w3.org/2005/Atom")        'The channel tag contains RSS feed details        xtwFeed.WriteStartElement("channel")        xtwFeed.WriteRaw("<atom:link href=""" & BaseURL & RSSLanguage & "/rss"" rel=""self"" type=""application/rss+xml"" />")        xtwFeed.WriteElementString("title", MyTitle)        xtwFeed.WriteElementString("link", BaseURL)        xtwFeed.WriteElementString("copyright", "Copyright Claytabase 2012")        xtwFeed.WriteElementString("language", RSSLanguage)        xtwFeed.WriteElementString("description", MyDescr)
        'Objects needed for connecting to the SQL         Using com As New SqlCommand("EXEC GetRSSPages '" + RSSLanguage + "'", con)            If con.State = ConnectionState.Closed Then                con.Open()            Else            End If            Using dr = com.ExecuteReader()                'Loop through the content of the database and add them to the RSS feed                 While dr.Read()                    xtwFeed.WriteStartElement("item")                    xtwFeed.WriteElementString("title", dr.Item("PageTitle").ToString())                    xtwFeed.WriteElementString("description", dr.Item("PageDescription").ToString())                    xtwFeed.WriteElementString("link", dr.Item("PageURL").ToString())                    xtwFeed.WriteElementString("pubDate", Format(dr.Item("PageDate"), "ddd, dd MMM yyyy hh:mm:ss") + " GMT")                    xtwFeed.WriteElementString("category", dr.Item("ChangeFrequency").ToString())                    xtwFeed.WriteElementString("guid", BaseURL + "/" + dr.Item("PageGUID").ToString())                    xtwFeed.WriteEndElement()                End While            End Using        End Using        'Close all tags         xtwFeed.WriteEndElement()        xtwFeed.WriteEndElement()        xtwFeed.WriteEndDocument()        xtwFeed.Flush()        xtwFeed.Close()        Response.End()    End SubEnd Class

C#

using System.Text;using Microsoft.VisualBasic;using System.Data.SqlClient;using System.Xml;public class RSS : System.Web.UI.Page{    private SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings("SqlConnection").ConnectionString);    protected void Page_Load(object sender, System.EventArgs e)    {        string RSSLanguage = "en";        string BaseURL = "https://www.claytabase.co.uk/";        string MyTitle = "Academy Title";        string MyDescr = "Academy Description";
        // Clear any previous output from the buffer        System.Web.UI.Page.Response.ClearContent();        System.Web.UI.Page.Response.ContentType = "text/xml";        System.Web.UI.Page.Response.Charset = "Utf-8";        XmlTextWriter xtwFeed = new XmlTextWriter(System.Web.UI.Page.Response.OutputStream, Encoding.UTF8);        xtwFeed.WriteStartDocument();        // The mandatory rss tag        xtwFeed.WriteStartElement("rss");        xtwFeed.WriteAttributeString("version", "2.0");        xtwFeed.WriteAttributeString("xmlns:atom", "https://www.w3.org/2005/Atom");        // The channel tag contains RSS feed details        xtwFeed.WriteStartElement("channel");        xtwFeed.WriteRaw("<atom:link href=\"" + BaseURL + RSSLanguage + "/rss\" rel=\"self\" type=\"application/rss+xml\" />");        xtwFeed.WriteElementString("title", MyTitle);        xtwFeed.WriteElementString("link", BaseURL);        xtwFeed.WriteElementString("copyright", "Copyright Claytabase 2012");        xtwFeed.WriteElementString("language", RSSLanguage);        xtwFeed.WriteElementString("description", MyDescr);
        // Objects needed for connecting to the SQL         using (SqlCommand com = new SqlCommand("EXEC GetRSSPages '" + RSSLanguage + "'", con))        {            if (con.State == ConnectionState.Closed)                con.Open();            else            {            }            using (var dr = com.ExecuteReader())            {                // Loop through the content of the database and add them to the RSS feed                while (dr.Read())                {                    xtwFeed.WriteStartElement("item");                    xtwFeed.WriteElementString("title", dr.Item["PageTitle"].ToString());                    xtwFeed.WriteElementString("description", dr.Item["PageDescription"].ToString());                    xtwFeed.WriteElementString("link", dr.Item["PageURL"].ToString());                    xtwFeed.WriteElementString("pubDate", Strings.Format(dr.Item["PageDate"], "ddd, dd MMM yyyy hh:mm:ss") + " GMT");                    xtwFeed.WriteElementString("category", dr.Item["ChangeFrequency"].ToString());                    xtwFeed.WriteElementString("guid", BaseURL + "/" + dr.Item["PageGUID"].ToString());                    xtwFeed.WriteEndElement();                }            }        }        // Close all tags         xtwFeed.WriteEndElement();        xtwFeed.WriteEndElement();        xtwFeed.WriteEndDocument();        xtwFeed.Flush();        xtwFeed.Close();        System.Web.UI.Page.Response.End();    }}

Wrapping up

Onto the code from the page load, and here is where it has been a bit more creative.

The field RSSLanguage is used in our CMS, telling the system which language is being used for each request, and the base URL would also be populated, for this example we've made them static fields.

The next few lines of code set out the encoding and response type, open an XML writer and set out some of the required headings, as these will rarely change, I have set these manually.

We can now move onto reading the data, so first job is to create an SQL command, and in this case we simply call the stored procedure which returns the required fields from the database, dependent on language input.

We then open the SQL connection, and declare a data reader to loop through the result set from the database.

We already know that the XML tag is item for each document, so we can open this up straight away.

Then populate each required item with the data, and ensure that your date is in the correct format, we will then close the tag by using the WriteEndElement.

Once the data has all been read, the code is closing the data reader, connections and the writing the end tags for each element opened earlier.

Once you have written and published yours, be sure to check it on the W3C RSS Validator.

Website design by Claytabase

This is a section of code that has been modified from Ousia Content Management System code, one of the quickest and most optimised systems on the market, part of our website design services.

These are available with sites starting at around £500.

more: Responsive and fast. Web Development, Design and Hosting with Content Management System