Read SharePoint Online list from ASP.NET

Today, IT world is all about connecting systems, so to connect an ASP.NET Web Forms based website to SharePoint Online can be a usual task for developers. This approach is classic and you can say is old fashion. We can say Web Forms are deprecate, but this is not true and this will be another topic on my blog.

But, let’s start with the beginning and present the steps you need for this.

Add required libraries to site

To be able to connect to SharePoint Online, or on Premise as well, you need to import some libraries into your website, or project if you decide to go for an ASP.NET project. These can be found on Microsoft site under the name of “SharePoint Server 2013 Client Components SDK” and can be downloaded from this address. Just copy and page these libraries to Bin folder you will enable SharePoint client objects into your code.

Create class to perform data operations

As you have imported required libraries, you can perform operations against a SharePoint list anywhere in your code. However, as you probably want a little bit of code organization, I would suggest you to create a class dedicated for this. I have called it “SharePoint”, which is probably not the best name, as it can create confusion.

using System;
using System.Collections.Generic;
using System.Security;
using System.Linq;
using System.Web;
using Microsoft.SharePoint.Client;

/// <summary>
/// Summary description for SharePoint
/// </summary>
public class SharePoint : IDisposable
{
    string username;
    SecureString password;
    string url;
    ClientContext context;

	public SharePoint(string url, string username, string password)
	{
        this.url = url;
        this.password = password.ToSecureString();
        this.username = username;
        this.context = new ClientContext(this.url);
        this.context.Credentials = new SharePointOnlineCredentials(this.username, this.password);
	}

  

   /// <summary>
   /// Get list items (method overloaded)
   /// </summary>
   /// <param name="listName">List name</param>
   /// <returns></returns>
    public Microsoft.SharePoint.Client.ListItemCollection GetListItems(string listName)
    {
        return this.GetListItems(listName, CamlQuery.CreateAllItemsQuery());
    }

    /// <summary>
    /// Get list items (method overloaded)
    /// <param name="listName">List name</param>
    /// <param name="camlQuery">CAML query</param>
    /// <returns></returns>
    public Microsoft.SharePoint.Client.ListItemCollection GetListItems(string listName, string camlQuery)
    {
        var oQuery = new CamlQuery();
        oQuery.ViewXml = camlQuery;
        return this.GetListItems(listName, oQuery);
    }

    /// <summary>
    /// Get list items (method overloaded)
    /// </summary>
    /// <param name="listName">List name</param>
    /// <param name="camlQuery">CAML query</param>
    /// <returns></returns>
    public Microsoft.SharePoint.Client.ListItemCollection GetListItems(string listName, CamlQuery camlQuery)
    {
        Web oWeb = context.Site.RootWeb;
        List oList = oWeb.Lists.GetByTitle(listName);
        Microsoft.SharePoint.Client.ListItemCollection oItems = oList.GetItems(camlQuery);
        context.Load(oItems);
        context.ExecuteQuery();
        return oItems;
    }
    
    void IDisposable.Dispose()
    {
        this.context.Dispose();
    }


    public static string GetFieldValue(object field = null)
    {
        if (field == null)
            return "";
        
        var fieldType = field.GetType().ToString();
       
    
        // user field
        if (fieldType  == "Microsoft.SharePoint.Client.FieldUserValue")
        {
            return ((Microsoft.SharePoint.Client.FieldUserValue)field).LookupValue;
        }
        // int32
        else if (fieldType == "System.Int32")
        {
            return Convert.ToString(field);
        }
        // string
        else if (fieldType == "System.String")
        {
            return Convert.ToString(field);
        }
        // Lookup field value
        else if (fieldType == "Microsoft.SharePoint.Client.FieldLookupValue") {
            return Convert.ToString(((Microsoft.SharePoint.Client.FieldLookupValue)field).LookupValue);
        }
        // date time
        else if(fieldType == "System.DateTime")
        {
            return Convert.ToDateTime(field).ToLongDateString();
        }
        else if (fieldType == "Microsoft.SharePoint.Client.ContentTypeId") {
            return ((Microsoft.SharePoint.Client.ContentTypeId)field).StringValue;
        }
        else
        {
            // return Convert.ToString(field);
            return fieldType;
        }   
    }
}

The class contains an overloaded method called “GetListItems” and a static method used for converting field values into string based on their types. There is a lot you can improve here, but it doesn’t fall under this post subject. Also we are not going to use for this example all signatures of “GetListItems” method, but for future use you can keep this method overloading.

