Sample .NET code that retrieves data stored in a SharePoint list
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at https://www.microsoft.com/info/cpyright.htm
Quite often we need to pull some data stored in a SharePoint list. If the application is running on the SharePoint server then we could use Microsoft.SharePoint dll to gain access to data, but if your application is running outside SharePoint server then you need to rely on SharePoint Web services. In this case we will be using Lists service exposed by SharePoint to get to the data.
WSS 3.0 SDK provides a good starting point for solving this task. Here is a link to a code sample provided in the SDK How to: Return List Items
Unfortunately, unless you are an experienced SharePoint developer you may run into a couple of problems with this sample. In this blog I will try to provide some additional explanations of the code and also add a few lines to make the sample a bit friendlier.
So the code listed below will retrieve data from a SharePoint list located at https://moss01/Lists/alex. The list is very simple and contains 2 columns: Titl and firstName. I am going to highlight the blocks if code that were not intuitive to me and required some furhter investigation on my part.
1. Adding Web Reference
WSS 3.0 SDK contains a table of all the Web Services that are exposed by WSS. Since in our case we will be accessing Lists service and my list resides at the root site, the url of the Web Reference should be https://moss01/sites/_vti_bin/lists.asmx.
If the list resided in a site named IT, then the Web Reference would be https://moss01/sites/IT/_vti_bin/lists.asmx
2. In my lab the machine that I was accessing the WSS from was not part of the domain so I needed to provide explicite credentitals. Here is how you could achieve this:
CredentialCache credCache = new CredentialCache();
credCache.Add(new Uri("https://moss01"), "Negotiate",
new NetworkCredential("administrator", "pass@word1", "contoso"));
listService.Credentials = credCache
If you want to run your code in the security context of the currenlty logged-on user then change the sample like so:
listService.Credentials =
System.Net.CredentialCache.DefaultCredentials;
3. SDK recommends to use GUIDs of the List and View when accessing them. So where do we find those GUIDs? Here is at least one way how to do this:
Access the list via your browser, then go to Settings->List Settings (I assume you are using WSS 3.0). Copy the URL from your browser, it should look something like this:
https://moss01/_layouts/listedit.aspx?List=%7BBAA69F38%2DDBC7%2D4ACD%2D82EE%2D2BC094B7F1E6%7D.
Copy the part after the List=. Now substitute %7B for {, %7D for } and %2d for -. This should give you the resulting GUID, in my case it was {BAA69F38-DBC7-4ACD-82EE-2BC094B7F1E6}. While still in the List Settings click on the view that you would like to use when accessing your list. In my case I only have the default “All Items” view.
Copy the URL from your browser, it will look somewhat like this:
Copy the data between View= and &Source. Perform the same substituions as we did for the List GUID, this will provide you with the GUID of the view.
4. Now once we executed
XmlNode nodeListItems = listService.GetListItems(listName, viewName, query, viewFields, rowLimit, queryOptions, null), how do we actually retrieve data from it. In order to understand the sample I think it would be helpful to take a look at the raw XML that is being returned.
Here is what I got in my case:
<rs
<rs:data ItemCount="2" xmlns:rs="urn:schemas-microsoft-com:rowset">
<z:row ows_Title="01" ows_firstName="Alex" ows_MetaInfo="1;# " ows__ModerationStatus="0" ows__Level="1" ows_ID="1" ows_owshiddenversion="2" ows_UniqueId="1;#{8DF2619B-1405-4D7A-90F6-3F1E39C8544D} " ows_FSObjType="1;#0" ows_Created="2007-08-28 12:33:41" ows_FileRef="1;#Lists/alex/1_.000" xmlns:z=" #RowsetSchema" />
<z:row ows_Title="02" ows_firstName="tom" ows_MetaInfo="2;# " ows__ModerationStatus="0" ows__Level="1" ows_ID="2" ows_owshiddenversion="2" ows_UniqueId="2;#{09CE20A5-1B01-49E9-BA96-5542E3473B82} " ows_FSObjType="2;#0" ows_Created="2007-08-28 13:16:21" ows_FileRef="2;#Lists/alex/2_.000" xmlns:z=" #RowsetSchema" />
</rs:data>
A couple of observations here:
· A prefix of ows_ is being added to the column names, so don’t try to look for the Title column; remember to prefix your columns with ows_.
· The rows of the list are returned in the z:row nodes which are children of the rs:data node
Here some sample code that will extract the values from XML
XmlNode nodeListItems =
listService.GetListItems(listName, viewName, query, viewFields, rowLimit, queryOptions, null);
XmlDataDocument xmlDocResult = new XmlDataDocument();
xmlDocResult.LoadXml(nodeListItems.InnerXml);
XmlNodeList rows = xmlDocResult.GetElementsByTagName("z:row");
foreach (XmlNode attribute in rows)
{
Console.WriteLine(attribute.Attributes["ows_Title"].Value);
Console.WriteLine(attribute.Attributes["ows_firstName"].Value);
}
5. I commented out this statement from the SDK sample
//query.InnerXml = "<Where><Gt><FieldRef Name=\"ID\" />" +
// "<Value Type=\"Counter\">1</Value></Gt></Where>";
This statement is meant to add some paramters to the query so that only a subset of the data is returned. Spefically only rows with ID (each row in the list has it is own unique ID) greater then 3 will be returned. Since I only had 2 rows in my list, I would be getting nothing back. For details on Collaborative Application Markup Language (CAML) see MSDN documentation.
using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.Xml;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
/*Declare and initialize a variable for the Lists Web service.*/
WebReference.Lists listService = new WebReference.Lists();
/*Populate credential cache with account information which posseses sufficient priviliges to access the list */
CredentialCache credCache = new CredentialCache();
credCache.Add(new Uri("https://moss01"), "Negotiate",
new NetworkCredential("administrator", "pass@word1", "contoso"));
listService.Credentials = credCache;
// Instantiate an XmlDocument object
System.Xml.XmlDocument xmlDoc = new System.Xml.XmlDocument();
// Assign values to the string parameters of the GetListItems //method, using GUIDs for the listName
// and viewName variables. For listName, using the list display //name will also work, but using the list GUID is
// recommended. For viewName, only the view GUID can be used. //Using an empty string for viewName forcese the default view
string listName = "{BAA69F38-DBC7-4ACD-82EE-2BC094B7F1E6}";
string viewName = "{2321D570-2A5C-4EC1-89D1-D5DF9FAA9E3C}";
string rowLimit = "150";
/*Use the CreateElement method of the document object to create elements for the parameters that use XML.*/
XmlElement query = xmlDoc.CreateElement("Query");
XmlElement viewFields = xmlDoc.CreateElement("ViewFields");
XmlElement queryOptions = xmlDoc.CreateElement("QueryOptions");
/*To specify values for the parameter elements (optional), assign CAML fragments to the InnerXml property of each element.*/
//query.InnerXml = "<Where><Gt><FieldRef Name=\"ID\" />" +
// "<Value Type=\"Counter\">1</Value></Gt></Where>";
viewFields.InnerXml =
"<FieldRef Name=\"Title\" /><FieldRef Name=\"firstName\" />";
queryOptions.InnerXml = "";
/* Declare an XmlNode object and initialize it with the XML response from the GetListItems method. The last parameter specifies the GUID of the Web site containing the list. Setting it to null causes the Web site specified by the Url property to be used.*/
XmlNode nodeListItems =
listService.GetListItems(listName, viewName, query, viewFields, rowLimit, queryOptions, null);
XmlDataDocument xmlDocResult = new XmlDataDocument();
xmlDocResult.LoadXml(nodeListItems.InnerXml);
XmlNodeList rows = xmlDocResult.GetElementsByTagName("z:row");
foreach (XmlNode attribute in rows)
{
Console.WriteLine(attribute.Attributes["ows_Title"].Value);
Console.WriteLine(attribute.Attributes["ows_firstName"].Value);
}
Console.ReadLine();
}
}
}