Tuesday, 13 May 2008

Starting out with Sharepoint 2007 BDC

This post is a simple step by step of how to set up BDC.

Here is a great video that walks you though it.
Here also is a virtual lab but you will need an MSDN login for this and it needs to run in IE.
Here is the PDF that goes with the lab.

Firstly go and get:
BDC Meta Man - this will save you hours as it creates the XML defintion files for you.
This tool is great although unless you pay for it you will only be able to add 2 tables max and you get a lot of popup messages telling you about the paid for version. But the free version is really good besides that.

Then you just select the type of connection you want. This post will only cover database connections.

So connect to your SQL 2005 database using the connect to datasource drop down inside BDC Meta Man.
then drag the table(s) on to the design surface. Drag 2 tables that are relates as you get more functionality in Sharepoint if you do this. (ie.. master details stuff)
Go and choose the columns you want etc... but you are better to get most of the columns as you can filter them later in MOSS.
One thing I had trouble with is not being able to connect to my database as the credentials it was passing were the IIS settings.

So in my XML defintion file that was created by BDC Meta Man I did the folllowing:

<LobSystemInstance Name="PropertyPortal_030Instance">
<Property Name="AuthenticationMode" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAuthenticationMode">PassThrough</Property>
<Property Name="DatabaseAccessProvider" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAccessProvider">SqlServer</Property>
<Property Name="RdbConnection Data Source" Type="System.String">MY_SERVER_NAME</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">MY_DATABASE_NAME</Property>
<Property Name="RdbConnection Integrated Security" Type="System.String">False</Property>
<Property Name="RdbConnection Password" Type="System.String">MY_PASSWORD</Property>
<Property Name="RdbConnection Pooling" Type="System.String">false</Property>
<Property Name="RdbConnection User ID"Type="System.String">MY_USER_NAME</Property>


As you can see I have put a user name and password in the config.

Ok so now you have your definition file.
Go to central adminitration and select your shared service from share services admin.
Look in the Business Data Catalog section on the right and select "Import application defintion".
Browse to your XML def. and upload it. I left all the setting the same.

Now go to your site and enable:
Office SharePoint Server Enterprise Site Collection features
Stay in your site and edit a page you want to add these things to.
Click on Add a web part.

Choose Business Data List
Business Data Related List

These 2 will be part of our master detail functionality.
The web part will prompt you to open the tool panel so do this for the business data list first.
Type: click on the book and select the parent table you configure in BDC Meta Man
Apprearance: you can change the title and widths etc..
Click ok.

Do the same for the child table.
Then click ok.

Go to the child webpart and click Edit drop down in top right corner and select connections / Get Related Item From .. and choose your parent web part.
You can also edit the view to remove some columns and do filtering if you like.
Think that's it... spend a bit of time configuring the page as it takes a while to get it looking good.
The most important thing was the SQL username and Password I mentioned above.