Business Connectivity Services (BCS) in my opinion is one of SharePoint’s most powerful, yet most under-utilized services. Let’s think about this for a second. If you are building a .NET app that needs to connect to an external database you need to create the connections to it and the basic CRUD methods into your data layer (you better be using a separate data layer or get off my blog ;-p ). Yes I know there are lots of code out there that will generate it for you, or you could just build your own.
But what if I told you that you could do it all with your already existing SharePoint environment and in a lot of cases without writing a single line of code. Now before you developers get out your torches and pitchforks (remember I am one of you), let me assure you, for the more complex data connections you will still need to write code. You will need to be able to write the code that can access, update and insert the data properly. All I am saying is that for simple to moderately difficult data operations you can do it within SharePoint without writing any code. Perhaps this means you could delegate some of this to the non-developers on your SharePoint support team so you only get the fun development work.
My intention is for this post to be the first in a series on SharePoint’s BCS Service. I haven’t completely planned out the entire series yet, but what I can tell you is that I will be starting out with the overview of BCS and it’s features here, then will move into building no-code solutions and move into topics that require custom code and hopefully showing you how SharePoint can be used to build a full solution that implements data stored outside of the SharePoint environment.
What is Business Connectivity Services?
You can find the Microsoft definition of BCS here. To summarize, BCS is a service offered within all flavors of SharePoint that allows SharePoint to connect to an external data source and surface the data contained there within the SharePoint environment. No custom solutions need to be build outside for users, it can all be done withing an already existing SharePoint environment and in some cases can be done without writing a single line of code. BCS actually started in SharePoint 2007 as Business Data Catalog but received a significant upgrade in 2010 and a new name. 2013 saw further enhancements to BCS which I will discuss further as we progress in this series. BCS is not limited to just surfacing the data though. With BCS you can actually edit, insert and delete data contained within an external source. This all sounds pretty simple and it can be. However, it can get loads more complex. What if you have to handle data across multiple tables and also have business processes that need to be considered. Well BCS can handle all that for you. You will likely be stepping outside of the no-code realm, but it can be done.
Why Should I Use BCS?
Get around SharePoint’s relational DB limitations
One of the biggest mistakes I see are users treating SharePoint lists like a relational database. For all the power and features that SharePoint has to make your business processes and collaboration so much easier it really was never meant to be a relational database system. You can find information on this in a previous post of mine. You can easily use BCS to surface data already existing in a relational system in a convenient manner for end users. BCS can also allow you to interact with these systems and even build out business process within SharePoint that could require a great deal of development in the external system to accomplish.
User tool integration with critical applications
One of the awesome abilities of BCS is the ability to to act as an intermediary between user’s day-to-day tools and the business’s critical application (SAP, SQL DB\.NET applications I am looking at you). This goes beyond just letting you edit data from within SharePoint. You can actually use BCS to access data from within rich-client applications like MS Word, Excel, etc. For example, invoices can be created that actually access data existing in SAP except you are doing it directly in a Word template of your invoice and not a form within SAP. It is simply that SharePoint is gathering the data from SAP and your Word template is accessing that data which is stored in an external list of SharePoint. It is then using Quick Parts to display the external date automatically within the document (more on this in a later post).
Leveraging SharePoint to simplify security
With Secure Store you can have data access controlled strictly through SharePoint. So instead of requiring administrators of the external data system to control the security for every user within the application’s space you can utilize one single account to grant access to the external system and then configure that account in the Secure Store. From there BCS utilizes that entry to access the external data source. Now your security can be controlled on the SharePoint list your user’s are utilizing which should already be setup for their access. This means one point of security admin for everyone that could use that data
This is just a broad overview of the BCS. There is so much more you can do with BCS that I don’t list here because my post would quickly become a book. In my next post I will cover BCS features you get with each version of SharePoint and a bit more on what you can do with.
Thanks for reading!