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

   

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

   

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

   

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

    

   

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.

    

13. The dialog will update with the selected Stored Procedure

    

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

    

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

     

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

    

21. Click ‘OK’ on the tool pane
22. Click the ‘Add’
23. Your External System data is now displayed in the SharePoint 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

    

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

    

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

    

<phill/>