jhermiz - RSSing Your Database Data (Finally a use for XML)

All the hype behind XML, do you ever wonder what good it really is? For years people have XMLized everything from blogs to language control in applications. XML XML XML...that 3 letter acronym that looks so cool but deep down you don't know too much about it. All those consultants throwing around buzz words like "O simple, you want that sharepoint list data, we'll XMLize it and port it into your RSS viewer"..um ok if you say so. Software packages start looking XMLized:

Okay so maybe not that bad...but come on seriously we've gotten to the point where people are using XML as a database back end. I'm serious...although I can't find the post at the moment :(..someone was creating seperate XML files and simulating them as tables. Let me state one thing real quick XML is NOT SQL so please stop treating it as if it were ... XML is NOT SQL Server so please stop treating it as it were ... XML is NOT Oracle so please stop treating it as it were ... I'll stop ranting now :).

Anyhow I like you have heard enough about XML and I've finally made a good use for it...ok maybe this is the second time I made a good use for it (the first had to deal with dynamically changing form labels based on a selected language (that was painful)). I personally don't use XML as a database back end like the few that are trying...but I developed something that will allow me to XML my data.

The issue was people within the company I work for wanted a central place on our Sharepoint portal server to access outside data (data from our database). Now I thought, simple I could create a web part and pull that data and display it within Sharepoint. Well that idea was gone when I was told that some managers use RSS readers to view company information. Ok...an RSS reader...great another acronym that lends itself to XML data.

Low and behold I found out that sharepoint 2k7 has an RSS reader web part and anything that worked with that RSS reader would work with any outside RSS reader. So I needed a way to RSS my database data.

Background Information

Before I give you the goodies (and boy I can just see you drooling for them) I'd like to give you background information on what kind of application I XMLized or RSSed..or whatever. The application is a helpdesk / asset tracking system in which members log issues into the system and those issues get assigned to facility members. A facility member is someone in a specific group such as IT. So you have an end user Jon Smith who is having an issue with his monitor. He simply opens the application logs a ticket about his monitor and sends it off to the facility. The facility then obtains this issue and assigns it to the specialist within that facility group, in this case a monitor problem is simply a pc technician issue. So we assign that ticket to the pc technician Joey Bologna.

So the application is basically an issue tracking system to help manage IT (or any facility for that matter) issues within our organization.

What I Wanted To RSS

The important aspect for our facility member Joey Bologna is that he has an issue from Jon Smith regarding fixing of a monitor. So this information to Joey Bologna is important and he would love to be able to see that data in an RSS reader without having to log into the application. So what I wanted to RSS is the actual issues / tickets that come into the system and to kick it up a notch I wanted to make certain the only tickets that Joey Bologna would see were any that were assigned to him. Joey Bologna does not care about tickets assigned to Anita Takeabath or Seymore Butts, he is only interested in his own tickets. So the thought process is to have individual XML files that pertained to facility members. Each member would have his or her own XML file. Within that XML file would be the actual data (assigned tickets) for the member.

Enter IO.StreamWriter

In order to generate XML file we would have to make use of the StreamWriter class. The StreamWriter class is used to write data to a file (could be text, could be html, could be XML, etc. etc.). In my case I needed to generate and write to an XML file.

The Code

Now lets talk about the actual code behind all of this. We will need the SQLClient Namespace, as well as the IO namespace. The SQLClient is needed to actually connect to our SQL database and use a reader to populate our XML file. The IO namespace is needed for the StreamWriter object to write the data to an xml file.

The first thing I do is pull the data:

Private Sub LXML()
        Dim drCF As SqlDataReader
        Dim drData As SqlDataReader
        Dim strXMLFileName As String

        drCF = GetLogins()    'get the client facilities

        If drCF.HasRows Then
            While drCF.Read()
                drData = GetLData(CType(drCF("LoginID"), Long))
                If drData.HasRows() Then
                    strXMLFileName = "L" & CType(drCF("LoginID"), Long) & ".xml"
                    CreateXMLFile(strXMLFileName, drData, "QuikFix Assigned Tickets")
                End If
                drData.Close()
            End While
        End If

        drData.Close()
        drCF.Close()

        drData = Nothing
        drCF = Nothing

End Sub

We need the GetLogins function to retrieve the data from a stored procedure and return it as an SQLDataReader:

Private Function GetLogins() As SqlDataReader
        Dim dr As SqlDataReader
        Dim cn As SqlConnection
        Dim c As SqlCommand

        Try
            cn = New SqlConnection(strConn)
            c = New SqlCommand("select_distinct_login_from_login_facility", cn)

            With c
                .CommandType = CommandType.StoredProcedure
                cn.Open()
                dr = .ExecuteReader()
            End With

Return dr

        Catch ex As Exception
            Throw New Exception(ex.ToString())
        Finally
            c = Nothing
        End Try
End Function

Once we have the data we create the XML file by calling CreateXMLFile and passing it the name of the file, the reader, and a description:

Private Sub CreateXMLFile(ByVal strFN As String, ByVal d As SqlDataReader, ByVal strTitle As String)
Dim SWriter As New IO.StreamWriter("\\jakah-iis-2\feeds\" & strFN)
FillHeader(SWriter, strTitle)

        While d.Read()
            SWriter.WriteLine("")
            SWriter.WriteLine("")
            SWriter.WriteLine("http://quikfix.jakah.com/TicketActions.aspx?TicketID=" & CType(d("TicketID"), String) & "")
            SWriter.WriteLine("" & Replace(CType(d("Ticket"), String), "&", " ") & "")
            SWriter.WriteLine("")
        End While

CloseHeader(SWriter)
SWriter.Close()
End Sub

XML files follow a pretty standard form when it comes to header and footer information, therefore I created 2 functions to create the header and the footer of the XML file:

Private Sub FillHeader(ByRef SWriter As IO.StreamWriter, ByVal strT As String)
        SWriter.WriteLine("")
        SWriter.WriteLine("")
        SWriter.WriteLine("")
        SWriter.WriteLine("")
        SWriter.WriteLine("")
        SWriter.WriteLine("http://quikfix.jakah.com")
        SWriter.WriteLine("Issue / Asset Tracking System")
        SWriter.WriteLine("en-us")
        SWriter.WriteLine("" & Now().ToString & "")
End Sub

Private Sub CloseHeader(ByRef SWriter As IO.StreamWriter)
SWriter.WriteLine("")
SWriter.WriteLine("")
End Sub

Big Deal, I Can't Use It Cause I Don't Have That DB

You're absolutely right, the code I posted will not work for you because not only do you not have the database we are talking about but you also do not have the sprocs, functions, etc. The point ofthis post is to show you just how to write XML files and once you have an XML file you simplyfeed the reader with that XML file and it will automatically display the data. I actually gave you the code to do that, all you have to do is modify the code to fit your needs based on whatkind of application you want to use this for.

What Happened

So what my code has done is generated XML files by doing the following:

pulling any data I wanted to XML to (the responsible or personnel who can be assigned a task in our example
using the responsible personnel from our example the application pulled the issues / tickets assigned to those responsible personnel
each record (user) becomes its own xml file
we generate the header of that XML file
we loop through the data and place data into the place holders of the xml file
we generate the footer and close the xml file
now the XML file is ready to go, we can simply tell the feeder to use the xml file and your data is feed to the reader

But The Data Is Out Of Date After 10 Minutes

Good point, after the XML file is generated you may be thinking 'but my data may change within the next hour'. In our example, Joey Bologna may get 2 more tickets in the next hour. The simple solution to all of this is your application becomes a scheduled task in the windows scheduler. That's right all you have to do is compile the application and place it on a server to have it run on a specified duration. That is you can set the task to run every hour, every day, even every minute. That way your XML files never go out of date, in addition, you are always updating your data even upto the minute (if you wanted to).

Watch It

After I finished my application I put it into production on a server that would run it once every 5 minutes (only because we have very little transactions and the performance hit is very very very small). So as it is running on the server as a scheduled task it refreshes the XML files every 5 minutes. The result is your application is generating XML feeds for other application(s) without you having to do anything else. Your other application(s) simply just need to know the XML feed that this application created.

A picture is worth a 1000 words...so I guess this video is worth a million!

Click here to watch it in action.