Dotnetdreamer's Weblog

SharePoint, Silverlight and Azure

MOSS Web Services – Accessing Sharepoint List data

Posted by Ramprasad Navaneethakrishnan on June 4, 2009

Sharepoint 2007 provides rich set of API’s that allows developers to develop custom components in Sharepoint to meet custom requirements.

SharePoint API’s can be classified into two types.

  1. Sharepoint Object Model
  2. Sharepoint Web Services

Sharepoint Object Model enables developers to customize almost anything that is in Sharepoint right from creating a Site Collection to connecting to external data sources. This object model is enclosed in 10 dll’s with 30 namespaces. A series of articles on consuming Sharepoint object model can be found here and here.

In on of my previous posts, I wrote down the steps to access SharePoint list data using SharePoint object model and CAML queries.

Now, lets see how to consume the Sharepoint Web Services in order to retrieve the Sharepoint List Data.

Step By Step

  1. Create a new windows form project.
  2. Add Web Reference to Sharepoint Lists Web Service
    1. Right-click ‘Service Reference’ and select ‘Add Service Reference’.
    2. In the Add Service Reference dialog box, click Advanced button.
    3. In the ‘Service Reference settings’ dialog box, click Add Web Reference.
    4. In the Url dropdown box, enter the url of your sharepoint site’s web service. If you are developing in the same system as the system in which MOSS is installed, then you can find the web services by clicking the link ‘Web Services in this Solution’ available in the Add Web Reference dialog box.
      1. Note : The _vti_bin directory present under the sharepoint website directory lists down all the web services available.
    5. Scroll through the list of Web Services. Click in the ‘Lists’ web service. Check the url in the url column and ensure that this Lists web service corresponds to your sharepoint site.
    6. Lists Web Service
    7. In the Web Reference name text box, give a name to the Lists web reference. Lets name it ListsWS.
    8. Click Add Reference.
    9. AddWebReference
  3. Place a button in the form. Name it btnGetListItems and the Caption can be Get List Items. Double click the button and generate the event handler.
  4. The Lists web service contains an class called Lists. Lets create an object for this Lists class.
    1. ListsWS.Lists list = new ListsWS.Lists();
  5. The List object contains a method called GetListItems(). We need to invoke this method with appropriate parameters to get the items from the list
    1. The first parameter to this method is the name of the list.
    2. The remaining parameters are there to filter the list based on user defined criteria’s. Without any criteria, to fetch all the items from the list, the method should look like the following.
      1. list.GetListItems(“MyListName”, “”, null, null, “”, null,null);
      2. The signature of GetListItems in MSDN
  6. The return type of the GetListItems() is an Xml Node.
  7. The XmlNode’s inner xml looks like the following.
  8. We need to process the xml node in order to access the list values.
    1. Below, I have processed the xml to obtain the value of the column ‘Title’ that is present in the list and displayed it in a label.
  9. The entire code is given below

ListWS.Lists lists = new ListWS.Lists();
lists.UseDefaultCredentials = true;

    XmlNode nodes = lists.GetListItems(“MyList1”, “”, null, null, “”, null, null);foreach (XmlNode node in nodes)
    {
        if (node.Name == “rs:data”)
        {
            for (int i = 0; i < node.ChildNodes.Count; i++)
            {
                if (node.ChildNodes[i].Name == “z:row”)
                {
                   lblResults.Text =  node.ChildNodes[i].Attributes[“ows_Title”].Value;
                }
            }
        }
    }

Hope this helps.

Thanks.

Advertisements

