BCS filters and MS SQL Stored Procedures with Parameters in SharePoint 2013

By

In this tutorial we will show you how to connect to a Microsoft SQL Server database using 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 BCS filters so our Stored Procedure needs to have parameters defined.

  1. Prerequisites:
    • SharePoint Server 2013
    • Meta Man (it doesn’t necessarily need to be installed on the SharePoint server but it requires .net Framework 4.5 installed on the machine where you are going to run Meta Man from)
    • MS SQL server with any sample database (for this walk through we are going to use the NorthWind sample database )
    • A SQL Stored Procedure with parameters such as :USE [Northwind]
      GO
      CREATE PROCEDURE [dbo].[GetProductsUsing2FiltersProductID_and_ProductName]
      @pID int,
      @pName nvarchar(80)
      AS
      BEGIN
      SET NOCOUNT ON;
      SELECT * FROM [Products] WHERE
      [ProductID]<@pID and [ProductName] like ‘%’+@pName+’%’
      END
      GO
  2. Open Meta Man and choose to connect to a SQL Server data source. Select or enter the database name you wish to work with and press arrow in order to connect to selected database.

    Connect to MS SQL server

  3. Upon successfully connecting to SQL Server you’ll see another window. In order to get a list of stored procedures click “Stored Procedures” item in the list on the left pane. Now we can see listed all the stored procedures for this particular database.

    List of stored procedures

  4. We create an external content type by dragging and dropping [dbo].[GetProductsUsing2FiltersProductID_and_ProductName] stored procedure onto our design surface which is the large area on the right side.

    Drag a stored procedure on the design surface

  5. When you drop the stored procedure you will be shown the following dialog, which allows to configure the external content type before creation. You should enter the external content type name and select the operation type as “Finder”. Select the first Parameter (@pID) and set it’s Default Value to zero for example

    Configure the external content type

  6. Next select the second Parameter (@pName) and also set it’s Default Value to “A”. Click “Create” button.

    Set default values for parameters

  7. You should have the following External Content Type on your Diagram

    Created External Content Type

  8. Now we need to deploy our BDC model. First we need to set URL of where you want to deploy your model. We set this URL by going “Settings” from the title bar.

    Meta Man settings

  9. In the model deployment URL textbox you should manually type URL of SharePoint 2013 Central Administration . Click “Save” button.

    Set BDC model deployment URL

  10. Once you have saved the URL to deploy your BDC model file to simply click “Deploy” button and your BDC model file will get deployed.

    Deploy BDC model file

  11. SharePoint 2013 will now go through and validate your BDC model file is correct, once it is done you will get a success dialog

    BDC Model was imported

  12. Browse to the SharePoint page where you want to add a Business Data List web part. Add a new Business Data List Web Part to this SharePoint page.

    Add Business Data List Web Part

  13. Click on the “Open the tool pane” link

     Open the tool pane

  14. Click on the icon to show the available External Content Types

    Open the picker to select the External Content Type 

  15. Select our “Northwind_MetaMan.Product” External Content Type, click “OK”. Click “OK” on the tool pane then.

    Select External Content Type

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

  17. Change the second Filter to be the @pName and enter values for the filters and click “Retrieve Data”. Your External System data provided by Stored Procedure is now displayed in the SharePoint Web Part

    Filtered external data from MS SQL database is displayed on SharePoint 2013 page We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com

    <Dmitry Kaloshin/>