- 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 2: C#
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
- Microsoft Visual Studio 2005 (For developing VB.NET and C#)
- Microsoft SQL Server as a database server
- Internet Information Services (IIS) to test the published website
- Create sample database on MS SQL Server
- Create ASP.NET Web Application on MS Visual Studio 2005 (VB.NET)
- Publish and test the result
Step-by-step to create dynamic RSS feed
Create sample database on MS SQL Server
- 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.
- Fill some sample data on table “tbl_Feed”. See the figure below for example.
- 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.
- The database, table and store procedure are like in the figure below.
Create ASP.NET Web Application on MS Visual Studio 2005 (VB.NET)
- Open MS Visual Studio 2005, create a new ASP.NET Web Application.
- You’ll see the page as the figure below.
- 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.
<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.
- I’ll change view to code so right click on the “Default.aspx” and select “View Code”.
- Import the required libraries on the top.
1 2 3
Imports System.Data.SqlClient Imports System.Xml Imports System.Text
- 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.")
- 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”.
- Line 13-15: Clear previous output if any and define the content type as “text/xml”.
- 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" ?>
- Line 21-23: Write this tag
- 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>
- 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.
- Line 33-36: Declare variables for make connection to SQL Server.
- Line 38-39: Open connection to SQL Server and execute query by calling a store procedure “sp_GetFeed” (variable sp_name).
- Line 40-47: Enter while loop on each return record and write each record to output screen as a item.
- Line 49-50: Close the SQL Server’s conntection.
- 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
- Line 55-56: Close channel and rss tags.
- Line 57-59: Close the connection. End the stream of data.
- Line 60: Send all result (in buffer) to display on the screen.
Publish and test the result
- Publish the web application to the website. Right click on the DynamicRSS_VB.NET – the Project Name and select Publish.
- 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.
- I have IIS Server on this local machine so I select Local Web Servers -> Default Web Site and click Open.
- Click Publish.
- See on the output view if the publish is success.
- Open Internet explorer and browse to http://localhost/Default.aspx. You’ll see your dynamic RSS Feed!.