22 Responses to “MOSS Web Services – Accessing Sharepoint List data”

  1. […] MOSS Web Services – Accessing SharePoint List data […]

  2. Sam said

    Thank you Ramprasad. I was exactly looking for this. That helped a lot.
    The only problem I am facing now is that if any field on the SharePoint list is null or blank and if I include that field in the viewField then the entire query fails.
    example:
    ListsWebService.Lists listService = new TestSharepoint.ListsWebService.Lists();
    listService.Credentials = System.Net.CredentialCache.DefaultCredentials;

    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”, “”);
    ndQueryOptions.InnerXml =”FALSE” +
    “TRUE”;
    ndViewFields.InnerXml = “”;

    ndQuery.InnerXml = “30”;
    try
    {
    XmlNode ndListItems =listService.GetListItems(“Employee”, null, ndQuery, ndViewFields, null, ndQueryOptions, null);
    }
    catch (System.Web.Services.Protocols.SoapException ex)
    {
    }
    Now, lets say if Employee Name (Employee_x0020_Name)in the list does not contain any data or is null then my query fails with the Exception-
    System.NullReferenceException: Object reference not set to an instance of an object.

    Do you see anything wrong in the code?

    Thank you,
    Sam

  3. Sam said

    I think one line got missed from the above code:
    {ndQueryOptions.InnerXml =
    “FALSE” +
    “TRUE”;}

  4. Sam said

    ndQueryOptions.InnerXml =
    “‘tagS’IncludeMandatoryColumns’tagE’FALSE’tagS’/IncludeMandatoryColumns’tagE'” +
    “‘tagS’DateInUtc’tagE’TRUE’tagS’/DateInUtc’tagE'”;
    replace:
    ‘tagS’ by

  5. Nirmal said

    hi,
    I am working on developing a excel add-in where it parses a raw excel sheet to a excel list. i am trying to synchronise the list with a sharepoint list. since i never worked on sharepoint web services, could you please let me know the possiblities in achieving it.
    p.s i have converted the raw data to list, only synchronise part is pending.

    Thanks in advance
    Regards,
    Nirmal

  6. Timothy Atwood said

    Nirmal

    Don’t know if you received your answer yet but there are many ways to achieve this goal. Can you further explain by sync? What are your scenarios such as:

    1) If item exists – update it
    2) If item exists in Excel but not in list, add it
    3) If item does not exist in Excel, but exists in Sharepoint – delete it

    Each one of these can be addressed quite easily:

    1) Match records based on unique value or values – grab the ID field as you will need it to update the list item
    2) If cannot find a match – can easily add with the New command
    3) Now, go the opposite route. Parse through SharePoint, and if it does not exist in Excel data base on the unique value or values, use Cmd Delete to delete the records, passing the SharePoint ID

    I prefer using Web Services as IT will never allow me to run code on their production servers directly.

    Some nuances – but fairly easy to do.

    Tim
    timothy.atwood@gmail.com

  7. Qadir said

    Nice article…We need to retirve data from external sql server database into the sharepoint lists. Can you please suggest the approach

  8. Jon R. said

    What’s truly driving me up a wall is this: Why in the heck doesn’t MS make SharePoint lists accessible in the same manner as MSSQL Databases? Why can’t I just add the resource as a data source, and thus have access to my list data in the same manner as I would any other database?
    Forcing us to go through convoluted (to us) XML parsing really isn’t very user friendly, especially when, with just a little effort on their part, we could get at it via “Add New Data Source” and have full functionality.
    Can you tell I hate all this XML parsing? Inner.xml, Outer.xml, context, blah blah blah. None of it makes sense, and frankly, it shouldn’t have to. They’ve missed a golden opportunity.

  9. sabih said

    Hi All,
    I have a set of webservices running in .net code .These webservices internally interact with Share point portal webservices in customised way .How can I interact with these webservices or write the business logic in Tibco BW or Java .How can I use the system libraries that are imported I have some to name a few.Please guide .

    Imports System.Xml
    Imports System.Text
    Imports System.Text.RegularExpressions
    Imports System.Data
    Imports System.Data.OleDb
    Imports System.Security
    Imports System.IO

    Regards,
    Sabih

  10. Jason F. said

    Jon R.,

    Wouldn’t that be awesome? Unfortunately, Microsoft repeatedly stresses to avoid direct modifications to the SharePoint database. If you’ve ever looked at the SharePoint DB, you would see why. Let’s just say it’s not your typical DB design. I’m sure they had their reasons, but i actually find the recommended methods easier to use than trying to puzzle through the SharePoint DB structure.

  11. sara said

    Nice work,
    Try this too with a query,
    SharePoint List Web Service GetListItems

  12. siva said

    Hi,
    Handsome article.. I want to clarrify a doubt… its possible to access the list data from sharepoint from outside (just like copy to another list or view data). But Whether it is possible to rewrite the same list or modify it… For example if duedate of that item is greater than todays date then can we change the color of that item to red or something like that… Is there any way to do this…?
    Thanks a lot for your suggestions…

  13. Dan said

    I am trying to use the above in VB, but I seem to be one level too high, only being able to access the rs:data level, but not the rows themselves. Code is below, I’ve put comments in for what names I am returning at the different stages.

    Dim nodeListItems As System.Xml.XmlNode = listService.GetListItems(listName, viewName, query, viewFields, rowLimit, queryOptions, Nothing)

    Dim s As String = “”

    For Each node As XmlNode In nodeListItems.ChildNodes
    If nodeListItems.Name = “rs:data” Then ‘here the Name is “listitems”
    For i As Integer = 0 To nodeListItems.ChildNodes.Count
    If nodeListItems.ChildNodes(i).Name = “z:row” Then ‘here the Name is “rs:data”
    s = nodeListItems.ChildNodes(i).Attributes(“ows_Title”).Value
    End If
    Next i
    End If
    Next node

    s assign is only temporary measure, so yes I know it doesn’t make sense.

    Any help appreciated.

  14. Dan said

    Ignore that, I realised the problem (using nodelistitems instead of node).

  15. Dan said

    Okay, but still having problems. I do not get past the first If statement (rs:data), the value of node.Name is #Whitespace. Any help appreciated.

    Dim nodeListItems As System.Xml.XmlNode = listService.GetListItems(listName, viewName, query, viewFields, rowLimit, queryOptions, Nothing)
    Dim s As String = “”
    For Each node As XmlNode In nodeListItems
    If node.Name = “rs:data” Then
    For i As Integer = 0 To nodeListItems.ChildNodes.Count
    If node.ChildNodes(i).Name = “z:row” Then
    s = node.ChildNodes(i).Attributes(“ows_Title”).Value
    End If
    Next i
    End If
    Next node

  16. harman said

    how to improve performance while accessing lists with attachments having items approx 1500 through webservice list.asmx
    i need to download attachment of item selected by user.

  17. Steve said

    I have been trying to get a GetListItems webservice call to work. Every time I execute a call I get the following error: The remote server returned an error: (500) Internal Server Error.

    I have reviewed the code many times and can’t find the error. This call should get the maximum ID in the list. Here is what I have:

    StringBuilder update = new StringBuilder();

    update.Append(“”);
    update.Append(“”);

    update.Append(“”);
    update.Append(“Shared Documents”);
    update.Append(“”);
    update.Append(“”);
    update.Append(“”);
    update.Append(“1”);
    update.Append(“FALSE”);
    update.Append(“”);
    update.Append(“”);

    update.Append(“”);
    update.Append(“”);

    HttpWebRequest req = (HttpWebRequest)WebRequest.Create(“http://sharepoint/sites/Corp/Legal/_vti_bin/Lists.asmx”);

    req.Headers.Add(“SOAPAction”, “http://schemas.microsoft.com/sharepoint/soap/GetListItems”);

    req.ContentType = “text/xml;charset=\”utf-8\””;
    req.Accept = “text/xml”;
    req.Method = “POST”;
    req.Credentials = new System.Net.NetworkCredential(“Administrator”, “filetrail”);

    Stream stm = req.GetRequestStream();

    XmlDocument doc = new XmlDocument();
    doc.LoadXml(update.ToString());
    doc.Save(stm);
    stm.Close();

    WebResponse resp = req.GetResponse();
    stm = resp.GetResponseStream();
    StreamReader r = new StreamReader(stm);
    string result = r.ReadToEnd();

  18. Steve said

    I have been trying to get a GetListItems webservice call to work. Every time I execute a call I get the following error: The remote server returned an error: (500) Internal Server Error.

    I have reviewed the code many times and can’t find the error. This call should get the maximum ID in the list. Here is what I have:

    StringBuilder update = new StringBuilder();
    
    update.Append("");
    update.Append("");
    
    update.Append("");
    update.Append("Shared Documents");
    update.Append("");
    update.Append("");
    update.Append("");
    update.Append("1");
    update.Append("FALSE"); 
    update.Append("");
    update.Append("");
    
    update.Append("");
    update.Append("");
    
    HttpWebRequest req = (HttpWebRequest)WebRequest.Create("http://sharepoint/sites/Corp/Legal/_vti_bin/Lists.asmx");
    
    req.Headers.Add("SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/GetListItems");
    
    req.ContentType = "text/xml;charset=\"utf-8\"";
    req.Accept = "text/xml";
    req.Method = "POST";
    req.Credentials = new System.Net.NetworkCredential("Administrator", "filetrail");
    
    Stream stm = req.GetRequestStream();
    
    XmlDocument doc = new XmlDocument();
    doc.LoadXml(update.ToString());
    doc.Save(stm);
    stm.Close();
    
    WebResponse resp = req.GetResponse();
    stm = resp.GetResponseStream();
    StreamReader r = new StreamReader(stm);
    string result = r.ReadToEnd();
    
    
  19. Steve said

    I have been trying to get a GetListItems webservice call to work. Every time I execute a call I get the following error: The remote server returned an error: (500) Internal Server Error.

    I have reviewed the code many times and can’t find the error. This call should get the maximum ID in the list. Here is what I have:


    StringBuilder update = new StringBuilder();

    update.Append("");
    update.Append("");

    update.Append("");
    update.Append("Shared Documents");
    update.Append("");
    update.Append("");
    update.Append("");
    update.Append("1");
    update.Append("FALSE");
    update.Append("");
    update.Append("");

    update.Append("");
    update.Append("");

    HttpWebRequest req = (HttpWebRequest)WebRequest.Create("http://sharepoint/sites/Corp/Legal/_vti_bin/Lists.asmx");

    req.Headers.Add("SOAPAction", "http://schemas.microsoft.com/sharepoint/soap/GetListItems");

    req.ContentType = "text/xml;charset=\"utf-8\"";
    req.Accept = "text/xml";
    req.Method = "POST";
    req.Credentials = new System.Net.NetworkCredential("Administrator", "filetrail");

    Stream stm = req.GetRequestStream();

    XmlDocument doc = new XmlDocument();
    doc.LoadXml(update.ToString());
    doc.Save(stm);
    stm.Close();

    WebResponse resp = req.GetResponse();
    stm = resp.GetResponseStream();
    StreamReader r = new StreamReader(stm);
    string result = r.ReadToEnd();

  20. anil said

    hi, i am new to sharepoint and this helped me alot. I would like to know how could these websevices be required in customer requirement with some example.

  21. zé luis said

    Anil, run. as fast as you can. sharepoint sucks. its the worse CMS ever.

  22. Addis Abebayehu said

    How do you get sharepoint list data from Flash as3? Please help. Thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: