Dynamics 365, WordPress, MySQL & Azure Service Bus

Post Date: February 24, 2017

Microsoft Dynamics Consultant

[vc_row][vc_column][cq_vc_zoomimage displaystyle=”magnify” magnifyimage=”6373″ bordercolor=”#ffffff” glassx=”50″ glassy=”110″][vc_separator][vc_empty_space height=”50px”][/vc_column][/vc_row][vc_row][vc_column][vc_wp_text title=”Summary”]This week we take a dive into proof of concept (POC) integration between Azure, WordPress and Dynamics 365. More specifically, integration between MySQL, Azure Service Bus and Dynamics 365.

If you ever have tried searching on the Internet for inspirational integration projects to work on between Dynamics 365 and Azure, you’ll quickly realize that literature on the subject is sparse and anecdotal. It has been suggested to me by former clients that one contributing factor for the lack of business uptake of Azure integration with Dynamics 365 is primarily because they don’t see a business need for it. In this post, I’ll demonstrate an example of a real-world implementation of Azure Service Bus with Dynamics 365.

This post was inspired by a project I worked on several years ago for a global manufacturing firm that ran their internal HR portal using WordPress. WordPress is a very respectable platform for creating websites especially if keeping the total cost of ownership, as low as possible, is a principle concern.

For the purposes of this POC, I’ve opted for WordPress, but realistically the website platform can be anything with the primary exception being (for this POC at least), the backend database must be MySQL and you must have access to the database remotely.[/vc_wp_text][/vc_column][/vc_row][vc_row][vc_column][vc_wp_text title=”Example Business Case”]Firstly, a little about what we’re trying to achieve here. Let’s take this hypothetical business case:

We are a company that manages festival events and bookings. We have a company website built using WordPress. This WordPress site is public-facing, this means that anyone with an Internet connection can view your website and submit information. What we’d like to do is capture event and booking registrations on our WordPress site, and then feed that into Dynamics 365 as a Lead record.

In keeping with our organisation future expansion and growth strategies, we would also like to add several other websites to our list of integration projects – but not just yet. We want to allow room for scale, but we don’t want to overthink the design or the complexity.[/vc_wp_text][vc_single_image image=”6345″ img_size=”large”][vc_empty_space height=”50px”][/vc_column][/vc_row][vc_row][vc_column][vc_wp_text title=”Step 1: Setup WordPress & Forms plugin”]To keep things simple, I opted to install a simple WordPress Plugin that allowed me to create dynamic forms. I didn’t want to spend time building my own PHP form pages or the MySQL backend. The WordPress form plugin I chose was Formidable Forms.

This meant that I had a simple table to work with:

[/vc_wp_text][/vc_column][/vc_row][vc_row][vc_column][vc_wp_text title=”Step 2: Connect to MySQL”]Assuming that you’re familiar with Visual Studio and C#, the first thing you’ll want to do is install the MySQL Visual Studio connector. When it comes to building C# applications in Visual Studio, the method that I favour above others is to download the official connector from MySQL’s website. As of now, the connector version is: 6.9.9 – download Connector here: MySQL Connector/Net.

Once the connector is installed – start a new Visual Studio project (Console, Forms or UWP – the choice is yours).

Add the MySQL reference assemblies (MySql.Data) to the project so that you have access to the correct namespace.

[/vc_wp_text][vc_message message_box_style=”solid” style=”square” message_box_color=”grey” icon_type=”entypo” icon_entypo=”entypo-icon entypo-icon-info”]Use your own Connection String illustrated in line 5.[/vc_message][/vc_column][/vc_row][vc_row][vc_column][vc_wp_text title=”Step 3: Build your Query”]Once you have established that you’re able to connect to your MySQL database remotely; it’s time to build a query:

[/vc_wp_text][/vc_column][/vc_row][vc_row][vc_column][vc_wp_text title=”Step 4: Close the MySQL Connection”]Add the final method to close the MySQL Connection.

[/vc_wp_text][/vc_column][/vc_row][vc_row][vc_column][vc_wp_text title=”Step 5: Connect to the Azure Service Bus”]The next step is to connect to the Azure Service Bus. This step assumes that you have an active subscription to Azure and that you are able to create a new Service Bus namespace. Connecting to Azure Service Bus is relatively straight forward – you can find the official documentation here.

Here is the code snippet that I used to connect to Azure:

[/vc_wp_text][/vc_column][/vc_row][vc_row][vc_column][vc_wp_text title=”Step 6: Send messages to the Service Bus Queue”]At this point you should already have:

  1. A MySQL Connection established.
  2. A MySQL Query constructed.
  3. A Connection to the Azure Service Bus namespace.

 

Now it’s time to send the results of the MySQL query to the Service Bus Queue. You can do so with the following code snippet:

[/vc_wp_text][vc_message message_box_style=”solid” style=”square” message_box_color=”grey” icon_type=”entypo” icon_entypo=”entypo-icon entypo-icon-info”]Don’t forget to edit line 5 & 6 with your endpoint and queue details.[/vc_message][/vc_column][/vc_row][vc_row][vc_column][vc_wp_text title=”Step 7: Test the Service Bus Queue”]At this point, if you were to test the Service Bus Queue you might see something like this:

I ran my code four times. Everytime I ran the program, the ‘Active Message Count’ slowly increased.[/vc_wp_text][/vc_column][/vc_row][vc_row][vc_column][vc_wp_text title=”Step 8: Connect to Dynamics 365″]You’ll need to establish a connection to Microsoft Dynamics 365. The code snippet below will allow you to connect to your instance of CRM.

[/vc_wp_text][vc_message message_box_style=”solid” style=”square” message_box_color=”grey” icon_type=”entypo” icon_entypo=”entypo-icon entypo-icon-info”]Edit lines 3, 4, 5 and replace with your CRM Username, Password and Organization URL.[/vc_message][/vc_column][/vc_row][vc_row][vc_column][vc_wp_text title=”Step 9: Connect to Dynamics 365″]You’ll need to establish a connection to Microsoft Dynamics 365. The code snippet below will allow you to connect to your instance of CRM.

[/vc_wp_text][vc_message message_box_style=”solid” style=”square” message_box_color=”grey” icon_type=”entypo” icon_entypo=”entypo-icon entypo-icon-info”]Edit lines 3, 4, 5 and replace with your CRM Username, Password and Organization URL.[/vc_message][/vc_column][/vc_row][vc_row][vc_column][vc_wp_text title=”Step 10: Retrieve Service Bus Message”]We’ve established our Dynamics 365 connection. The next step is to retrieve the Azure Service Bus messages. The snippet below will retrieve the messages:

[/vc_wp_text][/vc_column][/vc_row][vc_row][vc_column][vc_wp_text title=”Step 11: Create the CRM Lead record”]Once you’ve received your Azure Service Bus message, it’s time to push that message into Dynamics 365.

Astute readers will notice that the code above takes the message and splits the message into “chunks”. This is because I originally stored the “message” in Azure as a full line.

The message is split into pieces of data, and this is then used to create a record.[/vc_wp_text][/vc_column][/vc_row][vc_row][vc_column][vc_wp_text title=”The Result”][/vc_wp_text][/vc_column][/vc_row][vc_row][vc_column][vc_wp_text title=”A final word”]You might be wondering why we even bothered to use Azure Service Bus in the first place? Why couldn’t we just retrieve the results from MySQL and push that data into Dynamics 365? The simple answer is scale. If you recall, our initial business case was to integrate a single WordPress site with Dynamics 365, however, we also made the assumption that we may integrate other WordPress websites with Dynamics 365 in the future.

In systems integration and especially true of service oriented architecture (SOA), communicating with decoupled systems becomes progressively challenging and complex as the number of platforms you are integrating with increases. For the sake of clarity, if in the future you have seven different website platforms (WordPress, Moodle, Blackboard etc.), all built in different scripting/programming languages; with a business requirement to integrate these platforms with Dynamics 365 – you will in essence be required to build seven different interfaces to Dynamics 365. However, using a Service Bus as the communications pipeline means that you only ever need to build one interface to the Service Bus from each platform. This means that you could, theoretically, build an infinite number of integrations with Dynamics 365 whilst keeping any overhead and maintenance as low as possible.[/vc_wp_text][/vc_column][/vc_row][vc_row][vc_column][vc_wp_text title=”Next Steps…”]The WordPress, Azure & Dynamics 365 integration app we just built has to be hosted somewhere, and must be running at all times. It’s an executable application. In the next post I’ll clean up the application and demonstrate how Azure Scheduler can be used to automate the task.[/vc_wp_text][/vc_column][/vc_row][vc_row][vc_column][vc_raw_js]JTNDc2NyaXB0JTIwdHlwZSUzRCUyMnRleHQlMkZqYXZhc2NyaXB0JTIyJTNFJTIwJTBBZnVuY3Rpb24lMjBzZXR1cCUyOCUyOSU3QmlmJTI4Y29uc29sZS5sb2clMjglMjJmaXJlJTIyJTI5JTJDMSUyMSUzRG1zZyUyOSU3QnZhciUyMGIlM0QlN0JuJTNBJTIyJTIwSW50ZXJhY3Rpb24lMjIlMkNzJTNBJTIyRHluYW1pY3MlMjAzNjUlMkMlMjBXb3JkUHJlc3MlMkMlMjBNeVNRTCUyMCUyNiUyMEF6dXJlJTIwU2VydmljZSUyMEJ1cyUyMiUyQ2IlM0ElMjJOZXclMjIlN0QlM0JqUXVlcnkuYWpheCUyOCU3QnVybCUzQSUyMmh0dHBzJTNBJTJGJTJGcHJvZC0wNC53ZXN0ZXVyb3BlLmxvZ2ljLmF6dXJlLmNvbSUzQTQ0MyUyRndvcmtmbG93cyUyRjk0NDA1MTVmYjRiZjQ4ZjNhMWE5NTYzMDk5MThjODdlJTJGdHJpZ2dlcnMlMkZtYW51YWwlMkZwYXRocyUyRmludm9rZSUzRmFwaS12ZXJzaW9uJTNEMjAxNi0wNi0wMSUyNnNwJTNEJTI1MkZ0cmlnZ2VycyUyNTJGbWFudWFsJTI1MkZydW4lMjZzdiUzRDEuMCUyNnNpZyUzRGlFTXhjQnpNekFwTmdZbEZ5aXp2M2t2VHlmbXZsLUVTX1E3TG9sOUd4clUlMjIlMkN0eXBlJTNBJTIyUE9TVCUyMiUyQ2RhdGElM0FKU09OLnN0cmluZ2lmeSUyOGIlMjklMkNjb250ZW50VHlwZSUzQSUyMmFwcGxpY2F0aW9uJTJGanNvbiUyMiUyQ3N1Y2Nlc3MlM0FmdW5jdGlvbiUyOGElMkNiJTJDYyUyOSU3QiU3RCU3RCUyOSU3RG1zZyUzRDElN0R2YXIlMjBtc2clM0QwJTNCalF1ZXJ5JTI4d2luZG93JTI5LnNjcm9sbCUyOGZ1bmN0aW9uJTI4YSUyOSU3QmpRdWVyeSUyOHdpbmRvdyUyOS5zY3JvbGxUb3AlMjglMjklM0JzZXR1cCUyOCUyOSU3RCUyOSUzQiUwQSUwQSUzQyUyRnNjcmlwdCUzRQ==[/vc_raw_js][/vc_column][/vc_row]

Leave a Reply