Using BCS filters for DB2 database in SharePoint 2010

In this walkthrough we will demonstrate how to define BCS filters for BCS external content types using BCS Meta Man. We support filters for SQL and Oracle (you may read about it here Using Filters in BCS Meta Man), but in this sample we will show using filters for DB2 database via ODBC connection. But the same way you can use the filters for any data source that supports ODBC connectivity.

1. 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
2. Open Visual Studio 2010
3. Add a New Project
4. Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
5. Give your project a name i.e DB2BCSMetaManFiltersDemo and click “OK”

   

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

   

7. If prompted, click “Trial” on the licensing dialog
8. 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

   

9. Click the “Add Connection” button to show the “Connection Dialog”
10. Select “ODBC Server” as Data Source type, enter DB2 ODBC connection string (for example it can be “Driver={IBM DB2 ODBC DRIVER};Database=SAMPLE;Hostname=cygnus;port=50000;Protocol=TCPIP;Uid=Administrator;Pwd=12345;Trusted_Connection=false;”), enter double quotes as left and right delimiters and “?” as parameter symbol and click “Connect” button

    

11. The Data Source Explorer will now be populated with your DB2 data

    

12. We will be working with the ADMINISTRATOR.”DEPARTMENT”, ADMINISTRATOR.”EMPLOYEE”, ADMINISTRATOR.”PROJECT” tables, to add them to the Model , just drag these tables from the “Data Source Explorer” onto the “Diagram”
13. When you drop the table you will be shown the following dialog, we should choose “.Net Assembly, click “OK”

    

14. When you add all tables your diagram should look like this. Now we will define a comparison filter for ADMINISTRATOR.”EMPLOYEE” table, wildcard filter for ADMINISTRATOR.”DEPARTMENT” and limit filter for ADMINISTRATOR.”PROJECT”.

    

15. Right click on the ADMINISTRATOR.”EMPLOYEE” table and choose “Manage External Content Type” menu item

    

16. In the Entity Management dialog go to Methods tab and select the Finder method

    

17. Click on “Add Filter” button in the “Filters” section and configure a comparison filter for ADMINISTRATOR.”EMPLOYEE” table’s Finder method. Click on OK button, and then Update entity’s modifications.

    

18. Using the above mentioned steps, define a wildcard filter for the ADMINISTRATOR.”DEPARTMENT” table.

    

19. Using the above mentioned steps, define a limit filter for the ADMINISTRATOR.”PROJECT” table.

    

20. If you open the generated service classes, you’ll find that the C# methods have been modified to accept filter parameters and filter the data being returned.

    

21. Rebuild the project and press CTRL+F5 to deploy the solution.
22. Go to your SharePoint page, and add two Business Data List web parts.

    

23. Set ADMINISTRATOR.”EMPLOYEE” as a type for one of them and ADMINISTRATOR.”DEPARTMENT” as a type for the other Business Data List web part.

    

24. Try to enter some filter values to display the records.

    

25. In order to check limit filter add a new External list by selection on SharePoint page “Site Action”->“More Options”->“External List”. Select our ADMINISTRATOR.”PROJECT” External Content Type, click “OK”. You may see only five records because we set 5 as default value for limit filter.

    

26. To set another value for limit filter go to “Modify view” on the ribbon

    

27. Scroll down to the “Data Source Filters” section and set new value

    

28. You may the see as many records as we set for
    the limit filter

    

29. The same way you may see how comparison filter works in the External list for ADMINISTRATOR.”EMPLOYEE” table

    

30. The same way you may see how wildcard filter works in the External list for ADMINISTRATOR.”DEPARTMENT” table

    

We hope this walkthrough will be useful for you. If you have any questions feel free to email them to support@lightningtools.com