Click here to Skip to main content
Click here to Skip to main content

Tagged as

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

, 6 Jan 2009
Rate this:
Please Sign up or sign in to vote.
An easy way to connect external database in sharepoint using Business Data Catalog

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">

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

  • Go to the central administration and select your SSP.

    SSP_small.JPG

  • Browse your metadata file and click “Import”

    Import_small.JPG

  • 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

    Tool.JPG

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

Now the Webpart will look like the following.

Display_small.JPG

History 

06/1/2009

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ranjitpl
Software Developer SmartBuzz
India India
I am software engineer.I am working on Microsoft Technologies. Currently working in Asp.net,C#.net,Vb.net,DotNetNuke and Microsoft Office Sharepoint Server 2007 (MOSS). I am Microsoft Certified in Dot net Framework.

Comments and Discussions

 
QuestionWhat tool do use to create ADF file? PinmemberMember 1477851-Dec-11 18:17 
GeneralAwesome PinmemberVivek27946-Jan-09 19:35 
GeneralFantastic PinmemberManvindersingh6-Jan-09 18:07 
GeneralCool PinmvpAbhijit Jana6-Jan-09 17:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 6 Jan 2009
Article Copyright 2009 by Ranjitpl
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid