Excel Addin : SharePoint List data using Web Service

In this post I will tell you how a SharePoint List can be accessed through the web service. In this example I have a list name “LearningList”, which is accessed through the web service. I have accessed the list, shown the data from the list on to the excel sheet and updated the data using the same web service. To have the source code to the example Click here.

CODE SNIPPET

•  Create a new Custom List and name it “LearningList”.
•  Create the fields as shown in the image

• Create a new Visual Studio 2010 project, select the Office 2010 template and in it select the Excel 2010.
•  Now add the WPF User Control name it “BookUserControl.xaml”,  add a class name it “Books.cs” and add a User Control name it “HostingControl.cs”.
• After adding all the above file the code structure looks something like : 

•  Now double click the BookUserControl.xaml and add the 5 labels, 3 textbox, 2 buttons and 1 listbox, in the way as shown in picture below.

• While adding you will notice that the XAML code is being written, which defines the property of the controls added.
• Double click the Buttons to add the event handlers for each of them in the code behind (this will also update the XAML code).
•  Now add the service reference to the code. Right click the project and select the “Add Service Reference”.
•   A new pop window will open, click “Advanced àAdd Web Reference à type the url of the list service. Example: http://<server name>/_vti_bin/Lists.asmx
•  Click the go button, which will show all the available methods with the service,  name the Service reference (SPListWebService) and click on the Add Reference button to add the reference to the project.

• Now double click the Books.cs and add the following code.

    class Books

    {

        public string bookTitle { get; set; }

        public string bookAuthor { get; set; }

        public string bookPrice { get; set; }

        public string bookStockCount { get; set; }

    }

•  Open the BookUserControl.xaml.cs by double clicking it and add the following code:

