BCS Stored Procedures – Specific Finder and IdEnumerator

Introduction

In this walkthrough we will be using Microsoft SQL Server Stored Procedures to add a Specific Finder and IdEnumerator Method to an existing BCS Meta Man External Content Type. By adding a Specific Finder Method we will be able to use the profile view on a record as well as being able to add an Association with another External Content Type. The IdEnumerator will give us the required functionality to perform searches on our External System data.

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 Specific Finder Method

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 single Customer information
-- =============================================
CREATE PROCEDURE [dbo].[uspGetCustomerByCustomerID]
    -- Add the parameters for the stored procedure here
    @CustomerID int = null
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     Title, FirstName, LastName, EmailAddress, Phone, CustomerID
FROM         SalesLT.Customer
WHERE     (CustomerID = @CustomerID)
END
GO

• A SQL Stored Procedure for the IdEnumerator Method:

USE [AdventureWorks]
GO
/****** Object:  StoredProcedure [dbo].[uspGetCustomerIDs]    Script Date: 07/16/2010 12:22:46 ******/
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 Customer Ids
-- =============================================
CREATE PROCEDURE [dbo].[uspGetCustomerIDs]
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
        -- Insert statements for procedure here
    SELECT CustomerID from SalesLT.Customer
    Order by CustomerID
END
GO

Walkthrough

1. Follow the ‘BCS Meta Man – BCS Model using Stored Procedures’ tutorial until you have a single External Content Type with a Finder Method defined

   

2. From the ‘BCS Meta Man Data Source Explorer’ drag and drop your Specific Finder Stored Procedure Method onto the existing External Content Type

   

3. The ‘Stored Procedure Configuration’ dialog will display
4. Select ‘Specific Finder’ as the Method Type
5. Select the first Parameter – CustomerID and set the Filter Type as Comparison and the Default Value as ‘0’

   

6. Click ‘OK’
7. The External Content Type will update with the new Specific Finder Method

   

8. Next drag and drop the Id Enumerator Method onto the existing External Content Type

   

9. Select ‘IdEnumerator’ as the Method Type, Click ‘OK’

   

10. Again, the External Content Type will update with the new Id Enumerator Method

    

11. The External Content Type is now ready to be deployed.

<phill/>