Exploring Business Connectivity Services (BCS) in SharePoint Foundation 2010

That’s right. It’s not a typo!! SharePoint Foundation 2010 now allows Business Connectivity Services that runs on BDC Technology. Read it yourself at - https://office2010.microsoft.com/en-us/sharepoint-foundation-help/what-s-new-in-microsoft-sharepoint-foundation-2010-HA010370062.aspx#_Toc257206748

This means that the feature which was only a part of Enterprise SKU and available to select few is now available to all and this is a huge Partner Service revenue opportunity for all you guys (Sorry, I always have MS Partners in mind.. its been 6+ years in Partner Support Team). Not all feature of BCS is available though. But the basic idea behind BCS of exposing data residing in LOB systems in SharePoint and manipulating those data (new in 2010) is available.

Below is what you can do with BCS in SharePoint Foundation 2010 (are we gonna call it SPF??):

• Enables you to connect to the following systems and expose the data through a declarative model – Databases, WCF and Web Service (at times with WS Proxy), .Net framework assemblies and a pluggable connector fir custom data sources
• External Data Columns – New name in 2010. What this means to you is that you can consume data in a LOB system (say Customer Name in CRM) in a existing SharePoint List
• External Lists – The data (actually the metadata) is available as a External List (new in 2010) though which users can Read / Edit / Delete without realizing the fact that they are manipulating data that’s residing from a external system.

Other features like External Data in Search, Office Client Integration and Offline capabilities are only available in SharePoint Server 2010 Std or Ent (and in SharePoint Server for Internet Sites Std and Ent) editions only and in this post I am only gonna focus on SPF (:-))

Before I start diving into this, a few things:

• This is a technology that allows you to connect to a backend data source which may have a front-end enterprise application. Like us, other software vendors also don't like you connecting to their backend DB and run read/write operation.
• Just because you can connect to the backend data source, doesn't mean you should!
• Evaluate and plan properly – If you are connecting to lets say Microsoft CRM, use web services and not the backend SQL Directly. Similarly check with other vendors if they have available web services for data read/write (98% does). You do have to spend some time understanding the web service and reading the definitions, but i can assure you that you will spend much more time trying to reverse engineer the SQL Schema and even if you do, the mode will the up-supported (by the vendor).

Now the interesting part…

We all know that to connect to an external system we need 2 things:

1. The BCS – Business Data Connectivity Services – You configure this in the Central Administration either Individually or through the Farm Config Wizard. If you create it manually, you have the pleasure of choosing your own database name.
2. An application Definition File – which tells SharePoint how to go, connect and read the data. Like earlier version, BCS is a Metadata store and doesn't actually store the data in SharePoint (it maintains a cache though). After exposing the data if a user interacts with it and the source system is not available SharePoint will throw up an error. For creating this application definition file we used to use either Visual Studio or BDC Definition Editor or something like a BDC Metaman. In 2010 we will use SharePoint Designer 2010!! Lets see these in action…

Fire up SharePoint Designer 2010 and open your SPF 2010 Site. There is a completely new look and feel and host of features in SPD 2010, but lets focus on our work first. Click on External Content Type (new in 2010)

Click on External Content Type on the Ribbon:

Enter Name and Display Name. Leave Office Item Type as Generic List (other options like Contact List also available but SPF doesn't allow you to edit this external List from Outlook anyway). Office Synch – doesn’t matter, it’s only available in Ent SKU. Click on External System. This is where you will define where the data resides. In my case its a SQL Server 2008.

Click on Add Connection and Select SQL Server as the Data Source Type:

Fill up the Connection String details and select User’s Identity and the Authentication Method (the others need Secure Store Application not available in SPF anyway)

Click OK. This pulls up the Database in the screen. Expand Table and Click the table which stores your data. In my case its a table called PatientMaster. Right click on the table and in the new fly out menu you see the available operations. In our case we will select Create All Operations, but you could also select any of them. The New Read Operation and New Read List Operation comes together. If you want people to update a record but not delete it, don't select the New Delete Operation. New Association is for associating this External Content Type to another. 

Note: If you want to use the same table structure, use the script below to create the table:

USE [User_Test]
GO

/****** Object:  Table [dbo].[PatientMaster]    Script Date: 04/21/2010 15:32:09 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PatientMaster](
    [PatientID] [nvarchar](20) NULL,
    [PatientName] [nvarchar](100) NULL,
    [PatientPhone] [nvarchar](100) NULL,
    [Address] [nvarchar](100) NULL,
    [City] [nvarchar](100) NULL,
    [ZIP] [int] NULL,
    [DOB] [date] NULL,
    [gender] [varchar](10) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

In the New Window click Next (Resolve any error if it shows on this screen)

On the Next screen you will see an error and 1 or more warnings. This is because we check what you are doing at runtime so that you don't have to come back on this step after publishing it in case there is any error. The Error says that At least one identifier should be specified and the warning tells you that no field has been selected in the Picker Control. Lets work on these.

Select PatientID and check the checkbox that says Map to Identifier (this acts like a primary key) and select Show in Picker.

You will notice that the Error and the Warning is now gone. There is still one warning below which says that the Identifier shouldn't allow nullable values and that the application could throw up a runtime exception if it returns a null value. To make this warning go away, either make the PatientID a Primary Key in the table above. or don't allow null in the PatientID value. Use the script to change the null value setting for the column:

ALTER TABLE PatientMaster
ALTER COLUMN PatientID nvarchar(20) not null

If you cancel out the operation and refresh the data connection and come back on this screen, you will see the warning gone.

Click Next. On this page its important that you define a Filter if you have huge data set. At least a Limit Filter. Limit Filter is used to set a limit to the amount of rows that’s being returned from the data source. If you know for sure that (with other filters) the resultset will the small, its fine, else we recommend putting a Limit Filter. Click on Add Filter Parameter and in the Filter Properties window select Limit as the Filter Type. Click Ok.

Enter Default Value for the Filter as 2000 (again, our recommended limit.) and you should see the Warning gone. Click Finish.

Now back on the External Content Type Page click on Create Lists and Form on the Ribbon. Optionally you can also create a Profile Page. What a Profiel Page does is when you pull up the profile for a particular record, it pulls up associated details like charts, KPI etc for that particular record.

This will let you create a new External List. Give a Name and Click Ok. If you check the Create InfoPath Form it will generate an error since it relies on InfoPath Forms Services which is a part of Ent SKU

This Data is now ready to be rendered in SharePoint. When you hit OK on the previous step, SPD published it to SharePoint. You can also export the model and Import it in SharePoint Central Admin Go to Central Admin and click on Manage Service Applications >> Click Manage on the Ribbon:

You will see PatientRecords. Check it and click Set Object Permissions on the Ribbon. This is the place where security is implemented so that everyone doesn't have access to the data. Add the Account (Or Security Group) and give the kind of permissions you want that account/group to have on this Model. Click OK.

Back on the Home Page under Lists you should see Patient Records >> Click on it. This will render the data from SQL. Select 1 record and click on Edit on the Ribbon. Edit the address and hit Save. Go back to SQL and see the data updated!!

Isn't this a AWSOME feature??

The data you just exposed to sharepoint can now be consumed in any List or Libraries. For Example lets create a Document Library called Patient Files. Upon creation go to Library Settings >> Add a New Column >> Choose External Data and Select the Model. Select PatientID as the Field To Be Shown on this Column and select Patient Name and City in the Additional Fields. Make sure it’s a mandatory field.

Now lets upload a file there. When you try to upload the File SharePoint enforces the capturing of Metadata and asks you to enter or choose the PatientID:

Browse for a PatientID or type one in and click resolve and click Save. You will see the document is now uploaded with Patient Name and City pulled from the SQL Server.

To conclude, it’s an amazing feature and people out there doesn't realize it. With 2010 a lot of process of creating and connecting to the External Data has been simplified – you don't need to be a developer to do this, you can write data back to the system with minimal efforts which saves a lot of hours if you had build a custom front end for it and this is free!!

Go ahead and use it, make data from LOB system available to your users.

See ya!

This posting is provided "AS IS" with no warranties, and confers no rights.