Business Connectivity Services Deep Dive (MDVWRK1) – my notes

SharePoint Connections 2010, Den Haag, 27 September 2010

Session: Business Connectivity Services Deep Dive (MDVWRK1)

Speaker: Scot Hillier (,

 Key ideas covered by this seminar:

  • External Content Types (ECT)
  • NO code BCS solutions
  • BCS architecture, BCS Solution features and service application
  • Information in this seminar applies mostly to SharePoint 2010 (and Office 2010 for client applications)


  • Information Workers = people turning on Outlook when they walk in their office; 
  • As solution providers we should ask ourselves: Are we helping really the users (information workers)?
    • Map net drives – creates masses of useless documents
    • Build applications – generally work on structured data..which is rather not the case for users.
    • SharePoint sites – traditionally works with info in lists in the content DBs; web parts don’t work with unstructured data in documents
  • Client tool for BCS applications: Office Outlook is seen as the primarily client tool for BCS applications development. SharePoint Workspace is much less as it is not customizable or very little.
    • Moving a ASP.NET to SharePoint scenario: copy the ASP.NET application to _layouts directory and it works but there is 0 integration: Data will not be indexed so it will not be present in search results
  • BCS lists have built in throttling (retrieval limited to 200 items ). Through configuration we can increase this limit (see below)
  • BCS server side features (help you to build applications like in ASP.NET):
    • External lists
    • Profile pages (present a single record from the DB – record view)
    • Actions
    • InfoPath Forms
    • External data web parts & Columns (in MOSS 2007 language they were called Business Data instead of External Data)
    • Search Integration
    • User profiles supplements
    • Custom solutions
  • BCS client side features (requiring Office 2010)
    • Office Outlook features (take info online)
    • SharePoint Workspace
    • Office Word External Data Column Integration (!!!)
    • Custom solutions
  • Not all BCS features are available in SharePoint Foundation. Check the slides for a list of BCS features SharePoint version.
  • BCS has a full API (we can talk to all objects client or server from BCS)
  • Varions types of solutions that we can build using BCS:
  • External Content Type (ECT) is the most important concept in all of BCS
    • It defines the columns of a particular entity
    • It maps the system from where it  takes the values
    • It defines the operations we can perform
  • The definition of ECT is FARM Wide
  • Connectors in BCS:
    • There are 4 but only three displayed: .NET, SQL, WCF. The Forth is ‘Custom connector’. What’s the difference between a  .NET and when is ‘Customer connector’?:
      • .NET connector = Connectors for schemas (e.g. DB Schemas or Exchange object model) which do not change (.NET built by people in house for internal systems)
      • ‘Customer connector’ = Connectors for schemas (e.g. DB Schemas or Exchange object model) which change in time (built by third parties in order to be sold .. more generic = ‘Customer connector’)
  • Steps to build a typical BCS Solution without code:
    • Define an External Content Type (ECT) using the SharePoint Designer
      • Define the connection using one of the available connectors
      • Define the operation (there is a wizard for operations and field mapping). OBS: You must define at least READ LIST and READ ITEM operations so that you can create an External list in SharePoint for use of this ECT later on.
      • Create an Eternal list in SharePoint and use the newly created ECT.
      • You are DONE!
      • Optional: create an office Add-in directly from the External List (VSTO package) so that you can use that data in your office application (like an external list of contacts for example). Furthermore, you can take that data offline with you and re-synchronize it when you go back online.
  • BCS Authentication (check slide):
    • Passthrough,
    • RevertoSelf (is disabled by default and can be activated with a powershell command),
    • WindowsCredentials,
    • rdbCredentials (for DBs connects using non Windows credentials returned from the SSS),
    • Credentials (for web services connects using non Windows credentials returned from the SSS)
    • IMPORTANT slide to show the connectors vs SharePoint2010 Authentication Scheme. Combinations of authentication schemes. 90% of BCS problems are found
    • BCS connection to data: NTLM vs Kerberos discussed for connections to database properties: when establishing a connection, connecting using the option ‘users’ identity’ doesn’t really work in most cases (especially on enterprise production systems with separated servers or for NTLM as there are two hops between the user and the sql data). Using Impersonated Windows or Custom Identity requires Secure Store Application ID
  • SharePoint Designer
    • Becomes with 2010 version a critical tool especially for building BCS solutions
    • Allows you to use ‘Generate all operations’ on a table and it will generate all CRUD operations. A wizard will help you map various fields for all these operations.
  • BCS Architecture:
    • On server side: BDC (Business Data Connectivity) Service Application has it’s own database for ECT content (db dedicated to this service application); + BDC Server runtime. On top of it we find the Secure Store Service (SSS),  the search, workflows and Web parts of SharePoint and finally the VSTO package and External Lists on the SharePoint Site level.
    • BDC services on client side includes the BDC client runtime and the client cache (allowing the client to take the date offline)
    • When deploying a VSTO package, the Client gets a proxy for the connector as well
  • Key LIMITS of external lists in SharePoint
    • You can not initiate an workflow from an item in an external list but you can use an item in an external list in a workflow initiated on a SharePoint list
    • Does not support event handlers
    • Using BDC server runtime can help in overcoming external lists limitations! – Custom web parts.
  • Key Throttling limits (default, maximum) for BCS:
    • Connections (global scope): 100, 500
    • Items (SQL scope) / rows returned for a query: 2k, 25k
    • Connection timeout (WCF, SQL scopes): 60s, 600s
    • Size of data (WCF scope): 3Mb, 150Mb
    • [Photo IMAG0499]
  • Business Data Connectivity (BDC) permissions:
    • Edit (CRUD operations)
    • Execute
    • Selectable in Clients (pick ECT for use in lists, web parts, etc)
    • Set Permissions (can set permissions)
    • Typical error when using the first time: YOU need to have Execute permissions on the model of data itself
  • THE server never caches the DATA but it CACHES the BCS model. ImportantfFor client/Server BCS solutions…the client needs to reconnect (take the data off line and then take it online again) or ask for a forced Update this list. (This needs to be planned).
  • Business Data Connectivity (BDC) Metadata model is  an xml file defining the External Content Type (Fields, Operations, Relationships, Actions) and the external system itself (server details, connections, etc). This file contains EVERYTHING you might define through the interface for the model. This file is the entire BCS solution.
  • Deploying BCS solutions:
    • The entire BCS solution in contained in the metadata model = xml file.
    • Deployment DEV-TEST-PROD: models can be Exported and imported.
    • SharePoint Designer supports export but no import. Importing is only supported by the Service Application interface (Central Administration).
    • Import/Export can be done using Powershell.
    • Partial Export or multiple file export: Between DEV and PROD environments there might be different permissions or resources (external systems) – the model can be exported in more than one single file (e.g. one with the ECT definition, one with the external systems definition and another one with permissions)
    • The Entity element has versioning!!! You must increase the versioning number when importing in PROD
  • When developing BCS applications to run on Client and Server you have to plan:
    •  Authentication (no secure store on the client side)
    • Updates (if the model change on the server it does not change for the client unless a forced Update the list is made or resync initiated (data offline and then online again) (see above the caching of model on the server level)
  • ‘.Bdcm’ files (exports of a BCS metadata model) are recognized by Visual Studio which proposes a certain number of tooling for editing the file but.. The tooling in Visual Studio only works with .NET assembly connectors! As an alternative,  the file must be edited as a simple normal xml file. (good XML knowledge is a must for a BCS solution developer)
  • BCS Data filters: SharePoint Designer does not support all types of filters. you should go and edit the XML Model file if you need a specific one. Check slide with filters.
  • Relations between more External Content Types – ECT associations are possible:
    • One to Many
    • Self Referential
    • Reverse
    • Many to Many, foreign keyless (this is supported in the model BUT not in SharePoint Designers
  • Association Navigator: able-ing the user to define relations between more ECT. Using the Association Navigator allow SharePoint to generate some UI. For instance if we defined the association (parent/children), on the profile page of the parent will have displayed his related childs.
  • ‘Profile Page’ a page containing all the fields of the given ECT. Once defined, it becomes the default action for the record.
  • Design and Presentation: Improving the UI for out of the box BCS solutions could be done through:
    • ASPX forms
    • Infopath forms
    • Format data by the external system before it-s fetched by BCS or create a database view or a web service that returns the formatted data (for instance as data only is not supported you could build a stored procedure that casts the datetime field to date only formatted as you wish to be displayed finally on the page)
    • Use xslt with External Data web parts
    • Create custom field type and field control.
    • Create a custom Web part that uses BDC runtime API
    • Create a .NET assembly connector that returns formatted data
  • Key REMARKS:
    • dateTime type of data is mapped to External Lists!
    • Date only not supported!
    • Integer64 not supported
    • Default pages (DispForm.aspx, EditForm.aspx, NewForm.aspx) use the ListViewWebPart wich you can not extend (no XSLT, no modification of ui possible). By creating a new page (e.g. MyNewForm.aspx) it will use the DataViewWebPart which is extensible and accepts XSLT for display of data.
  • SharePoint Designer 2010 feature: save site as template will save it as a solution package .wsp!
  • BCS Solutions Client Side: Client Data Synchronization:
    • Click-once deployment package: package is created in ‘ClientSolution’ folder
    • Client cache – things to know:
      • It Is on Client side, per user, in memory, SQL CE database!!
      • Client caches the data and the model! Remember that the server only caches the model, never the data
      • There is as well an ‘operation queue’
      • BCSSync.exe
        • Calls the ‘Finder’ method to populate the cache
        • ‘SpecificFinder’ method is necessary to complete cache population
        • Client CACHE considerations: There is no way of deleting the data in the cash…but there is a garbage collection for stale data (compare the old data with the new data at the end of cache expiration time and flags the old data as ‘ garbage’ which ultimately will be deleted but the entire operation is 100% self managed by the BCS and not by you as user)
      • PLANNING: Plan for client/server calls (especially if you have parent child relations when you could end up with many many calls)
  • BCS Solutions for Microsoft Outlook
    • We can create declarative solutions:
      • Custom views
      • Custom form regions (see below for definition)
      • Custom ribbon elements
      • When deploying VSTO add.ins solutions to Outlook, Outlook creates a ‘REGION’ for fields he does not understand from the model. It’s good to know that Custom Form Regions are created direct in MS Outlook 2010 (saved in the .ofs file)
      • Tooling:
  • 4 Object models for BCS
    • BDC Server Runtime (Microsoft.Sharepoint.dll, Microsoft.BusinessData.dll)
    • BDC Client Runtime (Microsoft.Ofice.BusinessData.dll, Microsoft.BusinessData.dll
    • Client Cache object model
    • Administration catalog object model
    • OBS: Client and Server object models are essentially identical depending on the catalog you access.
    • Using BDC server runtime can help in overcoming external lists limitations! – Custom web parts.
  • BCS solutions cannot run in sandbox solutions. (Requires more trust)
  • Demo contains an app in console mode accessing the data catalog and a web part calling a ‘Finder’ method and ‘SpecificFinder’ method.
  • .NET Assembly connectors
    • .NET Assembly Connector is supported by tooling in Visual Studio (tooling to create the XML model file: BDC Model Explorer, Method details, Properties pane, etc): in Visual Studio choose ‘Business Data Connectivity Model’
    • Two classes are mandatory for a .NET assembly connector: the services class and the entity class
    • They are to be deployed always as FARM level features
    • Needs full trust to run (the assembly is deployed in the GAC)
  • SEARHC: enable search on the BCS model:
    • Modify the LobSystemInstance element of the model and find the properties:
      • RootFinder – it tells search which Finder to call in order to index things
      • ShowInSearchUI – makes this model appear in Search Service Application UI (listed as Business Data in Content Sources); there is the possibility to create a new Scope and then have the search performed only in this scope (e.g. a particular ECT interesting for the company or for a department)
      • Security applied on search results (generic):
        • Crawl time trimming only works with windows compatible external systems
        • Query time trimming might be very resource consuming
  • SOA Architecture hint: rather develop the Web service returning the desired data on the External System and use WCF to connect it to SharePoint.

4 Responses to Business Connectivity Services Deep Dive (MDVWRK1) – my notes

  1. […] Business Connectivity Services Deep Dive (MDVWRK1) – my notes […]

  2. Patheems says:

    What exactly do you mean with the SOA Architecture hint? Do you mean that if you have a SOA Architecture not to use BCS?

    • uscatu says:

      Hi Patheems,
      no…I was probably too short in my rush:) The idea I wanted to share is that in case you have an SOA in place in your organization, it would be nice to “respect” it and its governance meaning that instead of querying directly from SharePoint with whatever method (e.g. launch an SQL query directly into the target application database and map the results in the BCS), you could nicely build WS (web services) on the application side exposing the data you need in SharePoint and than connect to these WS. In this case you will maintain the two applications (SharePoint and the target app) decoupled. ok?

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: