OData Integration with SharePoint

Odata data sources can be integrated and synchronized without code to SharePoint on-premises lists using the Layer2 Business Data List Connector.

In case you are looking for Online data integration, you will find the right tool here.

Figure 1. It's easy to integrate OData data with SharePoint. Just create a list and click "Connect to external data source" in your list settings.

OData Integration with SharePoint - Features & Benefits

• Very easy to setup in a few minutes: Create a new SharePoint list, click "Connect to external data source" in the list settings, select the data provider, enter connection settings and data query as shown below ... that's it.
• No changes in the OData data source required: No programming, no additional tools.
• One-way and optional two-way connection: You can write-back the changes made in SharePoint to the OData source automatically with full CRUD (Create / Update / Delete) functionality. The SharePoint list can act as a full-featured front-end for selected OData data.
• Connected list data always up-to-date: The connected OData query updates the SharePoint list automatically in background (via SharePoint Timer Job), or alternatively, on-demand (Action Menu / Ribbon Button, URL, via workflow, API).
• Well-known BCS external list issues and limitations are completely solved: ALL list features are to you. Views, sorting and grouping, filters, calculated fields, search, managed metadata. Lookups, additional columns and attachments can be created as normal. All kind of lists can be used, e.g. contacts, tasks, calendar, or custom lists. You can take external data offline via Outlook.
• Workflows and notifications on external data change: List workflows and change notifications per RSS or email can be used to take business actions in SharePoint, when external Odata data records are changed.
• Application logging, reporting, and notifications: A SharePoint list is used to store settings and log information. SharePoint item versioning and workflows can be used to manage reporting and notifications. Direct notification per email in case of errors is supported as well.
• Highest Security, best performance, easy to maintain: SharePoint Secure Store can be used to store security relevant configuration information safely in one central place. Users are working with the SharePoint lists as an external data cache with highest security and performance. Large data sources can be synchronized.
• 100+ more external systems supported: Layer2 Data Providers included (e.g. for external SharePoint or Office 365, Exchange, Dynamics ERP/CRM, OData, XML/RSS, SOAP), vendor specific data providers can be used (e.g. SQL Server Oracle, MySQL etc.), 3rd party data providers also supported, e.g. for custom ERP/CRM, Facebook or Twitter. See here for supported systems and applications.

OData Specific SharePoint List Configuration Settings

In the Layer2 Business Data List Connector, the data connection must be configured as follows:

Figure 2. Example connection configuration to connect a native SharePoint list to an OData data entity via Layer2 Business Data List Connector.

• Select the Layer2 Data Provider for Odata. This provider is part of the ADO.NET provider package and should already be installed. You will find more information about the provider within the Layer Data Provider for OData specifications page.
• You can use a connection string like this to connect:
  url=http://mydomain.com/mydatasource/mydatasource.svc; Collection=myCollection; User=myUser; Password=myPassword; Authentication=Windows;
• Several different authentication methods are supported. See provider specification linked above for more information.
• You can make use of query options supported by the data provider and source system (not all options are supported by every source system). For specification of supported queries, see provider specification linked above as well as the user documentation for the Odata service itself.
  Example query:
  $select=myField1, myField2&$filter=myField eq 'Value1'
• The select statement also accepts SQL-like queries in addition to the Odata standard. See the provider documentation linked above for more information.
• Please enter an appropriate primary key (a column with unique values; CustomerID in this example), depending on your query. Using the Data Preview option can help with this.
• You can map your list columns to specific external data source fields in the Layer2 Business Data List Connector. Please take care about data types (simple type conversions are supported).
• Write-back is supported, if the OData service supports both read and write. In case of inserts (full CRUD) via external systems, please take care to use an appropriate primary key.
• No programming required for setup a connection and sync.
• No need to open your local network for access from outside. No 3rd party involved.

Odata Connection Details

Provider:

Layer2 Data Provider for OData

Connection String Example:

url=http://mydomain.com/mydatasource/mydatasource.svc; Collection=myCollection; User=myUser; Password=myPassword; Authentication=Windows;

Select Statement Example:

$select=myField1, myField2&$filter=myField eq 'Value1'

SharePoint Integration with OData - Examples, Known Issues, and Workarounds

There are the following known issues and workarounds with OData integration:

• The data provider supports write-back, but not all OData services support write. Check your service configuration or service documentation, if unsure.
• The Layer2 Business Data List Connector currently comes with two OData providers listed in the Connection Manager's provider's dropdown. One of the OData providers is marked as (Deprecated). It is still provided for compatibility reasons (especially regarding .NET 3.5). We advise to migrate existing connections to the newer one by just switching the provider in the dropdown. Please test and verify any changes in existing syncs carefully.
• SharePoint 2010 only uses the deprecated provider (as it must use the .NET 3.5 version of the provider package), but it is labeled without (Deprecated) as it is the only OData provider option available.
• The .NET 3.5 version of the provider (marked as (Deprecated) in SharePoint 2013/2016) only supports V2 OData services with ATOM output. If you have the option, please use the newer provider as it has much more features (V3/V4 protocol, JSON support, etc.) and better memory management, but  it requires .NET 4.5 or higher. It does not run with the .NET 3.5 version.
• Many OData services we support have their own pages with specific setup instructions and known issues for that service. Please check here to see if your system/service is covered!

 Ready to go next steps?