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

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

August 26, 2008 Programming 18 Comments
This entry is part 1 of 2 in the series Create Dynamic RSS Feed from MS SQL Server using ASP.NET

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# >>

Related Posts

18 Comments

  1. saquib September 22, 2008

    Hi, you do a best job to create rss feed for sql database and i like tell you that it’s works great, but i have category id field in my table so by this i show product record in a formview in my product page, so is it possible to add hyperlink inplace of link field.

    i’m waiting for your reply

    thank you

  2. linglom September 23, 2008

    When you use feed reader software to read the dynamic feed in the example above, it’ll be hyperlink automatically on element.

  3. Robert Woltz February 11, 2009

    This does not work properly. I have tried everything but it only display the first three lines and not the rest of the elements. Can you provide some assistance please?

    Here is the code:

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

    Partial Class Arizona
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    Dim sqlCon As String = System.Configuration.ConfigurationManager.AppSettings(“MSIdbConnectionString”)
    Dim spName As String = System.Configuration.ConfigurationManager.AppSettings(“sp_name”)

    Response.Clear()
    Response.ContentType = “text/xml”

    Dim xml As XmlTextWriter = New XmlTextWriter(Response.OutputStream, Encoding.UTF8)
    xml.WriteStartDocument()

    xml.WriteStartElement(“rss”)
    xml.WriteAttributeString(“version”, “2.0”)

    xml.WriteStartElement(“channel”)
    xml.WriteElementString(“title”, “Arizona”)
    xml.WriteElementString(“description”, “WOW what a text”)
    xml.WriteElementString(“link”, “http://www.nursesinshape.com”)
    xml.WriteElementString(“copyright”, “2009 Medical Solutions International- All Rights Reserved”)
    xml.WriteElementString(“managingEditor”, “Recruiter”)

    Try
    Dim reader As SqlDataReader
    Dim cn As New SqlConnection(sqlCon)

    ‘ Location is set to Arizona for a default. In production it is set to RequestQueryString

    Dim Location As String
    Location = “Arizona”

    Dim cmd As New SqlCommand(spName, cn)
    cmd.Parameters.AddWithValue(“@Location”, Location)
    cmd.CommandType = Data.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(“copyright”, reader(4).ToString())
    xml.WriteElementString(“managingEditor”, reader(5).ToString())
    xml.WriteEndElement()

    End While

    reader.Close()
    cn.Close()

    Catch ex As Exception
    Response.Write(ex.Message)

    End Try

    xml.WriteEndElement()
    xml.WriteEndElement()
    xml.WriteEndDocument()
    xml.Flush()
    xml.Close()
    Response.End()

    End Sub
    End Class

    Sincerely,

    RTW

  4. linglom February 12, 2009

    Hi, Robert Woltz
    Is there any error message or exception?

    What’s the output (first three lines) that you’ve mentioned? Does it has output from SQL Server?

  5. Robert Woltz February 12, 2009

    Hello linglom

    What I get is the Description, Link and Title that’s all the rest doesn’t show. When I check the aspx page view I see all the data at the top. It just does not display correctly.

    If you go to the http://rss.msinursestaffing.com/All.aspx page you can see it working like this or you can view by state (Location) at http://rss.msinursestaffing.com/Location.aspx?X=Arizona

    I receive no errors.

    Everything is drawing from the slq data base.

    Thank you for your quick response before and I will look forward to your insight on this project.

    Sincerely,

    RTW

  6. linglom February 14, 2009

    Hi, Robert Woltz
    It seems that there isn’t anything wrong! The links that you posted, they have “copyright” and “managingEditor” elements already. You can check by view source code in the browser on the page. But the data aren’t show in the feed because the feed reads only first three elements which are common elements.

    If you really want to show the missing data, I think you may have to modify the feed reader – the aspx page in this case.

  7. Rajesh Singh March 26, 2009

    Hi,

    I have created RSS feed using asp.net but i am little bit confused regarding how user can access. please provide me the procedure so i can easily work on it.

    Thanks

  8. wiriya March 27, 2009

    I found this problem when Open Internet explorer and browse to http://localhost/Default.aspx.

    It’s shown

    The XML page cannot be displayed
    Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.

    ——————————————————————————–

    Invalid at the top level of the document. Error processing resource ‘http://127.0.0.1/Default.aspx’. Line 1, Position 1

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not foun…

    can you help me to solve this problem

    ขอบคุณล่วงหน้าคับ

  9. linglom March 28, 2009

    Hi, Rajesh Singh
    When you finish create a feed, you deploy it to the web server. Then, users can access the feed from the web server.

    Hi, Wiriya
    I think the problem is about connecting to the SQL Server. Verify that you can query the data from SQL Server successfully.

  10. von April 24, 2009

    hi linglom,
    you mention that “The RSS’s content will be changed every time when the data on MS SQL Server have changed.”, how is that achieved?

    i am new to this..please enlight me!

  11. linglom April 26, 2009

    Hi, Von

    The content is dynamically generated each time you refresh the page from the SQL Server. When you modify the data on SQL Server and then you reload the RSS’s page, the page will gather the latest updated data to show on the page.

  12. von April 30, 2009

    so it will be something like “i set my page to refresh automatically(perhaps every 5 mins) and the stored procedure will fetch new data from the database, and add into my application?

    do i actually need a RSS reader to decipher the data?

  13. Brandon March 25, 2010

    THANKS!!! Very helpful.

  14. Wesley April 21, 2010

    Dear Linglom,

    I am not sure my server can run asp.net.
    Could you send me how to Create Dynamic RSS Feed from MS SQL Server using ASP please?

    Thank in advance,
    Wesley.

  15. Matthew August 3, 2010

    Very impressive! One of the most helpful & clearly written articles I have ever seen on the internet…thanks!

  16. Siham October 28, 2010

    Idid all stps pt the error was
    cmd.CommandType = CommandType.StoredProcedure
    CommandType not declared

  17. rashid October 28, 2010

    hi
    i found these errove ”
    The XML page cannot be displayed
    Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.

    ——————————————————————————–

    Invalid at the top level of the document. Error processing resource ‘http://localhost:18646/news-2/RSStest.aspx’. Line 1, …

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not foun…

  18. Paria December 14, 2013

    thanks, it’s very helpful for me 🙂

Leave a Reply