BCS Model using Stored Procedures with Parameters – BCS Meta Man

Introduction

In this tutorial we will show you how to connect to a Microsoft SQL Server database using BCS Meta Man and a Stored Procedure to return filtered data to a SharePoint Business Data List. In this walkthrough we will be creating a Finder Method with filters so our Stored Procedure needs to have parameters defined.

Prerequisites

• SharePoint Foundation 2010 or SharePoint Server 2010
• Visual Studio Professional or higher
• BCS Meta Man
• AdventureWorks SQL Database
• A SQL Stored Procedure with 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 filtered Customer information
-- =============================================
CREATE PROCEDURE [dbo].[uspGetCustomerFiltered]
    -- Add the parameters for the stored procedure here
    @FirstName nvarchar(50) = null,
    @LastName nvarchar(50) = 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 CustomerID, Title, FirstName, LastName, EmailAddress from SalesLT.Customer
    where FirstName  = IsNull(@FirstName,FirstName)
    and LastName = IsNull(@LastName,LastName)
END

GO

Walkthrough

1. Open Visual Studio 2010
2. Add a New Project
3. Select the ‘SharePoint 2010’ node, and then the ‘Empty SharePoint Project’ project type
4. Give your project a name i.e BCSMetaManFilteredStoredProcedure 

     

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. Add a new item to the newly created Project, either right-click on the Project in the Solution Explorer and Navigate to Add New Item, or select the Project Item and press ‘Ctrl+Shift+A’
8. Select the ‘Lightning Tools’ node, select ‘BCSMetaMan’ Item Type, give your model a name, such as ‘CustomerModel’ and click ‘Add’ 

   

9. If prompted, click Trial on the licensing dialog

   

10. 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. 

    

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

    

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

    

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

    

15. Drag the Stored Procedure onto the main Diagram

      

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

    

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

    

18. Select the Method Type as ‘Finder’’
19. Select the first Parameter (FirstName) and set it’s Filter Type to Comparison and the Default Value to ‘*’

    

20. Next select the second Parameter (LastName) and also set it’s Filter Type to Comparison and the Default Value to ‘*’

    

21. Click ‘OK’
22. The External Content Type will be created on the Diagram

    

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

     

27. Select our ‘BCSMetaManFilteredStoredProcedure.Customer’ External Content Type, click ‘OK’ 

    

28. Click ‘OK’ on the tool pane
29. Click the ‘Add’ link on the Business Data List Web Part to add the second Filter option

    

    [Click to view a larger image]

30. Change the second Filter to be the LastName and enter values for the filters and click ‘Retrieve Data’
31. Your External System data is now displayed in the SharePoint Web Part 

     

<phill/>