Using WSS v3 for a database

As many of you know I am on the board of CodeMash. We are now working on updating the site for this year's conference.  We just pushed v1 live, and are now working on the attendee registration features.

Last year we had a great site. It was ASP.NET, web services built with the service software factory, and SQL Server 2005. It worked great.

One of the features for the site was a news channel that was on the homepage, a news page, and an rss feed. In order to manage the news content we also had an admin tool, and some supporting tables and so on.

This worked fine.

But this year, as I was refactoring, I just didn't want to deal with remote DBs, and upgrading the admin interface. So, one day while I was talking to local WSS bad boy (amongst other things), I came up with the idea of having the news content come from a WSS list that was on our planning portal.

Sounded easy to me; wire the news business object to the web services for WSS instead of some EntLib code that I had in place, and we're done. Check it in, go to bed.

In theory, it's that easy. I ran into the dreaded Loosey-Goosey anti-pattern that most of the WSS web services are based on. I don't know how I would do it better, but it still is Loosey-Goosey.

You see, many of them take a fragment of XML for a parameter, it isn't strongly typed in anyway. So the consumer has to munge together this XML (which is really CAML), and submit it in, and hope it is right. And if you screw up the official name for a list, view, or column (versus the displayed name), you really don't get much in the way of helpful information in the exception. You get 'field not found', which doesn't tell you which of the 20 fields you queried for wasn't found. Frankly, it made me miss the days of classic asp and vb6 com with 'Internal Server Error 500".

I want to share how I eventually figured out how to store and retrieve the news content form a list in WSS for our site. I don't know if this is the best way to do it, but it works for me.

Also, as a side note, I eventually found a tool that helps you write the CAML. It is published by U2U here. It even inspects your WSS list to know what all of the column names are. There is a library you can reference in your .NET application that loads the generated CAML (and 'connection string') so that you can abstract the WSS calls away from your code. This is a really cool idea. Unfortunately while I was able to use it to help me craft the CAML, I couldn't get the library to work. It might be because I am using Orcas Beta 2.

I started by adding a reference to the Lists web service for our WSS server. I had to provide username/password credentials to get through security. The URL I used was in this form:

http://<<site name>>/_vti_bin/lists.asmx

I did this with the normal 'add web reference' process in VS. Remember that the URL for WSS services is virtualized to the subsite that you want to access. So if the root had a collection of sites, and you wanted to query a list in a site in the collection, you would have to put that in the URL of the service: http://wss.mysite.com/subsite/_vti_bin/lists,asmx.

Once you have the reference, you can create an instance of the proxy class. You then have to add the credentials you want the proxy to use to access the list. You will need a 'using System.Net;' for the NetworkCredential namespace.

org.CodeMash.wss.Lists listService = new org.CodeMash.wss.Lists();
listService.Credentials = new NetworkCredential("WSSUsername", "WSSpassword");

You then need to craft several pieces of CAML. The first is the query expression. This is similar to a filter statement in SQL. Then you need a fragment that represents the columns/fields you want returned. The below statement is like this SQL:

select Title, ID, Created, Expires, Body, Summary From Listname where Expires > "07-15-2007" order by Created

  Here is the code:

XmlDocument xmlDoc = new System.Xml.XmlDocument();

XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element, "Query", "");

XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, "ViewFields", "");

XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element, "QueryOptions", "");

ndViewFields.InnerXml = "<FieldRef Name='Title' /><FieldRef Name='ID' /><FieldRef Name='Created' /><FieldRef Name='Expires' /><FieldRef Name='Body' /><FieldRef Name='Summary' />";

ndQuery.InnerXml = "<OrderBy><FieldRef Name=\"Created\" Ascending=\"False\" /></OrderBy><Where><Gt><FieldRef Name=\"Expires\"/> <Value Type=\"DateTime\">2007-07-15T00:00:00</Value></Gt></Where>";

Then, I create a generic collection of the business objects I am going to create. I plan on creating one NewsItem object for each item in the news list returned. After that I call the 'GetListItems' method on the List Service. You need to pass in the name of the list, the view to query against (which is optional), and the query expression, fields to be returned, and the query options. There are a few other optional parameters. You should go look up the full spec if you need to know all the details. That method is going to return a bunch of CAML. I use XMLNode to iterate over the collection of nodes, creating a new NewsItem object using the new C# 3.0 constructor syntax. This is really handy I have to say. You can see that I have to cast each value to the real type I want. Also, each field in the list is returned as an attribute on the item element in the CAML. After that I just return the generic collection of NewsItems back to the caller.

List<NewsItem> headlines = new List<NewsItem>();

                         XmlNode ndListItems = listService.GetListItems("Site News Feed", null, ndQuery, ndViewFields, null, null, null);

               String xpq = "//*[local-name() = 'data' and namespace-uri() = 'urn:schemas-microsoft-com:rowset']/*[local-name() = 'row' and namespace-uri() = '#RowsetSchema']";
               XmlNodeList nodes = ndListItems.SelectNodes(xpq);
               foreach (XmlNode aNode in nodes)
               {
                   headlines.Add(new NewsItem
                   {
                       CreateDate = Convert.ToDateTime(aNode.Attributes["ows_Created"].Value),
                       ExpireDate = Convert.ToDateTime(aNode.Attributes["ows_Expires"].Value),
                       Headline = aNode.Attributes["ows_Title"].Value,
                       NewsBody = aNode.Attributes["ows_Body"].Value,
                       NewsId = Convert.ToInt32(aNode.Attributes["ows_ID"].Value),
                       Summary = aNode.Attributes["ows_Summary"].Value
                   });
               }

Once a user clicks on a headline, I need to fetch the complete news article to display in a popup window. About the only thing that changes is the CAML on the query, and that I am receiving one record back, and not a collection. In this case, I am going to query on the item ID that WSS has, sort of like a primary key in a database.

public static NewsItem GetNewsItem(Int32 itemId)
        {
            org.codemash.wss.Lists listService = new org.codemash.wss.Lists();
            listService.Credentials = new NetworkCredential("WSSUsername", "WSSPassword");

            XmlDocument xmlDoc = new System.Xml.XmlDocument();

            XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element, "Query", "");
            XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, "ViewFields", "");
            XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element, "QueryOptions", "");

            ndViewFields.InnerXml = "<FieldRef Name='Title' /><FieldRef Name='ID' /><FieldRef Name='Created' /><FieldRef Name='Expires' /><FieldRef Name='Body' />";

            ndQuery.InnerXml = "<Where><Eq><FieldRef Name=\"ID\" /><Value Type=\"Counter\">" + itemId + "</Value></Eq></Where>";

             XmlNode ndListItems = listService.GetListItems("Site News Feed", null, ndQuery, ndViewFields, null, null, null);

                String xpq = "//*[local-name() = 'data' and namespace-uri() = 'urn:schemas-microsoft-com:rowset']/*[local-name() = 'row' and namespace-uri() = '#RowsetSchema']";
                XmlNodeList nodes = ndListItems.SelectNodes(xpq);
                XmlNode aNode = nodes[0];
               NewsItem aNewsItem = new NewsItem
                    {
                        CreateDate = Convert.ToDateTime(aNode.Attributes["ows_Created"].Value),
                        ExpireDate = Convert.ToDateTime(aNode.Attributes["ows_Expires"].Value),
                        Headline = aNode.Attributes["ows_Title"].Value,
                        NewsBody = aNode.Attributes["ows_Body"].Value,
                        NewsId = Convert.ToInt32(aNode.Attributes["ows_ID"].Value)
                    };
                       return aNewsItem;
        }

The great thing is I don't have to write an admin console, or anything like that. The tool is already there for me in WSS. The news authors can now go in there and control the content. Add items, remove items, stage them for future publication. All with very little work for me.

I implemented the same concept for our 'Topic Submission' form. This is where speakers can submit topics for consideration. In this case, I have a normal web form. Once I validate and control all of the input, instead of writing into a database, I write it to a list in WSS. Now the team that is managing content can easily see what topics have been submitted. With WSS' great filtering and views they can create views that are broken out by tracks, or author, or approval status. The code is below (some stuff was ripped out to make it simpler.) The tricky part is knowing what the field names really are. You have to spelunk the list design form and look at the URL in the browser to get these. For example, my field on the list I created called SpeakerBio is really named 'Speaker_x0020_Bio' by WSS. I did some REAL basic-hacky-ill advised-don't do this at home exception checking at the end. (Yeah, and I know I should have used a string builder. so sue me. this code is going to be run about 200 times, and that's it, and performance isn't an issue.)

private void SubmitATopic(string SessionTitle, string SessDesc, string SpeakerName, string SpeakerEmail, string SpeakerPhone, string SpeakerBio, string Track, int PrevGiven)
        {
            string ListId = "ListGUID";

            org.codemash.wss.Lists listService = new org.codemash.wss.Lists();
            listService.Credentials = new NetworkCredential("WSSUsername", "WssPassword");

            string strBatch = "<Method ID='1' Cmd='New'>" +
                "<Field Name='Title'>" + SessionTitle + "</Field>" +
                "<Field Name='Session_x0020_Description'>" + SessDesc + "</Field>" +
                "<Field Name='Speaker_x0020_Name'>" + SpeakerName + "</Field>" +
                "<Field Name='Speaker_x0020_Email'>" + SpeakerEmail + "</Field>" +
                "<Field Name='Phone_x0020_Number'>" + SpeakerPhone + "</Field>" +
                "<Field Name='Speaker_x0020_Bio'>" + SpeakerBio + "</Field>" +
                "<Field Name='Track'>" + Track + "</Field>" +
                "<Field Name='UniqueID0'>" + System.Guid.NewGuid().ToString() + "</Field>" +
                "<Field Name='Session_x0020_Previously_x0020_P'>" + PrevGiven.ToString() + "</Field>" +
                "</Method>";

            XmlDocument xmlDoc = new System.Xml.XmlDocument();

            System.Xml.XmlElement elBatch = xmlDoc.CreateElement("Batch");

            elBatch.SetAttribute("OnError", "Continue");

            elBatch.InnerXml = strBatch;

            XmlNode ndReturn = listService.UpdateListItems(ListId, elBatch);

            if (ndReturn.InnerText != "0x00000000")
                throw new System.Exception(ndReturn.InnerText);
        }

Anyway, this works fairly well, and saved me from having to build a whole bunch of backend infrastructure. Which is good, because I had to focus on reading the last Dune book. But that is a whole different post.

Comments

Post a Comment

Popular posts from this blog

Farewell

I don't often explicitly state that I work for Quick Solutions when I post to my blog. Others that work with me talk about QSI all the time. I do this because I want the articles to be what 'I' have to say about technology, not what QSI has to say. I started working for QSI in June of 2003. I was about to start my own consulting company, and they asked me to come in to help them start their solutions business (having built a high quality technical staffing business). I thought, why not? Getting to build what I want to build on someone else's dime definitely reduces my personal risk, and if it didn't work out, I could just pick back up the 'start my own company' thing. Nearly five years have passed, and we have built a great team. A team that is considered one of the leading development shops in our region. Each, and EVERY person on this team would be considered a rock star at any company. We have a great culture for the team, and a deep seated ownership
Read more

How does an Architect pack?

Today we are packing for the big move. Quick Solutions is moving to some new swanky office space, with an awesome new delivery center. The new center has wall to wall whiteboards, network projectors for group programming (pair programming is so 20th century), a game room, new open seating (shared workspace is the key to productivity), and lots of other new goodies. It is an amazing investment by the company, and shows their commitment to building the best team on the block. As we are packing, we started joking around (not that joking around is rare, and would have occurred regardless of the packing state). How does and architect pack? An architect doesn't pack anymore. He did that ten years ago. He goes to plenty of packing conferences, and has read a lot of books on packing. An architect will map out how the whole enterprise should pack, and even dictate what (clearly out of date) packing supplies and processes should be used. These processes will be the perfect match for some
Read more

Job security is a myth, and how IT Pros are Thriving

No matter how you slice it, the economy is definitely in a state of concern. Some call it a recession, some a depression, others it is a crash. Since all of those words are overloaded, I will choose to use the word Grapefruit to represent the current situation. How concerned you are with grapefruit depends on who you are, and your personal situation. It is these times of grapefruit when there is the most opportunity. Crazy, I know. But now is the time to start that crazy idea, to outmaneuver an opponent, or get desperate enough to take risks you normally wouldn’t take (personally, or as a company) during up times. All boats float in the high tide. Only great captains can navigate the low tide. There is no such thing as job security. Surprise. There just isn’t, it is a complete myth. ‘Job Security’ might have made sense two generations ago, but it just doesn’t work now. You make your own security, during good times, and during grapefruits. Through keeping your skills sharp, thro
Read more