Guest Author:Brett Lonsdale
Lightning Tools Ltd

Microsoft got it right this time around!   The Business Data Catalog (BDC) in Microsoft Office SharePoint Server 2007 was missing one vital ingredient when trying to use the BDC.  The missing ingredient was a tool to generate the Application Definition File (ADF) which was imported into SharePoint and described the data that you are connecting to and how to connect to it.  Without tooling you had to create the Application Definition File by hand. This task was excruciatingly painful even if you had written hundreds of them before.  We are actually quite thankful to Microsoft as this missing ingredient enabled us to write a tool and start a business providing tools specifically for the BDC and later on – other areas of SharePoint.

In SharePoint 2010 the now named Business Connectivity Services comes with two tools for two different calibres of user; SharePoint Designer 2010 for Power Users, and Visual Studio 2010 Professional BCS Tooling for Developers.  The purpose of this article is to describe when to use either tool.  

Firstly, let’s discuss what the tools actually do for us.  In Microsoft Office SharePoint Server 2007 you were creating something called a LOBSystem which stood for Line of Business System.  Each LOBSystem contained Entities, and these Entities described the Table, View or Stored Procedure that you were going to be connecting to.  Terminology has changed! Now you create a Business Data Model instead of a LOBSystem, and an External Content Type (ECT) instead of an Entity. A model can contain 1 or more ECTs which can also be associated.  Below you can see the New External Content Type creator screen in SharePoint Designer 2010.

The SharePoint Designer 2010 External Content Type Page

An External Content Type describes the data that you will display and how to display it.  It describes each column such as Name, Address, Phone Number and the data type of each column e.g. String or Date/Time.  The External Content Type now has many methods that allow you to read, create, update and delete the data.  Both SharePoint Designer 2010 and Visual Studio 2010 Professional will provide you with a way to create the Business Data Model and External Content Types.  So let’s find out which tool is write for you…

SharePoint Designer 2010

The coolest thing about SharePoint Designer 2010, is that you don’t have to write any code.  Yes, I know – you’ve heard THAT before.  Honestly, you don’t.  Well, assuming that what you want to achieve is within the boundaries of what SharePoint Designer 2010 enables you to do.  I guess it is like saying that you can create workflows in SharePoint Designer without writing code.  That is also true, but depending on your requirements of the workflow, sometimes you have to turn to code.

SharePoint Designer 2010 allows you to connect to three different types of Data Source; SQL Server, .NET Shim, or WCF Web Services.  I’m imagining readers raising eyebrows right now thinking ‘My users won’t know what an earth a .NET Shim or WCF Web Service is!’.  Quite right – So let us first concentrate on SQL.  If you are connecting to a SQL Server, the only information that you need to be armed with is the name of the SQL Server, how you are going to authenticate, and which tables, stored procedures or views you are going to connect to.  With this information to hand, you can connect easily to the datasource selecting the columns to display, and the methods that you want to create.  It takes a little bit of getting used to, but once you have the hang of it, you can connect to SQL and have your data displayed on the page in the format of an External List in just a few minutes.

Here is the ‘What if’. What if the data that you connect to is remote, very heavily normalized, or even you don’t use SQL.  You happen to have Oracle, DB2, Lotus Notes, or any other data source for that matter.  I’m going to chuck SAP and Siebel in there just for Bing and Googles sake J. The below image shows you the available data sources in SharePoint Designer 2010.

Selecting a data source in SharePoint Designer 2010

You can connect to a WCF Web Service.  The WCF Web Service would help you to connect to data that perhaps isn’t on your network and has to travel through a firewall.  Unfortunately, not any WCF Web Service will do.  BCS if very specific about how it wants the data presented so that it can be consumed.  So even if you have WCF Web Services already, you may find yourself creating new ones that work with BCS. Visual Studio would be the tool of choice to write the WCF Service.

What’s a .NET Shim?  Well, that is new to BCS.  A .NET Shim is an assembly that contains a Business Data Model  which has all of the necessary BCS methods to connect and retrieve business data from your Line of Business Systems and pretty much does the same thing as a WCF Web Service except for the remote bit.  You can connect to any data source, create mashups of data from different data sources, transform your data, and then present it for consumption to BCS.  So, already we are talking about writing code, unless you fit snugly into this box that says you simply want to connect to a few tables from a SQL Server database.  None the less – It is a HUGE improvement over BDC.

Visual Studio 2010 Professional

VS.NET 2010 is armed with SharePoint Tools for Visual Studio which allows you to create a Business Data Model Project.  The beauty of this is that you can achieve whatever you want to achieve because you are writing code.  (Don’t stop reading though – at the end of this is a happy ending). It doesn’t matter what you want to connect to, using Visual Studio you can connect to the source, create your Business Data Model and BCS will consume your data.  You can also write the WCF Services, and .NET Shims that SPD 2010 allows you to connect to.

Using Visual Studio 2010 you will see the ability to create a Business Data Model diagram which allows you to specify each method or property that you are likely going to need.  When you are finished doing that, it will create a stub for each method.  Your job is then to write the code for each method.

That is where BCS Meta Man comes in.  BCS Meta Man is a tool for Visual Studio 2010 Professional.  It does really just one thing, but a very useful thing.  It writes the code for you! So you can connect to SQL and Oracle and get the code written for you.  All you really need to do is tell BCS Meta Man the server names, table names etc and then hit F5.  Of course, you may want to tweak the code a little bit, which you can do, it is all there in the Visual Studio project allowing you to modify it. Below is a Business Data Connectivity Model in Visual Studio 2010.

So now with BCS – we have two tools SharePoint Designer 2010 and BCS Meta Man that allow you to connect to your data source without writing any code.  You can download a beta version of BCS Meta Man now by visiting www.lightningtools.com.  Our blog site also has endless information on BDC and BCS which can be found at the same location.

Guest Author:Brett Lonsdale
Lightning Tools Ltd

Brett Lonsdale is a SharePoint developer who specializes within the Business Data Catalog, co-owner of Lightning Tools Ltd, Co-host on The SharePoint Pod Show www.sharepointpodshow.com..  Strategically (kind of) Brett has based himself in Florida where he lives with his wife and daughter.  You can read Brett’s blog on www.brettlonsdale.com, and also follow him on twitter @brettlonsdale