public partial class BooksUserControl : UserControl

    {

        //Collection makes it easier to query and bind data to controls

        List<Books> myBooks = new List<Books>();

        public string ListName = "LearningList";

        public BooksUserControl()

        {

            InitializeComponent();

        }

        private void btnLoad_Click(object sender, RoutedEventArgs e)

        {

            lstBxBooksTitle.Items.Refresh();

            //Accessing the List Web Service : Provide Credentials and URL of the

list Web Service

            SPListsWebService.Lists myListProxy = new SPListsWebService.Lists();

            myListProxy.Credentials =

     System.Net.CredentialCache.DefaultCredentials;

            myListProxy.Url = "http://<server name>/_vti_bin/lists.asmx";

            //Get the list items by making call to the GetListItem() method

            try

            {

                XmlNode xmlListItems = myListProxy.GetListItems(ListName, null,

null, null, null, null, null);

                //XElement newRoot =  new XElement("

                foreach (XmlNode outerNode in xmlListItems.ChildNodes)

                {

                    if (outerNode.NodeType.Equals(System.Xml.XmlNodeType.Element))

                    {

                        foreach (XmlNode innerNode in outerNode.ChildNodes)

                        {

                            if (innerNode.NodeType.Equals(System.Xml.XmlNodeType.Element))

                            {

                                XmlNode listFieldTitle = innerNode.Attributes.GetNamedItem("ows_Title");

                                XmlNode listFieldAuthor = innerNode.Attributes.GetNamedItem("ows_Author0");

                                XmlNode listFieldPrice = innerNode.Attributes.GetNamedItem("ows_Price");

                                XmlNode listFieldInStock = innerNode.Attributes.GetNamedItem("ows_InStock");

                                Books objBooks = new Books();

                                objBooks.bookTitle = listFieldTitle.InnerText;

                                objBooks.bookAuthor = listFieldAuthor.InnerText;

                                objBooks.bookPrice = listFieldPrice.InnerText;

                                objBooks.bookStockCount = listFieldInStock.InnerText;

                                myBooks.Add(objBooks);

                                lstBxBooksTitle.Items.Add(objBooks.bookTitle);

                            }

                        }

                    }

                }

            }

            catch (Exception ex)

            {

                MessageBox.Show("Error Occured: " + ex.Message);

            }

        }

        private void btnUpdate_Click(object sender, RoutedEventArgs e)

        {

            Excel.Worksheet myWorksheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;

            int index = 0, temp = 0;

            string bookTitleUpdate = Convert.ToString(lstBxBooksTitle.SelectedItem);

            string bookAuthorUpdate = txtAuthor.Text;

            string bookPriceUpdate = txtPrice.Text;

            string bookInStockUpdate = txtStockCount.Text;

            foreach (Books objBooks in myBooks)

            {

                temp++;

                if (objBooks.bookTitle.Equals(bookTitleUpdate))

                {

                    index = temp;

                }

            }

            //Accessing the List Web Service : Provide Credentials and URL of the list Web Service

            SPListsWebService.Lists myListProxy = new SPListsWebService.Lists();

            myListProxy.Credentials = System.Net.CredentialCache.DefaultCredentials;

            myListProxy.Url = "http://br-pc-350:2789/_vti_bin/lists.asmx";

            //get the list id and the View id

            XmlNode xmlListView = myListProxy.GetListAndView(ListName, "");

            string ListId = xmlListView.ChildNodes[0].Attributes["Name"].Value.ToString();

            string ViewId = xmlListView.ChildNodes[1].Attributes["Name"].Value.ToString();

            //Create a XML for updating the list

            XmlDocument xmlDoc = new XmlDocument();

            XmlElement xmlBatch = xmlDoc.CreateElement("Batch");

            xmlBatch.SetAttribute("OnError", "Continue");

            xmlBatch.SetAttribute("ListVersion", "1");

            xmlBatch.SetAttribute("ViewName", ViewId);

            xmlBatch.InnerXml = "<Method ID='1' Cmd='Update'>" + "<Field Name='ID'>" + index + "</Field>" + "<Field Name='Title'>" + bookTitleUpdate + "</Field><Field Name='Author0'>" + bookAuthorUpdate +

                "</Field><Field Name='Price'>" + bookPriceUpdate + "</Field><Field Name='InStock'>" + bookInStockUpdate + "</Field></Method>";

            //Update the List using the Proxy

            try

            {

                XmlNode xmlReturn = myListProxy.UpdateListItems(ListName, xmlBatch);

                MessageBox.Show("List Item Updated Succesfully..!!");

            }

            catch (Exception ex)

            {

                MessageBox.Show("Error Occured " + ex.Message);

            }

        }

        private void lstBxBooksTitle_SelectionChanged(object sender, SelectionChangedEventArgs e)

        {

            string bookAuthor = null, bookPrice = null, bookStockCount = null;

            string selectedBook = Convert.ToString(lstBxBooksTitle.SelectedItem);

            //LinQ Query

            var bookData = from p in myBooks.Where(p => p.bookTitle == selectedBook)

                           select new { p.bookAuthor, p.bookPrice, p.bookStockCount };

            foreach (var tmp in bookData)

            {

                bookAuthor = tmp.bookAuthor;

                bookPrice = tmp.bookPrice;

                bookStockCount = tmp.bookStockCount;

            }

            Excel.Worksheet myWorkSheet = Globals.ThisAddIn.Application.ActiveSheet as Excel.Worksheet;

            myWorkSheet.Cells[1, 1] = "Title";

            myWorkSheet.Cells[1, 2] = "Author";

            myWorkSheet.Cells[1, 3] = "Price";

            myWorkSheet.Cells[1, 4] = "Stock Count";

            //Displaying the Data on to the Excel Sheet

            myWorkSheet.Cells[2, 1] = selectedBook;

            myWorkSheet.Cells[2, 2] = bookAuthor;

            myWorkSheet.Cells[2, 3] = bookPrice;

            myWorkSheet.Cells[2, 4] = bookStockCount;

            //Displaying the data in the text Box

            txtAuthor.Text = bookAuthor;

            txtPrice.Text = bookPrice;

            txtStockCount.Text = bookStockCount;

        }

    }

• Now compile the code by pressing the F6.
•  Now your WPF control will be available in the toolbox, drag and drop it on to the HostingControl user control. (as shown in in the picture below)

•  Now open the This.Addin.cs and add the following code :

public partial class ThisAddIn

    {

        HostingControl objHostingControl = new HostingControl();

        string hostingControlTitle = "Books Data";

        Office.CustomTaskPane ctp;

        private void ThisAddIn_Startup(object sender, System.EventArgs e)

        {

            ctp = this.CustomTaskPanes.Add(objHostingControl, hostingControlTitle);

            ctp.Visible = true;

            ctp.DockPosition = Microsoft.Office.Core.MsoCTPDockPosition.msoCTPDockPositionRight;

        }

        private void ThisAddIn_Shutdown(object sender, System.EventArgs e)

        {

        }

        #region VSTO generated code

        /// <summary>

        /// Required method for Designer support - do not modify

        /// the contents of this method with the code editor.

        /// </summary>

        private void InternalStartup()

        {

            this.Startup += new System.EventHandler(ThisAddIn_Startup);

            this.Shutdown += new System.EventHandler(ThisAddIn_Shutdown);

        }

       

        #endregion

    }

•   Now you can run your code which will appear somewhat like this:

·          

Using exception handling scope to handle errors in client object model : SharePoint 2010

While using client object model in SharePoint 2010, many a times we come across situations where in we call the
ctx.ExecuteQuery() method and some error occurs on the server due to which we get a 'ServerException' which does not give us the opportunity to make corrections in the operation that we are trying to do.

The ExceptionHandlingScope class models a try/catch behaviour for such situations.When we add the code within this scope,it is processed as if its present in a try/catch and gives the opportunity to correct the error in the catch/finally block.

For eg. lets take an example where in we are trying to update the description of a list using managed client object model.If the list does not exist in the target site, it will throw an exception.Now, in the catch block of the exception handling scope , we can create the list in the site and then update its description.

The code below demonstrates this:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Microsoft.SharePoint.Client;

namespace Sharepoint2010TestConsoleApp

{

    class Program

    {

        static void Main(string[] args)

        {

           using (ClientContext ctx = new ClientContext("http://br-pc-341:2222"))

            {

                //Set up error handling

                ExceptionHandlingScope xScope = new ExceptionHandlingScope(ctx);

                using (xScope.StartScope())

                 {

                    using (xScope.StartTry())

                    {

                        //Try to update the description of a list named "Test List"

                        List testList = ctx.Web.Lists.GetByTitle("Test List");

                        testList.Description = "Test List Description";

                        testList.Update();

                    }

                    using (xScope.StartCatch())

                    {

                        //Fails if the list "Test List" does not exist

                        //So, we'll create a new list

                        ListCreationInformation testListCI = new ListCreationInformation();

                        testListCI.Title = "Test List";

                        testListCI.TemplateType = (int)ListTemplateType.GenericList;

                        testListCI.QuickLaunchOption = Microsoft.SharePoint.Client.QuickLaunchOptions.On;

                        List list = ctx.Web.Lists.Add(testListCI);

                    }

                    using (xScope.StartFinally())

                    {

                        //Try to update the list now if it failed originally

                        List testList = ctx.Web.Lists.GetByTitle("Test List");

                        if (testList.Description.Length == 0)

                        {

                            testList.Description = "Test List Description";

                            testList.Update();

                        }

                    }

                }

                //Execute the entire try-catch as a batch!

                ctx.ExecuteQuery();

                Console.WriteLine("Description Updated !!");

            }

        }

    }

}

This console application updates the description of the list named 'Test List'. If the list does not exist , the list is
created in the catch block and then the description is updated.

The list with the updated description can be seen on the sharepoint site:

Making metadata fields of document library available in Search & Search results

Sometimes there may be a requirement, where we have exposed some metadata fields in a document library and we need those fields to be available when we use SharePoint search, and also in search results.

To achieve this, we need to carry out the following steps:

1. Create content type with metadata fields (This step has already been covered in my previous post - http://www.directsharepoint.com/2011/12/create-custom-content-type-with-custom.html#links)
2. Create managed properties for the metadata fields (for content type fields)
3. Run full crawl on the server so that the properties become available for mapping.
4. Map crawled properties to the Managed properties.

The code snippets for implementing the above are mentioned below:

//Get the default shared service provider                         

ServerContext sc = ServerContext.GetContext(SPContext.Current.Site);

PropertyInfo srpProp = sc.GetType().GetProperty(

    "SharedResourceProvider", BindingFlags.NonPublic | BindingFlags.Instance);

object srp = srpProp.GetValue(sc, null);

PropertyInfo srpNameProp = srp.GetType().GetProperty(

    "Name", BindingFlags.Public | BindingFlags.Instance);

string sspName = (string)srpNameProp.GetValue(srp, null);

//Get search Schema

ServerContext serverContext = ServerContext.GetContext(sspName);

SearchContext searchContext = SearchContext.GetContext(serverContext);

Schema schema = new Schema(searchContext);                   

Once we have the ssp and search schema details, we read the content type fields and create managed properties for the metadata.

//Getting the content type object

SPContentType siteCT = web.ContentTypes["CustomDocLibCT"];

//Getting the content type's fields

CTFields = siteCT.Fields;

//Loop on the content type fields to find the internal names of the columns

FindInternalNamesOfMetadata();                          

//Putting values in an array

InternalNamesArray = InternalNames.Split(';');

//Looping on the array

for (int i = 0; i < InternalNamesArray.Length; i++)

{

    string managedPropertyName = InternalNamesArray[i];

    string PropertyPublished = string.Format("ows_{0}",InternalNamesArray[i]);

    if(!schema.AllManagedProperties.Contains("MyCustom"+managedPropertyName))

    {

        ManagedProperty newManagedProperty = schema.AllManagedProperties.Create("MyCustom" + managedPropertyName, ManagedDataType.Text);

        //Get crawled property to map in the Managed Property            

        CrawledProperty cprop = null;

        foreach(CrawledProperty prop in schema.QueryCrawledProperties   (PropertyPublished, 1000, Guid.NewGuid(), string.Empty, true))

        {

            if (prop.Name == PropertyPublished)

            {

               cprop = prop;

               break;

            }

        }

        if (cprop != null)

        {

             // Map the crawled prop to the Managed Prop                  

             MappingCollection mappings = new MappingCollection();

             mappings.Add(new Mapping(cprop.Propset, cprop.Name, cprop.VariantType, newManagedProperty.PID));

             newManagedProperty.SetMappings(mappings);

             

             // Set Some other properties                  

             newManagedProperty.FullTextQueriable = true;

             newManagedProperty.EnabledForScoping = true;

             newManagedProperty.Update();

        }                                   

    }                                                          

}

//Create new managed property - All Metadata & Assign all metadata field crawled properties to it

createAllMetadataProperty(schema, InternalNamesArray);

InternalNamesArray = null;

InternalNames = string.Empty;

 The method createAllMetadataProperty is used to create a property that will search for all the metadata values collectively.

private void createAllMetadataProperty(Schema schema, string[] IntNamesArray)

{

    if (schema.AllManagedProperties.Contains("MyCustomAllMetadata"))

    {

        schema.AllManagedProperties["MyCustomAllMetadata "].DeleteAllMappings();

        schema.AllManagedProperties["MyCustomAllMetadata "].Delete();

    }

    string PropertyPublishedNew = string.Empty;

    MappingCollection mappings = new MappingCollection();

    //create managed property

    ManagedProperty newManagedProperty = schema.AllManagedProperties.Create("MyCustomAllMetadata ", ManagedDataType.Text);

   

    // Get the (crawled) property you want to map in the ManagedProperty            

    for (int i = 0; i < IntNamesArray.Length; i++)

    {

        PropertyPublishedNew = string.Format("ows_{0}", IntNamesArray[i]);

        foreach (CrawledProperty cprop in schema.QueryCrawledProperties(PropertyPublishedNew, 1000, Guid.NewGuid(), string.Empty, true))

        {

            // Map the crawled properties to the Managed Prop    

            mappings.Add(new Mapping(cprop.Propset, cprop.Name, cprop.VariantType,newManagedProperty.PID));

        }

    }

    newManagedProperty.SetMappings(mappings);

    // Set Some other properties                  

    newManagedProperty.FullTextQueriable = true;

    newManagedProperty.EnabledForScoping = true;

    newManagedProperty.Update();

    IntNamesArray = null;

}