How to: Configure an Association Navigator Using a Stored Procedure


Microsoft® SharePoint® Designer 2010 provides only the ability to create one-to-many associations between external content types (ECTs). However, a line-of-business (LOB) application might require many-to-many relationships as well. To support many-to-many associations, you must edit the Business Data Catalog (BDC) Model file to include an association navigator.

Note: This how-to topic assumes that you have an existing BDC Model (.bdcm) file with ECTs and a many-to-many relationship that needs to be modeled correctly. To create the BDC Model file used in this topic, see How to: Create and Export a BDC Model Using SharePoint Designer.


To configure an association navigator by using a stored procedure

  1. Open the database that you created when you created the BDC Model.
  2. Add a Microsoft SQL Server® stored procedure that will go through the MachineParts table named GetPartsByMachineID, and return the parts for a given machine ID. See the following example.
     --  -------------------------------------------------    
    --  Author:       <Author,,Name>
    --  Create date:  <Create Date,,>
    --  Description:  <Description,,>
    --  --------------------------------------------------
    CREATE PROCEDURE GetPartsByMachineId
          -- Add the parameters for the stored procedure here
          @machineId int
         -- SET NOCOUNT ON added to prevent extra result sets from
         -- interfering with SELECT statements.
         SET NOCOUNT ON;
         -- Insert statements for procedure here
         SELECT Parts.* FROM Machines
         INNER JOIN MachineParts ON Machines.ID=MachineParts.MachineId
         INNER JOIN Parts ON MachineParts.PartSKU=Parts.SKU
         WHERE Machines.ID=@machineId;
  3. Open the BDC Model file in an XML editor, and find the <Entity> tag for the Parts ECT.
  4. For each entity element in the file, modify the namespace in <Entity Namespace=”<your name space> to <Entity Namespace="DataModels.ExternalData.PartsManagement".
  5. In the <Methods> element, add a new <Method> that defines a method for navigating the many-to-many relationship to retrieve parts for the computer, as shown in the following example. This method is called an association navigator.
    <Method Name="GetPartsForMachine" DefaultDisplayName="Get Parts For Machine">
    Note: The <Method> tag must conform to the BDC Model Schema definition. You can find the BdcMetadata.XSD file in the \TEMPLATE\ directory of your Microsoft Office SharePoint Server 2007 installation, typically at <Root>\Program Files\Microsoft Office Server\14\TEMPLATE\XML\BDCMetaData.xsd.

  6. Add the following child properties for the method that you created in step 4. These properties describe the stored procedure.
    <Method Name="GetPartsForMachine" DefaultDisplayName="Get Parts For Machine">
         <Property Name="BackEndObject"  Type="System.String">GetPartsByMachineID</Property>
         <Property Name="BackEndObjectType" Type="System.String">SqlServerRoutine</Property>
         <Property Name="RdbCommandText" Type="System.String">[dbo].[GetPartsByMachineID]</Property>
         <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089">StoredProcedure</Property>
         <Property Name="Schema" Type="System.String">dbo</Property> 
  7. After the properties, specify the input and return parameter values in the Parameters element for the method, as shown in the following example. The Parameters element is a child of the <Method> tag.
      <Parameter Direction="In" Name="@machineId">
        <TypeDescriptor TypeName="System.Nullable`1[[System.Int32, mscorlib, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089]]"  IdentifierName="Id" IdentifierEntityName="Machines" IdentifierEntityNamespace="DataModels.ExternalData.PartsManagement" Name="@machineId">
            <DefaultValue MethodInstanceName="GetPartsByMachineID" Type="System.Int32">1</DefaultValue>
      <Parameter Direction="Return" Name="GetPartsByMachineID">
        <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="GetPartsByMachineID">
            <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="GetPartsByMachineIDElement">
                <TypeDescriptor TypeName="System.String" IdentifierName="SKU" Name="SKU">
                    <Property Name="Size" Type="System.Int32">255</Property>
                    <NormalizeString FromLOB="NormalizeToNull" ToLOB="NormalizeToEmptyString" />
                <TypeDescriptor TypeName="System.String" Name="Name">
                    <Property Name="ShowInPicker" Type="System.Boolean" >true</Property>
                    <Property Name="Size" Type="System.Int32" >255</Property>
                    <NormalizeString FromLOB="NormalizeToNull" ToLOB="NormalizeToNull" />
                <TypeDescriptor TypeName="System.String"  Name="Description">
                    <Property Name="Size" Type="System.Int32" >255</Property>
                    <NormalizeString FromLOB="NormalizeToNull" ToLOB="NormalizeToNull" />
  8. Define the method instance as a child of the method element, which describes this method as an association navigator type. See the following example.
      <Association Name="GetPartsByMachineID" Type="AssociationNavigator" ReturnParameterName="GetPartsByMachineID" DefaultDisplayName="Parts Read with Sproc">
        <SourceEntity Namespace="DataModels.ExternalData.PartsManagement" Name="Machines" />
        <DestinationEntity Namespace="DataModels.ExternalData.PartsManagement" Name="Parts"/>
  9. Define an AssociationGroup for the new association. The AssociationGroup must be located after the closing tag for the Methods (</Methods>). See the following example.
        <AssociationGroup Name="PartAssociationGroup">
          <AssociationReference AssociationName="GetPartsByMachineID" Reverse="false" EntityNamespace="DataModels.ExternalData.PartsManagement" EntityName="Parts" />
  10. Save the new .bdcm file.
  11. Use the SharePoint Central Administration Web page to import the updated .bdcm file. To do this:
    1. Open Central Administration, and browse to Application Management.
    2. Click Application Management, click Manage Service Applications, and then select Business Data Connectivity Service.
    3. Because the ECT’s were already created, they will appear in the list of ECT’s. You must delete the Machines, Parts, and MachineParts external content types before you import the .bdcm file. Select them individually, and then, on the ribbon, click Delete.
    4. On the ribbon, click Import, and then browse to the .bdcm file that you created. Click OK.
    5. Confirm that the file was imported successfully (there should not be any import errors. You should see the following Warnings screen. You can ignore the warnings.