What is the best way to connect to custom database using BCS?

There are times when you want to connect from SharePoint to some custom database (DB2, Oracle, Siebel, TeraData, your BPM suite, and so on.) using Business Connectivity Services (BCS), unfortunately out-of-the-box only SQL server is supported, in order to connect to other vendors some effort is required and few techniques (Creating web service, .Net connectivity assembly or crafting the BCS xml model file manually) can be employed. Each different technique carry some limitations and strengths, following is a comparison table which may ease your decision.

How to connect to custom data database (which is not supported OOTB) using BCS

.Net Connectivity Assembly

Connect to a custom developed Web Service

Craft BCS model Xml file manually

Explanation
  1. Develop custom .NET connectivity assembly
  2. Create External Content type using SPD wizard
    1. Develop custom WS which exposes the data source functionality
    2. Create External Content type using SPD wizard
  1. Develop custom WS which exposes the data source functionality
  2. Create External Content type using SPD wizard
  1. Manually create BCS model file (XML)
Example Link Link Link
Required Developer Skill (cost of maintenance) High Average No development. However SharePoint professional is required for the BCS model crafting.
Are SharePoint Skills required for creating the data layer Required Not required Required
Effort to add new field High. (1) Re-deploy new solution package with new assembly version. (2) Create new external content type version Average.  Update WS. Create new external content type version Average. Update XML file. Create new external content type version
Manipulate data on the pipe (e.g. Add some business logic) Possible Possible Not Possible
Expose Business Logic (Not only data, e.g. invoke some business process) Possible Possible Not possible
Requires another web application No Yes. Required to host the WS. No
Enjoy built-in authentication methods (To authenticate the client) No Yes Yes
Support custom authentication (against the DB) Possible Possible Not possible
Use advanced data types (e.g. Streams, BLOBs, documents) Yes No Yes
Always feasible Yes. As long as the remote DB has .NET connection provider or supports OLEDB/ODBC. Yes. No.
Supports search enumeration (Allows to crawl the remote DB in chunks, fetch group of records iteratively) Yes. Requires dedicated method in code. Partially. Requires editing the model xml files manually and dedicated operation on the WS level. No.
Performance Optimal. No overhead. Slight overhead due to networking & serialization. Slight overhead in parsing the model for each connection.
Ease of deployment to office clients (e.g. Outlook add-in) Complex. No VSTO. Average. Automatic VSTO support. Average. Automatic VSTO support.
Add intermediate services (e.g. Caching) Possible Possible Not possible
 
  • Hi,
    Do you know is crafting the xml file manually to connect to databases other than SQL Server (i.e. Oracle) will remove the necessity to install Oracle Client on the SharePoint Server Hosting the BCS Service?

    (one can never stop hoping…)

© 2017 Yoni Goldberg. All rights reserved.