MySQL and Business Connectivity Services

By admin BCS Meta Man Business Connectivity Services 2nd September 2010

In this walkthrough we will demonstrate how to integrate a MySQL database with Microsoft SharePoint 2010 and display, add, update MySQL data on SharePoint 2010 page through ODBC. We will go through how to connect to your MySQL using BCS Meta Man, we will just be looking at the quickest way to get your MySQL data into SharePoint 2010 using a .Net Assembly Connector as easily as possible.

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
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 MySQLBCSMetaManDemo and click “OK”
On the next dialog box, enter the url 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
We will be working with the `customers` table, to add it to the Model , just drag it from the “Data Source Explorer” onto the “Diagram”
When you drop the table you will be shown the following dialog, we want to choose “.Net Assembly” , click “OK”. (Database can be used if you want to create a model which connects straight to your MySQL without using a .Net Assembly i.e no code solution)
Accept the default entity name by clicking “Next”
Accept the default “Identifiers” by clicking “Next”
Select and accept all available methods to be created, click “Generate” (There are 5 methods available in the list: “Finder”, “Specific Finder”, “IdEnumerator”, “Creator”, “Updater”)
Watch as the code is generated for you, think of the time it is saving!
Press F5 to deploy, 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”
Click on the icon to show the available External Content Types
Select our MySQLBCSMetaManDemo.CustomersModel.customers External Content Type, click “OK”
Click “Create”
Your External System data from MySQL database 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. We will change for example “contactLastName”, “contactFirstName” and “phone” and click “Save”
This will save the changes directly back to your MySQL that the external content type is hooked up to
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 customer. We will enter information about new customer and click “Save”
This will insert a new row directly to your MySQL database