Create Ajax enabled WCF service to communicate with SharePoint

What you need now is WCF service to communicate with SharePoint. I choose an Ajax enabled one because I wanted to use it through script manager. But if you prefer, you can use a non-Ajax enabled one to consume it using jQuery.

When you actually execute a query against a SharePoint list, you obtain a ListItemCollection object. As far as I know it cannot be de-serialized by default, so you will need to implement this. So you can create following classes to serve as data contract for the web service.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ServiceModel;
using System.Runtime.Serialization;


/// <summary>
/// Represent list item in a collection returned by WCF service
/// </summary>
[CollectionDataContract(Name = "SharePointListItem")]
public class SharePointListItem : Dictionary<string, string>
{
  
}


/// <summary>
/// Represents a collection of SharePoint list items returned by WCF service
/// </summary>
[CollectionDataContract(Name = "SharePointListItemCollection")]
public class SharePointListItemCollection : List<SharePointListItem>
{

}

Implementation of WCF service is simple. In our case, not to complicate the explanations too much, I have created only one method called “GetListItems”, which will get all items for specified list.

using System;
using System.Configuration;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.ServiceModel.Web;
using System.Text;
using System.Xml;
using System.Web.Script.Serialization;
using Microsoft.SharePoint.Client;

[ServiceContract(Namespace = "")]
[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class ListData
{
	// To use HTTP GET, add [WebGet] attribute. (Default ResponseFormat is WebMessageFormat.Json)
	// To create an operation that returns XML,
	//     add [WebGet(ResponseFormat=WebMessageFormat.Xml)],
	//     and include the following line in the operation body:
	//         WebOperationContext.Current.OutgoingResponse.ContentType = "text/xml";



    [WebGet(ResponseFormat=WebMessageFormat.Json)]
    public SharePointListItemCollection GetListItems(string listName)
    {
        string url = ConfigurationManager.AppSettings["spUrl"];
        string username = ConfigurationManager.AppSettings["spUsername"];
        string password = ConfigurationManager.AppSettings["spPassword"];
        SharePointListItemCollection results = new SharePointListItemCollection();
        using(SharePoint sp = new SharePoint(url,username, password))
        {
        
            Microsoft.SharePoint.Client.ListItemCollection items = sp.GetListItems(listName);
            foreach (ListItem item in items)
            {
                SharePointListItem r = new SharePointListItem();
                foreach (KeyValuePair<string, object> field in item.FieldValues)
                {
                    // SharePointItemField f = new SharePointItemField();
                    // f.FieldName = field.Key;
                    // f.FieldValue = SharePoint.GetFieldValue(field.Value);
                    r.Add(field.Key, SharePoint.GetFieldValue(field.Value));
                }
                results.Add(r);
            };
          
           
        }
        return results;
    }


}

As you noticed, SharePoint class is initialized with 3 parameters, and we are taking these from application settings. To you need to add them in web.config.

  <appSettings>
    <add key="spUsername" value="username" />
    <add key="spPassword" value="password" />
    <add key="spUrl" value="https://url" />
  </appSettings>

Add reference to the WCF service

As the WCF service is Ajax enabled, you need to add a reference to it script manager tag. This will generate a JavaScript for you and will eliminate time waste for writing Ajax calls to the service.

    <asp:ScriptManager runat="server" EnablePartialRendering="true" LoadScriptsBeforeUI="false" EnablePageMethods="true">
        <Services>
            <asp:ServiceReference Path="~/_Services/ListData.svc" />
        </Services>
     </asp:ScriptManager>

Getting the data

And this is the last step, getting the data from SharePoint list. This will take place when page loads to prevent performance issues rendering the mark-up.

function pageLoad(source, args) {
    var service = new ListData();
    service.GetListItems('List name',
        // Success
        function (response) {
            // Do something with JSON response
        },
        // Fail
        function (response) {
            alert(args.get_message());
        });
}

The response is JSON type, so it is up to you what you are going to do with it. My suggestion is to use JavaScript template engine to display it on the page.

Advertisements

Getting list items from SharePoint Online to Windows phone

Getting data from SharePoint Online to Windows phone is not too complicated. But I am referencing here only projects targeting Windows 8.0, as Microsoft supply a SharePoint SDK only for this platform and I am not aware yet of a SDK targeting Windows 8.1.

As a concrete example, I have created an announcement list called “Company News” in a SharePoint Online environmental and I trying to display it in a client mobile application. The structure of announcement list is simple, being only interested in getting only 3 columns: Title (single line of text), Body (plain text), and Category (choice field).

I will start presenting structure of my project, which is a Silverlight blank application for Windows phone, and explain it step by step.

Sprint Structure

CompanyUpdate.cs is a simple class representing data structure of my SharePoint list. A list of CompanyUpdate objects will be used as source for a longlistselector control.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace SPRInt
{
    class CompanyUpdate
    {
        public string Title { get; set; }
        public string Category { get; set; }
        public string Body { get; set; }

    }
}

ExtensionMethods.cs contains a single static class with a single extension method, designed to replace br tags with /n and strip all other HTML tags. That’s because content of Body column contains HTML tags but my application is not HTML based.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace SPRInt
{
     public static class ExtendedMethods
    {
         public static string StripTags (this string input)
         {
             string _input = Regex.Replace(input, @&amp;amp;amp;amp;amp;quot;(&amp;amp;amp;amp;amp;lt;br */&amp;amp;amp;amp;amp;gt;)|(\[br */\])&amp;amp;amp;amp;amp;quot;, &amp;amp;amp;amp;amp;quot;\n&amp;amp;amp;amp;amp;quot;);
             _input = Regex.Replace(_input, @&amp;amp;amp;amp;amp;quot;(&amp;amp;amp;amp;amp;lt;.*?&amp;amp;amp;amp;amp;gt;)&amp;amp;amp;amp;amp;quot;, String.Empty;
             return _input;
         }

    }
}

We are moving now to ListOperations.cs , which is actually the core of the application.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Microsoft.SharePoint.Client;
using Microsoft.SharePoint.Client.Phone;

namespace SPRInt
{
    class ListOperations
    {
        string url;

        /// &amp;amp;amp;amp;amp;lt;summary&amp;amp;amp;amp;amp;gt;
        /// List operations constructor
        /// &amp;amp;amp;amp;amp;lt;/summary&amp;amp;amp;amp;amp;gt;
        /// &amp;amp;amp;amp;amp;lt;param name=&amp;amp;amp;amp;amp;quot;url&amp;amp;amp;amp;amp;quot;&amp;amp;amp;amp;amp;gt;&amp;amp;amp;amp;amp;lt;/param&amp;amp;amp;amp;amp;gt;
        public ListOperations(string url)
        {
            this.url = url;
        }

        /// &amp;amp;amp;amp;amp;lt;summary&amp;amp;amp;amp;amp;gt;
        /// Get list items
        /// &amp;amp;amp;amp;amp;lt;/summary&amp;amp;amp;amp;amp;gt;
        /// &amp;amp;amp;amp;amp;lt;param name=&amp;amp;amp;amp;amp;quot;listName&amp;amp;amp;amp;amp;quot;&amp;amp;amp;amp;amp;gt;&amp;amp;amp;amp;amp;lt;/param&amp;amp;amp;amp;amp;gt;
        public void GetItems(string listName, Action&amp;amp;amp;amp;amp;lt;List&amp;amp;amp;amp;amp;lt;CompanyUpdate&amp;amp;amp;amp;amp;gt;&amp;amp;amp;amp;amp;gt; callback)
        {
            Authenticator sprintAuthenticator = new Authenticator();
            sprintAuthenticator.AuthenticationMode = ClientAuthenticationMode.MicrosoftOnline;
            sprintAuthenticator.CookieCachingEnabled = true;

            ClientContext ctx = new ClientContext(this.url);
            ctx.Credentials = sprintAuthenticator;

                List spList = ctx.Web.Lists.GetByTitle(listName);
                CamlQuery query = CamlQuery.CreateAllItemsQuery();
                ListItemCollection items = spList.GetItems(query);
                ctx.Load(items);

                ctx.ExecuteQueryAsync(
                    (Object sender, ClientRequestSucceededEventArgs args) =&amp;amp;amp;amp;amp;gt; {
                        List&amp;amp;amp;amp;amp;lt;CompanyUpdate&amp;amp;amp;amp;amp;gt; companyUpdates = new List&amp;amp;amp;amp;amp;lt;CompanyUpdate&amp;amp;amp;amp;amp;gt;();
                        foreach (ListItem item in items)
                        {
                            CompanyUpdate cUpdate = new CompanyUpdate();
                            cUpdate.Title = Convert.ToString(item[&amp;amp;amp;amp;amp;quot;Title&amp;amp;amp;amp;amp;quot;]);
                            cUpdate.Category = Convert.ToString(item[&amp;amp;amp;amp;amp;quot;Category&amp;amp;amp;amp;amp;quot;]);
                            cUpdate.Body = Convert.ToString(item[&amp;amp;amp;amp;amp;quot;Body&amp;amp;amp;amp;amp;quot;]).StripTags();
                            companyUpdates.Add(cUpdate);
                        }
                        callback(companyUpdates);
                    },
                    (Object sender, ClientRequestFailedEventArgs args) =&amp;amp;amp;amp;amp;gt; {

                    }
                );

        }

    }
}

You can note I am using Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Phone namespaces in the file, these being available because I have SharePoint SDK for Windows phone libraries to my project. Being now able to use classes coming from these namespace, I can make use of Authenticator class (responsible to authenticate me with SharePoint Online) and other client object model classes. Class is instantiated with url of our SharePoint and has a method called GetItems which accepts two parameters: the name of the list and an action which serves as callback method.

It remains MainPage.xaml and associated cs file. There is nothing complicated with this file. It is a simple Silverlight page containing a LongListSelector. When page is loaded, data is getting from the SharePoint list and displayed into the LongListSelector.

<phone:PhoneApplicationPage
 x:Class="SPRInt.MainPage"
 xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
 xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
 xmlns:phone="clr-namespace:Microsoft.Phone.Controls;assembly=Microsoft.Phone"
 xmlns:shell="clr-namespace:Microsoft.Phone.Shell;assembly=Microsoft.Phone"
 xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
 xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
 mc:Ignorable="d"
 FontFamily="{StaticResource PhoneFontFamilyNormal}"
 FontSize="{StaticResource PhoneFontSizeNormal}"
 Foreground="{StaticResource PhoneForegroundBrush}"
 SupportedOrientations="Portrait" Orientation="Portrait"
 shell:SystemTray.IsVisible="True" Loaded="PhoneApplicationPage_Loaded">

 <!--LayoutRoot is the root grid where all page content is placed-->
 <Grid x:Name="LayoutRoot" Background="Transparent">
 <Grid.RowDefinitions>
 <RowDefinition Height="Auto"/>
 <RowDefinition Height="*" />
 </Grid.RowDefinitions>



 <!--TitlePanel contains the name of the application and page title-->
 <StackPanel x:Name="TitlePanel" Grid.Row="0" Margin="12,17,12,28">
 <TextBlock Text="SPRInt for mobile" Style="{StaticResource PhoneTextNormalStyle}" />
 <TextBlock Text="News" Style="{StaticResource PhoneTextTitle1Style}"/>
 
 </StackPanel>

 <!-- Data displayed here -->
 <phone:LongListSelector Grid.Row="1" Name="lstCompanyNews" Margin="12,0,12,0">
 <phone:LongListSelector.ItemTemplate>
 <DataTemplate>
 <StackPanel Orientation="Vertical">
 <TextBlock Text="{Binding Title}" Style="{StaticResource companyupdatetitle}" />
 <TextBlock Text="{Binding Category}" Foreground="AliceBlue" />
 <TextBlock Text="{Binding Body}" Foreground="AliceBlue" />
 </StackPanel> 
 </DataTemplate>
 </phone:LongListSelector.ItemTemplate>

 </phone:LongListSelector>


 </Grid>

</phone:PhoneApplicationPage>
       
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Navigation;
using Microsoft.Phone.Controls;
using Microsoft.SharePoint.Client;
using Microsoft.SharePoint.Client.Phone;
using Microsoft.Phone.Shell;

using SPRInt.Resources;

namespace SPRInt
{
 public partial class MainPage : PhoneApplicationPage
 {
 // Constructor
 public MainPage()
 {
 InitializeComponent();
 }

 private void PhoneApplicationPage_Loaded(object sender, RoutedEventArgs e)
 {
 ListOperations ops = new ListOperations("https://sprint");
 Action<List<CompanyUpdate>> callback = (List<CompanyUpdate> items) =>
 {
 this.Dispatcher.BeginInvoke(() =>
 {

 this.lstCompanyNews.ItemsSource = items;
 });
 };
 ops.GetItems("Company News", callback);

 }

 }
}