{"id":11946,"date":"2018-06-11T15:16:36","date_gmt":"2018-06-11T09:46:36","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=11946"},"modified":"2019-07-10T09:30:40","modified_gmt":"2019-07-10T09:30:40","slug":"integrating-dynamics-365-with-azure-functions-part-2","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2018\/06\/integrating-dynamics-365-with-azure-functions-part-2\/","title":{"rendered":"Integrating Dynamics 365 with Azure Functions – Part 2"},"content":{"rendered":"

\"Integrating<\/p>\n

Introduction:<\/strong><\/p>\n

In our\u00a0recent\u00a0<\/a>blog, We saw how to create an Azure function and now that we have our Azure function ready and hosted, let\u2019s 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.<\/p>\n

Let us modify the code from our previous blog<\/a> 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.<\/p>\n

Since VS Code does not provide intellisense,\u00a0we will write the code in VS and then copy it to VS Code \u2013 Is there a better way to do this?<\/p>\n

In VS,\u00a0we have the following function ready<\/p>\n

using Newtonsoft.Json;\r\nusing Newtonsoft.Json.Linq;\r\nusing System;\r\nusing System.Collections.Generic;\r\nusing System.Data;\r\nusing System.Data.SqlClient;\r\nusing System.Dynamic;\r\n static ExpandoObject ReadData(string id)\r\n        {\r\n            DataSet ds = null;\r\n            dynamic obj = null;\r\n            try\r\n            {\r\n                \/\/connection string\r\n                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;\";\r\n\r\n                \/\/ado connection object\r\n                SqlConnection con = new SqlConnection(connectionString);\r\n\r\n                \/\/query customer\r\n                string query = \"select * from [SalesLT].[Customer] where CustomerID = \" + id;\r\n\r\n                \/\/create adapter\r\n                SqlDataAdapter adapter = new SqlDataAdapter(query, con);\r\n\r\n                \/\/execute query\r\n                adapter.Fill(ds);\r\n\r\n                \/\/Check if record found\r\n                if (ds!= null && ds.Tables.Count >0 && ds.Tables[0].Rows.Count > 0)\r\n                {\r\n                    DataRow dr = ds.Tables[0].Rows[0];\r\n\r\n                    obj = new ExpandoObject();\r\n                    obj.Fname = dr[\"FirstName\"].ToString();\r\n                    obj.Lname = dr[\"LastName\"].ToString();\r\n                    obj.Email = dr[\"EmailAddress\"].ToString();\r\n                }\r\n            }\r\n            catch (Exception ex)\r\n            {\r\n\r\n                throw new Exception(ex.Message);\r\n            }\r\n\r\n            return obj;\r\n        }\r\n<\/pre>\n
We will copy this function to VS Code and save. You will find the following errors reported for missing references.<\/p>\n
\"Integrating<\/p>\n
To reference assemblies in .csx we need to add the following lines in the run.csx<\/p>\n
\"Integrating<\/p>\n
Create a bin folder and copy Newtonsoft dll there so that it is referenced correctly here.<\/p>\n
Next, replace the code to use full qualified type names \u201cSystem.Data.DataRow\u201d<\/p>\n
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<\/p>\n
public static async Task<HttpResponseMessage> Run(HttpRequestMessage req, TraceWriter log)\r\n{\r\n    log.Info(\"C# HTTP trigger function processed a request.\");\r\n\r\n    \/\/ parse query parameter\r\n    string name = req.GetQueryNameValuePairs()\r\n        .FirstOrDefault(q => string.Compare(q.Key, \"name\", true) == 0)\r\n        .Value;\r\n\r\n    if (name == null)\r\n    {\r\n        \/\/ Get request body\r\n        dynamic data = await req.Content.ReadAsAsync<object>();\r\n        name = data?.name;\r\n    }\r\n\r\n\/\/call function to retrieve data from Azure SQL\r\n    dynamic obj = ReadData(name);\r\n\r\n    \/\/convert result to json string to be returned\r\n    string resp = Newtonsoft.Json.JsonConvert.SerializeObject(obj);\r\n    \r\n    return req.CreateResponse(HttpStatusCode.OK, resp);\r\n    \/\/ return name == null\r\n    \/\/     ? req.CreateResponse(HttpStatusCode.BadRequest, \"Please pass a name on the query string or in the request body\")\r\n    \/\/     : req.CreateResponse(HttpStatusCode.OK, \"Hello \" + name);\r\n}<\/pre>\n
Once it compiles fine, upload the code to Azure and test it.<\/p>\n
Execution from Postman will now show the following result<\/p>\n
\"Integrating<\/p>\n
Let us now call this from a workflow assembly<\/p>\n
The following code snippet would invoke the azure function using HTTP POST request.<\/p>\n
Note: We are accepting Function URL and secret as a workflow parameter<\/p><\/blockquote>\n
\/\/read url\r\n                string url = executionContext.GetValue(AzureFunctionURL);\r\n\r\n                \/\/read secret\r\n                string secret = executionContext.GetValue(AuthCode);\r\n\r\n                \/\/ TODO: Implement your custom Workflow business logic.\r\n                tracingService.Trace(\"Before calling azure function\");\r\n\r\n                \/\/Uri FuncURL = new Uri(url);\r\n                UriBuilder uriBuilder = new UriBuilder(url);\r\n                uriBuilder.Query = \"code=\" + secret;\r\n\r\n                tracingService.Trace(\"Azure URL: \" + uriBuilder.Uri.ToString());\r\n\r\n                string result = Post2Azure(uriBuilder,tracingService,service).Result;\r\n\r\nprivate static async Task<string> Post2Azure(UriBuilder uriBuilder, ITracingService tracingService, IOrganizationService service)\r\n        {\r\n            string result = string.Empty;\r\n                \/\/create http client object\r\n                HttpClient client = new HttpClient();\r\n\r\n                \/\/data to send\r\n                var data = \"{\\\"name\\\": \\\"5\\\"}\";\r\n\r\n                \/\/send in byte format\r\n                var buffer = System.Text.Encoding.UTF8.GetBytes(data);\r\n                var byteContent = new ByteArrayContent(buffer);\r\n                byteContent.Headers.ContentType = new System.Net.Http.Headers.MediaTypeHeaderValue(\"application\/json\");\r\n\r\n                tracingService.Trace(\"send request\");\r\n                \/\/execute request\r\n                HttpResponseMessage response = client.PostAsync(uriBuilder.Uri, byteContent).Result;\r\n\r\n                if (response == null)\r\n                {\r\n                    tracingService.Trace(\"no response received\");\r\n                    throw new InvalidOperationException(\"Failed to obtain the httpResponse\");\r\n                }\r\n\r\n\r\n                result = await response.Content.ReadAsStringAsync();\r\n\r\n                tracingService.Trace(\"response read: \" + result);\r\n\r\n                \/\/remove extra \\\r\n                result = result.Replace(@\"\\\", \"\");\r\n                \/\/remove the start double quote\r\n                result = result.Remove(0, 1);\r\n                \/\/remove ending double quote\r\n                result = result = result.Remove(result.Length - 1, 1);\r\n\r\n                JObject ob = JObject.Parse(result);\r\n\r\n                tracingService.Trace(\"responseparsed to json: \");\r\n\r\n                \/\/Create a CRM contact\r\n                Entity contact = new Entity(\"contact\");\r\n                contact[\"firstname\"] = ob[\"Fname\"].ToString();\r\n\r\n                tracingService.Trace(\"firsname to json: \" + ob[\"Fname\"].ToString());\r\n\r\n                contact[\"lastname\"] = ob[\"Lname\"].ToString();\r\n\r\n                tracingService.Trace(\"firsname to json: \" + ob[\"Lname\"].ToString());\r\n\r\n                contact[\"emailaddress1\"] = ob[\"Email\"].ToString();\r\n                Guid id = service.Create(contact);\r\n                tracingService.Trace(\"contact created \" + id.ToString());\r\n\r\n            }\r\n<\/pre>\n
Register the workflow assembly and execute the workflow. It would read from Azure SQL and create a contact in CRM<\/p>\n
In the next step, we would register the Azure function as a Webhook.<\/p>\n
Conclusion:<\/strong><\/p>\n
Using the simple steps above the user can register the workflow assembly and execute the workflow. In our next blog<\/a> will see how to register the Azure function as a webhook\u00a0and register steps for messages that you would like the custom logic to be executed for.<\/p>\n
\"Copy<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Introduction: In our\u00a0recent\u00a0blog, We saw how to create an Azure function and now that we have our Azure function ready and hosted, let\u2019s 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\u2026 Read More: Integrating Dynamics 365 with Azure Functions – Part 2 »<\/a><\/span><\/p>\n","protected":false},"author":13,"featured_media":11956,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,18,19],"tags":[187,188],"class_list":["post-11946","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-functions","category-dynamics-365-v9-2","category-dynamics-crm","tag-azure-functions","tag-azure-functions-dynamics-365"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t