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 

     

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’

    

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.

   

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’ 

   

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

    

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

    

12. Drag the Stored Procedure onto the main Diagram

     

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

    

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

    

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

    

16. The External Content Type will be created on the 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

     

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

    

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

    

<Phill/>