MySQL Stored Procedures and the Business Connectivity Services in SharePoint 2010

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

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 PROCEDURE classicmodels.delete_office(IN p_id INT)
  SQL SECURITY INVOKER
  BEGIN
  DELETE FROM offices WHERE officecode = p_id;
  END

Walkthrough

Open Visual Studio 2010
Add a New Project
Expand the “SharePoint” node, select “Lightning Tools (2010)” node and then the “BCS Meta Man” project type
Give your project a name i.e MySQLStoredProceduresBCSMetaManDemo and click “OK”
On the next dialog box, enter the name of where you want to deploy your model, and click “Finish” button
If prompted, click “Trial” on the licensing dialog
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
Click the “Add Connection” button to show the “Connection Dialog”
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
The Data Source Explorer will now be populated with your MySQL data
Drag and drop your Finder Stored Procedure onto the design surface and enter External Content type name, i.e “Office”, click “OK”
Choose the Finder as the method type and click “OK”
The External Content Type will be created on the Diagram
In order to set an identifier right-click on the External Content Type, select “Manage External Content type” menu item
Click “Edit Identifiers” button on the “Entity Management” dialog, select an identifier and click “OK” and “Update” button then
The External Content Type will update with the new identifier
From the “BCS Meta Man Data Source Explorer” drag and drop your Specific Finder stored procedure onto the existing External Content Type
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”.
The External Content Type will update with the new Specific Finder Method
Next drag and drop the Id Enumerator stored procedure onto the existing External Content Type
Select “IdEnumerator” as the Method Type, Click “OK”
The External Content Type will update with the new Id Enumerator Method
From the “BCS Meta Man Data Source Explorer” drag and drop your Creator stored procedure onto the existing External Content Type
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”.
The External Content Type will update with the new Creator Method
Next drag and drop your Updater stored procedure onto the existing External Content Type
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”.
The External Content Type will update with the new Updater Method
Finally drag and drop your Deleter stored procedure onto the existing External Content Type
Again the “Stored Procedure Configuration” dialog will be displayed. Select “Deleter” as the Method Type. Click “OK”.
The complete External Content Type will look like the following
Press F5 to deploy The External Content Type, this will load up your SharePoint Page once deployed
Add a new External list by selection on SharePoint page “Site Action”->“More Options…”->“External List”
Give your External List a name and Click on the icon to show the available External Content Types
Select created MySQLStoredProceduresBCSMetaManDemo.Office External Content Type, click “OK”. Click “Create” button then.
The external data provided by MySQL stored procedure is now displayed in the SharePoint External List
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.
You will be presented with the View Item dialog
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.
While viewing the external list, if you click the Items tab on the ribbon you’ll see a New Item button
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”
This will insert a new row directly to your MySQL database