SharePoint 2007: BDC - The Business Data Catalog

This post is in continuation to a series of blogposts I put up on BDC - the Business Data Catalog.

Table of Contents:

Here is a good 30,000 foot view of BDC -

Some of you may ask "Well that ain't such a huge big deal", because you could also write a custom webpart to do exactly that, as you will see in the following blogposts - you are tad bit missing the point. Missing the point because, BDC is a no code solution. Even though, the honkin' XML goo you need to work with to make BDC work may leave you longing to write code instead, but a no code solution means, tools could be built to author and maintain this metadata, and programmers could be freed from this responsibility. Also, BDC works very very well with everything else in SharePoint (as I will demonstrate in the upcoming blogposts), so it is indeed much more sophisticated than writing your own webparts. I guess you could implement all of what BDC gives you by hand, but you could also row all the way to japan in a small boat - doesn't mean you should ! :-)

So to get BDC working, (i.e. bring external data into MOSS), you first need to author a BDC application. I'm going to call "BDC Application" as Big Honkin' XML Goo! (BHXG), or just XML Goo! in short, because I think it is a much more appropriate name. The Xml Goo needs to contain all the information necessary about the BDC application, and then it needs to be imported into the appropriate shared service provider.

This XML Goo is imported into the shared service provider that then takes the responsibility of reading all this metadata about the LOB (line of business) data, and making it available to various sharepoint sites, other sharepoint features, and other custom applications over a common runtime object model.

As I just said, the Xml Goo needs to contain all the information necessary about the BDC application, and then it needs to be imported into the appropriate shared service provider. Common sense would tell us that this information would need to contain atleast the following -

- Where is my LOB data

- How can I access it (i.e. what security model and credentials to use)

- What am I extracting (the definition of the entities)

- What are the parameters I wish to allow in extraction of these entities

- What custom actions can I perform on those entities

- If I have a number of entities, what are the various associations between those entities.

So let me write a quick "Hello World" BDC application that requires atleast the first 4 of the above points.

Now considering that all of you are familiar with "Northwind" (yeah yeah I know I'm boring, but atleast consistently boring), Northwind has a "Customers" table. If I wished to extract "Customer" using a SQL Query that looks like this -

SELECT CustomerID, ContactName, Address, City FROM Customers WHERE CustomerID = @CustomerID

I could specify a BDC application that would let me do exactly the above. The application would look a tad bit like this (get ready for the Huge Honkin' Xml Goo) -

<?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.0" Name="NorthWindApp" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">

  <Properties>

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

  </Properties>

  <LobSystemInstances>

    <LobSystemInstance Name="NorthWindTraders">

      <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">192.168.0.107\SQLSERVER</Property>

        <Property Name="RdbConnection Initial Catalog" Type="System.String">Northwind</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="Customer">

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

      <Properties>

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

      </Properties>

      <Identifiers>

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

      </Identifiers>

      <Methods>

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

        <Method Name="GetCustomers">

          <Properties>

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

              SELECT CustomerID, ContactName, Address, City FROM Customers WHERE CustomerID = @CustomerID

            </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="CustomerID" >

              <Properties>

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

              </Properties>

            </FilterDescriptor>

          </FilterDescriptors>

          <Parameters>

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

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

                <DefaultValues>

                  <DefaultValue MethodInstanceName="CustomerFinderInstance" Type="System.String">ALFKI</DefaultValue>

                </DefaultValues>

              </TypeDescriptor>

            </Parameter>

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

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

                <TypeDescriptors>

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

                    <TypeDescriptors>

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

                        <LocalizedDisplayNames>

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

                        </LocalizedDisplayNames>

                      </TypeDescriptor>

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

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

                        <LocalizedDisplayNames>

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

                        </LocalizedDisplayNames>

                        <Properties>

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

                        </Properties>

                      </TypeDescriptor>

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

                        <LocalizedDisplayNames>

                          <LocalizedDisplayName LCID="1033">Address</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="CustomerFinderInstance" Type="Finder" ReturnParameterName="Customers" />

          </MethodInstances>

        </Method>

      </Methods>

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

    </Entity>

  </Entities>

</LobSystem>

Now, don't tell me that I didnt' warn ya! It is huge eh? Now here's the sucky part - No IDE support to author the above, yeah you do have a schema, but if you work with BDC, you will soon find out why a schema just ain't enough. Some valiant efforts are underway to create tools that will allow a more friendly way of authoring a BDC application, I certainly hope MSFT addresses this in Orcas (but I am not counting on it either), but once you get beyond the above hugeness of XML, it ain't so bad.

If you sniff through the above XML, you will see various logical peices emerge. At the top, I have -

  <LobSystemInstances>

    <LobSystemInstance Name="NorthWindTraders">

      <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">192.168.0.107\SQLSERVER</Property>

        <Property Name="RdbConnection Initial Catalog" Type="System.String">Northwind</Property>

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

        <Property Name="RdbConnection Pooling" Type="System.String">false</Property>

      </Properties>

    </LobSystemInstance>

  </LobSystemInstances>

Nice huh? As you can see, I am using "PassThrough" authentication. PassThrough means, the dude (or dudette) loggedin to the sharepoint site using windows credentials, their credentials matter when connecting to the backend LOB data source. So this will work if,

a) You are using Kerberos, or
b) You are using NTLM and everything including the user is on a single machine (good for demos only).

