how to create a database connection for the Business Data Catalog in Microsoft Office SharePoint Server 2007

• Download Code - 1.18 KB

Introduction

The Business Data Catalog feature of Microsoft Office SharePoint Server 2007 provides an easy way to integrate business data from back-end server applications. You register business data exposed in databases or through Web services in the Business Data Catalog by creating metadata that describes the database or Web service. The Business Data Catalog then uses this metadata to make the right calls into the data source to retrieve the relevant data. The objective of this article access the LOB application’s data through BDC and display it in the Sharepoint site inside the Business Data Lists Webpart. To generate LOB we can use different tools like Microsoft business Data Catalog Definition Editor for Microsoft office SharePoint server 2007 or BDCMetaMan.  The BDCMetaMan is not free but Microsoft business Data Catalog Definition Editor is free.

What is Problem with Microsoft business Data Catalog Definition?

When we generate LOB with Microsoft BDC Editor it does not generate Method instance “Finder” but it does not show up in the list when trying to add it to a Business Data list. We have to add “Finder” method instance to XML file manually. After adding “Finder” it will show BDC in Business Data type Picker. 

Start Writing the BDC Metadata in XML

Metadata is an XML file. Start writing xml, In this we start with LobSystem tag. LobSystem tag define the namespace , The name of the LOB System, the type of system Database or web service.

<LobSystemInstances>

 <LobSystemInstance Name="Test1" >

 <Properties>

 <Property Name="rdbconnection Data Source" Type="System.String">SMTSRV01</Property>

 <Property Name="rdbconnection Initial Catalog" Type="System.String">Test</Property>

 <Property Name="rdbconnection Integrated Security" Type="System.String">SSPI</Property>

 <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 Pooling" Type="System.String">false</Property>

 </Properties>

</LobSystemInstance>

Now, create an entity and define its properties.

<Entity EstimatedInstanceCount="100" Name="Product">

<Properties>

<Property Name="Name" Type="System.String">Name</Property>

</Properties>

<Identifiers>

<Identifier Name="ProductID" TypeName="System.String" />

</Identifiers>

There should be a method that gets the details of the entity based on the input parameters.  The following tags define a method called “FindProducts” which has a query tha t gets the name and productid of the products entity based on the entered productid. Also the input parameter is defined using the parameter tag with Direction attribute as “In”. The output is the product entity having two members namely name and productid which is defined in the parameter tags with the Direction attribute as “Return”.

<Methods>

 <Method Name="FindProducts">

 <Properties>

 <Property Name="RdbCommandText" Type="System.String">

 SELECT ProductID, Name,Price FROM Product WHERE ProductID = @ProductID

 </Property>

 <Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>

 </Properties>

 <FilterDescriptors>

 <FilterDescriptor Type="Comparison" Name="ProductID">

 <Properties>

 <Property Name="Comparator" Type="System.String">Equals</Property>

 </Properties>

 </FilterDescriptor>

 </FilterDescriptors>

 <Parameters>

 <Parameter Direction="In" Name="@ProductID">

 <TypeDescriptor TypeName="System.String" IdentifierName="ProductID"
    AssociatedFilter="ProductID" Name="ProductID">

 <DefaultValues>

 <DefaultValue MethodInstanceName="ProductFinderInstance"
    Type="System.String" > 1 </DefaultValue>

 </DefaultValues>

 </TypeDescriptor>

 </Parameter>

 <Parameter Direction="Return" Name="Products">

 <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0,
    Culture=neutral, PublicKeyToken=b77a5c561934e089"

 IsCollection="true" Name="ProductDataReader">

 <TypeDescriptors>

 <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0,
    Culture=neutral, PublicKeyToken=b77a5c561934e089"

 Name="ProductDataRecord">

 <TypeDescriptors>

 <TypeDescriptor TypeName="System.String" IdentifierName="ProductID" Name="ProductID">

 <LocalizedDisplayNames>

 <LocalizedDisplayName LCID="1033">ProductID</LocalizedDisplayName>

 </LocalizedDisplayNames>

 </TypeDescriptor>
  <TypeDescriptor TypeName="System.String" Name="Price">

 <LocalizedDisplayNames>

 <LocalizedDisplayName LCID="1033">Price</LocalizedDisplayName>

 </LocalizedDisplayNames>

 </TypeDescriptor>

 <TypeDescriptor TypeName="System.String" Name="Name">

 <LocalizedDisplayNames>

 <LocalizedDisplayName LCID="1033">Name</LocalizedDisplayName>

 </LocalizedDisplayNames>

 <Properties>

 <Property Name="DisplayByDefault" Type="System.Boolean">true</Property>

 </Properties>

 </TypeDescriptor>

 </TypeDescriptors>

 </TypeDescriptor>

 </TypeDescriptors>

 </TypeDescriptor>

 </Parameter>

 </Parameters>

