Accessing Sharepoint data with C#

You need to create customized reports based on Sharepoint data and you don't have the access to the server to create a new web part to access it directly. You need to resort to other options to generate the report. Fortunately, Sharepoint allows some methods to access its data, so you can get the information you need. If you are using C#, you can use one of these three methods:

Client Side Object Model (CSOM) - this is a set of APIs that allow access to the Sharepoint data and allow you to maintain lists and documents in Sharepoint
REST API - with this API you can access Sharepoint data, not only using C#, but with any other platform that can perform and process REST requests, like web or mobile apps
SOAP Web Services - although this kind of access is being deprecated, there are a lot of programs that depend on it, so it's being actively used until now. You can use this API with any platform that can process SOAP web services.

This article will show how to use these three APIs to access lists and documents from a Sharepoint server and put them in a WPF program, with an extra touch: the user will be able to export the data to Excel, for further manipulation.

To start, let's create a WPF project in Visual Studio and name it SharepointAccess. We will use the MVVM pattern to develop it, so right click the References node in the Solution Explorer and select Manage NuGet Packages and add the MVVM Light package. That will add a ViewModel folder, with two files in it to your project. The next step is to make a correction in the ViewModelLocator.cs file. If you open it, you will see an error in the line

using Microsoft.Practices.ServiceLocation;

Just replace this using clause with

using CommonServiceLocator;

The next step is to link the DataContext of MainWindow to the MainViewModel, like it's described in the header of ViewModelLocator:

<Window x:Class="SharepointAccess.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:d="http://schemas.microsoft.com/expression/blend/2008"
        xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
        xmlns:local="clr-namespace:SharepointAccess"
        mc:Ignorable="d"
        Title="Sharepoint Access" Height="700" Width="900"
        DataContext="{Binding Source={StaticResource Locator}, Path=Main}">

Then, let's add the UI to MainWindow.xaml:

<Grid>
    <Grid.Resources>
        <DataTemplate x:Key="DocumentsListTemplate">
            <StackPanel>
                <TextBlock Text="{Binding Title}" />
            </StackPanel>
        </DataTemplate>
        <DataTemplate x:Key="DocumentTemplate">
            <StackPanel Margin="0,5">
                <TextBlock Text="{Binding Title}" />
                <TextBlock Text="{Binding Url}" />

            </StackPanel>
        </DataTemplate>
        <DataTemplate x:Key="FieldsListTemplate">
            <Grid >
                <Grid.ColumnDefinitions>
                    <ColumnDefinition Width="150" />
                    <ColumnDefinition Width="*" />
                </Grid.ColumnDefinitions>
                <TextBlock Text="{Binding Key}" TextTrimming="CharacterEllipsis"/>
                <TextBlock Text="{Binding Value}" Grid.Column="1" TextTrimming="CharacterEllipsis"/>
            </Grid>
        </DataTemplate>
    </Grid.Resources>
    <Grid.ColumnDefinitions>
        <ColumnDefinition Width="*" />
        <ColumnDefinition Width="*" />
        <ColumnDefinition Width="*" />
    </Grid.ColumnDefinitions>
    <Grid.RowDefinitions>
        <RowDefinition Height="40" />
        <RowDefinition Height="*" />
        <RowDefinition Height="40" />
    </Grid.RowDefinitions>
    <StackPanel Grid.Row="0" Grid.ColumnSpan="3" HorizontalAlignment="Stretch" Orientation="Horizontal">
        <TextBox Text="{Binding Address}" Width="400" Margin="5" HorizontalAlignment="Left"
                 VerticalContentAlignment="Center"/>
        <Button Content="Go" Command="{Binding GoCommand}" Width="65" Margin="5" HorizontalAlignment="Left"/>
    </StackPanel>
    <StackPanel Orientation="Horizontal"  Grid.Row="0" Grid.Column="3" 
                      HorizontalAlignment="Right" Margin="5,5,10,5">
        <RadioButton Content=".NET Api" IsChecked="True" GroupName="ApiGroup" Margin="5,0"
                     Command="{Binding ApiSelectCommand}" CommandParameter="NetApi" />
        <RadioButton Content="REST" GroupName="ApiGroup"
                     Command="{Binding ApiSelectCommand}" CommandParameter="Rest" Margin="5,0"/>
        <RadioButton Content="SOAP"  GroupName="ApiGroup"
                     Command="{Binding ApiSelectCommand}" CommandParameter="Soap" Margin="5,0"/>
    </StackPanel>
    <ListBox Grid.Column="0" Grid.Row="1" ItemsSource="{Binding DocumentsLists}" 
             ItemTemplate="{StaticResource DocumentsListTemplate}"
             SelectedItem="{Binding SelectedList}"/>
    <ListBox Grid.Column="1" Grid.Row="1" ItemsSource="{Binding Documents}"
             ItemTemplate="{StaticResource DocumentTemplate}"
             SelectedItem="{Binding SelectedDocument}"/>
    <ListBox Grid.Column="2" Grid.Row="1" ItemsSource="{Binding Fields}"
             ItemTemplate="{StaticResource FieldsListTemplate}"
             />
    <TextBlock Text="{Binding ListTiming}" VerticalAlignment="Center" Margin="5" Grid.Row="2" Grid.Column="0" />
    <TextBlock Text="{Binding ItemTiming}" VerticalAlignment="Center" Margin="5" Grid.Row="2" Grid.Column="1" />
  </Grid>