Another thing you have to be careful of using "PassThrough" is, when "Search" tries to index BDC data, the "Search" user's credentials will be tried on the database. Yuck!. But the simplicity of this approach makes it very very appealing. Other options are RevertToSelf (where the Application pool identity takes over), WindowsCredentials (Office SSO), RdbCredentials (SSO + DbCredentials), or web service credentials sent as digest (challenge/response) or basic (clear text).

Next, in the above XML, I have defined an Entity called "Customer", Customer has an identifier called "CustomerID" (a.k.a. primary key in BDC land), and it has a method called "GetCustomers".

GetCustomers uses the simple SQL Statement I specified earlier

SELECT CustomerID, ContactName, Address, City FROM Customers WHERE CustomerID = @CustomerID

And, it contains the definition of the input parameter (CustomerID) alongwith a default value, and a return type of DataReader with the necessary representation for all columns.

Finally, I have a "MethodInstance" of type "Finder" with a return parameter of type "Customer". Interesting huh? The MethodInstance type affects how this method will be used, for instance "Finder" is the equivalent of pulling out a list, SpecificFinder is pulling out a singleton entity, IDbEnumerator is for search, and scalar is for a singleton value, and so on and so forth.

Great!

Now with the Honkin' Huge Xml Goo written, go ahead and import that into the Shared Service Provider using the following steps -

• Go to the SSP
• Click on Business data catalog//import application definition
• Browse to the Xml Goo as your "Application Definition File", choose to import as a "Model", and hit "Import"

If you have setup everything properly (including security), on both the database and the sharepoint server & network, you should see the following warning -

No method instance of type SpecificFinder defined for for application 'NorthWindApp', entity 'Customer'. Profile page creation skipped.

Ya'know -- just ignore that warning for now :-). What the above is telling you is that, "You fool, you forgot to include the "Specific Finder" kinda method instance, so you can't really view a single entity. Sure you can pull in a list - but what good does that do?"

Uhhm .. since this is just a Hello World example, just ignore it for now. We'll fix it later.

So guess what, assuming your security is setup properly, your BDC app is ready to eat. You can use it in the following manner.

• Go to your favorite sharepoint site.
• Edit a page that accepts webparts.
• Add a webpart called "Business Data List". If you created this site using the Blank Template, you will need to first go and enable this webpart in the webpart gallery (Site Actions --> Site Settings --> WebPart gallery).
• When you add the WebPart, it'd look like this -
  
• Do exactly that, Click on "Open the Tool Pane" (or you can also "Modify Shared Web Part" under the Edit menu).
• In the tool pane, put in "Customer (NorthWindTraders)" under the "Type" text field. It's better if you use the finder button, so there are no typos. Mine looks like this -
  
• Hit "OK" at the bottom of the page, and your webpart should look like this now -
  
• Click "Exit Edit Mode", and type in a Customer ID of "ALFKI" (or whatever else you please), hit "Retreive Data" and assuming you actually followed all I wrote in this post above properly, you should see results in your BDC webpart as shown below -
  

AWESOME! :-)

But, what good is a "List" with "One" item? :-/.

So let us next extend this example, so it fetches me a number of results back.