Business Connectivity Service Associations using DB2 Stored Procedures in SharePoint 2010

Introduction

In this walkthrough we will demonstrate you how to connect to DB2 Database and use stored procedures to create an association between two External Content Types using BCS Meta Man.

We are going to follow on from the ‘DB2 Stored Procedures and the Business Connectivity Services in SharePoint 2010

Prerequisites

  • Microsoft SharePoint Server 2010
  • Microsoft Visual Studio 2010 Professional or higher
  • BCS Meta Man
  • Download and install “IBM Data Server Runtime Client (Windows 32-bit AMD and Intel x86)” http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217 on a machine where BCS Meta Man runs
  • Download and install “IBM Data Server Runtime Client (Windows AMD64 and Intel EM64T)” http://www-01.ibm.com/support/docview.wss?rs=4020&uid=swg21385217 on a machine where SharePoint 2010 runs
  • DB2 stored procedures  such as:
    1. CREATE PROCEDURE GET_ALL_DEPARTMENTS()
      DYNAMIC RESULT SETS 1
      P1: BEGIN
      DECLARE v_departments CURSOR WITH RETURN for
      SELECT * FROM DEPARTMENT;
      OPEN v_departments;
      END P1
    2. CREATE PROCEDURE GET_DEPARTMENT_BY_ID(IN P_ID CHAR(3))
      DYNAMIC RESULT SETS 1
      P1: BEGIN
      DECLARE v_departments CURSOR WITH RETURN for
      select * from DEPARTMENT where DEPTNO=P_ID;
      OPEN v_departments;
      END P1
    3. CREATE PROCEDURE GET_ALL_EMPLOYEES()
      DYNAMIC RESULT SETS 1
      P1: BEGIN
      DECLARE v_employees CURSOR WITH RETURN for
      SELECT * FROM EMPLOYEE;
      OPEN v_employees;
      END P1
    4. CREATE PROCEDURE GET_EMPLOYEE_BY_ID(IN P_ID CHAR(6))
      DYNAMIC RESULT SETS 1
      P1: BEGIN
      DECLARE v_employees CURSOR WITH RETURN for
      select * from EMPLOYEE where EMPNO=P_ID;
      OPEN v_employees;
      END P1
    5. CREATE PROCEDURE GET_EMPLOYEE_BY_DEPARTMENT_ID(IN P_ID CHAR(3))
      DYNAMIC RESULT SETS 1
      P1: BEGIN
      DECLARE v_employees CURSOR WITH RETURN for
      select * from EMPLOYEE where WORKDEPT=P_ID;
      OPEN v_employees;
      END P1

Walkthrough

  1. Using the ‘DB2 Stored Procedures and the Business Connectivity Services in SharePoint 2010’ Tutorial create two External Content Types “Department” and “Employee” with Finder (Read List operation) and SpecificFinder (Read Item operation) methods only.
  2. You should have the following External Content Types on your Diagram

    Both External Content Types on the design surface

  3. Switch to the ‘Toolbox’ pane in Visual Studio (Ctrl-Alt-X)
  4. Select the ‘Association’ toolbox item from the list

    Select the Association toolbox control

  5. With the Association selected, Click and Drag from ‘Department’ to ‘Employee’

    Drag and Drop between your two External Content Types

  6. When you release the ‘Association Configuration’ dialog will show
  7. Switch back to the ‘BCS Meta Man Data Source Explorer’ tool window pane
  8. Drag the Association Stored Procedure onto the Arrow in the ‘Association Configuration’ dialog, it will turn green when OK to release

    Drag and Drop the DB2 Association Stored Procedure

  9. The dialog will update with the selected Stored Procedure

    Association Configuration dialog

  10. Select ‘P_ID’ in the Parameter and ‘DEPTNO’ in the Identifier drop-down list boxes, Click ‘Save’

    Map the DB2 stored procedure parameter to identifier

  11. The diagram will upd
    ate to show an arrow indicating we have an association between the two External Content Types

    Association has been created

  12. Press F5 to deploy, this will load up your SharePoint Page once deployed
  13. Add a new Business Data List Web Part to your SharePoint page

    Add Business Data List Web Part

  14. Click on the ‘Open the tool pane’ link
  15. Click on the icon to show the available External Content Types

    Open the picker to select your External Content Type

  16. Select our ‘BCSMetaMan1.Department’ External Content Type, click OK

    Select the Department External Content Type

  17. Click ‘OK’ on the tool pane
  18. The external data provided by DB2 stored procedure is now displayed in the SharePoint Web Part

    External data provided by DB2 stored procedure is now shown within SharePoint 2010

  19. Next, Add a new ‘Business Data Related List’ Web Part to the page
  20. Configure the Web Part to use the ‘BCSMetaMan1.Employee’ External Content Type
  21. The Web Part will display a message saying the following

    Need to setup web part connection

  22. To do this we can set up the connection using the small arrow from the top right of our Web Part

    Use a web part menu to create a connection

  23. Once this is done we can click on the arrows next to an item in our ‘Department’ web part and the employees related to this department will display on our ‘Employee’ web part

    Related list web part now shows the association data provided by DB2 stored procedure

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