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.
- 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
- 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.
- 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.
- 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.
- 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
- Next select the second Parameter (@pName) and also set it’s Default Value to “A”. Click “Create” button.
- You should have the following External Content Type on your Diagram
- 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.
- In the model deployment URL textbox you should manually type URL of SharePoint 2013 Central Administration . Click “Save” button.
- 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.
- SharePoint 2013 will now go through and validate your BDC model file is correct, once it is done you will get a success dialog
- 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.
- Click on the “Open the tool pane” link
- Click on the icon to show the available External Content Types
- Select our “Northwind_MetaMan.Product” External Content Type, click “OK”. Click “OK” on the tool pane then.
- Click the ‘Add’ link on the Business Data List Web Part to add the second Filter option
- 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 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/>
