External Content Type based on Stored Procedures

By admin BCS Meta Man Blog 16th September 2013

In this tutorial we will show you how to connect to your External System using 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 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 )
- An SQL Stored Procedure without parameters such as: USE [Northwind]
  GO
  CREATE PROCEDURE [dbo].[GetAllTerritories]
  AS
  BEGIN
  SET NOCOUNT ON;
  SELECT * FROM [Territories]
  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].[GetAllTerritories] 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”. 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.Territory” External Content Type, click “OK”. Click “OK” on the tool pane then.
Your External System data is now displayed in the SharePoint Web Part