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 I: 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

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

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

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

    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.
    Import the required libraries

  7. 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:

    1. 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”.
    2. Line 26-28: Clear previous output if any and define the content type as “text/xml”.
    3. 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" ?>
    4. Line 34-36: Write this tag 
      <rss version="2.0"></rss>
    5. 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>

    First part of the Code

  8. 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.

    1. Line 46-49: Declare variables for make connection to SQL Server.
    2. Line 51-52: Open connection to SQL Server and execute query by calling a store procedure “sp_GetFeed” (variable sp_name).
    3. Line 53-60: Enter while loop on each return record and write each record to output screen as a item.
    4. Line 62-63: Close the SQL Server’s conntection.

    Second part of the Code

  9. 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

    1. Line 69-70: Close channel and rss tags.
    2. Line 71-73: Close the connection. End the stream of data.
    3. Line 74: Send all result (in buffer) to display on the screen.

    Third part of the Code

Share and Enjoy:
  • Digg
  • del.icio.us
  • Reddit
  • Slashdot
  • Technorati
  • Google Bookmarks
  • Live
  • MSN Reporter
  • RSS
  • Twitter
  • email
  • Facebook
  • Netvibes
  • PDF
  • Yahoo! Buzz

Related post