Business Connectivity Service Associations using Stored Procedures – BCS Meta Man

Introduction

In this tutorial we will show you how to connect to Microsoft SQL Server and use stored procedures to create an association between two External Content Types using BCS Meta Man.

We are going to follow on from the ‘BCS Meta Man – Using Stored Procedures – Adding a Specific Finder and IdEnumerator

Prerequisites

  • SharePoint Foundation 2010 or SharePoint Server 2010
  • Visual Studio Professional or higher
  • BCS Meta Man
  • AdventureWorks SQL Database
  • A SQL Stored Procedure for the Finder Method for the Orders Table such as
USE [AdventureWorks]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Lightning Tools Ltd
-- Create date: 07/16/2010
-- Description:    Sample Stored Procedure to return Order information
-- =============================================
CREATE PROCEDURE [dbo].[uspGetOrders]
    -- Add the parameters for the stored procedure here
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT     CustomerID, SalesOrderID, TotalDue
FROM         SalesLT.SalesOrderHeader
END

GO

  • A SQL Stored Procedure for the association such as
USE [AdventureWorks]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Lightning Tools Ltd
-- Create date: 07/16/2010
-- Description:    Sample association Stored Procedure to return Orders by Customer ID
-- =============================================
CREATE PROCEDURE [dbo].[uspGetOrdersByCustomerID]
    -- Add the parameters for the stored procedure here
    @CustomerID int = 0
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT     SalesLT.Product.Name,
SalesLT.SalesOrderDetail.OrderQty,
SalesLT.SalesOrderHeader.TotalDue,
SalesLT.SalesOrderHeader.CustomerID, SalesLT.SalesOrderDetail.SalesOrderID,
SalesLT.Product.Color FROM SalesLT.SalesOrderDetail INNER JOIN SalesLT.SalesOrderHeader ON SalesLT.SalesOrderDetail.SalesOrderID = SalesLT.SalesOrderHeader.SalesOrderID
INNER JOIN SalesLT.Product ON SalesLT.SalesOrderDetail.ProductID = SalesLT.Product.ProductID WHERE (SalesLT.SalesOrderHeader.CustomerID = @CustomerID) END GO

Walkthrough

  1. Complete the ‘BCS Meta Man – Using Stored Procedures – Adding a Specific Finder and IdEnumerator’ Tutorial

  2. You should have the following External Content Type on your Diagram

    External Content Type already on diagram

  3. From the ‘BCS Meta Man Data Source Explorer’ drag and drop your ‘Orders’ Stored Procedure Finder Method onto the diagram
  4. Name the External Content Type ‘Order’, Click ‘OK’
  5. Select ‘Finder’ as the Method Type, Click ‘OK’
  6. You should now have 2 External Content Types on the diagram

    Both External Content Types on the design surface

  7. Switch to the ‘Toolbox’ pane in Visual Studio (Ctrl-Alt-X)
  8. Select the ‘Association’ toolbox item from the list

    Select the Association toolbox control

  9. With the Association selected, Click and Drag from ‘Customer’ to ‘Orders’

     

    drag and drop between your two ECTs

  10. When you release the ‘Association Configuration’ dialog will show
  11. Switch back to the ‘BCS Meta Man Data Source Explorer’ tool window pane
  12. Drag the Association Stored Procedure onto the Arrow in the ‘Association Configuration’ dialog, it will turn green when OK to release.

    Drag and drop your association stored procedure

  13. The dialog will update with the selected Stored Procedure

    Map the Store Procs parameters to identifiers

  14. Select ‘CustomerID’ in both the Parameter and Identifier drop-down list boxes, Click ‘Save’
  15. The diagram will update to show an arrow indicating we have an association between the two External Content Types

    Association has been created

  16. Press F5 to deploy, this will load up your SharePoint Page once deployed
  17. Add a new Business Data List Web Part to your SharePoint page
  18. Click on the ‘Open the tool pane’ link
  19. Click on the icon to show the available External Content Types

    Open the picker to select your ECT 

  20. Select our ‘BCSMetaManStoredProcedure.Customer’ External Content Type, click OK 

    Select the Customer External Content Type

  21. Click ‘OK’ on the tool pane
  22. Click the ‘Add’
  23. Your External System data is now displayed in the SharePoint Web Part

    Business Data List Web Part

  24. Next, Add a new ‘Business Data Related List’ Web Part to the page
  25. Configure the Web Part to use the ‘Orders’ External Content Type
  26. The Web Part will display a message saying the following

    Need to setup web part connections

  27. To do this we can set up the connection using the small arrow from the top right of our Web Part

    Use the web part menu to create a connection

  28. Once this is done we can click on the arrows next to an item in our ‘Customer’ web part and their orders will display on our ‘Orders’ web part

    Related list web part now show association data

<phill/>