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:
  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”

    Create DB2BCSMetaManFiltersDemo project

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

    Deploy as a farm solution

  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

    Open the BCS MetaMan data source explorer

  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

    DB2 ODBC connection string

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

    See the tables and views available to use

  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”

    Choose the model type ".Net Assembly"

  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”.

    External content types created with BCS MetaMan

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

    Manage the External Content Type

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

    Manage the methods so you can add BCS Filters

  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.

    Create a comparison BCS Filter

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

    Create a wildcard BCS Filter

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

    Create a limit BCS Filter

  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.

    Finder method with limit filter

  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.

    Add Business Data List web part

  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.

    Business Data List web parts with BCS Filters

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

    Using BCS Filters for DB2 tables

  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.

    Using BCS limit Filter for DB2 table in External list

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

    Modify view of External list

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

    New value for limit filter

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

    Using BCS limit Filter for DB2 table in External list with new value

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

    Using BCS comparison Filter for DB2 table in External list

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

    Using BCS wildcard Filter for DB2 table in External list

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