Using Data From SharePoint 2003 Lists

A buddy of mine asked me to help him figure out how to get the data from a SharePoint list for a project he’s working on.  I thought rather than showing him one way, I’d show him three and let him decide which method is best for his application.  Now I think I’ll share this with the rest of the world in case anybody else is doing the same sort of thing.

SharePoint 2003 has several web services that you can use to access information stored in a SharePoint site, all of which are located in the _vti_bin directory of a server running SharePoint 2003.  Here’s a quick list of the services available (taken from Using Microsoft Windows SharePoint Services with the Microsoft Office System)

To retrieve information from a List we’ll use the Lists.asmx web service and the GetListItems method in particular.  To do this, add a web reference to your application setting the URL to the location of the Lists web service.  The screen should look something like this:

After adding the web reference to your application you can use the following code to retrieve the list data from the web service as an XmlNode.

Private Function GetListNode() As XmlNode

    Dim RCPILists As New RCPILists.Lists

    RCPILists.Credentials = New System.Net.NetworkCredential("PaulBallard", _

"password")

    Dim node As XmlNode = RCPILists.GetListItems("Vacation Calendar", _

       String.Empty, Nothing, Nothing, String.Empty, Nothing)

    Return node

End Function

Let’s take a look at what the XML returned looks like when querying a list of events.

<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="24">

              <z:row ows_fRecurrence="0" ows_Attachments="0" ows_LinkTitle="Tulsa .NET User Group Meeting"

                     ows_EventDate="2004-09-27 18:00:00" ows_EndDate="2004-09-27 21:00:00" ows_Title="Tulsa .NET User Group Meeting"

                     ows_ID="2" ows_owshiddenversion="1" />

              <z:row ows_fRecurrence="0" ows_Attachments="0" ows_LinkTitle="INETA Board Meeting in Vegas"

                     ows_EventDate="2004-10-01 16:00:00" ows_EndDate="2004-10-03 16:00:00" ows_Title="INETA Board Meeting in Vegas"

                     ows_ID="1" ows_owshiddenversion="2" />

              <z:row ows_fRecurrence="0" ows_Attachments="0" ows_LinkTitle="Dallas .NET User Group" ows_EventDate="2004-10-14 18:00:00"

                     ows_EndDate="2004-10-14 21:00:00" ows_Title="Dallas .NET User Group" ows_ID="5" ows_owshiddenversion="2" />

              <z:row ows_fRecurrence="0" ows_Attachments="0" ows_LinkTitle="New Orleans .NET User Group Weekend"

                     ows_EventDate="2004-10-16 09:00:00" ows_EndDate="2004-10-18 21:00:00" ows_Title="New Orleans .NET User Group Weekend"

                     ows_ID="3" ows_owshiddenversion="2" />

              <z:row ows_fRecurrence="0" ows_Attachments="0" ows_LinkTitle="OOPSLA" ows_EventDate="2004-10-23 00:00:00"

                     ows_EndDate="2004-10-28 00:00:00" ows_Title="OOPSLA" ows_ID="4" ows_owshiddenversion="2" />

              <z:row ows_fRecurrence="0" ows_Attachments="0" ows_LinkTitle="Oklahoma City .NET User Group"

                     ows_EventDate="2004-11-01 00:00:00" ows_Title="Oklahoma City .NET User Group" ows_ID="6"

                     ows_owshiddenversion="1" />

              <z:row ows_fRecurrence="0" ows_Attachments="0" ows_LinkTitle="DevConnections" ows_EventDate="2004-11-07 00:00:00"

                     ows_EndDate="2004-11-10 23:00:00" ows_Title="DevConnections" ows_ID="11" ows_owshiddenversion="1" />

              <z:row ows_fRecurrence="0" ows_Attachments="0" ows_LinkTitle="Thanksgiving" ows_EventDate="2004-11-25 00:00:00"

                     ows_Title="Thanksgiving" ows_ID="7" ows_owshiddenversion="1" />

              <z:row ows_fRecurrence="0" ows_Attachments="0" ows_LinkTitle="CES - Las Vegas" ows_EventDate="2005-01-06 00:00:00"

                     ows_EndDate="2005-01-07 23:00:00" ows_Title="CES - Las Vegas" ows_ID="12" ows_owshiddenversion="2" />

              <z:row ows_fRecurrence="0" ows_Attachments="0" ows_LinkTitle="Devscovery Austin" ows_EventDate="2005-01-11 00:00:00"

                     ows_EndDate="2005-01-13 23:00:00" ows_Title="Devscovery Austin" ows_ID="14" ows_owshiddenversion="1" />

              <z:row ows_fRecurrence="0" ows_Attachments="0" ows_LinkTitle="TechEd 2005 in Orlando" ows_EventDate="2005-06-03 00:00:00"

                     ows_EndDate="2005-06-11 00:00:00" ows_Description="Remember to have new liver standing by"

                     ows_Title="TechEd 2005 in Orlando" ows_ID="24" ows_owshiddenversion="1" />

       </rs:data>

