Do You Really Need that Web Part? SharePoint 2010 Business Connectivity Services, long name, AWESOME FEATURE!

  • Article

ShrPt10_h_cL_epsBusiness Connectivity Services is a feature I have heard about, but never had a chance to try. In the hands on labs at TechEd I sat down and launched a virtual machine and followed step by step instructions to try it out. There were even proctors around to give me a hand when I got stuck. The perfect time to try out BCS!

BCS (sometimes referred to as BDC Business Data Connectivity) allows you to connect external data to SharePoint. I know you can do that with web parts, but then you have to show your users how to access and use the web parts, they already know lists! With the BCS features in SharePoint 2010 you can create lists that read data from external data sources like flat files and databases. You could do it in SharePoint 2007 but it was a LOT of work to set it up for read and write.  It’s a *lot* easier in SharePoint 2010. You have two choices for setting up BCS: SharePoint designer or Visual Studio. In the lab I was able to try and compare both methods.

In the first part of the lab I set up a list that pointed to a database table using SharePoint Designer. It took me about 5-10 minutes. The list allowed the user to read and update the database table. Not bad for 10 minutes work! I’ve summarized the steps below, sorry there aren’t any screenshots, that’s the one drawback to using someone else’s hardware. (found a video that shows you how to create the external content type with SharePoint Designer if you want to try it yourself)

  1. Edit your site in SharePoint Designer.
  2. In SharePoint designer, add a new External Content Type.
  3. Set the external system of your content type to point to your data source (for example a SQL Server database)
  4. Use the Data Source Explorer tab to select the table you wish to access
  5. Right click the table name and select Create All Operations to create operations that will be used to read and update the underlying data source.
  6. Use the properties section to map the columns in your data source to your client (for example if you are reading contacts from a database table that you want to access from Outlook, you need to specify which column in the database table contains the e-mail address.
  7. Because your database table may contain millions of records you should also add a filter to limit the number of records returned, this acts like a Where clause restricting the number of records returned.
  8. Now create an External List using your External Content Type. (NOTE: You will need to ensure your users have the necessary permissions to access the External Content Type in the Business Data Connectivity Service)

During the second part of the lab, I created a list from a flat file using Visual Studio. It took me 50 minutes and that was following step by step instructions that provided all the code in detail! I can tell you right now, if you have a choice, use SharePoint designer! The functionality is great, and more flexible, but just a heads up, if you are going to use Visual Studio to set up your external list, be prepared to spend a fair bit of time going through Help, blogs, and your code to make sure you haven’t missed anything. (found a video on how to set up BDC in Visual Studio 2010)

Today’s my 5 are tips for SharePoint 2010 developers working with BCS, but they aren’t really My 5, these tips come from Penny Coventry (@pjcov) she was one of the Microsoft Certified Trainers working as a Technical Learning Guide in the Hands On Labs. She knows more about this feature than I do!

Penny’s 5 Tips for SharePoint Developers getting started with BCS (in no particular order)

  1. Check out the book Professional Business Connectivity Services in SharePoint 2010 by Scott Hiller and Brad Stevenson.
  2. If you are going to do a lot of BCS, check out the tool bcs Meta Man from Lightning Tools.
  3. Work closely with your administrator, to ensure you have the necessary permissions to create your business data connectivity model and to ensure that your users can use your model.
  4. You may need to configure Secure Store Services because by in most situations Windows Authentication cannot be used to connect to your data source (and of course most of us have our SQL databases set up to do Integrated Security! This makes it very difficult to use BCS in SharePoint Foundation because it does not come with Secure Store Services. You can do it, but be prepared for a rather long and arduous battle.
  5. If you need to use the data in workflows, stick to web parts to access your external data, but if all you want to do is give users a way to view and update that data, Business Connectivity Services is the way to go.

Okay It’s Penny’s 5, but I am adding Number 6, Penny is quite the SharePoint guru and has written her share of SharePoint books as well so I am going to recommend you check out some of her books as well.