Sharepoint 2010 – External Lists and Content Types + Business Connectivity Service INTEGRATING SugarCRM + PHP + MySQL

Posted in: Blog, SharePoint 2010 | Jan 10, 2013 at 9:32 PM

No comments

Hi everyone,

After attending a session at Sharepoint Saturday Perth regarding BCS and External Lists and Content Types, I was actually very-very interested in using it to connect to a non-Microsoft systems.

So far I notice that a lot of the integrations project are with other Microsoft apps such as MS CRM, Axapta, etc. But what about with a totally different system such as PHP + MySQL?

In this opportunity I want to share with you the walkthrough to represent MySQL data in Sharepoint using BCS and External Lists.

First of all let me introduce to you the PHP apps I want to integrate with: Sugar CRM. It’s an open source Customer Relationship Management written in PHP and using MySQL as the database backend. See below:

So, it’s now time for me to prepare for the integration. First of all I need to use Visual Studio 2010 to create the project. If you open Visual Studio and create new project, under Sharepoint -> 2010 you will see this project template: Business Data Connectivity Model.

Let’s create the project. By default it will look like the following:

You will also see the BDC Explorer window:

– It has a feature that is deployed at Farm level.

– It also has the BDC entity model on it.

Now, it’s time to do a bit of class renaming. By default you will get Entity1.cs and Entity1Service.cs. Entity1.cs is the entity that will be passed through/from BDC and Entity1Service.cs is the “business logic” class that BDC will use to read methods from. There are 2 methods by default: ReadList (used to display ALL items on External Lists) and ReadItem (used to display a particular item). You obviously can add more to this to perform CRUD operations.

Let’s now rename Entity1.cs to SugarCRMContact.cs and Entity1Service.cs to SugarCRMContactService.cs. If we now go to the BDC Explorer window, I would actually expect Visual Studio to auto-rename the BDC Entity object but it actually doesn’t. Therefore, we need to rename this as well. Let’s rename Entity1 to SugarCRMContact. We will need to modify the return object as well.

I’m hoping that this automation will be fixed in the released version of VS 2010.

The project is available for download. Therefore, if you get lost/confused with this step, don’t worry.

Once we’ve done all the renaming, it’s now time to create the “repository”/data layer class to connect to MySQL. As we all know, it’s always good to program with DDD/TDD (Domain-Driven-Design/Test Driven Development) methodology in mind so that our code is decoupled and not tight/dependent to each other. There are a lot of posts on the Internet about DDD/TDD so I won’t discuss much about this.

Once I’m finished with my data layer class, it’s now time to serve them in Sharepoint using BCS. If I now go back to my SugarCRMContactService.cs class, I can now do the following:

==

public static IEnumerable ReadList()

{
List bdcContacts = new List();

IContactRepository contactRepository = new DefaultContactRepository();

List contacts = contactRepository.GetContacts();

foreach (ContactEntity contact in contacts)

{

SugarCRMContact bdcContact = new SugarCRMContact();

bdcContact.ContactId = contact.ContactId.ToString();

bdcContact.FirstName = contact.FirstName;

bdcContact.LastName = contact.LastName;

bdcContacts.Add(bdcContact);

}

return bdcContacts;

}

==

Once we’ve done that, deploy your WSP to your farm and that’s it! Sharepoint will automatically create the BDC entity for you. Go to Central Admin -> Application Management -> Manage Service Applications then click on Business Data Connectivity then you’ll see the following:

Now we can start creating the External List for it. Open your site in Sharepoint Designer 2010, then go to External Content Types. You will automatically see your custom BDC entity. Double click on it and you’ll be brought to this page:

On the top click Create List & Form. Give your list a name and that’s it! If you now go back to your site and View All Site Content, you’ll see your external list in there. When you click it…

There you go: MySQL data in Sharepoint.

Download the project here.

Note:

1. You will need to download SugarCRM (http://www.sugarcrm.com/) and WAMPServer. WAMPServer runs PHP + MySQL + Apache at the same time.

2. Add the following connection string in your Sharepoint site web.config:

<add key=”ConnectionString” value=”Server=localhost;Database=sugarcrm;User=root;Password=;” />

No Comments Yet.