Business Connectivity Services in SharePoint 2013 – Access an External List via REST

By

One of the new things about the External List in SharePoint 2013 is the ability to interact with the External List via REST (the Representational State Transfer). In this blog post we’ll demonstrate how to pull the external data from an External List in a WPF client application.

1. Prerequisites:

  • Server machine with Microsoft SharePoint 2013 installed
  • Client machine with Microsoft Visual Studio 2010 Professional or higher

2. Create an External Content Type (follow along with our blog post here : Creating an External Content Type with SharePoint Designer 2013) on the SharePoint server. In my example I have one External Content Type called Territory.

Create External Content Type

 

3. Click on the External Content Type in Central Administration and remember the column names. We’ll use them later. I have “RegionID”, “TerritoryDescription”, “TerritoryID”

remember-fields-of-ect

4. Create an External List on the SharePoint page based on the External Content Type.

create-external-list

 

5. Your data from external datasource is now displayed in the SharePoint External List

external-list

 

6. In order to retrieve items within the external list check this URL in the browser http://<your site>/_api/web/lists/getbytitle(‘<your external list name>’)/items (in my case it looks like http://lyra/_api/web/lists/getbytitle(‘Territory’)/items)

Now let’s create a WPF application that will pull the data from the External List via REST. On a client machine open Visual Studio

7. Add a New Projecy

8. Expand the “Visual C#/Windows” node, select “WPF Application” project type

9. Give your project a name i.e “RESTfulBCSDemo” and click “OK”

create-wpf-application

 

10. In order to display the Line of Business data from the External Content Type in our WPF client application we will add a ListBox control to our main window from the Toolbox. Give ListBox a name i.e. externalDataListBox.

add-list-box

11. Set the properties of the ListBox in xaml file as follows

<ListBox Margin=”12″ Name=”externalDataListBox” FontFamily=”Courier New” FontSize=”14″ ItemsSource=”{Binding ExternalData}”/>

12. Now add several “using” directives in the code-behind class file MainWindow.xaml.cs :

using System.Net;
using System.Xml.Linq;

13. Add following members to MainWindow class and initialize them properly:

private string userName = “<your user name>”;

private string pwd = “<your password>”;

private string domain = “<your domain>”;

private string requestUrl = “<URL endpoint to retrieve items within the external list>”;

private List<string> columnNames = new List<string>(new string[] { “<your column1>”, “<your column2>”, “<etc>” });

14. Add a method that will pull data from external list:

private void PullDataFromExternalList()
{
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(requestUrl);
request.Credentials = new CredentialCache { { new Uri(requestUrl), “NTLM”, new NetworkCredential(userName, pwd, domain) } };
request.Accept = “application/atom+xml”;
using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
{
if (response.StatusCode != HttpStatusCode.OK)
throw new Exception(String.Format(
“Server error (HTTP {0}: {1}).”,
response.StatusCode,
response.StatusDescription));
XDocument doc = XDocument.Load(response.GetResponseStream());
XNamespace d = “http://schemas.microsoft.com/ado/2007/08/dataservices”;
XNamespace m = “http://schemas.microsoft.com/ado/2007/08/dataservices/metadata”;
XNamespace atom = “http://www.w3.org/2005/Atom”;

var columnNamesWithNamespace = columnNames.Select(c => d + c);

foreach (var entity in doc.Descendants(atom + “entry”))
{
string s = string.Empty;
foreach (var property in entity.Element(atom + “content”).Element(m + “properties”).Elements().Where(p =>
columnNamesWithNamespace.Contains(p.Name)))
{
s += property.Value.Trim().PadRight(20) + “t”;
}
externalData.Add(s);
}
}
}

15. In order to display the data in the list box add the following code:

public MainWindow()
{
InitializeComponent();
DataContext = this;
externalData = new List<string>();
PullDataFromExternalList();
}

private List<string> externalData;
public List<string> ExternalData
{
get { return externalData; }
}

16. Your code-behind class file should look like this

rest-code-view

 

17. Run the application and voila, we can now see the Line of Business Data from the Business Connectivity Services – External List “Territory” in our client WPF application

bcs-data-via-rest

 

In SharePoint 2013 we have multiple ways of accessing our BCS data programmatically. Using the C# client side object model as described in our post here:

https://lightningtools.com/blog/business-connectivity-services-in-sharepoint-2013-using-the-client-object-model/

Or using REST as we described above. One of the downsides of Client Side Object Model is it can only be used in .NET applications – so if you want to use a non .NET programming language the REST api is the way to go.

We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com

<Dmitry Kaloshin/>