Code Snippet: Get Item Data from an External List on the Client

Applies to: SharePoint Server 2010

In this article
Description
Prerequisites
To use this example

Description

The following code snippet shows how to retrieve list item data from an external list from the client computer using the SharePoint Client object model.

Prerequisites

• Microsoft SharePoint Server 2010 or Microsoft SharePoint Foundation 2010 on the server.

• At least one external list on the server.

• Microsoft Office Professional Plus 2010 and Microsoft .NET Framework 3.5 on the client computer.

• Microsoft Visual Studio.

To use this example

1. Start Visual Studio on the client computer and create a C# Console application project. Select .NET Framework 3.5 when you create the project.

2. From the View menu, click Property Pages to bring up the project properties.

3. In the Build tab, for the Platform target, select Any CPU.

4. Close the project properties window.

5. In Solution Explorer, under References, remove all project references except for System and System.Core.

6. Add the following references to the project:

   1. Microsoft.SharePoint.Client

   2. Microsoft.SharePoint.Client.Runtime

   3. System.Data.Linq

   4. System.XML

7. Replace the autogenerated code in Program.cs with the code listed at the end of this procedure.

8. Replace the values of <TargetSiteUrl> and <TargetListName> with valid values.

9. Save the project.

10. Compile and run the project.

using System;
using Microsoft.SharePoint.Client;
using System.Linq.Expressions;
using System.Collections.Generic;
using System.Xml;
using System.IO;
using System.Text;
using System.Globalization;

namespace Microsoft.SDK.Sharepoint.Samples
{
    /// <summary>
    /// This example shows how to retrieve list item data 
    /// from an external list.
    /// 
    /// You'll need to explicitly specify the field data in both
    /// the CAML query and also ClientContext.Load.
    /// </summary>
    class Program
    {
        // Note: Replace these with your actual Site URL and List name.
        private static string TargetSiteUrl = "<TargetSiteUrl>";
        private static string TargetListName = "<TargetListName>";

        /// <summary> 
        /// Example to show using CSOM to retrieve external List data.        
        /// </summary>        
        static void Main(string[] args)
        {
            ClientContext clientContext = new ClientContext(TargetSiteUrl);

            List externalList = clientContext.Web.Lists.GetByTitle(
                TargetListName);

            // To properly construct the CamlQuery and 
            // ClientContext.LoadQuery,
            // we need some View data of the Virtual List.
            // In particular, the View will give us the CamlQuery 
            // Method and Fields.
            clientContext.Load(
                externalList.Views,
                viewCollection => viewCollection.Include(
                    view => view.ViewFields,
                    view => view.HtmlSchemaXml));

            // This tells us how many list items we can retrieve.
            clientContext.Load(clientContext.Site,
                s => s.MaxItemsPerThrottledOperation);

            clientContext.ExecuteQuery();

            // Let's just pick the first View.
            View targetView = externalList.Views[0];
            string method = ReadMethodFromViewXml(
                targetView.HtmlSchemaXml);
            ViewFieldCollection viewFields = targetView.ViewFields;

            CamlQuery vlQuery = CreateCamlQuery(
                clientContext.Site.MaxItemsPerThrottledOperation,
                method,
                viewFields);

            Expression<Func<ListItem, object>>[] listItemExpressions =
                CreateListItemLoadExpressions(viewFields);

            ListItemCollection listItemCollection =
                externalList.GetItems(vlQuery);

            // Note: Due to limitation, you currently cannot use 
            // ClientContext.Load.
            //       (you'll get InvalidQueryExpressionException)
            IEnumerable<ListItem> resultData = clientContext.LoadQuery(
                listItemCollection.Include(listItemExpressions));

            clientContext.ExecuteQuery();

            foreach (ListItem li in resultData)
            {
                // Now you can use the ListItem data!

                // Note: In the CamlQuery, we specified RowLimit of 
                // MaxItemsPerThrottledOperation.
                // You may want to check whether there are other rows 
                // not yet retrieved.                
            }
        }

        /// <summary>
        /// Parses the viewXml and returns the Method value.
        /// </summary>        
        private static string ReadMethodFromViewXml(string viewXml)
        {
            XmlReaderSettings readerSettings = new XmlReaderSettings();
            readerSettings.ConformanceLevel = ConformanceLevel.Fragment;

            XmlReader xmlReader = XmlReader.Create(
                new StringReader(viewXml), readerSettings);
            while (xmlReader.Read())
            {
                switch (xmlReader.NodeType)
                {
                    case XmlNodeType.Element:
                        if (xmlReader.Name == "Method")
                        {
                            while (xmlReader.MoveToNextAttribute())
                            {
                                if (xmlReader.Name == "Name")
                                {
                                    return xmlReader.Value;
                                }
                            }
                        }
                        break;
                }
            }

            throw new Exception("Unable to find Method in View XML");
        }

        /// <summary>
        /// Creates a CamlQuery based on the inputs.
        /// </summary>        
        private static CamlQuery CreateCamlQuery(
            uint rowLimit, string method, ViewFieldCollection viewFields)
        {
            CamlQuery query = new CamlQuery();

            XmlWriterSettings xmlSettings = new XmlWriterSettings();
            xmlSettings.OmitXmlDeclaration = true;

            StringBuilder stringBuilder = new StringBuilder();
            XmlWriter writer = XmlWriter.Create(
                stringBuilder, xmlSettings);

            writer.WriteStartElement("View");

            // Specifies we want all items, regardless of folder level.
            writer.WriteAttributeString("Scope", "RecursiveAll");

            writer.WriteStartElement("Method");
            writer.WriteAttributeString("Name", method);
            writer.WriteEndElement();  // Method

            if (viewFields.Count > 0)
            {
                writer.WriteStartElement("ViewFields");
                foreach (string viewField in viewFields)
                {
                    if (!string.IsNullOrEmpty(viewField))
                    {
                        writer.WriteStartElement("FieldRef");
                        writer.WriteAttributeString("Name", viewField);
                        writer.WriteEndElement();  // FieldRef
                    }
                }
                writer.WriteEndElement();  // ViewFields
            }

            writer.WriteElementString(
                "RowLimit", rowLimit.ToString(CultureInfo.InvariantCulture));

            writer.WriteEndElement();  // View

            writer.Close();

            query.ViewXml = stringBuilder.ToString();

            return query;
        }

        /// <summary>
        /// Returns an array of Expression used in 
        /// ClientContext.LoadQuery to retrieve 
        /// the specified field data from a ListItem.        
        /// </summary>        
        private static Expression<Func<ListItem, object>>[]
            CreateListItemLoadExpressions(
            ViewFieldCollection viewFields)
        {
            List<Expression<Func<ListItem, object>>> expressions =
                new List<Expression<Func<ListItem, object>>>();

            foreach (string viewFieldEntry in viewFields)
            {
                // Note: While this may look unimportant, 
                // and something we can skip, in actuality,
                //       we need this step.  The expression should 
                // be built with local variable.                
                string fieldInternalName = viewFieldEntry;

                Expression<Func<ListItem, object>>
                    retrieveFieldDataExpression =
                    listItem => listItem[fieldInternalName];

                expressions.Add(retrieveFieldDataExpression);
            }

            return expressions.ToArray();
        }
    }
}