Layer2 Data Provider for OData: Specifications for Layer2 Cloud Connector

​​The Layer2 Cloud Connector for SharePoint can be used to connect almost any external data sources to native SharePoint lists and libraries, for example via the OData protocol as used by many applications, e.g. SAP or Microsoft CRM/ERP systems. This FAQ gives the full specifications of the Layer2 Data Provider for OData that is fully included in the product.

The Layer2 Data Provider for OData can connect data sources via the OData protocol. The Open Data Protocol (OData) is a Web protocol for querying and updating data via web services. OData is being used to expose and access information from a variety of sources including, but not limited to, relational databases, file systems, content management systems and traditional Web sites. 

 Fig. You can use the OData.org demo services to verify the connector.

Normally, an OData request is presented in a single URL. Layer2 OData provider splits the select portion and the data collection URL portion to present them as a connection string and a separate select or query statement.

Connection String:

• URL
  
  This is the Service Root URL for the OData source. The service root URL identifies the root of an OData service. The resource identified by this URL MUST be an AtomPub Service Document (as specified in [RFC5023]) and follow the OData conventions for AtomPub Service Documents (or an alternate representation of an Atom Service Document, if a different format is requested).
  OData: JSON Format specifies such an alternate JSON-based representation of a service document. The service document is required to be returned from the root of an OData service to provide clients with a simple mechanism to enumerate all of the collections of resources available for the data service. This information is mandatory.
• Collection
  
  The resource path section of an URL identifies the resource to be interacted with (such as Customers, a single Customer, Orders related to Customers in London, etc.). The resource path enables any aspect of the data model (collections of entities, a single entity, properties, Links, service operations, etc.) exposed by an OData service to be addressed.
• Authentication
  
  This setting specifies how the Layer2 Cloud Connector authenticates against the OData source. It can be one of the following:
  
  Anonymous
  This is the default value and used to connect anonymous resources.
  
  IECookie
  If an authentication cookie has been created using the cookie manager, this authentication method can be configured to authenticate by using the authentication cookies. (See The Cookie Manager for more details). 
  
  Microsoft_Modern 
  You can use Microsoft_Modern to authenticate against SharePoint. 
  
  Microsoft_Modern_ProjectWebApp
  Use this authentication method to authenticate against Microsoft Project. 
  
  Windows
  You can use Windows AD authentication to login. Please provide user name and password as well, e.g. "username=myDomain\myUserName; password=myPassword".
  
  SharePoint_FBA
  The SharePoint Form Based Authentication (FBA) with username and password parameters required.
  
  
  These methods are Dynamics CRM-specific for connecting to their OData service:
  
  DynamicsCrmOnline
  For connecting to Microsoft Dynamics CRM Online. Username and password parameters required.
  
  Microsoft_Modern_CRM
  This is the default authentication method to access Microsoft Dynamics CRM Online instances and works in most cases. This authentication does not need any further connection-string settings, other than the URL of the connected system. The authentication needs to be setup in the UI, for more information check the Layer2 Cloud Connector User Documentation.
  
  DynamicsCrmADFS
  For connecting to an Microsoft Dynamics CRM Online instance that uses ADFS. Username, Onlineuser, and password parametesr required. For example: "username=myDomain\myUserName; onlineuser=myusername@domain.com; password=myPassword;"
  
  NextCRMOnline
  Custom authentication for use with the NextCRM system, but also works with many other Online CRM providers (online and on-premises). Username and password parameters required.
  
  AdfsOnPremiseNTLM
  For use with systems that have ADFS on-premises authentications that require an NTLM handshake to happen. Username and password parameters required.

See below for connection string samples.

Select Statement:

A Select statement for the Layer2 Data Provider for OData refers to the query options section of an OData URL which is specified to control the amount and order of the data requested. All OData services MUST follow the query string parsing and construction rules defined in OData documentation section and its subsections. Some services, like Dynamics CRM, do not support all type of queries (see the system-specific documentation about their OData service for more information). See below for examples. 

Layer2 Data Provider for OData: Sample Connection Strings

• Url=http://services.odata.org/V2/Northwind/Northwind.svc;Collection=Products;Authentication=Anonymous

Layer2 Data Provider for OData: Sample Select Statements

• In case no data query is given, the whole collection is returned as given in the connection string.
• ​Enter $select=* to select all fields without any condition.
• Field Set: Enter $select=myField1, myField2 to select the data fields myField1 and myField2 only. Please note that field names are case sensitive (depending on OData source implementation). It is strongly recommended to remove unwanted fields from query to improve performance.
• For quick data preview performance while testing, you can use the $top=<value> parameter to limit the number of records pulled. For example, $select=myField1, myField2&$top=10 will only get the first 10 records. Make sure to remove the top filter once you are ready to sync the data.
• Filter: Enter $select=ProductID, ProductName&$filter=ProductID eq 3 to filter for specific ProductID. You can use use eq operator for filtering. OData protocol enables you to use other relational operators such as not equal (ne), less than (lt), less or equal (le), greater than (gt), greater or equal (ge).
• Logical Operators: Enter $select=ProductID, ProductName&$filter=(ProductID gt 3) or (ProductID eq 1) to form logical expressions with and, or, not and brackets.
• Arithmetical operations: You can apply standard operators to add (add), subtract (sub), multiply (mul), divide (div), or find remainder (mod): $select=ProductID, ProductName&$filter=(UnitPrice mul UnitsInStock) lt 45 and UnitsInStock ne 0.
• Numerical functions: If your properties are numbers you can apply floor, ceiling, and round functions. Example of the query that uses these functions is $select=ProductID, ProductName&$filter=floor(Price) eq 3 or ceiling(Price) eq 3.
• String functions: There are a lot of string functions you can use in your filter expressions like this:
  length(string): $select=ProductID, ProductName&$filter=length(ProductName) lt 7.
  trim(string), toupper(string), tolower(string): $select=ProductID, ProductName&$filter=toupper(ProductName) eq 'CHAI'.
  substringof(‘part’, text): $select=ProductID, ProductName&$filter=substringof('Chai',ProductName).
  endswith(text, ‘part’): $select=ProductID, ProductName&$filter=endswith(ProductName,'Chai').
  startswith(text, ‘part’): $select=ProductID, ProductName&$filter=startswith(ProductName,'Chai').
• Date / Time Functions: If you have datetime properties in the resources you can use several date part functions such as year(), month(), day(), hour(), minute(), and second(). As an example: $select=ProductID, ProductName&$filter=year(OrderDate) eq 1996.
• Sort Order Functions: You can order results by some property or function using the $orderby query option. Default order is ascending but you can change it. Some examples are:
  $select=ProductID, ProductName&$orderby=ProductID desc
  $select=ProductID, ProductName&$orderby=length(ProductName)

Please read more about OData query options here:
http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part2-url-conventions.html

Layer2 Data Provider for OData: Step-by-Step introduction

You can simply configure your OData connection in the Layer2 Cloud Connector Connection Manager.

Fig. Example connection string and select statement to connect to Microsoft CRM via OData.

A step-by-step introduction how to setup the Layer2 Cloud Connector for SharePoint using the Layer2 Data Provider for OData to connect to Microsoft CRM Online is given here.

Layer2 Data Provider for OData: Use Cases & Known Issues 

• Many applications today can expose data via OData web requests. The provider can be used to connect to this type of data entity and replicate to native SharePoint lists, e.g. SharePoint 2010/2013 on-premises or in the cloud (SharePoint Online/Office 365).
• Please note that the Cloud Connector modifies only changed data in case of modified OData results - not all items in list. Change notifications, alerts and workflows can be used as usual. The content of additional unmapped columns are kept while updating items in SharePoint.
• You can use the Professional Edition of the Cloud Connector to sync OData sources with almost any other data entities, e.g. SQL, OData, etc. (Not just with SharePoint!)
• Please note that this provider can work bi-directionally, if supported by the target system's OData service. It can write-back changes, as done in a SharePoint List or library (or an offline copy of that, like with Outlook).
• The Cloud Connector saves a cache of the metadata from the OData service for performance reasons. This can cause issues if the cache is old; for example, a newly added field to the OData service is not selectable in the Connector (as it's not present in the cache). You can fix this by going to C:\ProgramData\Layer2 Cloud Connector\Metadata, finding the .metadata file with the name of your OData service, and deleting it. The next sync or authentication to that service will cause a new version of the cache to be created.

Ready to go next steps?