SharePoint 2007: BDC - Introducing associations between entities

Posted on 4/24/2007 @ 5:19 AM in #SharePoint by | Feedback | 17194 views


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:

  • Introduction
  • In-built Webparts
  • Lists with Business data type columns.
  • Search
  • User Profiles
  • Other custom applications targeting a common runtime object model.
  • Making it easier to author XML Applications: BDCMetaMan

    In my series on BDC, I just got done describing a somewhat complete business data application definition, that lets you import data from Northwind.Customers. I provided both a Finder method and a specific finder method. In this blogpost, I am going to demonstrate adding associations between multiple entities defined in a BDC app. Specifically, I will add an entity for "Order", and I will fetch out orders for a given Customer.

  •  

    To achieve this, use the following steps -

    • Start with the XML Goo you already used here.
    • Add a new entity shown as below -
    • <Entity EstimatedInstanceCount="20" Name="Order">

         <Identifiers>

           <Identifier Name="OrderID" TypeName="System.Int32"/>

         </Identifiers>

      </Entity>

    • Under the Customers Entity, add a new method shown as below -
    • <Method Name="GetOrdersForCustomer">

        <Properties>

          <Property Name="RdbCommandText" Type="System.String">Select OrderID, ShipName, ShipAddress, ShipCity From Orders Where CustomerID=@CustomerID</Property>

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

        </Properties>

        <Parameters>

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

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

          </Parameter>

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

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

              <TypeDescriptors>

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

                  <TypeDescriptors>

                    <TypeDescriptor TypeName="System.Int32" IdentifierEntityName="Order" IdentifierName="OrderID" Name="OrderID">

                      <LocalizedDisplayNames>

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

                      </LocalizedDisplayNames>

                    </TypeDescriptor>

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

                      <LocalizedDisplayNames>

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

                      </LocalizedDisplayNames>

                      <Properties>

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

                      </Properties>

                    </TypeDescriptor>

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

                      <LocalizedDisplayNames>

                        <LocalizedDisplayName LCID="1033">Ship Address</LocalizedDisplayName>

                      </LocalizedDisplayNames>

                      <Properties>

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

                      </Properties>

                    </TypeDescriptor>

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

                      <LocalizedDisplayNames>

                        <LocalizedDisplayName LCID="1033">Ship City</LocalizedDisplayName>

                      </LocalizedDisplayNames>

                      <Properties>

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

                      </Properties>

                    </TypeDescriptor>

                  </TypeDescriptors>

                </TypeDescriptor>

              </TypeDescriptors>

            </TypeDescriptor>

          </Parameter>

        </Parameters>

      </Method>

    • As you can see above, I've added a method "GetOrdersForCustomer", that runs a SQL Query and selects certain fields for "Order" for the specified CustomerID. Pretty simple stuff.
    • Next, add the relevant association -
    • <Associations>

        <Association AssociationMethodEntityName="Customer" AssociationMethodName="GetOrdersForCustomer" AssociationMethodReturnParameterName="Orders" Name="CustomerToOrder" IsCached="true">

          <SourceEntity Name="Customer" />

          <DestinationEntity Name="Order" />

        </Association>

      </Associations>

    • Thats it.

    Save and Redeploy the application using the following steps -

  • Go to the SSP
  • Click n Business Data catalog//view applications --> Delete the previous application (or you could also version, but I'm gonna be lazy and consistently lazy)
  • 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". You will see the Profile Page Creation skipped message for "Orders".
  • Now go ahead and use the application exactly as I described in this blog post here. You should see Customers and an individual customer details show up just as before.

    In order to use the above association,

    • Add a "Business Data Related List" webpart.
    • Choose Order (NorthWindTraders) entity, and the appropriate relationship (CustomerToOrder). My screen looks like this -
    • Hit "OK".
    • Now go ahead and connect the Customer List webpart to the newly added "Order List" webpart as shown below -
    • Click "Exit Edit Mode"
    • You can now see the webparts connected and working as shown below:

    As you can see, you can use the supplied association and query for orders for a given customer.

    Sound off but keep it civil:

    Older comments..


    On 1/23/2008 2:37:32 PM sanjay said ..
    in this step, I get the following error:

    Application definition import failed. The following error occurred: The IdentifierName Attribute of the TypeDescriptor named 'CustomerID' of the Parameter named '@CustomerID' of the Method named 'GetOrdersForCustomer' of the Entity named 'Order' does not match any of the defined Identifiers for the Entity. Error was encountered at or just before Line: '314' and Position: '16'.

    whats the problem?


    On 2/28/2008 11:08:09 PM Wayne said ..
    I get the same error as Sanjay...

    Application definition import failed. The following error occurred: The IdentifierName Attribute of the TypeDescriptor named 'OrderID' of the Parameter named 'Orders' of the Method named 'GetOrdersForCustomer' of the Entity named 'Customer' does not match any of the defined Identifiers for the Entity. Error was encountered at or just before Line: '164' and Position: '13'.

    Is there a solution for the problem.


    On 3/7/2008 6:53:14 AM Johan said ..
    Just add the new Order entity above the Cutomer entity in XMLGoo


    On 7/8/2008 2:02:47 PM mike said ..
    it is unclear where the associations should go.


    On 7/8/2008 2:08:33 PM mike said ..
    Ok I figured it out by looking at the schema (http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog/bdcMetadata.xsd). It goes directly under the LobSystem. Also it appears order is a big issue in the XML. So the entities must come first, in the order of Order, Customer. The association must come after entities or an error will be thrown saying it cannot find the Order entity.

    Hope this helps someone save time.


    On 8/25/2008 11:31:33 AM Bani said ..
    I would like to create a filter that has a predefined list of values (sort of like a drop down for US States). So the filter would be "State" "is equal to" and then a drop down list for the users to choose from. Is that possible? If so, how could I do that?


    On 8/27/2008 12:28:08 PM Muhammad Arshad said ..
    Does BDC provide Realtime Syncronization.?


    Means i update the list ..Will it update the my database?


    On 9/12/2008 5:04:30 PM Dhwani said ..
    I am trying to create a Data List Web Part, with a Related List Web part. I wanted the Related List webpart to have Actions. Here is the file:

    <?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="WebService" Version="1.0.0.0" Name="LookingAgain" xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">;


    <Properties>


    <Property Name="WsdlFetchUrl" Type="System.String">http://myserver.ddd.net/ServiceName/SearchService.svc?wsdl</Property>;


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


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


    </Properties>


    <LobSystemInstances>


    <LobSystemInstance Name="LookingAgain_Instance">


    <Properties>


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


    <Property Name="WebServiceAuthenticationMode" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.WebService.HttpAuthenticationMode">PassThrough</Property>


    </Properties>


    </LobSystemInstance>


    </LobSystemInstances>


    <Entities>


    <Entity EstimatedInstanceCount="10000" Name="Case">


    <Properties>


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


    </Properties>


    <Identifiers>


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


    </Identifiers>


    <Methods>


    <Method Name="GetCasesByID">


    <FilterDescriptors>


    <FilterDescriptor Type="Wildcard" Name="CaseID" />


    </FilterDescriptors>


    <Parameters>


    <Parameter Direction="In" Name="CaseID">


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IdentifierName="CaseID" AssociatedFilter="CaseID" Name="CaseID" />


    </Parameter>


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


    <TypeDescriptor TypeName="BDC.CaseList[],LookingAgain" IsCollection="true" Name="Return">


    <TypeDescriptors>


    <TypeDescriptor TypeName="BDC.CaseList,LookingAgain" Name="Item">


    <TypeDescriptors>


    <TypeDescriptor TypeName="System.Boolean, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CanEdit" />


    <TypeDescriptor TypeName="System.Boolean, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CanEditSpecified" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IdentifierName="CaseID" Name="CaseID" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CaseNumber" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CaseStatus" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CaseType" />


    <TypeDescriptor TypeName="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CaseTypeID" />


    <TypeDescriptor TypeName="System.Boolean, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CaseTypeIDSpecified" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CaseYear" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="Notes" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="RepEntityID" />


    </TypeDescriptors>


    </TypeDescriptor>


    </TypeDescriptors>


    </TypeDescriptor>


    </Parameter>


    </Parameters>


    <MethodInstances>


    <MethodInstance Type="Finder" ReturnParameterName="Return" ReturnTypeDescriptorName="Return" ReturnTypeDescriptorLevel="0" Name="CaseFinder" />


    <MethodInstance Type="SpecificFinder" ReturnParameterName="Return" ReturnTypeDescriptorName="Return" ReturnTypeDescriptorLevel="0" Name="CaseSpecificFinder" />


    </MethodInstances>


    </Method>


    </Methods>


    </Entity>


    <Entity EstimatedInstanceCount="10000" Name="Player">


    <Properties>


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


    </Properties>


    <Identifiers>


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


    </Identifiers>


    <Methods>


    <Method Name="SearchData_Simple">


    <FilterDescriptors>


    <FilterDescriptor Type="Wildcard" Name="RepEntityID" />


    <FilterDescriptor Type="Wildcard" Name="EntityClub" />


    </FilterDescriptors>


    <Parameters>


    <Parameter Direction="In" Name="RepEntityID">


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IdentifierName="RepEntityID" AssociatedFilter="RepEntityID" Name="RepEntityID" />


    </Parameter>


    <Parameter Direction="In" Name="EntityClub">


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" AssociatedFilter="EntityClub" Name="EntityClub" />


    </Parameter>


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


    <TypeDescriptor TypeName="BDC.BDCData[],LookingAgain" IsCollection="true" Name="Return">


    <TypeDescriptors>


    <TypeDescriptor TypeName="BDC.BDCData,LookingAgain" Name="Item">


    <TypeDescriptors>


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="EntityAgent" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="EntityClub" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="EntityCollege" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="EntityID" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="EntityName" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="EntityPos" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="EntityType" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IdentifierName="RepEntityID" Name="RepEntityID" />


    </TypeDescriptors>


    </TypeDescriptor>


    </TypeDescriptors>


    </TypeDescriptor>


    </Parameter>


    </Parameters>


    <MethodInstances>


    <MethodInstance Type="Finder" ReturnParameterName="Return" ReturnTypeDescriptorName="Return" ReturnTypeDescriptorLevel="0" Name="EntityFinder" />


    <MethodInstance Type="SpecificFinder" ReturnParameterName="Return" ReturnTypeDescriptorName="Return" ReturnTypeDescriptorLevel="0" Name="EntitySpecificFinder" />


    </MethodInstances>


    </Method>


    <Method Name="GetCasesByEntity">


    <FilterDescriptors>


    <FilterDescriptor Type="Wildcard" Name="RepEntityID" />


    </FilterDescriptors>


    <Parameters>


    <Parameter Direction="In" Name="RepEntityID">


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IdentifierName="RepEntityID" AssociatedFilter="RepEntityID" Name="RepEntityID" />


    </Parameter>


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


    <TypeDescriptor TypeName="BDC.CaseList[],LookingAgain" IsCollection="true" Name="Return">


    <TypeDescriptors>


    <TypeDescriptor TypeName="BDC.CaseList,LookingAgain" Name="Item">


    <TypeDescriptors>


    <TypeDescriptor TypeName="System.Boolean, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CanEdit" />


    <TypeDescriptor TypeName="System.Boolean, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CanEditSpecified" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IdentifierEntityName="Case" IdentifierName="CaseID" Name="CaseID" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CaseNumber" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CaseStatus" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CaseType" />


    <TypeDescriptor TypeName="System.Int32, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CaseTypeID" />


    <TypeDescriptor TypeName="System.Boolean, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CaseTypeIDSpecified" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="CaseYear" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="Notes" />


    <TypeDescriptor TypeName="System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IdentifierName="RepEntityID" Name="RepEntityID" />


    </TypeDescriptors>


    </TypeDescriptor>


    </TypeDescriptors>


    </TypeDescriptor>


    </Parameter>


    </Parameters>


    </Method>


    </Methods>


    </Entity>


    </Entities>


    <Associations>


    <Association Name="MethodInstance0" AssociationMethodEntityName="Player" AssociationMethodName="GetCasesByEntity" AssociationMethodReturnParameterName="Return" AssociationMethodReturnTypeDescriptorName="Return" AssociationMethodReturnTypeDescriptorLevel="0" IsCached="true">


    <SourceEntity Name="Player" />


    <DestinationEntity Name="Player" />


    </Association>


    </Associations>


    </LobSystem>

    But if I click on the ECB menu for the related list I get "No actions".

    WOuld someone be able to point out what needs to change in my app definition file?


    On 1/13/2009 4:23:05 PM Dennis said ..
    I created an application with two entities. For some reason, only one entity is available in the business data list. Then I created an additional application with yet another entity and imported it in the BDC. For some reason that application isn't available either. I can only use one single entity in total. Does somebody know what I am doing wrong?


    On 2/11/2010 3:56:40 PM Iraj said ..
    I am doing everything as you noted and it all works. However, I can not select anything in my bdc list. The radio buttons show up but when I click on any of them the page reloads without anything happening. What am I doing wrong? Thanks for your prompt reply