SharePoint Himadrish

Business Data Catalog, in short we call it as BDC, is one of the major collaboration mechanism in SharePoint. BDC has been used broadly by enterprise system to bridge between other legacy systems with SharePoint.  For example, collaborate between Siebel data into SharePoint. Users in SharePoint should able to find the contacts which are present in Siebel table. Other touch points could be Lotus notes, SQL server, SAP and many more.

To implement BDC, we need to feed up data in the XML file format, which basically contain the information about connection string/mechanism to connect other source of data i.e. Oracle database or SQL Server.  This XML file is termed as Application Definition File (ADF). There are a few tools now a days in the market which can help you to generate the ADF file.  One of the popular tools is BDC Meta man.  This tool has been launched by LightningTools. This toll will help you to generate the ADF file which can be feed up into SharePoint system to connect other touch point like SAP. Here you can find an overview of the BDC Meta man.
In this article, for the simplicity and available resources, I have selected SQL Server as other source of data. In SQL Server, I have created two tables emp, and empDetails.  Empid is the foreign key between these two tables.  Next some data has been feed up into both the tables.
Objective of this article is to implement BDC which will able to retrieve employee information from SQL Server tables (i.e. from emp and empDetails) and display the information in SharePoint through using BDC. The total process is divided into three parts. Part one is the generation of Application Definition File. Part two is to feed up the newly created ADF file into SharePoint through share service provider. And in part three, the actual implementing of the BDC in SharePoint site so that the information is able to display in the SharePoint.
To generate the application definition file, the BDC Meta Man 4.0.0.3 has been used.

Part one:

Open BDC Meta Man >> click on Connect to data source >> Put the correct credential and server name/ip there

BDC Meta man will able to display you the databases and related table in the left pan. As in the below picture, there are two tables under the MOSS database. Right click on the tables and drag it into the design pan in the right hand side.

Next step is to integrate the relation ship between the two tables. Simple drag and drop the arrow from emp table to empDetails table. You can check the details relationship in the left bottom of the BDC Meta Man navigation bar.

Once the relation and rule implementation has been done through BDC, the next step is to create the ADF file. Before proceed to this, you need to setup the configuration so that BDC Metal Man can understand where to save the newly generated ADF file. Go to Configuration >> Settings

You need to select the path where the file will be generated.

This is the final step of the Part1. To generate the file, click on Build menu and then click on submenu Generate Defination File.

Go to the location and check the file. Below the file that has been generated for this example.

XMl File structure:

<?xml version=”1.0″ encoding=”utf-8″ standalone=”yes”?>
<LobSystem xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xsi:schemaLocation=”http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadataResource.xsd” Name=”MOSSLOBSystem” xmlns=”http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog”>
<Properties>
<Property Name=”WildcardCharacter” Type=”System.String”>%</Property>
</Properties>
<AccessControlList>
<AccessControlEntry Principal=”MOSS\administrator”>
<Right BdcRight=”Edit” />
<Right BdcRight=”Execute” />
<Right BdcRight=”SetPermissions” />
<Right BdcRight=”SelectableInClients” />
</AccessControlEntry>
<AccessControlEntry Principal=”MOSS\test1″>
<Right BdcRight=”Edit” />
<Right BdcRight=”Execute” />
<Right BdcRight=”SetPermissions” />
</AccessControlEntry>
</AccessControlList>
<LobSystemInstances>
<LobSystemInstance Name=”MOSSInstance”>
<Properties>
<Property Name=”DatabaseAccessProvider” Type=”Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAccessProvider”>SqlServer</Property>
<Property Name=”AuthenticationMode” Type=”Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAuthenticationMode”>PassThrough</Property>
<Property Name=”RdbConnection Data Source” Type=”System.String”>MOSS\SQLEXPRESS</Property>
<Property Name=”RdbConnection Initial Catalog” Type=”System.String”>MOSS</Property>
<Property Name=”RdbConnection Integrated Security” Type=”System.String”>SSPI</Property>
<Property Name=”RdbConnection Pooling” Type=”System.String”>false</Property>
</Properties>
</LobSystemInstance>
</LobSystemInstances>
<Entities>
<Entity Name=”dbo.emp”>
<Properties>
<Property Name=”DefaultAction” Type=”System.String”>View Profile</Property>
</Properties>
<AccessControlList>
<AccessControlEntry Principal=”MOSS\administrator”>
<Right BdcRight=”Edit” />
<Right BdcRight=”Execute” />
<Right BdcRight=”SetPermissions” />
<Right BdcRight=”SelectableInClients” />
</AccessControlEntry>
<AccessControlEntry Principal=”MOSS\test1″>
<Right BdcRight=”Edit” />
<Right BdcRight=”Execute” />
<Right BdcRight=”SetPermissions” />
</AccessControlEntry>
</AccessControlList>
<Identifiers>
<Identifier Name=”[empid]” />
</Identifiers>
<Methods>
<Method Name=”Getdbo.[emp]“>
<Properties>
<Property Name=”RdbCommandText” Type=”System.String”>Select [empid],[userid],[pwd],[email] From dbo.[emp]</Property>
<Property Name=”RdbCommandType” Type=”System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089″>Text</Property>
</Properties>
<AccessControlList>
<AccessControlEntry Principal=”MOSS\administrator”>
<Right BdcRight=”Edit” />
<Right BdcRight=”Execute” />
<Right BdcRight=”SetPermissions” />
<Right BdcRight=”SelectableInClients” />
</AccessControlEntry>
<AccessControlEntry Principal=”MOSS\test1″>
<Right BdcRight=”Edit” />
<Right BdcRight=”Execute” />
<Right BdcRight=”SetPermissions” />
</AccessControlEntry>
</AccessControlList>
<Parameters>

Part Two

In this part, we will see how to feed up this newly generated ADF file into SharePoint through shared service provider. To do so, open the SharePoint Central Administration GUI and click on the shared service provider.
Click on Import application definition link (the first link) under Business Data Catalog

Browse the generated XML file from your file system and click on Import button. This will process the XML file generated the ADF inside SharePoint.

Completing the process, it will display the information that “Application Definition Import Successful”.  Click Ok, and there will be next pages to provide permission and other associated properties setup pages for BDC. So, you have done the second part.

Part Three

Third part is to integrate and use the BDC in the SharePoint site so the data from the LOB could be visible in SharePoint. To implement the same in SharePoint, go to site collection administration GUI i.e. go to site settings >>  Click on Site features under Site Administration section.

Activate Office SharePoint Server Enterprise Site features there if it is not in Active state so far.

Now, go to the home page and click on Edit Page under Site action. The page is in design view, and you can add web part there.

• Click on Add Web Part in the middle webpart zone.
• Select Business Data List under Business Data webpart catalog and click on Add. This webpart is now added on your page, and you need to setup the webpart so that this could use the correct application definition file.

Click on “Open the tool pane and choose the type of data to display”. Next, click on Browse image under Type and select the correct ADF file from the list.
Now, go to the Appearance section and provide proper title of this BDC data catalog (default name is Business Data List).

Click on apply and then click on ok. Close the design box. Here we go.

So good so far. Now, let’s back to the SQL Server.  Add few more data in the emp table. Again come back to the SharePoint GUI, and refresh the data. YES, the updated data is reflected there.

Good luck