Thursday, November 20, 2008

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.

4 comments:

Anonymous said...

hi

i dont knwon that where can i write that xml file means at what location. and we have to write manually on same xml file or anyother settings for that ?

Plz help me i'm very excite for do BDC.

Malay Vasavada said...

Hi,

You can write this XML file anywhere. In notepad also. and just save it as XML file later onnce you are done with you XML structuring.

Then simply upload and follow the article.

You can also buy BDC XML Generator tool if you do not want to write it own your own.

Let me know if you have any further query.

Thanks.

Anonymous said...

Thanks Manam for help me now i have tool of BDC XML Generator tool in it i add the lobsystem then connect my database of sqlserver and then drag n drop my table. but after these process at end i dont get that xml file which generated by this tool.

can u plz tell me how generate from this tool i followed steps which i wrote before but i n able to get that xml file.

plz help me !!!

SharePoint Kings said...

there are few tools available on net.
if you can buy then BDC Metaman is the one which is famous.




Share your SharePoint Experiences with us...
As good as the SharePointKings is, we want to make it even better. One of our most valuable sources of input for our Blog Posts comes from ever enthusiastic Visitors/Readers. We welcome every Visitor/Reader to contribute their experiences with SharePoint. It may be in the form of a code stub, snippet, any tips and trick or any crazy thing you have tried with SharePoint.
Send your Articles to sharepointkings@gmail.com with your Profile Summary. We will Post them. The idea is to act as a bridge between you Readers!!!

If anyone would like to have their advertisement posted on this blog, please send us the requirement details to sharepointkings@gmail.com