Business Data Catalog (BDC) Part - 1

Hi All,

In a simplest manner if I explain you what BDC means that it can be nothing better than saying that fetching, displaying and interacting business data into the MOSS.

see Business Data Catalog (BDC) Part 2

and

Business Data Catalog (BDC) Part - 3

Business Data Catalog (BDC) Part - 4

Business Data Catalog (BDC) Part - 5

Business Data Catalog (BDC) Part - 6

Business Data Catalog (BDC) Part - 7

for further reading.

One important thing to keep in mind that it is not available in standard edition of MOSS, for this facility you have to buy very expensive Enterprise edition of MOSS.

Any database record can be brought in to MOSS with the help of BDC. The main advantage of using BDC is that it is no code solution. By no code, I mean you do not need to write functions that uses the stored procedure to fetch the data, returns you data reader or dataset and then you use them to bind it to grid or loop through them and bind it to any source control.

Yes, here you need not to write any code for that. All you need to write is one XML file that will be the bottom line for BDC to execute. Yes, of course it needs practice to work on that XML, but once you are comfortable with it, then really it is an easy task.

Being XML in the background process of BDC, you can connect almost with every source of database. You can connect you SQL Server, Oracle, My SQL or most databases. You can then have a facility to make a search out of it that displays the result set in the grid form that is the built in form of BDC.

Here we have to use BDC web part to display the data being fetched and executed by XML.

So without talking much let’s dive into BDC first part to understand very basic concept of it.

Here we will take Pubs database of SQL Server as an example to understand BDC. Consider Authors table in it. We have au_fname column as a text field. We will create a BDC XML that will connect to SQL Server database and fetch the record from the search query from BDC web part and then web part display the result.
Let’s start with the XML.

<?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 BDCMetadata.xsd" Type="Database" Version="1.0.0.1" Name="Authors" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">

<Properties>

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

</Properties>

<LobSystemInstances>

<LobSystemInstance Name="AuthorsTraders">

<Properties>

<!--AuthenticationMode can be set to PassThrough, RevertToSelf, RdbCredentials, or WindowsCredentials. -->

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

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

<Property Name="RdbConnection Data Source" Type="System.String">I3punestn7
</Property>

<Property Name="RdbConnection Initial Catalog" Type="System.String">Pubs</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 EstimatedInstanceCount="100" Name="Author">

<!-- EstimatedInstanceCount is an optional attribute-->

<Properties>

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

</Properties>

<Identifiers>

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

</Identifiers>

<Methods>

<!-- Defines a method that brings back Customer data from the back-end database.-->

<Method Name="GetAuthors">

<Properties>

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

SELECT au_fname, city, state,address FROM authors WHERE au_fname = @au_fname

</Property>

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

<!-- For database systems, can be Text, StoredProcedure, or TableDirect. -->

</Properties>

<FilterDescriptors>

<!-- Define the filters supported by the back-end method (or sql query) here. -->

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

<Properties>

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

</Properties>

</FilterDescriptor>

</FilterDescriptors>

<Parameters>

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

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

<DefaultValues>

<DefaultValue MethodInstanceName="AuthorFinderInstance" Type="System.String">Dean</DefaultValue>

</DefaultValues>

</TypeDescriptor>

</Parameter>

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

<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="AuthorsDataReader">

<TypeDescriptors>

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

<TypeDescriptors>

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

<LocalizedDisplayNames>

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

</LocalizedDisplayNames>

</TypeDescriptor>

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

<!-- Do not use the AssociatedFilter attribute in return parameters.-->

<LocalizedDisplayNames>

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

</LocalizedDisplayNames>

<Properties>

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

</Properties>

</TypeDescriptor>

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

<LocalizedDisplayNames>

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

</LocalizedDisplayNames>

<Properties>

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

</Properties>

</TypeDescriptor>

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

<LocalizedDisplayNames>

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

</LocalizedDisplayNames>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstance Name="AuthorFinderInstance" Type="Finder" ReturnParameterName="Authors" />

</MethodInstances>

</Method>

</Methods>

<!-- Enter your Action XML here -->

</Entity>

</Entities>

</LobSystem>

Now, you have to go to your central administration. Go there and go to your SSP created. There under Business Data Catalog section click on import application definition. Browse through your XML that we have just created, and import it.

When you import the application, if you find this message coming on the screen, ignore it for now. Else there is a problem with your XML, you need to correct it.



Once you are done with this, hit OK and come to a page where it shows a summary about your application that you have just uploaded.



Now open a page where you want to import this BDC web part. Open your site and click on edit this page. Add web part, search for Business Data List web part under Business Data Section of All Web parts. Once imported, click on tool pane.



And then search for your web part from there, and select the application that we had uploaded.



After importing it, it will look very similar to this.



Now query the data and you will see the result set.

When I worked with the BDC, I got in lot of troubles. I initially faced lot of problems because nothing was succeeding. Even my XML was right, at the last moment when I press retrieve data, it was giving me Error. Then I looked very carefully at XML then I realized that number of fields that you want to retrieve must be there in the type descriptor tag. Also keep in mind that if there are three fields in the query, you must have only three type descriptor in XML. This is my hard work. :)

So finally you have data in return.



This ends our first part. I will continue this with next part when we will achieve some more functionality.