SharePoint Web Services and DataSet: How to Connect to Lists Web Service, Get Item and Attachment Information through DataSet

SharePoint 2007 and SharePoint 2010 contains out-of-the-box web services that allows you to get item information from document libraries and custom lists in an external program. In order to connect to the web service follow these steps:

1. Add a web reference to the Lists web service of your site or site collection in your Visual Studio project. The Lists web service is accessible at http://your_sharepoint_site/_vti_bin/Lists.asmx in your SharePoint site.
   
   Note: Make sure that you are adding reference to the Lists web service on site collection level or sub-site level and not the list level. If you add web reference on the list level for e.g., http://your_sharepoint_site/lists/mylist/_vti_bin/Lists.asmx., you will get errors.
2. Create the list web service object.
3. Set the Network Credentials to be used when calling the web service.
4. Get the list ID by editing your list, and copying the GUID from URL. You can decode the URL by going to http://meyerweb.com/eric/tools/dencoder/.
5. Get the view ID by editing your view in the list and copying the view GUID from URL.
6. Set the fields you want to review from the view.
7. Set the query options to filter the results. This is done via CAML.
8. Call the GetListItems() method to retrieve item information.
9. Convert the XmlNode retrieved from GetListItems() method to a DataSet object. I found this method somewhere on the Internet, many thanks to the author!
10. At this point you have two options: either bind the results to your grid or loop through the list item information.

I wrote a small console program for demonstration. Fee free to use the code as needed!

Blogger Labels: SharePoint,Services,DataSet,Connect,Lists,Service,Item,Attachment,Information,libraries,custom,reference,collection,Visual,Studio,_vti_bin,Note,Make,errors,Create,Network,Credentials,GUID,tools,options,CAML,Call,GetListItems,method,Convert,XmlNode,Internet,author,grid

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Text;

using WSDemo.MySPList;

using System.Net;

using System.Security;

using System.Xml;

namespace SPWSDemo

{

    class Program

    {

        static void Main(string[] args)

        {

            //Connect to the service. The service reference is added as web reference

            MySPList.Lists lstSvc = new MySPList.Lists();

            //seting the URL is a must otherwise it may through exception

            lstSvc.Url = "http://myspsiteurl/sites/demositecol/_vti_bin/lists.asmx";

            //Set network credentials

            NetworkCredential cr = new NetworkCredential("DOMAIN\\User", "Your_Password");

            lstSvc.Credentials = cr;

            lstSvc.PreAuthenticate = true;

            XmlDocument doc = new XmlDocument();

            XmlElement query = doc.CreateElement("Query");

            XmlElement viewFields = doc.CreateElement("ViewFields");

            XmlElement queryOptions = doc.CreateElement("QueryOptions");

            XmlElement folder = doc.CreateElement("Folder");

            queryOptions.AppendChild(folder);

            string listID = "A48C699B-84B0-4ED8-BDA9-18B7713E83C5";

            string viewID = "2A05F988-0CF1-4981-B1A8-B4117137DE9D";

            //viewFields.InnerXml = "<ViewFields><FieldRef Name=\"Some Field\" /></ViewFields>";

            //_x0020_ is the XML equivalent for whitespace. If your column names have space, you have to use this.

            queryOptions.InnerXml = "<QueryOptions><OrderBy><FieldRef Name=\"_x0020_\" Ascending=\"False\" /></OrderBy></QueryOptions>";

            //First parameter is list GUID, can be obtained by editing the list and getting values from the URL

            //First parameter is view GUID, can be obtained by editing the view and getting values from the URL 

            XmlNode allItemsView = lstSvc.GetListItems(listID,

         viewID, query, viewFields, null, queryOptions, null);

            //Convert the XML to Dataset so that data manipulation is easier

            DataSet ds = ConvertToDataSet(allItemsView);

            foreach (DataTable dt in ds.Tables)

            {

                Console.WriteLine("Table Name:" + dt.TableName);

                foreach (DataRow dr in dt.Rows)

                {

                    //The service will return lots of fields including internal SharePoint fields. The field name will be in the format ows_[Column Name]

                    for (int i = 0; i < dt.Columns.Count; i++)

                    {

                        Console.WriteLine(dt.Columns[i].ColumnName + "=" + dr[i].ToString());

                    }

                    //attachments

                    if (dt.TableName == "row")

                    {

                        Console.WriteLine("=====================\r\nAttachments\r\n=========================");

                        if (dr["ows_Attachments"] != null)

                        {

                            XmlNode attachmentNode = lstSvc.GetAttachmentCollection(listID, dr["ows_ID"].ToString());

                            DataSet dsAttachment = ConvertToDataSet(attachmentNode);

                            foreach (DataTable dtAttachment in dsAttachment.Tables)

                            {

                                foreach (DataRow drAttachment in dtAttachment.Rows)

                                {

                                    for (int k = 0; k < dtAttachment.Columns.Count; k++)

                                    {

                                        Console.WriteLine(dtAttachment.Columns[k].ColumnName + "=" + drAttachment[k].ToString());

                                    }

                                }

                            }

                        }

                    }

                }

            }

            Console.ReadLine();

        }

        public static DataSet ConvertToDataSet(XmlNode xmlnodeinput)

        {

            //declare data set object

            DataSet dataset = null;

            if (xmlnodeinput != null)

            {

                XmlTextReader xtr = new XmlTextReader(xmlnodeinput.OuterXml, XmlNodeType.Element, null);

                dataset = new DataSet();

                dataset.ReadXml(xtr);

            }

            return dataset;

        }

    }

}