Yoni Goldberg

SharePoint Expert, Human Workflow specialist. WWW.GOLDBERGYONI.COM

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

with 3 comments

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
 
Advertisements

Written by Yoni Goldberg

December 28, 2011 at 7:56 pm

3 Responses

Subscribe to comments with RSS.

  1. 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…)

    Mor Shemesh

    December 29, 2011 at 10:27 am


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: