Tags: , , , , , | Posted by Spyros Moschovakos on 3/11/2011 10:33 PM | Comments (0)

As you may already know, if you need to programmatically access Sharepoint (2007/2010) data from a remote machine, the Sharepoint Object Model is not an option. Sharepoint provides a set of web service methods to expose its list data to third party applications (more details can be found here). While this is very nice and flexible, it forces you to perform some XML parsing and composing (CAML) in order to build the query and extract the results. Using the functions below, you can use a more object-oriented way to retrieve items.

Let's assume you have a custom list named "Products" that has an "ID", "Title", "SerialNumber", "Category" (lookup), "IsEnabled" (Yes/No), and "Owner" (person or group) fields. Before you do anything, a web reference that points to http://<server-url>/_vti_bin/lists.asmx should be added in your project (here we use the class name "WSS_List" for our web reference). 

If you needed to query an item based on its title and serial number, using the wrapper functions this could be done like this:


Dim l As New WSS_List.Lists()

Dim params As New NameValueCollection
params.Add("Title", "Product A")
params.Add("SerialNumber", "4500058C")

Dim xQuery As XmlNode = MOSSQueryHelper.ConstructListQuery(params)
Dim xResults As XmlNode = l.GetListItems("Products", "", xQuery, Nothing, "", Nothing, "")

Dim Rows As List(Of NameValueCollection) = MOSSQueryHelper.ParseListResults(xResults, True)
For Each _row As NameValueCollection In Rows
	Console.WriteLine("Product ID:" & _row("ID"))
	Console.WriteLine("Product Title:" & _row("Title"))

If you just needed a single Product based on its ID, the only parameter should be the ID value:

params.Add("ID", "2")

Note that the inputs must be defined in a NameValueCollection object, where [Name] is the Column and [Value] the actual value to compare. The results are returned as a collection of NameValueCollection objects, each one of which represent a single item/record.  

Apart from simple textual fields, the class also supports queries based on lookup fields (they are treated differently in CAML). In the example above, if we wanted to find products based on the CategoryID, we should define our parameters as follows:

params.Add("Category", "5_{LOOKUPID}")

Yeah I know I know,  it's quick and dirty, however you are free to modify it and make it even more structured according to your needs. A similar way is used to filter for a field of sharepoint type "Person or group" (provide user id) or "Yes/No" (provide 0 or 1). This time, the {USER} and {BOOLEAN} suffixes are used:

params.Add("Owner", "7_{USER}")
params.Add("IsEnabled", "1_{BOOLEAN}")

The full source code of the class in VB .NET is the following:

Imports System.Xml

Public Class MOSSQueryHelper

    Public Shared Function ConstructListQuery(ByVal Arguments As NameValueCollection) As XmlNode
        'use VALUE_{LOOKUPID} for lookup ids

        Dim tmpStr As String = ""
        tmpStr &= "<Query>"
        If Arguments IsNot Nothing AndAlso Arguments.Count > 0 Then
            tmpStr &= "<Where>"

            For i As Integer = 1 To Arguments.Count
                Dim arg As String = Arguments.Keys(i - 1)

                If i = 1 Then
                    If Arguments.Count > 1 Then
                        tmpStr &= "<And>"
                    End If
                    If Arguments.Count > 2 Then
                        tmpStr &= "<And>"
                    End If
                End If

                If i = 3 Then
                    If Arguments.Count > 2 Then
                        tmpStr &= "</And>"
                    End If
                    If Arguments.Count > 3 Then
                        tmpStr &= "<And>"
                    End If
                End If

                tmpStr &= "<Eq>"
                If Arguments(arg).EndsWith("_{LOOKUPID}") Then
                    tmpStr &= "<FieldRef Name=""" & arg & """ LookupId=""TRUE""  />"
                    Arguments(arg) = Arguments(arg).Replace("_{LOOKUPID}", "")
                    tmpStr &= "<Value Type=""Lookup"">" & Arguments(arg) & "</Value>"
                ElseIf Arguments(arg).EndsWith("_{BOOLEAN}") Then
                    tmpStr &= "<FieldRef Name=""" & arg & """ />"
                    Arguments(arg) = Arguments(arg).Replace("_{BOOLEAN}", "")
                    tmpStr &= "<Value Type=""Boolean"">" & Arguments(arg) & "</Value>"
                ElseIf Arguments(arg).EndsWith("_{USER}") Then
                    tmpStr &= "<FieldRef Name=""" & arg & """ LookupId=""TRUE"" />"
                    Arguments(arg) = Arguments(arg).Replace("_{USER}", "")
                    tmpStr &= "<Value Type=""User"">" & Arguments(arg) & "</Value>"
                    tmpStr &= "<FieldRef Name=""" & arg & """ />"
                    tmpStr &= "<Value Type=""Text"">" & Arguments(arg) & "</Value>"
                End If

                tmpStr &= "</Eq>"

                'last parameter
                If i = Arguments.Count Then
                    If Arguments.Count > 3 Then
                        tmpStr &= "</And>"
                    End If
                    If Arguments.Count > 1 Then
                        tmpStr &= "</And>"
                    End If
                End If
            tmpStr &= "</Where>"
        End If

        tmpStr &= "</Query>"

        Dim tmpDoc As New XmlDocument

        Return tmpDoc.DocumentElement
    End Function

    Public Shared Function ParseListResults(ByVal nd As XmlNode, Optional ByVal RemovePrefix As Boolean = True) As List(Of NameValueCollection)
        Dim tmpList As New List(Of NameValueCollection)
        '<listitems xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" 
        'xmlns:  dt = "uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
        '   xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema" 
        '   xmlns="http://schemas.microsoft.com/sharepoint/soap/">
        '   <rs:data ItemCount="4">
        '      <z:row ows_Number_Field="6555.00000000000" 
        '        ows_Created = "2003-06-18T03:41:09Z"
        '         ows_ID="3" ows_owshiddenversion="3" />
        '      <z:row ows_Number_Field="78905456.0000000" 
        '        ows_Created = "2003-06-18T17:15:58Z"
        '         ows_ID="4" ows_owshiddenversion="2" />
        '         ...
        '   </rs:data>

        For Each child As XmlNode In nd.ChildNodes
            If child.NodeType = XmlNodeType.Element Then
                For Each attr As XmlNode In child.ChildNodes
                    If attr.Name = "z:row" Then
                        Dim tmp As New NameValueCollection
                        For Each attr2 As XmlAttribute In attr.Attributes
                            If RemovePrefix Then
                                tmp(attr2.Name.Replace("ows_", "")) = attr2.Value
                                tmp(attr2.Name) = attr2.Value
                            End If
                    End If
            End If

        Return tmpList
    End Function

End Class


That's it! Currently the above CAML builder serves relatively simple search scenarios, since it composes a correct CAML for up to 4 parameters, that are combined only in AND mode and the operators are only "equals". You can of course enrich it to support more operations (for example "OR" search or "Like"/"Greater Than" operators among the parameters and their values).

Happy CAMLing Cool

Add comment

  Country flag
  • Comment
  • Preview