Introduction
This is our second article in the Dynamics 365 and Azure series. In the first article, we had a basic introduction about Azure function and setting Azure function apps. In this article, we are going to discuss how we can read CSV files from Blob storage and process it to create/update contact records in Dynamics 365. Let’s say we have the following CSV file that we are going to upload to Azure Blob storage which will initiate our Azure function trigger and will process it.
Dynamics 365 CE And Azure Function
Details

We created an Azure function app in our last article, now the first thing we need to do is to refer to Dynamics 365 SDK, to get connected to our Dynamics 365 organization. First, log in to the Azure portal and navigate to the Azure function app that we created in the last article. Navigate to Platform Features from the Azure function app and click on App Service Editor under development tools. Right click on your Azure function trigger and add project.json file and assemble it like the following. I have done this integration for Dynamics 365 8.2.
Dynamics 365 CE And Azure Function
Now, we need to navigate to run.csx and first, we need to add a reference like the following:
  1. #r "System.Data"
  3. using System;
  4. using System.Text;
  5. using System.Data;
  6. using System.Net;
  7. using Microsoft.Xrm.Sdk;
  8. using Microsoft.Xrm.Sdk.Query;
  9. using Microsoft.Xrm.Sdk.Client;

Now, we will be using the following code in our Run method where we will be calling another method, CreateContacts, to process the CSV file.

  1. public static void Run(Stream myBlob, string name, TraceWriter log) {
  2. try {
  3. CreateContacts(myBlob, name, log);
  4. } catch (Exception e) {
  5. log.Info($ "Error occured: {e.Message}");
  6. }
  7. }

Here is the code to process CSV files from myblob storage-

  1. //read CSV file and store into data table
  2. private static DataTable GetContactDataFromCSV(Stream myBlob, TraceWriter log) {
  3. string Fulltext;
  4. DataTable contactDatatbl = new DataTable();
  5. try {
  6. using(StreamReader contactReader = new StreamReader(myBlob)) {
  7. while (!contactReader.EndOfStream) {
  8. //read full line
  9. Fulltext = contactReader.ReadToEnd().ToString();
  11. //split rows based on new line
  12. string[] rows = Fulltext.Split('\n');
  14. for (int i = 0; i < rows.Count() - 1; i++) {
  15. string[] rowValues = rows[i].Split(','); //split each row with comma to get individual values
  16. {
  17. log.Info($ "Reading file headers");
  18. if (i == 0) {
  19. for (int j = 0; j < rowValues.Count(); j++) {
  20. contactDatatbl.Columns.Add(rowValues[j]); //add headers
  21. }
  22. } else {
  23. DataRow dr = contactDatatbl.NewRow();
  24. for (int k = 0; k < rowValues.Count(); k++) {
  25. dr[k] = rowValues[k].ToString();
  26. }
  27. contactDatatbl.Rows.Add(dr);
  28. //add other rows
  29. }
  30. }
  31. }
  32. }
  33. }
  34. } catch (Exception e) {
  35. log.Info($ "Error occured in GetContactDataFromCSV Details {e.Message}");
  36. }
  37. return contactDatatbl;
  38. }
  39. //create or update contact
  40. private static void CreteContacts(Stream myBlob, string name, TraceWriter log) {
  41. Entity contact = null;
  42. try {
  43. //get CRM service instance
  44. OrganizationServiceProxy service = GetServiceProxy(log);
  45. string firstName = string.Empty;
  46. string lastName = string.Empty;
  47. string email = string.Empty;
  48. //read blob file into data table
  49. DataTable contactRecords = GetContactDataFromCSV(myBlob, log);
  50. log.Info($ "Total Records :{contactRecords.Rows.Count}");
  51. //loop datatable rows
  52. foreach(DataRow row in contactRecords.Rows) { //clear any existing
  53. firstName = string.Empty;
  54. lastName = string.Empty;
  55. email = string.Empty;
  56. //get firstname
  57. firstName = (row["FirstName"].ToString() != "" ? row["FirstName"].ToString() : string.Empty); //get last name
  58. lastName = (row["LastName"].ToString() != "" ? row["LastName"].ToString() : string.Empty); //get last name
  59. email = (row["Email"].ToString() != "" ? row["Email"].ToString() : string.Empty); //query contact to check if record already exists
  60. contact = GetContactDetails(firstName, lastName, email, service, log);
  61. //check for contact
  62. if (contact == null) {
  63. //create new contact
  64. //call method of create contact
  65. } else {
  66. //update existing contact
  67. //call method for contact update based on the contactId
  68. }
  69. }
  70. } catch (Exception e) {
  71. log.Info($ "Error occured in process cotnact records {e.Message}");
  72. }
  73. }
  74. private static Guid GetContactDetails(string firstname, string lastname, string email, OrganizationServiceProxy service, TraceWriter log) {
  75. Guid contactId = Guid.Empty;
  76. try {
  77. QueryExpression query = new QueryExpression {
  78. EntityName = "contact", ColumnSet = new ColumnSet(new string[] {
  79. "firstname",
  80. "lastname",
  81. "emailaddress1"
  82. }),
  83. Criteria = {
  84. Filters = {
  85. new FilterExpression {
  86. FilterOperator = LogicalOperator.And, Conditions = {
  87. new ConditionExpression("firstname", ConditionOperator.Equal, firstname),
  88. new ConditionExpression("lastname", ConditionOperator.Equal, lastname),
  89. new ConditionExpression("emailaddress1", ConditionOperator.Equal, email)
  90. }
  91. }
  92. }
  93. }
  94. };
  95. EntityCollection contactResultss = service.RetrieveMultiple(query);
  96. if (contactResultss.Entities.Count > 0)
  97. return contactResultss.Entities.FirstOrDefault().Id;
  98. } catch (Exception e) {
  99. log.Info($ "Error occured in Get Contact Details {e.Message}");
  100. }
  101. return contactId;
  102. }

The above code will read CSV files from Blob storage into the data table and will check if the same record exists with a combination of first name, last name and email. It will call the update method otherwise it will create it.

And we can use the following method to connect to Dynamics 365 organization.

  1. private static OrganizationServiceProxy GetServiceProxy(TraceWriter log)
  2. {
  3. IServiceManagement<IOrganizationService> orgServiceManagement = ServiceConfigurationFactory.CreateManagement<IOrganizationService>(new Uri(".... /XRMServices/2011/Organization.svc"));
  4. AuthenticationCredentials authCredentials = new AuthenticationCredentials();
  5. authCredentials.ClientCredentials.UserName.UserName = "[email protected]";
  6. authCredentials.ClientCredentials.UserName.Password = "password";
  7. AuthenticationCredentials tokenCredentials = orgServiceManagement.Authenticate(authCredentials);
  9. return new OrganizationServiceProxy(orgServiceManagement, tokenCredentials.SecurityTokenResponse);
  10. }

Keep in mind to connect using hardcoded credentials also we need include following references in our Azure function trigger. We can include the reference for ActiveDirectory assembly in project.json just like we did for Dynamics 365 V 8.

  1. using Microsoft.IdentityModel.Clients.ActiveDirectory;
This way we can process CSV files from Blob storage, and connect to Dynamics 365 to create or update contact record. But the problem in this approach is to connect to Dynamics 365 using a hard-coded username and password, so in the next article, we will discuss how to configure Server to Server authentication instead of using hard-coded credentials.
Stay tuned for the next article!