Step 4 (Optional): Define Associations

Article
10/16/2013

Associations link related entities within a line-of-business (LOB) system. For example, a customer is associated with a sales order in the AdventureWorks system because a customer makes sales orders. There is a one-to-many relationship between customers and sales orders. An association holds pointers to the parent and child entities as well as a pointer to the business logic that allows a client to get the child entity from the parent entity. The traversal of an association is merely a method call on the LOB system. Associations make it easier for you to create master-detail applications. For more details on associations in the Business Data Catalog, see Association.

This topic shows you how to define an association between customers and sales orders in the AdventureWorks2000 database. Finding the sales orders of a particular customer is a common business need. Defining this association in the Business Data Catalog enables you to find the sales orders, given a customer ID. Defining associations is a two-step process. First, define the method in the Customer entity that will return the sales orders, given a customer ID. Second, define an association between the two entities, Customer and SalesOrder, by specifying the method defined in the Customer entity.

Note

This is a simple example of a one-source, one-destination entity. If you have multiple source entities and only one destination entity, you might expect to define the association logic in the destination entity. However, the Business Data Catalog allows you to define the association logic in any entity in the same LobSystem instance.

Prerequisites

Step 2: Define Entities, Methods, and Filters, and if needed, Step 3 (Optional): Define Actions

To define an association

Open the AdventureWorks2000.xml file from Step 2 or Step 3.

Add the following XML within the <Entities> tag, after the Product entity definition. This XML defines the SalesOrder and Customer entities. Notice that the Customer entity also contains a definition for a method called GetSalesOrdersForCustomer() that returns the sales orders, given a customer ID. This is the association method.