</listitems>

Many of you may recognize this XML format as that of an ADO Recordset with the columns of the records prefixed with “ows_’.  While a relatively simple format, it’s not conducive to working with the data in .NET.  So then, here are the three examples of working with this data to fill a Windows Forms ListBox:

1.      Convert it to a Dataset (Easiest)

While the format of the ADO Recordset doesn’t match that of an ADO.NET Dataset, the Dataset can read the data in as XML and infer the layout of the tables.  To read the XML data into a Dataset you need to create an XmlTextReader that wraps the XmlNode returned by the web service and then call the Dataset’s ReadXml() method passing in the XmlTextReader.  Here is what that code might look like:

Private Sub btnGetDS_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetDS.Click

    Dim node As XmlNode = GetListNode()

    Dim xr As New XmlTextReader(node.OuterXml, XmlNodeType.Element, Nothing)

    Dim ds As New DataSet

    ds.ReadXml(xr)

    ListBox1.DataSource = ds.Tables("row")

    ListBox1.DisplayMember = "ows_Title"

End Sub

When you read this XML into the dataset, there will be two tables created.  The first one, at index 0, will be the “data” table and will contain one record with the ItemCount in it.  The second table will be the “row” table and will contain the data from the list with a reference to the parent “data” record.  Also remember that columns in the table will be prefixed with “ows_”.

2.      Access Data Using XPath Queries (Easy)

You can use XPath to locate a single element or a group of elements from the node retrieved using the XmlNode.SelectNodes and XmlNode.SelectSingleNode methods.  To search for a single element in the list the code would look something like this:

xpq = "//*[@ows_Title='Thanksgiving']"

Dim node2 As XmlNode = node.SelectSingleNode(xpq)

However, to retrieve all of the rows returned using XPath, we can’t simply use “//data/rows” because of the namespace prefixes.  You could create an XPathNavigator loading up the proper namespaces but that struck me as a lot of work.  So instead I would use this code to issue an XPath query that would return all rows.

Dim xpq As String = "//*[local-name() = 'data' and namespace-uri() = 'urn:schemas-microsoft-com:rowset']/*[local-name() = 'row' and namespace-uri() = '#RowsetSchema']"

Dim nodes As XmlNodeList = node.SelectNodes(xpq)

For idx As Int32 = 0 To nodes.Count - 1

   ListBox1.Items.Add(nodes(idx).Attributes("ows_Title").Value)

Next

3.      XmlSerialization (Not-So Easy)

It’s often preferable to work with data in terms of objects with properties.  Datasets and XML nodes are relatively large objects and take time to build where as custom business objects contain only the code you decide is there.  The key in using XML Serialization for list items are the Xml Attributes defined in the System.Xml.Serialization namespace.

To start we create the generic shell classes that every list will use.  These classes represent the “<listitems>” element and “<rs:data>” elements.  To specify how the XmlSerializer should deserialize the XML from the Web Service into one of these objects we will apply the XmlElementAttribute to each class specifying the ElementName and Namespace properties.  Here is what the code for that looks like:

#Region "Generic List Classes, should not need to be changed"

<XmlRoot(ElementName:="listitems", Namespace:="http://schemas.microsoft.com/sharepoint/soap/")> _

