I had the good fortune recently to get assigned to a small project that lent itself perfectly to trying out one of the new features in Microsoft Office Sharepoint Server — namely, the Business Data Catalog, or the BDC.
Basically, the requirement was simply to pull some data from our time submission and accounting database (Solomon), slice and dice by user, do a little math, and display the data on our Intranet site.
Coming from a more of a custom development / N-tier background, i was initially afraid that I was going to be faced with either a) getting laughed down for proposing that we deploy and manage an entire web service just to support a single query from a single data source or b) be reduced to putting data access code in a Web Part — i.e. in the presentation tier.
This scenario is exactly what the BDC is meant for. It gives you a relativally simple and flexible way to get from SharePoint to a back-end datasource. The datasource can be an ODBC capable databse or a web service.
There are two things you’ll need to tackle when dealing with the BDC — getting your datasource wired up to the BDC, and then getting it to display in SharePoint. The former is all about understanding the BDC metadata model and schema, the latter can be handled with the built-in capabilities of SharePoint or via a little custom code. I’ll briefly walk through my experience with both…
All of the work on the back-end of the BDC involves an xml application definition file. This file defines the methods and "objects" that will be exposed via the BDC. Aside from authoring this application definition file, you’ll also need to deploy and configure it using the Shared Services administration site.
The BDC application definition schema can be a little intimidating at first — especially if the system you are integrating with is complicated. There are a number of tools availalbe to help you create your application definition — Todd Baginiski’s is a good example (http://www.sharepointblogs.com/tbaginski/archive/2006/07/27/9541.aspx). My best advice in this area is to start small in order to get familiar with the schema and then build from there. Get something simple working against your datasource and then add complexity as needed. I found that auto-generating led to too much complexity too soon, so I chose to roll my own…
On the front-end, there are a couple of different ways to go. Depending on your needs, you will either be using the built-in Business Data Web Parts, plain old SharePoint lists, or creating a custom Web Part that uses the Microsoft.Office.Server.ApplicationRegistry namespace.
The built-in Business Data Web Parts are pretty straight forward. They support querying the catalog, dispaying details, etc. If your presentation requirements are very simple, and the data is coming out of the BDC in exactly the format that you need, then you may be able to get away with using these Web Parts.
Any SharePoint list can also contain a column (or columns) that is bound to the BDC. Simply set the type of the column to Business Data and choose the type and field. You will be given the option to automatically pull associated fields for the item as well.
Things become a lot more interesting when you start going directly against the BDC API. You can set up your Application Definition to basically return you recordsets. You can them loop them in your code and do as you wish. The programming model is a little different than ADO.NET, but it’s not difficult.
Here’s an sample of what you’ll end up doing to get to your data:
NamedLobSystemInstanceDictionary instanceDictionary = ApplicationRegistry.GetLobSystemInstances();
LobSystemInstance systemInstance = instanceDictionary["YourApplicationName"];
Entity entity = systemInstance.GetEntities()["YourEntity"];
FilterCollection filterCollection = entity.GetFinderFilters();
FilterCollection newFilterCollection = new FilterCollection();
newFilterCollection.Add(Utilities.GetFilter(filterCollection, "yourfield", ComparisonFilterType));
IEntityInstanceEnumerator entityInstanceEnumerator = null;
((ComparisonFilter)newFilterCollection[0]).Value = "YourValue";
//The bdc api throws an ObjectNotFoundException when there’s no hit based on the
//input to the specificfinder. So we’ll just swallow the exception and return null.
try
{
entityInstanceEnumerator = entity.FindFiltered(newFilterCollection, systemInstance);
}
catch (ObjectNotFoundException)
{
//no data…
}
ArrayList output = new ArrayList();
while (entityInstanceEnumerator.MoveNext())
{
output.Add(entityInstanceEnumerator.Current);
}
return output;
There’s a few lines of code to get hooked up to the right enity, then it’s just looping the data that comes back. There are a few things you have to do manually that are a little annoying, like handling an exception when there are zero records returned. Also, getting the filters set up seems a little harder than it should be. All of this is well worth it though, considering all the stuff you don’t have to do…
Overall, I’m very happy with what the BDC was able to do for me. There’s a fair amount you have to learn to get going, but it’s well worth the investment. It’s going to be a lot easier now to integrate SharePoint with external systems since you don’t have to worry about all of the issues of deployment and security for an application tier.
The only real complaints I have are the standard developer gripes about SharePoint in general. It’s frustrating to develop in a mode where there’s such a lag between compiling and seeing the results. I’m not running Server 2k3 on my laptop, so I need to compile, copy to a server, bounce IIS and then wait for the app to re-jit before I can see the results. It would be nice to something like "MOSS Express" or maybe a good mock object to facilitate development.
So — my first impression is very positive. Especially for something that is really a v1 feature. I expect that things will only get better as the tools and Web Part vendors start catching up.
