BCS Stored Procedures – Specific Finder and IdEnumerator in SharePoint 2013 using Meta Man

By

In this walkthrough we will be using Microsoft SQL Server Stored Procedures to add a Specific Finder and IdEnumerator operations to an existing External Content Type based on Finder Stored Procedure. By adding a Specific Finder Method we will be able to use the profile view on a record as well as being able to add an Association with another External Content Type. The IdEnumerator will give us the required functionality to perform searches on our External System data.

We are going to follow on from the External Content Type based on Stored Procedure in SharePoint 2013 using Meta Man walk through.

  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 for the Specific Finder Method:USE [Northwind]
      GO
      CREATE PROCEDURE
      [dbo].[GetTerritoryByID]
      @tID nvarchar(20)
      AS
      BEGIN
      SET NOCOUNT ON;
      SELECT
      * FROM [Territories] WHERE [TerritoryID]=@tID
      END
      GO
       
    • A SQL Stored Procedure for the IdEnumerator Method:USE [Northwind]
      GO
      CREATE PROCEDURE
      [dbo].[GetTerritoryIDs]
      AS
      BEGIN
      SET NOCOUNT ON;
      SELECT
      [TerritoryID] FROM [Territories];
      END
      GO
       

     

  2. Follow the External Content Type based on Stored Procedure in SharePoint 2013 using Meta Man <URL MUST BE CORRECTED> tutorial until you have a single External Content Type with a Finder Method definedExternal Content Type with Finder operation 
  3. From the Stored Procedures list drag and drop your Specific Finder Stored Procedure Method onto the existing External Content TypeDrag a Specific Finder stored procedure on the design surface 
  4. You will be prompted for a operation type. Select “Specific Finder” and click “OK”.Select “Specific Finder” operation type 
  5. For each input parameter select the appropriate identifier to map it to. Click “Create”.Configure Specific Finder operation 
  6. The External Content Type will update with the new Specific Finder Method External Content Type with Finder and Specific Finder operations 
  7. Next drag and drop the IdEnumerator stored procedure onto the existing External Content Type Drag an IdEnumerator stored procedure on the design surface 
  8. Select “IdEnumerator” as the operation type, Click “OK” Select “IdEnumerator” operation type 
  9. Click “Create” button.Configure IdEnumerator operation 
  10. Again, the External Content Type will update with the new IdEnumerator operationExternal Content Type with Finder, Specific Finder and IdEnumerator operations 
  11. 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 
  12. In the model deployment URL textbox you should manually type URL of SharePoint 2013 Central Administration . Click “Save” button.Set BDC model deployment URL  
  13. 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 
  14. 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
  15. Browse to the site where you want to add the External List. Click “Site Contents”->”add an app”.Add an app to SharePoint site
  16. Select “External List” Select “External List”
  17. Enter a name for External list and click on the icon to show the available External Content Types External list name
  18. Select our generated Northwind_MetaMan.Territory External Content Type, click “OK”Select External Content Type
  19. Click “Create” Create External List
  20. Your External System data from MS SQL database is now displayed in the SharePoint External List External data from MS SQL database is displayed on SharePoint 2013 page
  21. You are able to view any record. Select “View Item” menu item.View External List Item
  22. You will be presented with the View Item pageView Item page
  23. We do have a few extra steps to do within the SharePoint UI in order to get our Territories appearing in our Search results. At first we need to get our External Content Type to create its profile page. Open up SharePoint 2013 Central Administration, click on Manage Service Applications. In the Service Applications screen click on the Business Data Connectivity Service Application link to open up it’s administration page. Hover the mouse over our Territory ECT and from the content drop down pick “Create/Upgrade Profile Page”Create/Upgrade Profile Page
  24. Click OK on the dialog message that shows up. Now our profile page will be createdProfile page
  25. Now we need to set our BCS application as a Search Content Source. Go back to the Manage Service Applications page and click the Search Service Application link. In the left hand sidebar menu click the Content Sources link, then in the next page click the New Content Source linkSearch Content Source
  26. Give your new Content Source a name, and select it’s type to be Line of Business Data. Upon selecting this the UI will refresh and the next section down will refresh to show External Data Sources. Select BCS application you want to crawl in our case MetaManLobSystemInstance_test. Click “OK”Select BCS application you want to crawl
  27. Perform a full crawl nowPerform a full crawl
  28. Once the crawl has finished you can go to your Search Center site, search for “Santa” and you’ll see a few results being returned from our Territory External Content Type.The data from MS SQL appears in SharePoint Search results

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/>