Public Class ListResults

    <XmlElement(ElementName:="data", Namespace:="urn:schemas-microsoft-com:rowset")> _

    Public Data As ListData

End Class

Public Class ListData

    Public Sub New()

    End Sub

    <XmlAttributeAttribute("ItemCount")> _

    Public ItemCount As Int32

    <XmlElement(ElementName:="row", Namespace:="#RowsetSchema")> _

    Public Items As ListItem()

End Class

#End Region

Next we have to specify the ListItem class that defines the columns in the list with the properties and attributes that we want to use within our application.  Any property in the XML that doesn’t have a corresponding property in the class is ignored.  In specifying this class we can also remap the column names to remove the “ows_” prefix.  Again, we’ll make heavy use of the XmlAttributeAttribute.  Here is the code for a record of an Events list in SharePoint 2003.

'Customize this class for your list. 

Public Class ListItem

    Private m_LinkTitle As String

    Private m_EventDate As String

    Private m_EventEndDate As String

    Private m_Recurrence As Int32

    Private m_ID As Int32

    Private m_Title As String

    Public Sub New()

    End Sub

    <XmlAttributeAttribute("ows_LinkTitle")> _

    Public Property LinkTitle() As String

        Get

            Return m_LinkTitle

        End Get

        Set(ByVal Value As String)

            m_LinkTitle = Value

        End Set

    End Property

    <XmlAttributeAttribute("ows_EventDate")> _

    Public Property EventDate() As String

        Get

            Return m_EventDate

        End Get

        Set(ByVal Value As String)

            m_EventDate = Value

        End Set

    End Property

    <XmlAttributeAttribute("ows_EndDate")> _

    Public Property EventEndDate() As String

        Get

            Return m_EventEndDate

        End Get

        Set(ByVal Value As String)

            m_EventEndDate = Value

        End Set

    End Property

    <XmlAttributeAttribute("ows_fRecurrence")> _

    Public Property Recurrence() As Int32

        Get

            Return m_Recurrence

        End Get

        Set(ByVal Value As Int32)

            m_Recurrence = Value

        End Set

    End Property

    <XmlAttributeAttribute("ows_Title")> _

    Public Property Title() As String

        Get

            Return m_Title

        End Get

        Set(ByVal Value As String)

            m_Title = Value

        End Set

    End Property

    <XmlAttributeAttribute("ID")> _

    Public Property ID() As Int32

        Get

            Return m_ID

        End Get

        Set(ByVal Value As Int32)

            m_ID = Value

        End Set

    End Property

End Class

Once the class is defined, we can use the XmlSerializer to convert the XML from the web service into an instance of this object.  Here is the code to does that and then binds the data to a ListBox.

Private Sub btnGetObj_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetObj.Click

    Dim node As XmlNode = GetListNode()

    Dim xr As New XmlTextReader(node.OuterXml, XmlNodeType.Element, Nothing)

    Dim ser As New XmlSerializer(GetType(ListResults))

    Dim lResults As ListResults = CType(ser.Deserialize(xr), ListResults)

    ListBox1.DataSource = lResults.Data.Items

    ListBox1.DisplayMember = "Title"

End Sub

Which to Choose

Now of course the question is which one should you use?  That I leave up to you fair reader, but here are some things to consider about each approach.

Datasets

Datasets are large objects and take up quite a bit of memory, but can be very useful if you are planning to bind the data directly to a UI control or if you want the user to have the ability to add/edit items in the list.  You can mitigate the memory overhead by caching the list results in a multi-user environment.

XML

The XML produced by the web service is difficult to work with and manipulate as compared to a Dataset or business object.  XmlNode objects also take up a lot of memory.  However, if you are looking for a single item in the list, this is a very expedient way to get it as it requires no other objects be created or managed. 

Xml Serialization

XML Serialization is useful for occasions when you need to work with the data returned as an object and apply business rules or logic to the object while it’s being used.  Keep in mind that the data returned in the recordset format cannot be automatically converted into .NET types by the XmlSerializer.  For example, the date format used in the XML will not parse into a DateTime property.  Therefore you may need to create a wrapper around these classes to provide better type safety and usefulness.

Download Sample Code