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”

    5 thumb MySQL and Business Connectivity Services using BCS Meta Man

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

    6 thumb2 thumb MySQL and Business Connectivity Services using BCS Meta Man

  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

    8 thumb2 thumb MySQL and Business Connectivity Services using BCS Meta Man

  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

    10 thumb2 thumb MySQL and Business Connectivity Services using BCS Meta Man

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

    11 thumb1 thumb MySQL and Business Connectivity Services using BCS Meta Man

  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)

    13 thumb2 thumb MySQL and Business Connectivity Services using BCS Meta Man

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

    14 thumb2 thumb MySQL and Business Connectivity Services using BCS Meta Man

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

     MySQL and Business Connectivity Services using BCS Meta Man "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”)

    16 thumb3 thumb MySQL and Business Connectivity Services using BCS Meta Man

  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”

    19 thumb2 thumb MySQL and Business Connectivity Services using BCS Meta Man

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

    20 thumb2 thumb MySQL and Business Connectivity Services using BCS Meta Man

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

    21 thumb4 thumb MySQL and Business Connectivity Services using BCS Meta Man

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

    23 thumb3 thumb MySQL and Business Connectivity Services using BCS Meta Man

  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. 

    24 thumb2 thumb MySQL and Business Connectivity Services using BCS Meta Man

  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”

    25 thumb3 thumb MySQL and Business Connectivity Services using BCS Meta Man

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

    26 thumb11 thumb MySQL and Business Connectivity Services using BCS Meta Man

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

    27 thumb3 thumb MySQL and Business Connectivity Services using BCS Meta Man

  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”

     MySQL and Business Connectivity Services using BCS Meta Man t: 0px; padding-right: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px; padding-top: 0px" title="New Item Dialog" border="0" alt="New Item Dialog" src="http://lightningtools.com/blog/images/lightningtools_com/blog/Windows-Live-Writer/MySQL-and-Business-Connectivity-Services_D41D/28_thumb5_thumb.png" width="244" height="228" />

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

    29 thumb2 thumb MySQL and Business Connectivity Services using BCS Meta Man

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

support@lightningtools.com

  1. Chris

    Is this still valid for SharePoint 2013?

     

Leave a Comment

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

*

* Copy This Password *

* Type Or Paste Password Here *

*