MySQL Stored Procedures and the Business Connectivity Services in SharePoint 2010

Introduction

In this walkthrough we will demonstrate how to integrate MySQL stored procedures with Microsoft SharePoint 2010 and display the data on SharePoint 2010 page. In order to do that we will describe how to create a new external content type by means of BCS Meta Man.

Prerequisites

  • Microsoft SharePoint Server 2010
  • Microsoft Visual Studio 2010 Professional or higher
  • BCS Meta Man
  • Download and install “Windows (x86, 32-bit), MSI Installer Connector-ODBC” http://www.mysql.com/downloads/connector/odbc/ on a machine where BCS Meta Man runs
  • Download and install “Windows (x86, 64-bit), MSI Installer Connector-ODBC” http://www.mysql.com/downloads/connector/odbc/ on a machine where SharePoint 2010 runs
  • MySQL stored procedures for CRUD operations such as:
    1. CREATE DEFINER = ‘root’@’localhost’
      PROCEDURE classicmodels.get_all_offices()
      BEGIN
        SELECT * FROM offices;
      END
    2. CREATE DEFINER = ‘root’@’localhost’
      PROCEDURE classicmodels.get_office_by_id(IN p_id INT)
      BEGIN
      SELECT * FROM offices WHERE officeCode=p_id;
      END
    3. CREATE DEFINER = ‘root’@’localhost’
      PROCEDURE classicmodels.get_office_ids()
      BEGIN
      SELECT officeCode FROM offices;
      END
    4. CREATE DEFINER = ‘root’@’localhost’
      PROCEDURE classicmodels.create_office(IN p_id INT, IN p_city VARCHAR(50),
      IN p_phone VARCHAR(50), IN p_address_line1 VARCHAR(50),
      IN p_address_line2 VARCHAR(50), IN p_state VARCHAR(50),
      IN p_country VARCHAR(50), IN p_postal_code VARCHAR(15),
      IN p_territory VARCHAR(10))
      BEGIN
      INSERT INTO offices values(p_id, p_city, p_phone, p_address_line1, p_address_line2,
        p_state, p_country, p_postal_code, p_territory);
      END
    5. CREATE DEFINER = ‘root’@’localhost’
      PROCEDURE classicmodels.update_office(IN p_id INT, IN p_city VARCHAR(50),
      IN p_phone VARCHAR(50), IN p_address_line1 VARCHAR(50),
      IN p_address_line2 VARCHAR(50), IN p_state VARCHAR(50),
      IN p_country VARCHAR(50), IN p_postal_code VARCHAR(15),
      IN p_territory VARCHAR(10))
      BEGIN
      UPDATE offices SET city=p_city, phone=p_phone, addressLine1=p_address_line1,
        addressLine2=p_address_line2, state=p_state, country=p_country, postalCode=p_postal_code,
         territory=p_territory WHERE officecode=p_id;
      END
    6. CREATE DEFINER = ‘root’@’localhost’
      PROCEDURE classicmodels.delete_office(IN p_id INT)
      BEGIN
      DELETE FROM offices WHERE officecode = p_id;
      END

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 MySQLStoredProceduresBCSMetaManDemo and click “OK”

    Create MySQLStoredProceduresBCSMetaManDemo project

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

  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

    Open the BCS Meta Man Data Source Explorer

  8. Click the “Add Connection” button to show the “Connection Dialog”
  9. Select “ODBC Server” as Data Source type, enter MySQL ODBC connection string (for example it can be “Driver={MySQL ODBC 5.1 Driver};Server=taurus;Port=3306;Database=classicmodels; User=root;Password=12345;Trusted_Connection=yes;”), enter “`” as left and right delimiters and “?” as parameter symbol and click “Connect” button

    MySQL ODBC connection string

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

    See the stored procedures available to use

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

    Drag and drop a Finder stored procedure and Enter External Content type name

  12. Choose the Finder as the method type and click “OK”

    Choose the Finder as the method type

  13. The External Content Type will be created on the Diagram

    External Content Type created on diagram

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

    Manage External Content type

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

    Set identifier

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

    External Content Type with identifier

  17. From the “BCS Meta Man Data Source Explorer” drag and drop your Specific Finder stored procedure onto the existing External Content Type

    Drag and drop Specific Finder stored procedure onto existing External Content Type

  18. The “Stored Procedure Configuration” dialog will be displayed. Select “SpecificFinder” as the Method Type. Select the first Parameter – p_id and set the Filter Type as Comparison and the Default Value as ‘0’. Click “OK”.

    Configuring a Specific Finder stored procedure

  19. The External Content Type will update with the new Specific Finder Method

    External Content Type with Finder and Specific Finder methods

  20. Next drag and drop the Id Enumerator stored procedure onto the existing External Content Type

    Drag and drop Id Enumerator Stored Procedure onto existing External Content Type

  21. Select “IdEnumerator” as the Method Type, Click “OK”

    Configuring Id Enumerator stored procedure

  22. The External Content Type will update with the new Id Enumerator Method

    External Content Type with Finder, Specific Finder and ID Enumerator methods

  23. From the “BCS Meta Man Data Source Explorer” drag and drop your Creator stored procedure onto the existing External Content Type

    Drag and drop Creator stored procedure onto existing External Content Type 

  24. The “Stored Procedure Configuration” dialog will be displayed. Select “Creator” as the Method Type. For each Parameter select the appropriate column to map it to. Click “OK”.

    Configuring Creator stored procedure

  25. The External Content Type will update with the new Creator Method

    External Content Type with with Finder, Specific Finder, ID Enumerator, Creator methods

  26. Next drag and drop your Updater stored procedure onto the existing External Content Type
  27. Again the “Stored Procedure Configuration” dialog will be displayed. Select “Updater” as the Method Type. For each Parameter select the appropriate column to map it to. Click “OK”.
  28. The External Content Type will update with the new Updater Method

    External Content Type with with Finder, Specific Finder, ID Enumerator, Creator and Updater methods

  29. Finally drag and drop your Deleter stored procedure onto the existing External Content Type
  30. Again the “Stored Procedure Configuration” dialog will be displayed. Select “Deleter” as the Method Type. Click “OK”.
  31. The complete External Content Type will look like the following

    External Content Type with with Finder, Specific Finder, ID Enumerator, Creator, Updater and Deleter methods

  32. Press F5 to deploy The External Content Type, this will load up your SharePoint Page once deployed
  33. Add a new External list by selection on SharePoint page “Site Action”->“More Options…”->“External List”

    Add a new external list

  34. Give your External List a name and Click on the icon to show the available External Content Types

    Select the available External Content Type

  35. Select created MySQLStoredProceduresBCSMetaManDemo.Office External Content Type, click “OK”. Click “Create” button then.

    Select Office External Content Type

  36. The external data provided by MySQL stored procedure is now displayed in the SharePoint External List

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

  37. You can hover over the first column of your list and you’ll see a drop down appear. If you click the drop down arrow you’ll get a number of options for that particular row of data – one of which will be View Item.

    View items through External List

  38. You will be presented with the View Item dialog

    View Item dialog

  39. Now click “Edit item” on the ribbon of the dialog. This option is only available if you create an Updater method for your External Content Type. Clicking on this link will open up a dialog form that allows you to edit that row of data and save it.

    Edit Item dialog

  40. While viewing the external list, if you click the Items tab on the ribbon you’ll see a New Item button

    Add new data to MySQL database through the external list

  41. This will bring up the “New Item” dialog where you can add a new Office. We will enter information about new Office and click “Save”

    New Item Dialog

  42. This will insert a new row directly to your MySQL database

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

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