Leon Zandman’s Blog

If you’re querying SharePoint content using a CAML query from code it’s a good habit to always populate the SPQuery instance’s ViewFields property. Otherwise the returned SPListItem instances might not contain any data for certain fields and throw an exception when you try to access those fields.

Specifying ViewFields involves creating a string of CAML FieldRef elements. For instance if we want our query to return items that contain data for the Title, Created and ID field we use this:

   1: <FieldRef Name=‘Title’/><FieldRef Name=‘Created’/><FieldRef Name=‘ID’/>

As you can see there’s some overhead of boilerplate markup involved. I’ve written a small piece of code that I always use to make my life a little easier. Today I happened to post this code in a reply I wrote on the MSDN forums and also decided to submit it as Community Content to the official SPQuery docs on MSDN. Then I thought I might as well share it with you here. So here it is:

   1: public static string BuildViewFieldsXml(params string[] fieldNames)

   2: {

   3:     const string TEMPLATE = @"<FieldRef Name=’{0:S}’/>";

   4:     StringBuilder sb = new StringBuilder();

   5:     foreach (string fieldName in fieldNames)

   6:     {

   7:         sb.AppendFormat(TEMPLATE, fieldName);

   8:     }

   9:     return sb.ToString();

  10: }

  11:  

  12: // Use it like this:

  13: SPQuery query = new SPQuery();

  14: query.ViewFields = BuildViewFieldsXml("Title", "Created", "ID");

  15:  

  16: // Note that you can specify a variable amount of string parameters, i.e.

  17: query.ViewFields = BuildViewFieldsXml("Title", "Created", "ID", "Author", "Gender");

Yeah, you’re right. This piece of code isn’t exactly rocket science. But you might appreciate it anyway

I’m planning on doing some posts about frustrating things I have encountered (and still do!) during my SharePoint development efforts. Here’s the first one:

Last year while working on a MOSS 2007 project for one of our customers I stumbled on what I thought was a bug in SharePoint 2007. I had created a custom list that was filled with Electronic Program Guide (EPG) information for the streaming video media that that site contained. I then created a Webpart that used ASP.Net AJAX to continually show the actual EPG information below the video stream.

In order to obtain the EPG items I used a CAML query to query the EPG list. The list was simply a custom list containing amongst others a column of type DateTime that was called "ProgramEnd". As the name suggests it contained the time the program ended. I then used the following code to create a query that was supposed to obtain the currently broadcasted item and all future items.

   1: const string EPG_QUERY_TEMPLATE = @"

   2: <Where>

   3:  <Geq>

   4:   <FieldRef Name=’ProgramEnd’ />

   5:   <Value Type=’DateTime’>{0}</Value>

   6:  </Geq>

   7: </Where>"; 

   8:  

   9: SPQuery query = new SPQuery();

  10: query.Query = String.Format(EPG_QUERY_TEMPLATE,

  11:     SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now)); 

  12: // …

To my surprise this query always returned too many items. Further investigation showed that time part of the query seemed to be ignored completely, so it returned the items as if no time was specified! So instead of getting the current item and all future items it returned all items broadcasted for that day.

After wasting a lot of time debugging, using the U2U Caml Query Builder and looking for an answer / solution on the internet I gave up and wrote a quick hack around it, which fortunately wasn’t very difficult. It would just run the query and run the results through some additional code that checked the ProgramEnd DateTime field and filter out the wrong items, like this:

   1: SPListItemCollection results = …; // The results from the query mentioned above

   2: // Trim the results to only include the current and future items

   3: List<SPListItem> trimmedItems = new List<SPListItem>();

   4: foreach (SPListItem result in results)

   5: {

   6:     DateTime programEnd = (DateTime)result["ProgramEnd"];

   7:     if (programEnd >= DateTime.Now)

   8:         trimmedItems.Add(result);

   9: } 

Fortunately this worked just fine and the customer was happy. I was not…

Today while surfing the Net I stumbled on this entry in the MSDN forums, from which I learned it wasn’t a bug, but that you need to included the "IncludeTimeValue" attribute to the CAML query, like this:

   1: <Where>

   2:  <Eq>

   3:   <FieldRef Name=‘programEnd’ />

   4:   <Value Type=‘DateTime’ IncludeTimeValue=‘TRUE’>

   5:    2008-07-30T12:00:00Z

   6:   </Value>

   7:  </Eq>

   8: </Where> 

