Have the Result Set of a Stored Proc Sent to the RSS feed.

Have the Result Set of a Stored Proc Sent to the RSS feed.

by Jbrooks 14. December 12:44

I wanted to monitor one of the my system from my desk top and from my phone. I found a simple solution whereby I can subscribe to the result set of a stored proc by using RSS. So I can having this feed MS Outlook, my phone or my web browser.

First, Visual Studio makes creating an RSS feeds a simple matter that's about 1 page of code.

I simply add an ASPX page to my project and remove most of the markup so it is only have 2 lines:

<%@ page language="C #" autoeventwireup="true" codebehind="Rss.aspx.cs"  inherits= "Rss.rss"  %>   

<%@ OutputCache duration= "All"varybyparam="None"%> 

Next the code behind simply calls the stored proc placing the results into a table and then loading up some of the RSS rel Ated collections VS2010 gives you.

Using System;

Using System.Data;

Using System.ServiceModel.Syndication;

Using System.Web;

Using System.Collections.Generic;

Using System.Xml;

Namespace RSS

{

Class Rss:System.Web.UI.Page

{

    void Page_Load (object sender, EventArgs e)

    {

        String id = request.querystring["id"];

        I don ' t want just anyone to subscribe and so do have a to know the GUID.

        "23f14ea1-1b20-443b-9b94-92c4ea4a8099")

            New Exception ("Guid not reconized");

        "Application/atom+xml";

        This gets the data from the database and populates a table.

        DataTable dt = Cdb.getfeed ();

        New SyndicationFeed ();

        Myfeed.title = Textsyndicationcontent.createplaintextcontent ("SampleApp Activity");

        Myfeed.description = Textsyndicationcontent

            . Createplaintextcontent (

                    SampleApp activity including exceptions. ");

        MYFEED.LINKS.ADD (Syndicationlink.createalternatelink (

            New Uri (Getfullyqualifiedurl ("/rss.aspx" )));

        MYFEED.LINKS.ADD (Syndicationlink.createselflink (

            New Uri (Getfullyqualifiedurl (Request.rawurl)));

        Myfeed.copyright = Textsyndicationcontent

                        . Createplaintextcontent ("Copyright SampleApp");

        "En-US";

        New List<syndicationitem> ();

        In dt. Rows)

        {

            New SyndicationItem ();

            Item. title = Textsyndicationcontent.createplaintextcontent (dr["title"). ToString ());

            New Syndicationperson ();

            "[Email protected]";

            Item. Authors.add (AuthInfo);

            RSS feeds can only has one author.

            The stored proc returns different categories of data that I am interested in.

            Switch (dr["category"). ToString ())

            {

                "Windfarms":

                "Windfarms":

                    Item. Links.add (Syndicationlink.createalternatelink (

                        New Uri (Getfullyqualifiedurl ("/windfarms.aspx" )));

                    "SampleApp windfarm";

                    Break

                "Exceptions":

                    Item. Links.add (Syndicationlink.createalternatelink (

                        New Uri (Getfullyqualifiedurl ("/errorlog.aspx" )));

                    "SampleApp Exception";

                    Break

                Default

                    "SampleApp";

                    Break

            }

            Item. Summary = Textsyndicationcontent.createplaintextcontent (

                dr["Summary"]. ToString ());

            Item. Categories.add (new Syndicationcategory (dr["category"). ToString ())); 

            Item. Publishdate = DateTime.Parse (dr["pubdate"]. ToString ());

            Item. Lastupdatedtime = Item. Publishdate;

            Item. Id = Item. Publishdate.tostring ();

            ADD the item to the feed

            Feeditems.add (item);

        }

        Myfeed.items = Feeditems;

        XmlWriter feedwriter = xmlwriter.create (Response.outputstream);

        New Atom10FeedFormatter (Myfeed);

        Atomformatter.writeto (Feedwriter);

        Feedwriter.close ();

    }

    String Getfullyqualifiedurl (string s)

    {

        New Uri (HTTPCONTEXT.CURRENT.REQUEST.URL, s);

        return u.tostring ();

    }

}

}

To has this feed my Outlook rss folder I just need to right click "RSS Feeds" and select "Add a New RSS feed ...".

Then enter the URL of my RSS feed. Don ' t forget to add the GUID at the end with? id=23f14ea1-1b20-443b-9b94-92c4ea4a8099

IF You site uses authentication in your site you'll have a to turn it off for the rss.aspx page. To does this you would add a entry in your Web Config file:

<location path="rss.aspx" >

    <system.web>

        <authorization>

            <allow users="*"/>

        </authorization>

    </system.web>

</location>

You should now has a folder in Outlook, that would get populated by the feed.

Have the Result Set of a Stored Proc Sent to the RSS feed.