Update an RSS Feed from a SQL Database with vb.net

Good Morning! Just a horrible night in regards to trying to sleep and stay there. So I decided to get up and do my blog since I have not had time recently to do any of that.

Today’s topic is how to update your RSS feed from your SQL database using vb.net. Pretty straightforward stuff. I had on on occasion to write this for a friend who needed a way to automatically update his feed. Drove me nuts for a while! Make it a great day!

Private Sub RssUpdate()
        ‘ Use an XmlTextWriter to write the XML data to a string…
        Dim writer As New XmlTextWriter(Replace(Server.MapPath("rss\News.xml"), "admin\", ""), Encoding.UTF8)
        ‘ start writing!
        writer.WriteStartDocument()
        ‘ write out <rss version="2.0">
        writer.WriteStartElement("rss")
        writer.WriteAttributeString("version", "2.0")
        ‘ write out <channel>
        writer.WriteStartElement("channel")
        ‘ write out <channel>-level elements
        writer.WriteElementString("title", ConfigurationManager.AppSettings("NewsRSS:PreTitle"))
        writer.WriteElementString("link", ConfigurationManager.AppSettings("NewsRSS:Link"))
        writer.WriteElementString("description", ConfigurationManager.AppSettings("NewsRSS:PreTitle"))
        writer.WriteElementString("pubDate", Format(Now(), "dddd, dd MMM yyyy HH:mm:ss"))
        writer.WriteElementString("ttl", "60") ‘Default time (minutes) that RSS Reader must check the feed
        ‘ write out an <item> element for each of the first X articles
        ‘ write out <item>
        writer.WriteStartElement("item")
        ‘Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("connSQL").ConnectionString)
        conn0.Open()
        Dim DataC As New SqlCommand("SELECT prID, prTitle, prDate, prPublication, prNews FROM News WHERE prSort < 11 ORDER BY prSort", conn0)
        Dim DataR As SqlDataReader = DataC.ExecuteReader()
        While DataR.Read()
            ‘ write out <item>
            writer.WriteStartElement("item")
            ‘ write out <item>-level information
            writer.WriteElementString("title", DataR.Item("prTitle"))
            writer.WriteElementString("link", ConfigurationManager.AppSettings("NewsRSS:Link") & "News" & DataR.Item("prID") & ".htm")
            writer.WriteElementString("description", "<p><STRONG>" & DataR.Item("prTitle") & "</STRONG><br>" & Format(DataR.Item("prDate"), "dd-MMM-yyyy") & "&nbsp;&nbsp;&nbsp;" & DataR.Item("prPublication") & "</p>" & Replace(DataR.Item("prNews"), "/UserFiles", "http://" & Request.ServerVariables("HTTP_HOST") & "/UserFiles"))
            writer.WriteElementString("author", ConfigurationManager.AppSettings("NewsRSS:Author"))
            ‘ use DateTimeFormatInfo "r" to use RFC 1123 date formatting (same as RFC 822)
            writer.WriteElementString("pubDate", Format(CType(DataR.Item("prDate"), DateTime), "dddd, dd MMM yyyy"))
            ‘ write out </item>
            writer.WriteEndElement()
        End While
        conn0.Close() ‘: DataC.Dispose() : conn.Dispose()
        ‘ write out </item>
        writer.WriteEndElement()
        ‘ write out </channel>
        writer.WriteEndElement()
        ‘ write out </rss>
        writer.WriteEndElement()
        writer.WriteEndDocument()
        writer.Close()

End Sub


Join me on Facebook

Technorati Tags: ,,,,,,,,,
,,,,,,,,
,,,,,,,,
,,,,,,,,
,,,,,,,,
,

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: