Business Connectivity Services Model using Stored Procedures – BCS Meta Man

Introduction

In this tutorial I will show you how to connect to your External System using BCS Meta Man, and use an SQL Stored Procedure to return data to a SharePoint Business Data List. In this walkthrough we will be creating just a Finder Method with no filters so our Stored Procedure should have no parameters defined.

Prerequisites

  • SharePoint Foundation 2010 or SharePoint Server 2010
  • Visual Studio Professional or higher
  • BCS Meta Man
  • AdventureWorks SQL Database
  • An SQL Stored Procedure without parameters 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 Customer information
-- =============================================
CREATE PROCEDURE [dbo].[uspGetCustomer]
    -- 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, Title, FirstName, LastName, EmailAddress
FROM         SalesLT.Customer
END

GO

Walkthrough

  1. Open Visual Studio 2010
  2. Add a New Project
  3. Select ‘SharePoint’ > ‘Lightning Tools (2010)’ , now you can select the BCS Meta Man project type.
  4. Give your project a name i.e BCSMetaManStoredProcedure 

    BCS Meta Man Project Item  

  5. Click ‘OK’
  6. On the next dialog box, enter the name of where you want to deploy your model, and make sure you select ‘Deploy as a farm solution’ , click ‘Finish’

    Deploy it as a farm solution 

  7. To show the ‘BCS Meta Man Data Source Explorer’ we need to enable it from the Menu item, this new window can be docked so it doesn’t get in your way.

    BCS Meta Man Data Source Explorer

  8. Click the ‘Add Connection’ button to show the ‘Connection Dialog’
  9. Select the Data Source type, enter your Server name and also select whether to ‘Use Windows Authentication’ or you provide logon credentials, click ‘Connect’ 

    Connect to Microsoft SQL Server

  10. The Data Source Explorer will now be populated with your Server databases

    SQL Server databases displayed

  11. Expand your Database and find the Finder Method Stored Procedure

    Expand to view your Stored Procedures

  12. Drag the Stored Procedure onto the main Diagram

    Drag a stored procedure onto the design surface 

  13. Select ‘Database’ as the Model Type and click ‘OK’

    Choose the Database Model Type

  14. You will be prompted for a name of the External Content Type being created

    Enter a name for your External Content Type

  15. Select the Method Type as ‘Finder’’ , click ‘OK’

    Select Finder as the method type

  16. The External Content Type will be created on the Diagram

    External Content Type created on diagram

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

    Select External Content Type for Business Data List 

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

    Pick your External Content Type

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

    Data is displayed in the Business Data List Web Part

<Phill/>