Ever since the launch of SharePoint 2013, Business Connectivity Services supports connections to OData. OData is especially useful within a SharePoint Online tenant as it provides a mechanism to connect to external data that resides in an on-premises server, but you wish to present that data within the cloud. Using SharePoint Online with OData BCS, you can connect to most external data sources either by importing a Business Data Connectivity Model File or by Building a SharePoint Add-In External Content Type within Visual Studio. Both scenarios are discussed within this post.
Business Connectivity Services is a suite of services including an External List, Business Data Web Parts, External Data Column, Office Client Integration and also User Profile Import and Enterprise Search in the On-Premises Enterprise license. It is important to note that within SharePoint On-Premises or SharePoint Online, if you build a SharePoint Add-In External Content Type, you are essentially only creating an External List. Whilst an External List has many benefits, there is much more to Business Connectivity Services than the External List itself. The External List will provide you with full CRUD operations, the ability to create views with sorting and filters as well as real-time great performance since the data is linked to and not imported. Importing the BDC Model File ensures that you can use the rest of the BCS Suite and easily set permissions on who can access the external data.
If you wish to connect to External Data that resides on-premises, you have a few choices on how you may connect to that data from within SharePoint Online:
- Synchronize the External Data Source with SQL Azure
- Create an OData Service EndPoint
- Import the On-Premises Data into a SharePoint List
The latter option we would not recommend since you are duplicating potentially thousands of rows of data with a SharePoint list. This duplication usually comes at a cost with storage, performance and synchronization issues.
Creating an OData Service EndPoint for your on-premises data is the first step to take to make the data available in either an App Scoped External List or a BDC Model File.
Create an OData Service EndPoint
Creating an OData Service EndPoint for SharePoint Online OData BCS to consume is not too difficult but will require that you have a license of Visual Studio. You won’t however have to write much code if any since exposing the Data as OData is all carried out using a simple wizard.
Steps to create a WCF Service to expose OData can be found here: http://www.codeproject.com/Articles/1087982/Create-a-WCF-DataService-in-Visual-Studio
Once you have built and tested the OData service, you can consume it within a SharePoint Hosted App/Add-In or by using a BDC Model which will be imported into the SharePoint Administration page.
Building a SharePoint Hosted Add-In External Content Type will require Visual Studio, but again there is little if any code to write. Remember though that this method will only provide the External list and not the other benefits that Business Connectivity Services has to offer.
To build the SharePoint Online OData BCS External List Add-In, follow these steps:
1. Start Visual Studio and Create a New Project using the SharePoint Add-In template.
2. Provide a sensible name for your project. (It doesn’t have to be too sensible)
3. Set the URL to debug your add-in to the URL of a developer site in SharePoint Online. Note, it does have to be a developer site to debug it directly. There is a great post here about setting up a SharePoint Online Development Environment for SharePoint Add-Ins. https://msdn.microsoft.com/en-us/library/office/fp179924.aspx
4. Select the SharePoint-Hosted Radio Button and Click Next.
5. After authenticating with SharePoint Online, select SharePoint Online as the target for your add-in.
6. Once the project has been created, right click the project and choose Add –> Content Types for an External Data Source
7. Specify the URL of your OData service. In this example I am using a sample service from OData.Org
8. Provide a name for your SharePoint Online OData BCS data source.
9. Select the data entities that you want to make available.
10. Click Finish. The External Content Type is created.
11. One last step is to set the startup URL of the SharePoint Add-In. Otherwise it will say “Hello Brett” when you run the Add-In. Or ok, it will be “Hello <yourname>”. So we are going to modify the start URL to be that of the External List.
12. Double click the AppManifest.xml file and change the Start Page to: SPOODataBCS/lists/products
13. Hit Build and Voila!
To build a BDC Model File to use OData is a bit more involved unless you use BCS Meta Man.
To perform this step, you won’t need to use Visual Studio. You can obtain a free download of BCS Meta Man which will give you 5 free External Content Types. Download BCS Meta Man from here: https://lightningtools.com/products/bcs-meta-man-2010-and-2013/
1. Install and Start BCS Meta Man
2. Click OData on the left hand side & provide a URL for your OData Service.
3. Optionally handle any authentication requirements using Secure Store.
4. Drag the entites that you wish to use onto the design surface
5. Select the methods that you wish to create such as Finder, Specific Finder, Creator, Updater and Deleter.
6. Test the Service by clicking the icon in the top right hand corner of the entity
7. Click Settings to specify the name of your BDC Model.
8. Click Generate. The model file will be created and can now be imported into SharePoint Online.
9. Navigate to your SharePoint Online Administration Page.
10. Click BCS, and then ‘Manage BDC Models and External Content Types’
11. Click Import and select your bdcm file.
12. Once the permissions are configured correctly, you will now be able to create External Lists anywhere in your tenant as well as External Data Columns, BDC Web Parts and integrate with Microsoft Office.
<Brett Lonsdale/>
