Dynamics 365 & WordPress Integration

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.

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.

Setup WordPress and 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:

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.

 internal static MySqlConnection Connect()
        {
            // Open the MySQL Connection
            // Remote MySQL hosts will need to have the external server access setup
            string connectionString = "Server=yoursite.com;Database=wordpress_database;User ID=database_user;Password=database_password";

            MySqlConnection mydbcon = new MySqlConnection(connectionString);

            return mydbcon;

        }

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:

internal static void QueryMySQL(MySqlConnection mydbcon)
        {
            mydbcon.Open();

            MySqlCommand command = mydbcon.CreateCommand();
            command.CommandText = "SELECT meta_value from wp_frm_item_metas WHERE item_id=1";

            IDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                AzureIntegrationForm._db_collection.Add((string)reader["meta_value"]);
            }

            CloseConnection(mydbcon, command, reader);
        }

Close the MySQL Connection

Add the final method to close the MySQL Connection.

 internal static void CloseConnection(MySqlConnection mydbcon, MySqlCommand command, IDataReader reader)
        {
            reader.Close();
            command.Dispose();
            mydbcon.Close();
        }

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:

 internal static Dictionary<string, string> Connect()
        {
            Dictionary<string, string> serviceBus = new Dictionary<string, string>();

            var connectionString = "your_azure_service_endpoint";
            var queueName = "wordpress_queue";
            serviceBus.Add(connectionString, queueName);

            return serviceBus;
        }

Send Messages to the Azure 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:

 internal static Dictionary<string, string> Connect()
        {
            Dictionary<string, string> serviceBus = new Dictionary<string, string>();

            var connectionString = "your_azure_service_endpoint";
            var queueName = "wordpress_queue";
            serviceBus.Add(connectionString, queueName);

            return serviceBus;
        }

Test the Service Bus

At this point, if you were to test the Service Bus Queue you might see something like this:

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.

internal static IOrganizationService Connect()
        {
            string username = "crm_username";
            string password = "crm_password";
            string orgServiceUrl = "https:..your_crm_organisation/XRMServices/2011/Organization.svc";

            try
            {
                Uri organizationUri = new Uri(orgServiceUrl);
                ClientCredentials credentials = new ClientCredentials();
                credentials.UserName.UserName = username;
                credentials.UserName.Password = password;

                using (OrganizationServiceProxy serviceproxy = new OrganizationServiceProxy(organizationUri, null, credentials, null))
                {
                    IOrganizationService service = (IOrganizationService)serviceproxy;
                    return service;
                }
            }
            catch (FaultException<Microsoft.Xrm.Sdk.OrganizationServiceFault> ex)
            {
              // Silent Fail
            }
            catch (System.TimeoutException ex)
            {
                // Silent FailConsole.WriteLine("Message: {0}", ex.Message);

            }
            catch (System.Exception ex)
            {
                // Silent Fail

            }

            return null;
        }

Retrieve the Azure 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:

internal static string RetrieveMessages(string sbConn, string queueName)
        {
            var client = QueueClient.CreateFromConnectionString(sbConn, queueName);
            string serviceMessage = string.Empty;

            client.OnMessage(message =>
            {
                serviceMessage = message.GetBody();
                string yes = "";
            });

            return serviceMessage;
        }

Create the CRM Record

Once you’ve received your Azure Service Bus message, it’s time to push that message into Dynamics 365.

internal static void CreateLead(IOrganizationService service, string recordmessage)
        {
            // Step 7: Split the returnMessage
            string[] crmRecord = recordmessage.Split('|');

            Entity lead = new Entity("lead");

            lead["subject"] = crmRecord[4];
            lead["fullname"] = crmRecord[1] + " " + crmRecord[2];
            lead["emailaddress1"] = crmRecord[3];

            try
            {
                service.Create(lead);
            }
            catch (Exception ex)
            {
                //Silent Fail
            }
        }

Result

Final Words

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.

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.