Lightning Tools

Introduction

In this tutorial we will show you how to connect to a Oracle Database Server using BCS 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 filters so our Oracle Stored Procedure needs to have parameters defined.

Prerequisites

• Microsoft SharePoint Server 2010
• Microsoft Visual Studio 2010 Professional or higher
• BCS Meta Man
• Oracle clients (This FAQ describes what should be installed)
• Oracle stored procedure for such as:
  1. create or replace function get_employees_filtered(p_last_name in varchar2, p_salary in number)
     return sys_refcursor
     is
       v_employees sys_refcursor;
     begin
       open v_employees for select * from employees where last_name like '%' || p_last_name || '%' and salary < p_salary;
       return v_employees;
     end get_employees_filtered;

Walkthrough

1. Open Visual Studio 2010
2. Add a New Project
3. Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
4. Give your project a name i.e BCSMetaManFilteredOracleStoredProcedure and click “OK”

   

5. On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button

   

6. If prompted, click “Trial” on the licensing dialog
7. To show the “BCS Meta Man Data Source Explorer” we need to enable it from the Menu item, this new window can be docked so it doesn’t get in your way

   

8. Click the “Add Connection” button to show the “Connection Dialog”
9. Select “ODBC Server” as Data Source type, enter Oracle ODBC connection string (for example it can be “Driver={Oracle in OraClient11g_home1};Dbq=XE_AURIGA;Uid=hr;Pwd=hr;Trusted_Connection=yes;”), enter double quotes as left and right delimiters and “?” as parameter symbol and click “Connect” button.

   

10. The Data Source Explorer will now be populated with your Oracle data

     

11. Drag and drop your Finder Stored Procedure with parameters onto the design surface and enter External Content type name, i.e “FilteredEmployee”, click “OK”

    

12. Select the Method Type as “Finder’'
13. Select the first Parameter (p_last_name) and set it’s Filter Type to Comparison and the Default Value to ‘a’

    

14. Next select the second Parameter (p_salary) and also set it’s Filter Type to Comparison and the Default Value to ‘4000’

    

15. Click ‘OK’
16. The External Content Type will be created on the Diagram

    

17. In order to set an identifier right-click on the External Content Type, select “Manage External Content type” menu item

    

18. Click “Edit Identifiers” button on the “Entity Management” dialog, select an identifier and click “OK” and “Update” button then

    

19. The External Content Type will update with the new identifier

     

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

    

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

    

24. Select our ‘ BCSMetaManFilteredOracleStoredProcedure.FilteredEmployee’ External Content Type, click ‘OK’

    

25. Click ‘OK’ on the tool pane
26. Click the ‘Add’ link on the Business Data List Web Part to add the second Filter option

    

27. Change the second Filter to be the P_SALARY and enter values for the filters and click ‘Retrieve Data’
28. Your Oracle 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/>

Click here to get an email when we update our blog