<Entity EstimatedInstanceCount="10000" Name="SalesOrder">
      <LocalizedDisplayNames>
        <LocalizedDisplayName LCID="1033">Sales Order</LocalizedDisplayName>
      </LocalizedDisplayNames>
      <Identifiers>
        <Identifier Name="SalesOrderID" TypeName="System.Int32" />
      </Identifiers>
      <Methods>
        <Method Name="GetSalesOrders">
          <Properties>
            <Property Name="RdbCommandText" Type="System.String">
              SELECT SalesOrderID, OrderDate, SubTotal, IndividualID
              FROM SalesOrderHeader, Individual WHERE (SalesOrderID &gt;= @MinSalesOrderID)
              AND (SalesOrderID &lt;= @MaxSalesOrderID) AND (SalesOrderNumber LIKE @SalesOrderNumber)
              AND SalesOrderHeader.CustomerID = Individual.CustomerID
            </Property>
            <Property Name="RdbCommandType" Type="System.String">Text</Property>
          </Properties>
          <FilterDescriptors>
            <FilterDescriptor Type="Comparison" Name="ID" >
              <Properties>
                <Property Name="Comparator" Type="System.String">Equals</Property>
              </Properties>
            </FilterDescriptor>
            <FilterDescriptor Type="Wildcard" Name="SalesOrderNumber" />
          </FilterDescriptors>
          <Parameters>
            <Parameter Direction="In"  Name="@MinSalesOrderID">
              <TypeDescriptor TypeName="System.Int32" IdentifierName="SalesOrderID" 
                              AssociatedFilter="ID" Name="MinSalesOrderID">
                <DefaultValues>
                  <DefaultValue MethodInstanceName="SalesOrderFinderInstance" Type="System.Int32">0</DefaultValue>
                </DefaultValues>
              </TypeDescriptor>
            </Parameter>
            <Parameter Direction="In"  Name="@MaxSalesOrderID">
              <TypeDescriptor TypeName="System.Int32" IdentifierName="SalesOrderID" AssociatedFilter="ID" Name="MaxSalesOrderID">
                <DefaultValues>
                  <DefaultValue MethodInstanceName="SalesOrderFinderInstance" Type="System.Int32">99999999</DefaultValue>
                </DefaultValues>
              </TypeDescriptor>
            </Parameter>
            <Parameter Direction="In"  Name="@SalesOrderNumber">
              <TypeDescriptor TypeName="System.String" AssociatedFilter="SalesOrderNumber" Name="SalesOrderNumber">
                <DefaultValues>
                  <DefaultValue MethodInstanceName="SalesOrderFinderInstance" Type="System.String">%</DefaultValue>
                  <DefaultValue MethodInstanceName="SalesOrderSpecificFinderInstance" Type="System.String">%</DefaultValue>
                </DefaultValues>
              </TypeDescriptor>
            </Parameter>
            <Parameter Direction="Return"  Name="SalesOrders">
              <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" IdentifierName="SalesOrderID" Name="SalesOrderID">
                        <LocalizedDisplayNames>
                          <LocalizedDisplayName LCID="1033">ID</LocalizedDisplayName>
                        </LocalizedDisplayNames>
                      </TypeDescriptor>
                      <TypeDescriptor TypeName="System.DateTime" Name="OrderDate">
                        <LocalizedDisplayNames>
                          <LocalizedDisplayName LCID="1033">Order Date</LocalizedDisplayName>
                        </LocalizedDisplayNames>
                        <Properties>
                          <Property Name="DisplayByDefault" Type="System.Boolean">true</Property>
                        </Properties>
                      </TypeDescriptor>
                      <TypeDescriptor TypeName="System.Int32" Name="IndividualID">
                        <LocalizedDisplayNames>
                          <LocalizedDisplayName LCID="1033">IndividualID</LocalizedDisplayName>
                        </LocalizedDisplayNames>
                        <Properties>
                          <Property Name="DisplayByDefault" Type="System.Boolean">true</Property>
                        </Properties>
                      </TypeDescriptor>
                      <TypeDescriptor TypeName="System.Decimal" Name="SubTotal">
                        <LocalizedDisplayNames>
                          <LocalizedDisplayName LCID="1033">SubTotal</LocalizedDisplayName>
                        </LocalizedDisplayNames>
                        <Properties>
                          <Property Name="DisplayByDefault" Type="System.Boolean">true</Property>
                        </Properties>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </TypeDescriptors>
              </TypeDescriptor>
            </Parameter>
          </Parameters>
          <MethodInstances>
            <MethodInstance Name="SalesOrderFinderInstance" Type="Finder" ReturnParameterName="SalesOrders" />
            <MethodInstance Name="SalesOrderSpecificFinderInstance" Type="SpecificFinder" ReturnParameterName="SalesOrders" />
          </MethodInstances>
        </Method>
      </Methods>
    </Entity>
    <Entity EstimatedInstanceCount="10000" Name="Customer">
      <Properties>
        <Property Name="Title" Type="System.String">FirstName</Property>
      </Properties>
      <Identifiers>
        <Identifier Name="IndividualID" TypeName="System.Int32" />
      </Identifiers>
      <Methods>
        <Method Name="GetCustomers">
          <Properties>
            <Property Name="RdbCommandText" Type="System.String">SELECT * FROM Individual WHERE (IndividualID &gt;= @MinIndividualID) AND (IndividualID &lt;= @MaxIndividualID) AND ((FirstName+' '+LastName) LIKE @Name)</Property>
            <Property Name="RdbCommandType" Type="System.String">Text</Property>
          </Properties>
          <FilterDescriptors>
            <FilterDescriptor Type="Comparison" Name="ID" >
              <Properties>
                <Property Name="Comparator" Type="System.String">Equals</Property>
              </Properties>
            </FilterDescriptor>
            <FilterDescriptor Type="Wildcard" Name="Name" />
          </FilterDescriptors>
          <Parameters>
            <Parameter Direction="In"  Name="@MinIndividualID">
              <TypeDescriptor TypeName="System.Int32" IdentifierName="IndividualID" AssociatedFilter="ID" Name="MinIndividualID">
                <DefaultValues>
                  <DefaultValue MethodInstanceName="CustomerFinderInstance" Type="System.Int32">0</DefaultValue>
                </DefaultValues>
              </TypeDescriptor>
            </Parameter>
            <Parameter Direction="In"  Name="@MaxIndividualID">
              <TypeDescriptor TypeName="System.Int32" IdentifierName="IndividualID" AssociatedFilter="ID" Name="MaxIndividualID">
                <DefaultValues>
                  <DefaultValue MethodInstanceName="CustomerFinderInstance" Type="System.Int32">99999999</DefaultValue>
                </DefaultValues>
              </TypeDescriptor>
            </Parameter>
            <Parameter Direction="In"  Name="@Name">
              <TypeDescriptor TypeName="System.String" AssociatedFilter="Name" Name="Name">
                <DefaultValues>
                  <DefaultValue MethodInstanceName="CustomerFinderInstance" Type="System.String">%</DefaultValue>
                  <DefaultValue MethodInstanceName="CustomerSpecificFinderInstance" Type="System.String">%</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.Int32" IdentifierName="IndividualID" Name="IndividualID">
                        <LocalizedDisplayNames>
                          <LocalizedDisplayName LCID="1033">ID</LocalizedDisplayName>
                        </LocalizedDisplayNames>
                      </TypeDescriptor>
                      <TypeDescriptor TypeName="System.String" Name="FirstName">
                        <Properties>
                          <Property Name="DisplayByDefault" Type="System.Boolean">true</Property>
                        </Properties>
                      </TypeDescriptor>
                      <TypeDescriptor TypeName="System.String" Name="LastName">
                        <Properties>
                          <Property Name="DisplayByDefault" Type="System.Boolean">true</Property>
                        </Properties>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </TypeDescriptors>
              </TypeDescriptor>
            </Parameter>
          </Parameters>
          <MethodInstances>
            <MethodInstance Name="CustomerFinderInstance" Type="Finder" ReturnParameterName="Customers" />
            <MethodInstance Name="CustomerSpecificFinderInstance" Type="SpecificFinder" ReturnParameterName="Customers" />
          </MethodInstances>
        </Method>
        <Method Name="GetSalesOrdersForCustomer">
          <Properties>
            <Property Name="RdbCommandText" Type="System.String">SELECT SalesOrderID, OrderDate, SubTotal,Individual.IndividualID FROM SalesOrderHeader,Individual WHERE SalesOrderHeader.CustomerID=Individual.CustomerID and Individual.IndividualID=@IndividualID</Property>
            <Property Name="RdbCommandType" Type="System.String">Text</Property>
          </Properties>
          <Parameters>
            <Parameter Direction="In"  Name="@IndividualID">
              <TypeDescriptor TypeName="System.Int32" IdentifierName="IndividualID" Name="IndividualID" />
            </Parameter>
            <Parameter Direction="Return"  Name="SalesOrders">
              <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="SalesOrder" IdentifierName="SalesOrderID" Name="SalesOrderID">
                        <LocalizedDisplayNames>
                          <LocalizedDisplayName LCID="1033">ID</LocalizedDisplayName>
                        </LocalizedDisplayNames>
                      </TypeDescriptor>
                      <TypeDescriptor TypeName="System.DateTime" Name="OrderDate">
                        <LocalizedDisplayNames>
                          <LocalizedDisplayName LCID="1033">Order Date</LocalizedDisplayName>
                        </LocalizedDisplayNames>
                        <Properties>
                          <Property Name="DisplayByDefault" Type="System.Boolean">true</Property>
                        </Properties>
                      </TypeDescriptor>
                      <TypeDescriptor TypeName="System.Int32" Name="IndividualID">
                        <LocalizedDisplayNames>
                          <LocalizedDisplayName LCID="1033">IndividualID</LocalizedDisplayName>
                        </LocalizedDisplayNames>
                        <Properties>
                          <Property Name="DisplayByDefault" Type="System.Boolean">true</Property>
                        </Properties>
                      </TypeDescriptor>
                      <TypeDescriptor TypeName="System.Decimal" Name="SubTotal">
                        <LocalizedDisplayNames>
                          <LocalizedDisplayName LCID="1033">SubTotal</LocalizedDisplayName>
                        </LocalizedDisplayNames>
                        <Properties>
                          <Property Name="DisplayByDefault" Type="System.Boolean">true</Property>
                        </Properties>
                      </TypeDescriptor>
                    </TypeDescriptors>
                  </TypeDescriptor>
                </TypeDescriptors>
              </TypeDescriptor>
            </Parameter>
          </Parameters>
        </Method>
      </Methods>
      <Actions>
        <Action Name="Send Email" Position="1" IsOpenedInNewWindow="true" Url="mailto:{0}.msn.com" ImageUrl="">
          <ActionParameters>
            <ActionParameter Name="FirstName" Index="0" />
          </ActionParameters>
        </Action>
      </Actions>
    </Entity>

