SharePoint BDC: Creating an ADF with the Microsoft BDC Definition Editor
Business Data Catalog (BDC) allows you to integrate an external database in your SharePoint environment. To do so, you need to create an Application Definition File (ADF) and import it to SharePoint.
An ADF is basically an XML file with all the necessary information describing the entities and methods to access the database. There are some paid tools to help you generate ADFs, but Microsoft itself offers a free tool to create them.
In this article, I will describe how to get this tool and some annoyances I found along the way.
Install the BDC Definition Editor
The BDC Definition Editor from Microsoft is included in the SharePoint Server 2007 SDK. After installing the SDK, you can find the setup for the BDC Definition Editor at "C:\Program Files\2007 Office System Developer Resources\Tools\BDC Definition Editor" (by default).
If you want to install the BDC Definition Editor on another machine, you can just copy the entire folder and run it elsewhere. Supported environments are Windows Server 2003 (En-US only) and Windows XP (En-US only). The installation file that you want to run is setup.exe. This will ensure that all the requirements are installed and, if not, it will install them:
- Microsoft .NET Framework 2.0
- Microsoft SQL Server 2005 Express Edition
- Windows Installer 3.1
It's basically a 90MB install for a 2MB tool. Go figure.
Once it's installed, you can find the tool in the Start Menu at ApplicationDefinitionDesigner.exe.
Creating the Application Definition File
I don't want to dwell too much on the use of the Application Definition Designer (aka BDC Definition Editor), because it's pretty straightforward:
- Start by adding a new LOB system (if you already have one created you can import it).
- Choose "Connect to Database".
- Type your connection string (if you aren't sure how to write one you can check some SQL Server 2005 connection string samples here).
- You will see a listing of all the tables. Drag the ones you need and then make any necessary adjustments you see fit (e.g. exclude some fields).
After doing this, creating the ADF is pretty easy. Just use the Export button and save the file.
Finder method for the Business Data List web part
Before you export the file, let me tell you about a caveat. For a reason I cannot phantom, the Application Definition Designer doesn't generate a method that the Business Data List web part relies upon to display data. In fact, if you import the ADF as it is, you will see the following error in the Business Data List web part:
"There are no Business Data Types loaded in the Catalog"
The strange thing is that it can easily be fixed. The web part is searching for a Finder method in the BDC entity, but the BDC Definition Editor by Microsoft doesn't generate one by default, though it generates two other methods for fetching data. One of these methods can easily be changed to a Finder method, instead of having to create your own.
To fix this, go to the Application Definition Designer. For each entity you want to use in the Business Data List web part:
- Expand Methods > FindAll_EntityName > Instances.
- Select the instance (FindAll_EntityName_Instance).
- In the property MethodInstanceType select Finder.
One last thing you might want to do is add some more output parameters to the method or it will only show the primary key by default, which is probably not that desirable.
Using SQL Authentication
If you're using SQL authentication you need to add some extra properties to your LOB System instance:
- Select your instance.
- Make sure AuthenticationMode is PassThrough and DatabaseAccessProvider is SqlServer.
- Browse to the Properties collection.
- Add these properties:
- RdbConnection Data Source: the SQL server address
- RdbConnection Initial Catalog: the name of the database
- RdbConnection User ID: username
- RdbConnection Password: password
- RdbConnection Integrated Security: set this to false
Import the ADF to SharePoint
Now you need to import your ADF into SharePoint. It's pretty simple:
- Open Central Administration.
- Go to the Shared Services Provider (SSP) of the desired application.
- Under Business Data Catalog choose Import application definition.
- Browse to your file and import it.
Once it's imported, you'll be able to check the entities. One more thing you probably want to do is change the access permissions of the BDC. You can do it one by one for each BDC entity, but it's much easier if you change for all of them at the same time:
- In the page of your BDC application select Manage Permissions.
- Add all the accounts you need using Add Users/Groups (you probably want NT AUTHORITY\Authenticated Users with Select in Clients permissions).
- Now click Copy all permissions to descendants (this will make sure that the permissions are copied to all the BDC entities).
Using the Business Data Catalog web parts
Now that you imported the ADF and have your BDC Application setup, you can use the BDC web parts that SharePoint provides (make sure you have the necessary features activated).
When you add a web part, under Business Data, you'll find several web parts that connect to your BDC. One of the most common to use is the Business Data List web part. Once you add it, you need to edit its properties and choose the entity you want to display.
You can also pair a Business Data List with a Business Data Item web part, which displays the details of a single row. To do that, first add a Business Data Item web part and then on the edit context menu of the Business Data List web part choose Connections > Send Selected Item To and select the Business Data Item web part instance. Now when you select on of the rows in the Business Data List web part, its details will show in the Business Data Item web part.
Resources
