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

    Customer External Content Type

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

    Drag and Drop Stored Procedure onto 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’

    Configuring a Specific Finder Stored Procedure

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

    Customer External Content Type with Finder and Specific Finder Method

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

    Drag and Drop Stored Procedure onto existing External Content Type

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

    Configuring Id Enumerator Stored Procedure

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

    Customer External Content Type with Finder, Specific Finder and Id Enumerator Method

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

<phill/>