Reusing and parameterising BCS External Content Types

Question

I recently posted a query here that asked about the best way of connecting to an external SQL store.

I got some great responses back but need a little more info, specifically around External Content Types. We are basically looking to replace several custom Web parts that query SQL stored procedures and views and display results in an SPGridView.

Reading through this article, I understand that I can create entities using Visual Studio. However, I am not sure how reusable these are.

1. In all cases we need to filter results based on unique IDs (e.g. a ClientID).

2. Parameters for the query should be set upon SPWeb creation.

3. We need to be able to specify the relevant External List in our various Web templates.

Currently we achieve the above using custom Web part properties and Web services, meaning that there is a lot of work involved during upgrades.

Is BCS the answer to this problem?

Many thanks in advance.

Answer 1

Going up through the gears:

1) For a simple scenario, use SharePoint Designer to create an external content type (formerly known as entities) and an external list based on this. Then use the normal SharePoint OM to query that list (or you can use the more powerful BCS Object Model). The SPD tools are surprising powerful and include the ability to pull in data from views (think stored procs work also but I never tried it).

2) If your back-end schema is fairly static, you can create a ".NET connector" in Visual Studio. This allows you to do some manipulation of the data, aggregation, etc.

3) For the ultimate in flexibility, at the expense of greater difficulty (both development and deployment), create a custom connector. Use this for non-SQL data sources or if you want to make a generic connector where the schema is changing.

If you can get away with 1) it will make things a lot simpler. If not, there's a good article to help you choose between 2) and 3).

Answer 2

I am working on a system with exactly the issues you talk about. My observations on your points;

1. To filter results based on unique IDs such as ClientID you can add a (comparison type) Filter Descriptor to the Finder method of your entity. The limitation I have found with External Lists is that these filters are only exposed by views on the list so for instance you would have to have a view per client which isn't always ideal.
2. Setting query parameters on SPWeb creation has actually been my preference as I have been able to use feature receivers and event handlers that create views and set parameters (see answer to 1).
3. Same answer as 2., I have been able to programmatically set up SPWebs with all the lists/views necessary using feature receivers and event handlers. This was also made much easier and more repeatable using List Instances.

Hope this helps a little to make your decision.

Answer 3

Yes. I didn't see any cases where custom web part for external storage is more suitable than Business Data Web Parts and External Lists.

BUT all BCS features available only in sharepoint 2010 enterprise.

Ask more detailed questions for concrete problems.

Answer 4

Good question. I have looked into ways or reusing/parameterising BCS .NET connectors, but haven't really found a solution. I blogged a while ago about the lack of context available inside custom BCS .NET Connector.

I understand that there is a difference between a .NET Connector and a Custom Connector.

Check this blog post for more details

A custom connector appears to be more powerful, but also more complicated to implement.