If only I had known it was this simple… What bothers me is that this little, but very important attribute seems to be totally undocumented. I couldn’t find any information about it in the WSS / SharePoint SDKs.

Ofcourse once I knew what to look for I found some other blogs and forums mentioning this issue. It turns out the UCSharp blog had already blogged about this way back in October 2007, only a few months after I searched for it. Even Karine Bosch, U2U’s "CAML Girl" and author of the famous U2U CAML Query Builder, says she only recently found out about this. Fortunately she has included support for the "IncludeTimeValue" attribute in her latest version of the U2U Caml Query Builder, which I know a lot of SharePoint developers use to construct and test their CAML queries.

I also noticed that someone called puneetspeed has added some Community Content to the online SharePoint SDK’s SPQuery docs explaining this issue. So hopefully Microsoft will add information about the "IncludeTimeValue" attribute to the official SDK text in the near future.

Just arrived back from my visit to the Microsoft DevDays 2008 in Amsterdam. It was a great day and I’ve attended some interesting sessions.

I’ve uploaded the photos I took to my Flickr account, so check ‘em out. I’ve also uploaded a small video of the “Holland Sport” bicycle race track we had at our booth. Check it out:

It was a very tiring day and I’m going to get some sleep now. I’ll blog about some more about the DevDays later on…

Last week I was refactoring some of my SharePoint code. I stumbled on a loop that created a new SPQuery instance for each iteration. The code was something like this:

   1: SPList list = GetCommentsList();

   2: const string VIEWFIELDS = "";

   3:  

   4: foreach (string param in params)

   5: {

   6:     // Create fresh new SPQuery instance…

   7:     SPQuery query = new SPQuery();

   8:     query.ViewFields = VIEWFIELDS;

   9:     query.Query = BuildQuery(param);

  10:  

  11:     // And use it…

  12:     SPListItemCollection items = list.GetItems(query);

  13:     ProcessItems(items);

  14: }

As the value of the ViewFields property remained the same for each iteration I decided to create just one SPQuery instance and reuse it, like this:

   1: SPList list = GetCommentsList();

   2: const string VIEWFIELDS = "";

   3:  

   4: // Create just one SPQuery instance…

   5: SPQuery query = new SPQuery();

   6: query.ViewFields = VIEWFIELDS;

   7:  

   8: foreach (string param in params)

   9: {

  10:     // And reuse it…

  11:     query.Query = BuildQuery(param);

  12:     SPListItemCollection items = list.GetItems(query);

  13:     ProcessItems(items);

  14: }

To my surprise this code didn’t work! The first time the SPQuery instance was used it worked just fine. However, during the next iterations of the foreach loop it didn’t seem to get updated.

So, what have we learned today? Never reuse SPQuery instances!

Well… That’s not entirely true. You can reuse SPQuery instances for a very valid reason. If you use the RowLimit property you can limit the number of items returned in the query, which is useful for paging as seen in this sample code (taken from MSDN):

   1: using (SPWeb oWebsiteRoot = SPContext.Current.Site.RootWeb)

   2: {

   3:     SPList oList = oWebsiteRoot.Lists["Announcements"];

   4:     SPQuery oQuery = new SPQuery();

   5:     oQuery.RowLimit = 10;

   6:     int intIndex = 1;

   7:  

   8:     do

   9:     {

  10:         Console.WriteLine("Page: " + intIndex);

  11:         SPListItemCollection collListItems = oList.GetItems(oQuery);

  12:  

  13:         foreach(SPListItem oListItem in collListItems)

  14:         {

  15:             Console.WriteLine(oListItem["Title"]);

  16:         }

  17:         oQuery.ListItemCollectionPosition = 

  18:           collListItems.ListItemCollectionPosition;

  19:         intIndex++;

  20:     } while(oQuery.ListItemCollectionPosition != null);

  21: }

So, only reuse SPQuery instances if you use paging. If you change the actual CAML query you should create a new SPQuery instance for it.

The Visual Studio & .NET Framework Evangelism team did it again. After their cool .NET 3.5 Training Kit they are now working on the follow up: the .NET 3.5 Enhancements Training Kit. It’s not final yet, but still very interesting!

Currently, the training kit contains six hands-on labs, made up of the following technologies:

1. ADO.NET Data Services
2. ADO.NET Entity Framework
3. ASP.NET AJAX History
4. ASP.NET Dynamic Data
5. ASP.NET MVC
6. ASP.NET Silverlight controls

Read more about it on Jonathan Carter’s blog.