Cannot Edit the Associated LOB through BCS Association

There is no way (through SharePoint designer) to provide edit capabilities on a BCS when it is in association (through a foreign key) with another BCS.

Read on, to know how I tried to attempt it..

Today I was able to work on the Write back capabilities of the BCS. It is straight forward to create a BCS on a SQL Server Table.
I just went ahead with Create All operations on my Data Source.
In the wizard, make sure to uncheck the Read-Only property for those columns which need to be Updatable.
Once you are done with the wizard click Finish.

So far, so good. But, I wanted to see how BCS behaves when I have associated tables. I wanted two sets of data coming from different sources say one coming from SQL Server and other from WCF. For the testing, I just tried with another SQL Server Table which had 1:1 association with the first one.
The first table is like tblUserExtended and the second one which had association was tblUser
Both these tables were linked through the tblUser::nUserId and tblUserExtended::nUserId (Think of this as another source which has some extended user data, stored for another application that has to be aggregated)

Follow the msdn article (http://msdn.microsoft.com/en-us/library/ff728816.aspx) to create the association between two BCS entities.

The reason I did that was to have a single external list which will list columns from both User and UserExtended tables and allow the SharePoint user to edit some columns of User and some columns of UserExtended.
SharePoint showed only the columns of tblUser. While the foreign key tblUser::nUserId represents as a tuple internally. So I could not edit columns of UserExtended in that view but association of the UserExtended tuple could be done.

SharePoint out of the box do not support editing the associated BCS (at least for the External Content types created using SharePoint designer).

May be there is a way to do it, but, I could not find it. Let me know if you have a solution for this.

Programming using BCS (Business Data Connectivity) API

All Searches in Bing & Google for BCS API leads to writing .net connector to BCS. But today I had to write some complex component which required me to write code against BCS to retrieve LOB data.
I know you may ask why BCS when you already have connectivity to the source of BCS. The reason being, I wanted to use BCS to work something like an Entity model and used in context of SharePoint 2010.

Thanks to chaholl's article I was able to do perform it easily.. Here is the link for it.
http://www.chaholl.com/archive/2010/03/15/creating-a-web-service-to-access-bcs-data-in-sharepoint.aspx

Today, I also downloaded the SharePoint 2010 SDK. It comes with a lot of samples and help when programming with SharePoint. It hardly takes like 200MB of your hard drive and is definitely worth that space, if you are planning to do a lot of SharePoint coding.

Something that I came across in the SDK articles that is worth the mention,

Restrictions in BDC:
BDC does not support the ICollection or IEnumerable interfaces to represent collections in data structures, as well as the generic ICollection, IEnumerable, and IList interfaces. All collections must implement IList.
Finder method returning multiple items, we require the return value to implement either IEnumerable or IEnumerator (except for database, where we support onlyIDataReader). BDC does not support generic versions of these IEnumerable and IEnumerator.

Displaying Business Data List Web Part based on a Query String value.

Here I will demonstrate how to display the External Content data in a filtered (at the web service) list format based on a Query String value.

I would recommend you to read my previous blog, titled: “BDC/BCS integration using WCF Service” to get more clarity on the how I created the External content type.

Background: The External content is being serviced through Web Service integration of Business Connectivity Service.
Web Service Finder (Read List) Operation takes in a parameter called userName and returns a List collection.

It was straight forward to select Business Data List web part.
1. Go to a page where the Web Part is to be added.
2. Site Actions -> Edit Page ->
3. Select Edit Tools (Insert) -> Select some location on the canvas and Click on Web Part.
4. Category: Business Data -> Web Parts: Business Data List
5. Click on Add
6. Configure the Data List Web Part by clicking on OPEN THE TOOL PANE link.
7. On the Properties pane on the right hand side, Select the External Content Type and click on OK.

Things that made it more complicated was, I needed to display the list based on a filter that is dynamic and the UI should not have a filter to select from. Follow the steps below to get things working in the way it has to be.

I had defined a Filter while defining the External Content Type let's say the name of the filter used was filterParam: Comparison

Create another web part to service the Data List web part with a dynamic value (Say a query string)
1. Category: Filters -> Web Parts: Query String Filters
Note: This web part will not be visible to configure unless you click on the Edit Web Part of the Business Data List Web Part.
2. Click on the Open the Tool Pane of the Query String Filter Web Part.
3. Set the Filter Name and Query String Parameter Name as 'userName'.
4. Modify the Advanced filter if required to configure no query/multi value query.
5. Click OK.

Create an association between the Query String Filter Web Part and the Business Data List Web Part.
1. Click on the Connections (found below the Edit web part) of the Data List Web Part.
2. Get Query Values From -> Query String URL Filter.
3. This will show a pop up to select the External content type user parameter ('filterParam' filter as defined in the external content type) to which to associate the query string.
4. Select the parameter and click Finish.

Finally, Ensure you do a Save and Close on the Ribbon to save the added two web parts with its configurations.


Test the URL with different query string value
Ex: http://server:port/sites/teams/SitePages/Collegues.aspx?userName=Bob

It should work! If not then Bing it and find it. That's what I did :)

To hide the filters on the web part and also to use the My Profile property, as a filter, do the following.
1. Click on the Edit Web of the Data List Web Part.
2. You should see an 'Edit View' link on the web part.
3. You can use filters like User Profile Properties on which the External Content type will filter. i.e. will be passed as a parameter to the Web Service.
4. Disable the Allow user to change the criteria check box if you do not want to see the filters on the web part.

BDC/BCS integration using WCF Service

I am sure you will find thousands of links on Business Connectivity Services (BCS) integrating with WCF Services. But, still I was not able to get it straight. I am documenting the pain points I faced hoping it will save that precious day.
As, the new term in SharePoint 2010 is BCS, I will try not to use its predecessor term BDC (Business Data Catalog). However both these terms will be interchangeably used in the blogs you find on net.
Following things you may want to verify when your WCF integration to your LOB is not working.
1) Ensure that you deploy WCF onto a web site in IIS. Don't try to host the WCF on the Visual Studio by debugging. It will throw an error that endpoint cannot be found.
Don't blame the SharePoint designer for not able to find the endpoint hosted on the Visual Studio.

