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