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

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

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

  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

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

25 Comments

  1. kavin September 24, 2008
  2. kavin September 24, 2008
  3. Dante February 16, 2009
  4. linglom February 17, 2009
  5. Dante February 26, 2009
  6. linglom March 4, 2009
  7. Dante March 5, 2009
  8. linglom March 6, 2009
  9. von April 24, 2009
  10. linglom April 26, 2009
  11. von April 30, 2009
  12. linglom April 30, 2009
  13. von May 1, 2009
  14. von May 5, 2009
  15. linglom May 12, 2009
  16. Geoff August 6, 2009
  17. Max Allen August 26, 2009
  18. linglom August 31, 2009
  19. Max Allen August 31, 2009
  20. linglom August 31, 2009
  21. Amy December 25, 2009
  22. linglom December 30, 2009
  23. Dumisani Wiseman October 8, 2010
  24. Winston June 18, 2011
  25. Gius February 24, 2013

Leave a Reply