Integrating Dynamics 365 with Azure Functions – Part 2

By | June 11, 2018

Integrating D365 with Azure Functions

Introduction:

In our recent blog, We saw how to create an Azure function and now that we have our Azure function ready and hosted, let’s look at invoking the function through a workflow. At this point, we will execute the function through an HTTP request instead of registering the function as a Webhook.

Let us modify the code from our previous blog to connect to a SQL database and return a record. We will then use the data returned from SQL to create a record in CRM.

Since VS Code does not provide intellisense, we will write the code in VS and then copy it to VS Code – Is there a better way to do this?

In VS, we have the following function ready

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Dynamic;
 static ExpandoObject ReadData(string id)
        {
            DataSet ds = null;
            dynamic obj = null;
            try
            {
                //connection string
                string connectionString = "Server=xxx.database.windows.net,1433;Initial Catalog=SampleSQL;Persist Security Info=False;User ID=xx;Password=xx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

                //ado connection object
                SqlConnection con = new SqlConnection(connectionString);

                //query customer
                string query = "select * from [SalesLT].[Customer] where CustomerID = " + id;

                //create adapter
                SqlDataAdapter adapter = new SqlDataAdapter(query, con);

                //execute query
                adapter.Fill(ds);

                //Check if record found
                if (ds!= null && ds.Tables.Count >0 && ds.Tables[0].Rows.Count > 0)
                {
                    DataRow dr = ds.Tables[0].Rows[0];

                    obj = new ExpandoObject();
                    obj.Fname = dr["FirstName"].ToString();
                    obj.Lname = dr["LastName"].ToString();
                    obj.Email = dr["EmailAddress"].ToString();
                }
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }

            return obj;
        }

We will copy this function to VS Code and save. You will find the following errors reported for missing references.

Integrating D365 with Azure Functions

To reference assemblies in .csx we need to add the following lines in the run.csx

Integrating D365 with Azure Functions

Create a bin folder and copy Newtonsoft dll there so that it is referenced correctly here.

Next, replace the code to use full qualified type names “System.Data.DataRow”

Let us call this function from the main function and pass the data received in the name querystring to the function to look up the record from SQL

public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)
{
    log.Info("C# HTTP trigger function processed a request.");

    // parse query parameter
    string name = req.GetQueryNameValuePairs()
        .FirstOrDefault(q => string.Compare(q.Key, "name", true) == 0)
        .Value;

    if (name == null)
    {
        // Get request body
        dynamic data = await req.Content.ReadAsAsync<object>();
        name = data?.name;
    }

//call function to retrieve data from Azure SQL
    dynamic obj = ReadData(name);

    //convert result to json string to be returned
    string resp = Newtonsoft.Json.JsonConvert.SerializeObject(obj);
    
    return req.CreateResponse(HttpStatusCode.OK, resp);
    // return name == null
    //     ? req.CreateResponse(HttpStatusCode.BadRequest, "Please pass a name on the query string or in the request body")
    //     : req.CreateResponse(HttpStatusCode.OK, "Hello " + name);
}

Once it compiles fine, upload the code to Azure and test it.

Execution from Postman will now show the following result

Integrating D365 with Azure Functions

Let us now call this from a workflow assembly

The following code snippet would invoke the azure function using HTTP POST request.

Note: We are accepting Function URL and secret as a workflow parameter

//read url
                string url = executionContext.GetValue(AzureFunctionURL);

                //read secret
                string secret = executionContext.GetValue(AuthCode);

                // TODO: Implement your custom Workflow business logic.
                tracingService.Trace("Before calling azure function");

                //Uri FuncURL = new Uri(url);
                UriBuilder uriBuilder = new UriBuilder(url);
                uriBuilder.Query = "code=" + secret;

                tracingService.Trace("Azure URL: " + uriBuilder.Uri.ToString());

                string result = Post2Azure(uriBuilder,tracingService,service).Result;

private static async Task<string> Post2Azure(UriBuilder uriBuilder, ITracingService tracingService, IOrganizationService service)
        {
            string result = string.Empty;
                //create http client object
                HttpClient client = new HttpClient();

                //data to send
                var data = "{\"name\": \"5\"}";

                //send in byte format
                var buffer = System.Text.Encoding.UTF8.GetBytes(data);
                var byteContent = new ByteArrayContent(buffer);
                byteContent.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue("application/json");

                tracingService.Trace("send request");
                //execute request
                HttpResponseMessage response = client.PostAsync(uriBuilder.Uri, byteContent).Result;

                if (response == null)
                {
                    tracingService.Trace("no response received");
                    throw new InvalidOperationException("Failed to obtain the httpResponse");
                }


                result = await response.Content.ReadAsStringAsync();

                tracingService.Trace("response read: " + result);

                //remove extra \
                result = result.Replace(@"\", "");
                //remove the start double quote
                result = result.Remove(0, 1);
                //remove ending double quote
                result = result = result.Remove(result.Length - 1, 1);

                JObject ob = JObject.Parse(result);

                tracingService.Trace("responseparsed to json: ");

                //Create a CRM contact
                Entity contact = new Entity("contact");
                contact["firstname"] = ob["Fname"].ToString();

                tracingService.Trace("firsname to json: " + ob["Fname"].ToString());

                contact["lastname"] = ob["Lname"].ToString();

                tracingService.Trace("firsname to json: " + ob["Lname"].ToString());

                contact["emailaddress1"] = ob["Email"].ToString();
                Guid id = service.Create(contact);
                tracingService.Trace("contact created " + id.ToString());

            }

Register the workflow assembly and execute the workflow. It would read from Azure SQL and create a contact in CRM

In the next step, we would register the Azure function as a Webhook.

Conclusion:

Using the simple steps above the user can register the workflow assembly and execute the workflow. In our next blog will see how to register the Azure function as a webhook and register steps for messages that you would like the custom logic to be executed for.

Copy System and Custom Entities in Dynamics CRM

Leave a Reply

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