- 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#
This post, I’ll cover only in the second section “Create ASP.NET Web Application on MS Visual Studio 2005 (C#)”. I’m not going to repeat other sections again since they’re same as in the previous post. The other sections can be found in “Create Dynamic RSS Feed from MS SQL Server using ASP.NET, Part 1: VB.NET”.
You can download the completed project for MS Visual Studio 2005 in zipped format – DynamicRSS C#The complete sections are listed below.
Section
- Create sample database on MS SQL Server
- Create ASP.NET Web Application on MS Visual Studio 2005 (C#)
- Publish and test the result
Create ASP.NET Web Application on MS Visual Studio 2005 (C#)
- 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.
- 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.
- 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
using System.Data.SqlClient; using System.Xml; using 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.
- Let’s start coding the first of the RSS. Type the following code in to the Page_Load().
22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
// Load value from web.config String sqlCon = System.Configuration.ConfigurationManager.AppSettings.Get("connStr"); String spName = System.Configuration.ConfigurationManager.AppSettings.Get("sp_name"); // Clear any previous output from the buffer Response.Clear(); Response.ContentType = "text/xml"; // XML Declaration Tag XmlTextWriter xml = 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:
- Line 22-24: Load the values from web.config to variables. The name “connStr” and “sp_name” must match in the web.config -> appSettings -> attribute “key”.
- Line 26-28: Clear previous output if any and define the content type as “text/xml”.
- Line 30-32: 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 34-36: Write this tag
<rss version="2.0"></rss> - Line 38-43: 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.
45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67
try { SqlDataReader reader; SqlConnection cn = new SqlConnection(sqlCon); SqlCommand cmd = 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(); } reader.Close(); cn.Close(); } catch (Exception ex) { Response.Write(ex.Message); }
Code Explanation: All the code in this part is wrapped into “Try…Catch” to avoid thrown exception.
- Line 46-49: Declare variables for make connection to SQL Server.
- Line 51-52: Open connection to SQL Server and execute query by calling a store procedure “sp_GetFeed” (variable sp_name).
- Line 53-60: Enter while loop on each return record and write each record to output screen as a item.
- Line 62-63: Close the SQL Server’s conntection.
- The last part is about closing the opened tags.
69 70 71 72 73 74
xml.WriteEndElement(); xml.WriteEndElement(); xml.WriteEndDocument(); xml.Flush(); xml.Close(); Response.End();
Code Explanation: These are closing and ending tags
- Line 69-70: Close channel and rss tags.
- Line 71-73: Close the connection. End the stream of data.
- Line 74: Send all result (in buffer) to display on the screen.
Hello Sir,
Thanks for sharing.
In line 51-52, you mentioned variable “sp_name” for calling the procedure.I couldn’t find it.
Help me please!
Oops I’m Sorry.
I got it now
Dear Sir,
I’ve tried you example down to the last letter. Yet, I get this result in IE7…
Login failed for user ‘sa’. The user is not associated with a trusted SQL Server connection.Test RSS Feedhttp://testLinkThis is example Feed from SQL ServerCopyright 2008. All rights reserved.
Of couse this is not my real login name or password. Yet, I got the same when I did use them. Thank you.
Dante’
Hi, Dante
You need to change the Authenticaiton mode on the SQL Server from Windows Authentication mode to SQL Server and Windows Authentication mode. Then, restart the SQL Server service. If you don’t know how to do, see Enable remote connection to SQL Server 2005 Express
After that, try to refresh the page again.
linglom,
It works. But I am having trouble getting the published dates to show. Thank you.
Dante’
Hi, Dante
Does it show up in xml format? I mean in the IE. You may have to check the query result from the SQL Server if it contains all elements that you want.
It shows up. I just cannot see it on my laptop. But,everyone that I’ve shared the link with see them just fine. I have another question. How would we have to modify, in VB and C#, to get similiar results to Apple’s RSS web site? Thank you. http://www.apple.com/rss
Hi, Dante
Is there any error message about why you can’t see on the laptop?
And you can see xml of a page by view source code. On IE, select View -> Source on the rss page and you’ll see the xml code which is sample for you to create one.
i’m using firefox 3, is there any way to view it in xml format? please help…
Hi, Von
Most of browsers can view xml format. The example is as the last screen shot of part I.
i couldnt display at all. what was displayed on my browser was just the description. i dont have the other columns display on firefox. when i click on view page code for firefox,i could see all the information there.
i guess it has to do with my browser settings. may i know what is the settings?
please help. thanks!
Hi, Von
What if you display it on Internet Explorer? Does it show correctly?
Try to view a sample feed – http://www.feedforall.com/sample.xml.
hmm…is it possible if i send you the screen shot of my web browser? so you have an idea of how it looks like?
i have been trying but not really able to display everything in format…please advise.
Hi, Von
You can send me a screenshot in the forum. Or you may post a comment with source code as text here.
I followed your article, but made some changes: with this code, you can publish more than 1 “item”
protected void Page_Load(object sender, EventArgs e)
{
Response.Clear();
Response.ContentType = “text/xml”;
XmlTextWriter xml = new XmlTextWriter(Response.OutputStream, Encoding.UTF8);
xml.WriteStartDocument();
xml.WriteStartElement(“rss”);
xml.WriteAttributeString(“version”, “2.0”);
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.”);
try
{
SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[“Users_Form_Data”].ToString());
SqlCommand myCommand = myConnection.CreateCommand();
myCommand.CommandText = “SELECT * FROM feeds ORDER BY date”;
SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);
DataSet mySet = new DataSet();
myAdapter.Fill(mySet);
foreach (DataRow myRow in mySet.Tables[“feeds”].Rows)
{
xml.WriteStartElement(“item”);
xml.WriteElementString(“title”, myRow[“title”].ToString());
xml.WriteElementString(“description”, myRow[“desc”].ToString());
xml.WriteElementString(“link”, myRow[“link”].ToString());
xml.WriteElementString(“pubDate”, myRow[“date”].ToString());
xml.WriteEndElement();
}
}
catch { Response.Write(“We are currenly experiencing Technical Difficulties”); }
xml.WriteEndElement();
xml.WriteEndElement();
xml.WriteEndDocument();
xml.Flush();
xml.Close();
Response.End();
}
wondering if anyone can help here. i have followed everything on the site and all works fine. what i am trying to do is to add another value in to the reader so when you click on the link it brings you to the website with a value on it need the extra code to add on to the end
xml.WriteElementString(“link”, “http://localhost/CS09/getimage.asp?id=”)????????
is this possible ?
Hi, Max Allen
Yes, if you want to add a parameter to url, you can add on the end of the text value.
xml.WriteElementString(“link”, “http://localhost/index.php?id=10”);
Hi linglom, sorry you must have miss understood. i need the value to come from the database as well so, if i had the link and the id in the database is there a way that i could pull them both together so i get the web address http://localhost/CS09/getimage.asp?id= and then the ID, 10
which will make http://localhost/CS09/getimage.asp?id=10
Yes, you can do that. First, you modify your store procedure to get an additional column (the ID column) that you want. Then, modify the code on step 8 from
xml.WriteElementString(“link”, reader[3].ToString());
to
xml.WriteElementString(“link”, reader[3].ToString() + “?id=” + reader[5].ToString());
(Assume that the column ID’s index is 5)
Thank you for this!!
My only question is how can I alter it so that it writes to a file like myrss.xml?
Hi, Amy
If you want the output to a xml file instead of on screen, you simply modify XmlTextWriter object on line 31.
From
To
where xmlFilePath is the file destination.
Dear Linglom team thanks for the above info you have no idea how much the info you provided has helped me with the project we’re currently working on.
keep up the good work guyz
from yours loving
DUT student
I tried it on with Visual Studio 2010 and MS SQL Server 2008 R2 express. I received error on line 49 “cmd.CommandType = CommandType.StoredProcedure;”
The error says ” the name ‘CommandType’ does not exit in the current context.
7 warnings in Web.config says, “Could not find schema information for the elecment ‘appsettings’.
Any suggetions?
use System.Data.CommandType.StoredProcedure; instead