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 

    Create empty SharePoint project  

  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 as Farm Solution 

  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’ 

    Add a new BCS Meta Man project item

  9. If prompted, click Trial on the licensing dialog

    Use the trial version

  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. 

    Open BCS Meta Man Data Source Explorer

  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’ 

    Connect to your SQL Server

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

    Expand to see your databases

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

    Keep expanding to see the stored procedures

  15. Drag the Stored Procedure onto the main Diagram

    Drag the stored procedure onto the design surface  

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

    Select database as the model type

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

    Give your External Content Type a name

  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 ‘*’

    Configure the stored procedure and parameters

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

    Configure each parameter in turn

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

    External Content Type configured

  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

    Browse the External Content Types 

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

    Pick your External Content Type from the picker

  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

    To use multiple filters click the Add link

    [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 

    View your filtered data 

<phill/>