SharePoint provides types and members in the Microsoft.SharePoint.Client namespace for accessing SharePoint remotely from a client computer or server. To get started with SharePoint client object model you will need Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Runtime assemblies. You can copy these DLLs from the %ProgramFiles%\Common Files\Microsoft Shared\web server extensions\14\ISAPI folder of the server that runs Microsoft SharePoint Foundation 2010.

Using the Microsoft.SharePoint.Client types you can get access to SharePoint List data. However in order to have design time support (Telerik Reporting Data Explorer, Wizards and Expression editor) with SharePoint as data source, you will need the following SharePointDataSource helper class. This class manages the SharePoint credentials, list schema and data retrieval. Just add the SharePointDataSource class to you project and build it. Then you can use it in Telerik Reporting ObjectDataSource as DataSource type and GetListData method as DataMember. The GetListData() method is parametrized with the required SharePoint credentials, List name and SharePoint URL thus you can directly set them in the ObjectDataSource wizard.

using System.Data;
using System.Net;
using System.Collections.Generic;
using Microsoft.SharePoint.Client;
 
namespace Telerik.Reporting
{
    class SharePointDataSource
    {
        /// <summary>
        /// Stores the list schema and data
        /// </summary>
        DataTable ListData { get; set; }
 
        /// <summary>
        /// Used by ObjectDataSource for retrieving the specified SP list data
        /// </summary>
        /// <param name="spFullUrl">SharePoint full URL</param>
        /// <param name="listName">List name</param>
        /// <param name="username">username</param>
        /// <param name="password">password</param>
        /// <param name="domain">domain</param>
        /// <returns>SharePoint List data as DataTable</returns>
        public DataTable GetListData(string spFullUrl,
                          string listName,
                          string username,
                          string password,
                          string domain)
        {
            var context = GetContext(spFullUrl,
                             username,
                             password,
                             domain);
            // build the CAML query to get ALL items
            var query = new CamlQuery();
            query.ViewXml = "";
            var list = context.Web.Lists.GetByTitle(listName);
            ListItemCollection items = list.GetItems(query);
            items.RetrieveItems().Retrieve();
            context.ExecuteQuery();
 
            ListData = new DataTable("ListData");
 
            var keys = new List<string>(InitializeSchema(items));
            FillDataTable(items,
                     keys);
 
            context.Dispose();
            return ListData;
        }
 
        void FillDataTable(ListItemCollection items,
                    List<string> keys)
        {
            foreach (ListItem item in items)
            {
                var rowValues = new object[keys.Count];
                var counter = 0;
                foreach (var key in keys)
                {
                    rowValues[counter++] = item.FieldValues[key];
                }
                ListData.LoadDataRow(rowValues, true);
            }
        }
 
        IEnumerable<string> InitializeSchema(ListItemCollection items)
        {
            var firstRow = items[0].FieldValues;
            foreach (var key in firstRow.Keys)
            {
                if (null != firstRow[key])
                {
                    ListData.Columns.Add(key, firstRow[key].GetType());
                    yield return key;
                }
            }
        }
 
        static ClientContext GetContext(string spFullUrl,
                             string username,
                             string password,
                             string domain)
        {
            var context = new ClientContext(spFullUrl);
            var credetentials = new NetworkCredential(username,
                                       password,
                                       domain);
            context.Credentials = credetentials;
            return context;
        }
    }
}

Check out SharePointDataSource.zip sample project that illustrates the suggested approach. In order to run it you have to reference the Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Runtime assemblies. Additionally you have to update the ObjectDataSource parameters with your SharePoint URL, credentials and list name.


About the Author

Peter Raykov

is a Software Developer in the Telerik Reporting division.

Related Posts

Comments