MySQL and Business Connectivity Services using BCS Meta Man

 BCS Meta Man, Business Connectivity Services, SharePoint 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.

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

    Create MySQLBCSMetaManDemo project

  6. On the next dialog box, enter the url 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 Meta Man Data Source Explorer

  9. Click the “Add Connection” button to show the “Connection Dialog”
  10. 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

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

    See the tables and views available to use

  12. 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”
  13. 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)

    Choose the model type

  14. Accept the default entity name by clicking “Next”

    Accept the default entity name

  15. Accept the default “Identifiers” by clicking “Next”

    Accept the identifiers selected "http://lightningtools.com/blog/images/lightningtools_com/blog/Windows-Live-Writer/MySQL-and-Business-Connectivity-Services_D41D/15_thumb2_thumb.png" width="244" height="244" />

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

    Accept the methods

  17. Watch as the code is generated for you, think of the time it is saving!
  18. Press F5 to deploy, this will load up your SharePoint Page once deployed
  19. Add a new External list by selection on SharePoint page “Site Action”->“More Options”->“External List”

    Add a new external list

  20. Click on the icon to show the available External Content Types

    Select the External Content Type

  21. Select our MySQLBCSMetaManDemo.CustomersModel.customers External Content Type, click “OK”

    Select the customers External Content Type

  22. Click “Create”
  23. Your External System data from MySQL database is now displayed in the SharePoint External List

    The data from MySQL is now shown within SharePoint 2010

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

    View item dialog

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

    Edit item dialog

  26. This will save the changes directly back to your MySQL that the external content type is hooked up to

    Updated external data from MySQL

  27. 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 through the external list

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

    New Item Dialog

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

    Updated External data from MySQL

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

support@lightningtools.com

Leave a Reply

Your email address will not be published. Required fields are marked *

*

HTML tags are not allowed.