2) When giving the URL at the SharePoint Designer give a fully qualified name of the machine and not just the machine names.
Local hosts are not welcome
You could try to add an entry in the hosts file of c:\windows\system32\drivers\etc\host
I added something like,
127.0.0.1 customers.server.com
My Service options were something like this,
http://mtalt005.server.com:8888/Collegue.svc?wsdl

3) Ensure that the WCF Service has anonymous access. Or else make sure that the service account running the WCF Service has appropriate permissions.
If appropriate credentials have to be used then you may want to consider adding an account in the Secure Storage Service of SharePoint and add that account in the Secure Storage Application ID when configuring the BCS.

4) Your Service should return a Composite (custom class) Object for Specific Finder i.e. ReadItem operation and an array of composite objects for Finder i.e. ReadList Operation. String or Array of Strings is not composite objects.

5) You will have to define the Filters when you are defining the input for the Read List Operations. This will be useful when you are expecting some input and return a list of objects.
Ex: Pass UserId as input to WCF Operation and expect a list of Collegues Objects.
// Sample
public class ColleguesSvc : ICollegue
{
public List GetAllCollegues(String userId)
{
// Read DB for the userId and get a list of Collegue objects and return them.
}
}
In the above case I had to define a filter at the Input Form of the ReadList Wizard.
FilterType = Comparison
Operator = Equals

6) Ensure that the Input parameter is String and not Int32. --- I am not sure about this. But I was facing some issue while doing this.

7) Ensure that you developed the WCF Service using Target Framework as 3.5
Though having 4.0 should not harm but just that you will have to make additional provision of reinstalling the IIS to ensure that it is compatible with 4.0. Windows Server 2008 R2 default comes with Framework 3.5. If you really want the WCF service to be 4.0 then make sure to have the application pool to be running under 4.0 and not 2.0

In the next blog I will mention about displaying the WCF content on a List.

First take on SharePoint 2010

With no prior experience on MOSS 2007, I thought it would be hard to adopt SharePoint 2010. Enrolled to a four day Training course and was on to an assignment in SharePoint 2010.

I will detail my experience in the subsequent blogs about my learnings in any topics that I come across in SharePoint 2010.

Overall, I feel Microsoft has done a great job in providing a lot of developer tools and also in improving the User Experience.
Hats on for the exhaustive collection of the MSDN articles. They have structured it very well.

Start with the following links:
http://technet.microsoft.com/en-us/library/cc794341.aspx
http://msdn.microsoft.com/hi-in/sharepoint/bb964529(en-us).aspx
http://technet.microsoft.com/hi-in/sharepoint/ff601871(en-us).aspx

A lot of improvement in both Office and SharePoint 2010 and collaboration within these products.

My best takes:
Service Application Architecture enhancing the SSP architecture.
Offline capabilities through SharePoint Workspace.
Office on the web.
Better UI navigation compared to its predecessors.

Some tools that greatly enhance the capabilities of a SharePoint developer are
Visual Studio : Through a lot of plugins and templates.
SharePoint Designer : Rapid UI development

The collaborative capabilities of these applications make it more attractive for the organizations to think about investing in SharePoint 2010 to ensure that they are able to make the best use of their data that is not reaching to all of the information workers.