Create Dynamic RSS Feed from MS SQL Server using ASP.NET, Part 1: VB.NET

This entry is part 1 of 2 in the series Create Dynamic RSS Feed from MS SQL Server using ASP.NET

Before I started, let’s get to know what RSS is.

Quoted from Wikipedia.org

RSS is a family of Web feed formats used to publish frequently updated content such as blog entries, news headlines, and podcasts in a standardized format. An RSS document (which is called a “feed,” “web feed,” or “channel”) contains either a summary of content from an associated web site or the full text. RSS makes it possible for people to keep up with web sites in an automated manner that can be piped into special programs or filtered displays.
The benefit of RSS is the aggregation of content from multiple web sources in one place. RSS content can be read using software called an “RSS reader,” “feed reader,” or an “aggregator,” which can be web-based or desktop-based. A standardized XML file format allows the information to be published once and viewed by many different programs.

Q: What benefits of doing this?
A: If you using RSS Feed, you can display it on various RSS Reader software. In my example, I create a dynamically RSS Feed and then I have installed RSS Reader on SharePoint so now I can get content on MS SQL Server to show up on SharePoint without coding anything on SharePoint. You can also apply RSS Feed to other RSS Reader software.

In this article, I’ll show how to create a RSS Feed to gather content from Microsoft SQL Server so that the RSS Feed will be dynamically. The RSS’s content will be changed every time when the data on MS SQL Server have changed. I will use Microsoft Visual Studio 2005, ASP.NET and code in VB.NET and C#. On this post, I’ll cover only VB.NET. For C#, I’ll write in the next post.

First, I’ll prepare a sample content database as a sample feed and create a store procedure that query the content database. Then, I’ll code on MS Visual Studio 2005 to gather feed from the MS SQL database and display them as RSS format. After that, I will publish the content to web site and test if the content shown as RSS Feed.

You can download the completed project on MS Visual Studio 2005 in zipped format – DynamicRSS VB.NET

For the SQL query to create sample database, tbl_Feed

Requirement

  1. Microsoft Visual Studio 2005 (For developing VB.NET and C#)
  2. Microsoft SQL Server as a database server
  3. Internet Information Services (IIS) to test the published website

Section

  1. Create sample database on MS SQL Server
  2. Create ASP.NET Web Application on MS Visual Studio 2005 (VB.NET)
  3. Publish and test the result

Step-by-step to create dynamic RSS feed

Create sample database on MS SQL Server

  1. On a database server, create a sample database. In this example, my database server is located at “BKKSQL001\INSTANCE01”. Create a database name as “TestRSS” and table name as “tbl_Feed” with columns as in the figure below.
    Create a sample database and table
  2. Fill some sample data on table “tbl_Feed”. See the figure below for example.
    Fill sample data on the table
  3. Create a new store procedure to query data from table “tbl_Feed” so that you can alter the query without modifying ASP.NET’s code at later.
    Create a new store procedure
  4. The database, table and store procedure are like in the figure below.
    The sample database

Back to top

Create ASP.NET Web Application on MS Visual Studio 2005 (VB.NET)

  1. Open MS Visual Studio 2005, create a new ASP.NET Web Application.
    Create A new ASP.NET Web Application
  2. You’ll see the page as the figure below.
    ASP.NET Web Application's Project
  3. Add some variables to the web.config so that these values can be changed without to recompile the project. Right click on Web.config and select Open, you’ll see the page as in the figure below.
    Web.config
  4. Alter
    <appsettings />

    to

    	<appsettings>
    		<add key="connStr" value="server=BKKSQL001\INSTANCE01;uid=sa;
    						pwd=password;database=TestRSS" />
    		<add key="sp_name" value="sp_GetFeed" />
    	</appsettings>

    Code Explanation: I have add two variables: “connStr” and “sp_name” which these values will be loaded into the code in next few steps. The first is the connection string to the database server provides with username, password and database name of the SQL Server. The latter is the store procedure name.
    Note: I encourage you to implement Windows Authentication for connecting to SQL Server rather than SQL Authentication. But in this tutorial, I’ve just used SQL Authentication to keep the tutorial simple and better to understand. If you want more information about Windows Authentication, see How To: Connect to SQL Server Using Windows Authentication in ASP.NET 2.0.
    Define variables in web.config

  5. I’ll change view to code so right click on the “Default.aspx” and select “View Code”.
    View Code
  6. Import the required libraries on the top.
    1
    2
    3
    
    Imports System.Data.SqlClient
    Imports System.Xml
    Imports System.Text

    Code Explanation: The first library is used to make connection with the MS SQL Server. The second is for using xml format since RSS is a xml format. The last one is optional.
    Import the required libraries

  7. Let’s start coding the first of the RSS. Type the following code in to the Page_Load().
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    
            ' Load value from web.config
            Dim sqlCon As String = System.Configuration.ConfigurationManager.AppSettings("connStr")
            Dim spName As String = System.Configuration.ConfigurationManager.AppSettings("sp_name")
     
            ' Clear any previous output from the buffer
            Response.Clear()
            Response.ContentType = "text/xml"
     
            ' XML Declaration Tag
            Dim xml As XmlTextWriter = New XmlTextWriter(Response.OutputStream, Encoding.UTF8)
            xml.WriteStartDocument()
     
            ' RSS Tag
            xml.WriteStartElement("rss")
            xml.WriteAttributeString("version", "2.0")
     
            ' The Channel Tag - RSS Feed Details
            xml.WriteStartElement("channel")
            xml.WriteElementString("title", "Test RSS Feed")
            xml.WriteElementString("link", "http://testLink")
            xml.WriteElementString("description", "This is example Feed from SQL Server")
            xml.WriteElementString("copyright", "Copyright 2008. All rights reserved.")

    Code Explanation:

    1. Line 9-11: Load the values from web.config to variables. The name “connStr” and “sp_name” must match in the web.config -> appSettings -> attribute “key”.
    2. Line 13-15: Clear previous output if any and define the content type as “text/xml”.
    3. Line 17-19: Create new XmlTextWriter object for writing xml tag to the output screen(Response.OutputStream). The xml.WriteStartDocument() write the xml declaration with version 1.0 tag like this:
      < ?xml version="1.0" encoding="utf-8" ?>
    4. Line 21-23: Write this tag
      <rss version="2.0"></rss>
    5. Line 25-30: Write channel tag which contains the details of this feed.
      <channel>
        <title>Test RSS Feed</title> 
        <link>http://testLink</link> 
        <description>This is example Feed from SQL Server</description> 
      </channel>

    First part of the Code

  8. Next, let’s coding on the middle part. Connecting to the database server.
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    
    Try
                Dim reader As SqlDataReader
                Dim cn As New SqlConnection(sqlCon)
                Dim cmd As New SqlCommand(spName, cn)
                cmd.CommandType = CommandType.StoredProcedure
     
                cn.Open()
                reader = cmd.ExecuteReader()
                While reader.Read()
                    xml.WriteStartElement("item")
                    xml.WriteElementString("title", reader(1).ToString())
                    xml.WriteElementString("description", reader(2).ToString())
                    xml.WriteElementString("link", reader(3).ToString())
                    xml.WriteElementString("pubDate", reader(4).ToString())
                    xml.WriteEndElement()
                End While
     
                reader.Close()
                cn.Close()
            Catch ex As Exception
                Response.Write(ex.Message)
    End Try

    Code Explanation: All the code in this part is wrapped into “Try…Catch” to avoid thrown exception.

    1. Line 33-36: Declare variables for make connection to SQL Server.
    2. Line 38-39: Open connection to SQL Server and execute query by calling a store procedure “sp_GetFeed” (variable sp_name).
    3. Line 40-47: Enter while loop on each return record and write each record to output screen as a item.
    4. Line 49-50: Close the SQL Server’s conntection.

    Second part of the Code

  9. The last part is about closing the opened tags.
    55
    56
    57
    58
    59
    60
    
            xml.WriteEndElement()
            xml.WriteEndElement()
            xml.WriteEndDocument()
            xml.Flush()
            xml.Close()
            Response.End()

    Code Explanation: These are closing and ending tags

    1. Line 55-56: Close channel and rss tags.
    2. Line 57-59: Close the connection. End the stream of data.
    3. Line 60: Send all result (in buffer) to display on the screen.

    Third part of the Code

Back to top

Publish and test the result

  1. Publish the web application to the website. Right click on the DynamicRSS_VB.NET – the Project Name and select Publish.
    Publish the web application
  2. On Publish Web, click on […] to browse to the available web server. If you already know where is your web server, you can type it in the target location.
    Browse to available web servers
  3. I have IIS Server on this local machine so I select Local Web Servers -> Default Web Site and click Open.
    Select a Local Web Server
  4. Click Publish.
    Select Publish
  5. See on the output view if the publish is success.
    Publishing status
  6. Open Internet explorer and browse to http://localhost/Default.aspx. You’ll see your dynamic RSS Feed!.
    Dynamic RSS Feed - See the dynamic RSS result on the Internet Explorer

Back to top

Series NavigationCreate Dynamic RSS Feed from MS SQL Server using ASP.NET, Part 2: C# >>

18 Comments

  1. saquib September 22, 2008
  2. linglom September 23, 2008
  3. Robert Woltz February 11, 2009
  4. linglom February 12, 2009
  5. Robert Woltz February 12, 2009
  6. linglom February 14, 2009
  7. Rajesh Singh March 26, 2009
  8. wiriya March 27, 2009
  9. linglom March 28, 2009
  10. von April 24, 2009
  11. linglom April 26, 2009
  12. von April 30, 2009
  13. Brandon March 25, 2010
  14. Wesley April 21, 2010
  15. Matthew August 3, 2010
  16. Siham October 28, 2010
  17. rashid October 28, 2010
  18. Paria December 14, 2013

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.