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.

4 Answers 4


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

  • Thanks Bill - will any of these options allow us to parameterise the queries on a per-site basis? e.g. site1 specifies ClientID 1, site2 = ClientID2. Apr 15, 2011 at 10:26
  • 1
    I think that will just be part of the implementation (or configuration) of your web part that queries your external list.
    – SPDoctor
    Apr 15, 2011 at 10:35
  • I will have a play with SharePoint designer and find out whether we can create external content types that are parametrised and therefore reusable. I'm also not clear on whether we can refer to ECTs in Web templates. Apr 15, 2011 at 11:47

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.

  • Thanks a lot for this Matt - in your opinion do you think the benefits of using the BCS approach outweigh the flexibility offered by a traditional SQL/DAL approach? Apr 15, 2011 at 12:53
  • Great answer, Matt, +1 from me. But it could be even better if contains some proof-links or code samples :) Apr 15, 2011 at 13:13
  • I think I'm missing the point of the WHERE clause in this blog from Todd: toddbaginski.com/blog/archive/2009/12/01/…. Can anyone point me in the right direction? Apr 15, 2011 at 14:03
  • omlin - I really want to dust off my blog and put something back into the community with my hard earned lessons on BCS from the past 6 months! Apr 15, 2011 at 15:05
  • Benjamin, that's a great question that I could easily spend 2 hours over a few beers discussing :). The reason that my project HAD to use BCS (IMO) was that we needed SSRS level reporting plus the SP2010 interface so the decision was virtually made for us. Apr 15, 2011 at 15:08

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.

  • 1
    This was the case in MOSS, but BCS features External Content Lists and Columns are available in SP Foundation in SharePoint 2010 along with the connector framework. SharePoint 2010 Standard CAL gives you Secure Store Service, search and profile pages. Enterprise CAL gives you Business Data Web Parts and Rich Client integration. chakkaradeep.com/post/… Apr 15, 2011 at 10:53

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.

  • Thanks - I suppose the question is whether utilising a BCS Custom Connector is more effort than keeping our custom Web parts which do the job but require quite a lot of rework during upgrades. Apr 15, 2011 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.