Business Connectivity Service Associations using DB2 Stored Procedures in SharePoint 2010

By admin BCS BCS Meta Man Business Connectivity Services 13th September 2011

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

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.
You should have the following External Content Types on your Diagram
Switch to the ‘Toolbox’ pane in Visual Studio (Ctrl-Alt-X)
Select the ‘Association’ toolbox item from the list
With the Association selected, Click and Drag from ‘Department’ to ‘Employee’
When you release the ‘Association Configuration’ dialog will show
Switch back to the ‘BCS Meta Man Data Source Explorer’ tool window pane
Drag the Association Stored Procedure onto the Arrow in the ‘Association Configuration’ dialog, it will turn green when OK to release
The dialog will update with the selected Stored Procedure
Select ‘P_ID’ in the Parameter and ‘DEPTNO’ in the Identifier drop-down list boxes, Click ‘Save’
The diagram will upd
ate to show an arrow indicating we have an association between the two External Content Types
Press F5 to deploy, this will load up your SharePoint Page once deployed
Add a new Business Data List Web Part to your SharePoint page
Click on the ‘Open the tool pane’ link
Click on the icon to show the available External Content Types
Select our ‘BCSMetaMan1.Department’ External Content Type, click OK
Click ‘OK’ on the tool pane
The external data provided by DB2 stored procedure is now displayed in the SharePoint Web Part
Next, Add a new ‘Business Data Related List’ Web Part to the page
Configure the Web Part to use the ‘BCSMetaMan1.Employee’ External Content Type
The Web Part will display a message saying the following
To do this we can set up the connection using the small arrow from the top right of our Web Part
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