Now create an instance fof the “FindProducts” method..

<MethodInstances>

 <MethodInstance Name="ProductFinderInstance" Type="Finder"
    ReturnParameterName="Products" />

</MethodInstances>

The complete metadata code is as follows:

<?xml version="1.0" encoding="utf-8" standalone="yes"?>

<LobSystem xmlns:xsi=<a href="http://www.w3.org/2001/XMLSchema-instance">http://www.w3.org/2001/XMLSchema-instance</a>
    xsi:schemaLocation="http://schemas.microsoft.com
/office/2006/03/BusinessDataCatalog BDCMetadata.xsd
" Type="Database" Version="1.0.0.0" Name="Test" 
    xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">

 <LobSystemInstances>

 <LobSystemInstance Name="Test" >

 <Properties>

 <Property Name="rdbconnection Data Source" Type="System.String">ServerName</Property>

 <Property Name="rdbconnection Initial Catalog" Type="System.String">DataBaseName</Property>

 <Property Name="rdbconnection Integrated Security" Type="System.String">SSPI</Property>

 <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 Pooling" Type="System.String">false</Property>

 </Properties>

 </LobSystemInstance>

 </LobSystemInstances>

 <Entities>

 <Entity EstimatedInstanceCount="100" Name="Product">

 <Properties>

 <Property Name="Name" Type="System.String">Name</Property>

 </Properties>

 <Identifiers>

 <Identifier Name="ProductID" TypeName="System.String" />

 </Identifiers>

 <Methods>

 <Method Name="FindProducts">

 <Properties>

 <Property Name="RdbCommandText" Type="System.String">

 SELECT ProductID, Name,Price FROM Product WHERE ProductID = @ProductID

 </Property>

 <Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>

 </Properties>

 <FilterDescriptors>

 <FilterDescriptor Type="Comparison" Name="ProductID">

 <Properties>

 <Property Name="Comparator" Type="System.String">Equals</Property>

 </Properties>

 </FilterDescriptor>

 </FilterDescriptors>

 <Parameters>

 <Parameter Direction="In" Name="@ProductID">

 <TypeDescriptor TypeName="System.String" IdentifierName="ProductID"
    AssociatedFilter="ProductID" Name="ProductID">

 <DefaultValues>

 <DefaultValue MethodInstanceName="ProductFinderInstance"
     Type="System.String" > 1 </DefaultValue>

 </DefaultValues>

 </TypeDescriptor>

 </Parameter>

 <Parameter Direction="Return" Name="Products">

 <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0,
    Culture=neutral, PublicKeyToken=b77a5c561934e089"

 IsCollection="true" Name="ProductDataReader">

 <TypeDescriptors>

 <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0,
    Culture=neutral, PublicKeyToken=b77a5c561934e089"

 Name="ProductDataRecord">

 <TypeDescriptors>

 <TypeDescriptor TypeName="System.String" IdentifierName="ProductID" Name="ProductID">

 <LocalizedDisplayNames>

 <LocalizedDisplayName LCID="1033">ProductID</LocalizedDisplayName>

 </LocalizedDisplayNames>

 </TypeDescriptor>

  <TypeDescriptor TypeName="System.String" Name="Price">

 <LocalizedDisplayNames>

 <LocalizedDisplayName LCID="1033">Price</LocalizedDisplayName>

 </LocalizedDisplayNames>

 </TypeDescriptor>

 <TypeDescriptor TypeName="System.String" Name="Name">

 <LocalizedDisplayNames>

 <LocalizedDisplayName LCID="1033">Name</LocalizedDisplayName>

 </LocalizedDisplayNames>

 <Properties>

 <Property Name="DisplayByDefault" Type="System.Boolean">true</Property>

 </Properties>

 </TypeDescriptor>

 </TypeDescriptors>

 </TypeDescriptor>

 </TypeDescriptors>

 </TypeDescriptor>

 </Parameter>

 </Parameters>

 <MethodInstances>

 <MethodInstance Name="ProductFinderInstance" Type="Finder" ReturnParameterName="Products" />

 </MethodInstances>

 </Method>

 </Methods>

 </Entity>

 </Entities>

</LobSystem>

After writing LOB we have to Import this to Sharepoint web site.

• Go to the central administration and select your SSP.

  

• Browse your metadata file and click “Import”

  

• A warning message will come. Click “Ok”.

  Now, go to your Sharepoint site and click “Edit Page” under Site Actions

• Click “Add a Web Part”
• Select the “Business Data List” Web Part under Business Data and click Add
• In the newly added Business Data List Web Part click Edit and select Modify Shared Web Part
• In the type textbox, click Browse

  

• In the Business Data Type Picker dialog box, select your BDC file and click Ok.

Now the Webpart will look like the following.

History 

06/1/2009