Add the following XML after the <Entities> tag to define the association between the Customer and SalesOrder entities:

  <Associations>
    <Association AssociationMethodEntityName="Customer" AssociationMethodName="GetSalesOrdersForCustomer" AssociationMethodReturnParameterName="SalesOrders" Name="CustomerToSalesOrder" IsCached="true">
      <!-- Associations are just subclasses of MethodInstances. 
      They can also take ReturnTypeDescriptorName optionally. For 
      details, see SampleWebServiceMetadata.-->
      <SourceEntity Name="Customer" />
      <DestinationEntity Name="SalesOrder" />
      <!-- The source and destination entities can be the same. 
      For more information, see the SampleWebService example.-->
    </Association>
  </Associations>

Save the XML file.
Before you add the application definition again, you must delete the AdventureWorksSample application you created in Step 1 from the Business Data Catalog. To delete AdventureWorksSample, follow these steps:
1. Open SharePoint 3.0 Central Administration.
2. In the left navigation pane, click the name of your Shared Services Provider (SSP).
3. In the Business Data Catalog section, click View Applications to view the registered applications.
4. Click AdventureWorksSample to open the View Application: AdventureWorksSample page.
5. Finally, click Delete Application in the Application Settings section.
Now, add the application definition to the Business Data Catalog. For details, see How to: Add an Application Definition to the Business Data Catalog.
Test the metadata by creating a Business Data List and an Association Web Part. For details, see How to: Test a Business Data Association.

Next Steps

Step 5 (Optional): Define IDEnumerator Methods and Enable Business Data Search

Step 4 (Optional): Define Associations

Prerequisites

To define an association

Next Steps

See Also

Tasks

Concepts

Additional resources