The first line in the grid will have a text box to enter the address of the Sharepoint site, a button to go to the address and three radiobuttons to select the kind of access you want.

The main part of the window will have three listboxes to show the lists on the selected site, the documents in each list and the properties of the document.

As you can see, we've used data binding to fill the properties of the UI controls. We're using the MVVM pattern and all these properties should be bound to properties in the ViewModel. The buttons and radiobuttons have their Command properties bound to a property in the ViewModel, so we don`t have to add code to the code behind file. We've also used templates for the items in the listboxes, so the data is properly presented.

The last line will show the timings for getting the data.

If you run the program, it will run without errors and you will get an UI like this, that doesn't do anything:

It's time to add the properties in the MainViewModel to achieve the functionality we want. Before that, we'll add two classes to manipulate the data that comes from the Sharepoint server, no matter the access we are using. Create a new folder named Model, and add these two files, Document.cs and DocumentsList.cs:

public class Document
{
    public Document(string id, string title, 
        Dictionary<string, object> fields,
        string url)
    {
        Id = id;
        Title = title;
        Fields = fields;
        Url = url;
    }
    public string Id { get; }
    public string Title { get; }
    public string Url { get; }
    public Dictionary<string, object> Fields { get; }
}

public class DocumentsList
{
    public DocumentsList(string title, string description)
    {
        Title = title;
        Description = description;
    }

    public string Title { get; }
    public string Description { get; }
}

These are very simple classes, that will store the data that comes from the Sharepoint server. The next step is to get the data from the server. For that we will use a repository that gets the data and returns it using these two classes. Create a new folder, named Repository and add a new interface, named IListRepository:

public interface IListRepository
{
    Task<List<Document>> GetDocumentsFromListAsync(string title);
    Task<List<DocumentsList>> GetListsAsync();
}

This interface declares two methods, GetListsAsync and GetDocumentsFromListAsync. These are asynchronous methods because we don want them to block the UI while they are being called. Now, its time to create the first access to Sharepoint, using the CSOM API. For that, you must add the NuGet package Microsoft.SharePointOnline.CSOM . This package will provide all the APIs to access Sharepoint data. We can now create our first repository. In the Repository folder, add a new class and name it CsomListRepository.cs:

public class CsomListRepository : IListRepository
{
    private string _sharepointSite;

    public CsomListRepository(string sharepointSite)
    {
        _sharepointSite = sharepointSite;
    }

    public Task<List<DocumentsList>> GetListsAsync()
    {
        return Task.Run(() =>
        {
            using (var context = new ClientContext(_sharepointSite))
            {
                var web = context.Web;

                var query = web.Lists.Include(l => l.Title, l => l.Description)
                     .Where(l => !l.Hidden && l.ItemCount > 0);

                var lists = context.LoadQuery(query);
                context.ExecuteQuery();

                return lists.Select(l => new DocumentsList(l.Title, l.Description)).ToList();
            }
        });
    }

    public Task<List<Document>> GetDocumentsFromListAsync(string listTitle)
    {
        return Task.Run(() =>
        {
            using (var context = new ClientContext(_sharepointSite))
            {
                var web = context.Web;
                var list = web.Lists.GetByTitle(listTitle);
                var query = new CamlQuery();

                query.ViewXml = "<View />";
                var items = list.GetItems(query);
                context.Load(list,
                    l => l.Title);
                context.Load(items, l => l.IncludeWithDefaultProperties(
                    i => i.Folder, i => i.File, i => i.DisplayName));
                context.ExecuteQuery();

                return items
                    .Where(i => i["Title"] != null)
                    .Select(i => new Document(i["ID"].ToString(), 
                    i["Title"].ToString(), i.FieldValues, i["FileRef"].ToString()))
                    .ToList();
            }
        });
    }
}

For accessing the Sharepoint data, we have to create a ClientContext, passing the Sharepoint site to access. Then, we get a reference to the Web property of the context and then we do a query for the lists that aren't hidden and that have any items in them. The query should return with the titles and description of the lists in the website. To get the documents from a list we use a similar way: create a context, then a query and load the items of a list.

We can call this code in the creation of the ViewModel:

private IListRepository _listRepository;

public MainViewModel()
{
    Address = ConfigurationManager.AppSettings["WebSite"];
    GoToAddress();
}

This code will get the initial web site url from the configuration file for the app and call GoToAddress:

private async void GoToAddress()
{
    var sw = new Stopwatch();
    sw.Start();
    _listRepository = new CsomListRepository(Address);

    DocumentsLists = await _listRepository.GetListsAsync();
    ListTiming = $"Time to get lists: {sw.ElapsedMilliseconds}";
    ItemTiming = "";
}

The method calls the GetListsAsync method of the repository to get the lists and sets the DocumentsLists property with the result.

We must also declare some properties that will be used to bind to the control properties in the UI:

public List<DocumentsList> DocumentsLists
{
    get => _documentsLists;
    set
    {
        _documentsLists = value;
        RaisePropertyChanged();
    }
}
public string ListTiming
{
    get => _listTiming;
    set
    {
        _listTiming = value;
        RaisePropertyChanged();
    }
}
public string ItemTiming
{
    get => itemTiming;
    set
    {
        itemTiming = value;
        RaisePropertyChanged();
    }
}
public string Address
{
    get => address;
    set
    {
        address = value;
        RaisePropertyChanged();
    }
}

These properties will trigger the PropertyChanged event handler when they are modified, so the UI can be notified of their change.

If you run this code, you will have something like this:

Then, we must get the documents when we select a list. This is done when the SelectedList property changes:

public DocumentsList SelectedList
{
    get => _selectedList;
    set
    {
        if (_selectedList == value)
            return;
        _selectedList = value;
        GetDocumentsForList(_selectedList);
        RaisePropertyChanged();
    }
}

GetDocumentsForList is:

private async void GetDocumentsForList(DocumentsList list)
{
    var sw = new Stopwatch();
    sw.Start();
    if (list != null)
    {
        Documents = await _listRepository.GetDocumentsFromListAsync(list.Title);
        ItemTiming = $"Time to get items: {sw.ElapsedMilliseconds}";
    }
    else
    {
        Documents = null;
        ItemTiming = "";
    }
}

You have to declare the Documents and the Fieldsproperties:

public List<Document> Documents
{
    get => documents;
    set
    {
        documents = value;
        RaisePropertyChanged();
    }
}

public Dictionary<string, object> Fields => _selectedDocument?.Fields;

One other change that must be made is to create a property named SelectedDocument that will be bound to the second list. When the user selects a document, it will fill the third list with the document's properties:

public Document SelectedDocument
{
    get => _selectedDocument;
    set
    {
        if (_selectedDocument == value)
            return;
        _selectedDocument = value;
        RaisePropertyChanged();
        RaisePropertyChanged("Fields");
    }
}

Now, when you click on a list, you get all documents in it. Clicking on a document, opens its properties:

Everything works with the CSOM access, so it's time to add the other two ways to access the data: REST and SOAP. We will implement these by creating two new repositories that will be selected at runtime.

To get items using the REST API, we must do HTTP calls to http://<website>/_api/Lists and http://<website>/_api/Lists/GetByTitle('title')/Items. In the Repository folder, create a new class and name it RestListRepository. This class should implement the IListRepository interface:

public class RestListRepository : IListRepository
{
    public Task<List<Document>> GetDocumentsFromListAsync(string title)
    {
        throw new NotImplementedException();
    }

    public Task<List<DocumentsList>> GetListsAsync()
    {
        throw new NotImplementedException();
    }
}

GetListsAsync will be:

private XNamespace ns = "http://www.w3.org/2005/Atom";
public Task<List<DocumentsList>> GetListsAsync()
{
    var doc = await GetResponseDocumentAsync(_sharepointSite + "Lists");
    if (doc == null)
        return null;

    var entries = doc.Element(ns + "feed").Descendants(ns + "entry");
    return entries.Select(GetDocumentsListFromElement)
        .Where(d => !string.IsNullOrEmpty(d.Title)).ToList();
}

GetResponseDocumentAsync will issue a HTTP Get request, will process the response and will return an XDocument:

public async Task<XDocument> GetResponseDocumentAsync(string url)
{
    var handler = new HttpClientHandler
    {
        UseDefaultCredentials = true
    };
    HttpClient httpClient = new HttpClient(handler);
    var headers = httpClient.DefaultRequestHeaders;
    var header = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 " +
        "(KHTML, like Gecko) Chrome/76.0.3809.100 Safari/537.36";
    if (!headers.UserAgent.TryParseAdd(header))
    {
        throw new Exception("Invalid header value: " + header);
    }
    Uri requestUri = new Uri(url);

    try
    {
        var httpResponse = await httpClient.GetAsync(requestUri);
        httpResponse.EnsureSuccessStatusCode();
        var httpResponseBody = await httpResponse.Content.ReadAsStringAsync();
        return XDocument.Parse(httpResponseBody);
    }
    catch
    {
        return null;
    }
}

The response will be a XML String. We could get the response as a Json object if we pass the accept header as application/json. After the document is parsed, we process all entry elements, retrieving the lists, in GetDocumentsListFromElement:

private XNamespace mns = "http://schemas.microsoft.com/ado/2007/08/dataservices/metadata";
private XNamespace dns = "http://schemas.microsoft.com/ado/2007/08/dataservices";
private DocumentsList GetDocumentsListFromElement(XElement e)
{
    var element = e.Element(ns + "content")?.Element(mns + "properties");
    if (element == null)
        return new DocumentsList("", "");
    bool.TryParse(element.Element(dns + "Hidden")?.Value ?? "true", out bool isHidden);
    int.TryParse(element.Element(dns + "ItemCount")?.Value ?? "0", out int ItemCount);
    return !isHidden && ItemCount > 0 ?
      new DocumentsList(element.Element(dns + "Title")?.Value ?? "",
        element.Element(dns + "Description")?.Value ?? "") :
      new DocumentsList("", "");
}

Here we filter the list by parsing the Hidden and ItemCount properties and returning an empty document if the document is hidden or has no items. GetDocumentsFromListAsync is:

private async Task<Document> GetDocumentFromElementAsync(XElement e)
{
    var element = e.Element(ns + "content")?.Element(mns + "properties");
    if (element == null)
        return new Document("", "", null, "");
    var id = element.Element(dns + "Id")?.Value ?? "";
    var title = element.Element(dns + "Title")?.Value ?? "";
    var description = element.Element(dns + "Description")?.Value ?? "";
    var fields = element.Descendants().ToDictionary(el => el.Name.LocalName, el => (object)el.Value);
    int.TryParse(element.Element(dns + "FileSystemObjectType")?.Value ?? "-1", out int fileType);
    string docUrl = "";

    var url = GetUrlFromTitle(e, fileType == 0 ? "File" : "Folder");
    if (url != null)
    {
        var fileDoc = await GetResponseDocumentAsync(_sharepointSite + url);
        docUrl = fileDoc.Element(ns + "entry")?.
            Element(ns + "content")?.
            Element(mns + "properties")?.
            Element(dns + "ServerRelativeUrl")?.
            Value;
    }

    return new Document(id, title, fields, docUrl);
}

It parses the XML and extracts a document and its properties. GetUrlFromTitle gets the Url from the Title property and is:

private string GetUrlFromTitle(XElement element, string title)
{
    return element.Descendants(ns + "link")
            ?.FirstOrDefault(e1 => e1.Attribute("title")?.Value == title)
            ?.Attribute("href")?.Value;
}

The third access method is using the Soap service that Sharepoint makes available. This access method is listed as deprecated, but it's still alive. You have to create a reference to the http://<website>/_vti_bin/Lists.asmx and create a WCF client for it. I preferred to create a .NET 2.0 Client instead of a WCF service, as I found easier to authenticate with this service.

In Visual Studio, right-click the References node and select the Add Service Reference. Then, click on the Advanced button and then, Add Web Reference. Put the url in the box and click the arrow button:

When you click the Add Reference button, the reference will be added to the project and it can be used. Create a new class in the Repository folder and name it SoapListRepository. Make the class implement the IListRepository interface. The GetListsAsyncmethod will be:

XNamespace ns = "http://schemas.microsoft.com/sharepoint/soap/";
private Lists _proxy;

public async Task<List<DocumentsList>> GetListsAsync()
{
    var tcs = new TaskCompletionSource<XmlNode>();
    _proxy = new Lists
    {
        Url = _address,
        UseDefaultCredentials = true
    };
    _proxy.GetListCollectionCompleted += ProxyGetListCollectionCompleted;
    _proxy.GetListCollectionAsync(tcs);
    XmlNode response;
    try
    {
        response = await tcs.Task;
    }
    finally
    {
        _proxy.GetListCollectionCompleted -= ProxyGetListCollectionCompleted;
    }

    var list = XElement.Parse(response.OuterXml);
    var result = list?.Descendants(ns + "List")
        ?.Where(e => e.Attribute("Hidden").Value == "False")
        ?.Select(e => new DocumentsList(e.Attribute("Title").Value,
        e.Attribute("Description").Value)).ToList();
    return result;
}

private void ProxyGetListCollectionCompleted(object sender, GetListCollectionCompletedEventArgs e)
{
    var tcs = (TaskCompletionSource<XmlNode>)e.UserState;
    if (e.Cancelled)
    {
        tcs.TrySetCanceled();
    }
    else if (e.Error != null)
    {
        tcs.TrySetException(e.Error);
    }
    else
    {
        tcs.TrySetResult(e.Result);
    }
}

As we are using the .NET 2.0 web service, in order to convert the call to an asynchronous method, we must use a TaskCompletionSource to detect when the call to the service returns. Then we fire the call to the service. When it returns, the completed event is called and sets the TaskCompletionSource to the desired state: canceled, if the call was canceled, exception, if there was an error or set the result if the call succeeds. Then, we remove the event handler for the completed event and process the result (a XmlNode), to transform into a list of DocumentsList.

The call to GetDocumentsFromListAsync is very similar to GetListsAsync:

XNamespace rs = "urn:schemas-microsoft-com:rowset";
XNamespace z = "#RowsetSchema";

public async Task<List<Document>> GetDocumentsFromListAsync(string title)
{
    var tcs = new TaskCompletionSource<XmlNode>();
    _proxy = new Lists
    {
        Url = _address,
        UseDefaultCredentials = true
    };
    _proxy.GetListItemsCompleted += ProxyGetListItemsCompleted;
    _proxy.GetListItemsAsync(title, "", null, null, "", null, "", tcs);
    XmlNode response;
    try
    {
        response = await tcs.Task;
    }
    finally
    {
        _proxy.GetListItemsCompleted -= ProxyGetListItemsCompleted;
    }

    var list = XElement.Parse(response.OuterXml);

    var result = list?.Element(rs + "data").Descendants(z + "row")
        ?.Select(e => new Document(e.Attribute("ows_ID")?.Value,
        e.Attribute("ows_LinkFilename")?.Value, AttributesToDictionary(e),
        e.Attribute("ows_FileRef")?.Value)).ToList();
    return result;
}

private Dictionary<string, object> AttributesToDictionary(XElement e)
{
    return e.Attributes().ToDictionary(a => a.Name.ToString().Replace("ows_", ""), a => (object)a.Value);
}

private void ProxyGetListItemsCompleted(object sender, GetListItemsCompletedEventArgs e)
{
    var tcs = (TaskCompletionSource<XmlNode>)e.UserState;
    if (e.Cancelled)
    {
        tcs.TrySetCanceled();
    }
    else if (e.Error != null)
    {
        tcs.TrySetException(e.Error);
    }
    else
    {
        tcs.TrySetResult(e.Result);
    }
}

The main difference is the processing of the response, to get the documents list. Once you have the two methods in place, the only thing to do is select the correct repository in MainViewModel. For that, we create an enum for the API Selection:

public enum ApiSelection
{
    NetApi,
    Rest,
    Soap
};

Then, we need to declare a command bound to the radiobuttons, that will receive a string with the enum value:

public ICommand ApiSelectCommand =>
    _apiSelectCommand ?? (_apiSelectCommand = new RelayCommand<string>(s => SelectApi(s)));

private void SelectApi(string s)
{
    _selectedApi = (ApiSelection)Enum.Parse(typeof(ApiSelection), s, true);
    GoToAddress();
}

The last step is to select the repository in the GoToAddress method:

private async void GoToAddress()
{
    var sw = new Stopwatch();
    sw.Start();
    _listRepository = _selectedApi == ApiSelection.Rest ?
        (IListRepository)new RestListRepository(Address) :
        _selectedApi == ApiSelection.NetApi ?
        (IListRepository)new CsomListRepository(Address) :
        new SoapListRepository(Address);

    DocumentsLists = await _listRepository.GetListsAsync();
    ListTiming = $"Time to get lists: {sw.ElapsedMilliseconds}";
    ItemTiming = "";
}

With the code in place, you can run the app and see the data shown for each API.

One last change to the program is to add a command bound to the Go button, so you can change the address of the web site and get the lists and documents for the new site:

public ICommand GoCommand =>
            _goCommand ?? (_goCommand = new RelayCommand(GoToAddress, () => !string.IsNullOrEmpty(Address)));

This command has an extra touch: it will only enable the button if there is an address in the address box. If it's empty, the button will be disabled. Now you can run the program, change the address of the website, and get the lists for the new website.

Conclusions

As you can see, we've created a WPF program that uses the MVVM pattern and accesses Sharepoint data using three different methods - it even has a time measuring feature, so you can check the performance difference and choose the right one for your case.

The full source code for this project is at https://github.com/bsonnino/SharepointAccess

Conclusions

Leave a Reply Cancel reply