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.
To reference assemblies in .csx we need to add the following lines in the run.csx
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
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.
