THE CRM CAFÉ: Console application to create a .csv file and Add data and Retrieve, and update data into D365

Console application to create a .csv file and Retrieve, and update data into D365

Here in this blog, I have covered 3 scenarios, follow the step by step process to learn and write a better console app within 5 - 10 mins:

1. Write a Console Application to Retrieve or Fetch contact records from Dynamics 365.

2. Create a .csv file and write records into a CSV file row-wise.

3. Read records from the CSV file and Update Contact Records back in CRM

Follow the step by step process :

1. Open Visual Studio Application
2. Navigate to File -> Click on new Project...
3. Select Visual C# -> Console Application(.Net Framework)
4. Give a name to your console application (e.g. ConsoleApp1)
5. Set the location where you want to store your console project (e.g. C:\Users\Console Project)
6. Provide a solution name (e.g. MyConsoleAppSolution)
7. Choose the right Framework and Click Ok to save your console application project.

Once saved the folder structure will look like as shown on the screen below:

Let's begin with your coding part:

1. First Go to References > Add required assembly References from the NuGet Package:
You can see in the below screen, that these are the mandatory references that are required for your console app. Once you added you can verify it in your reference file.

2. Open > App.config file > Write below code in that.

In App.config file you add your connectionStrings and if you have any key-value pair you can add it inside appSettings. Follow the code below.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7" />
</startup>
<connectionStrings>

<add name="CRMConnection" connectionString="AuthType=Office365;Username=crmtest@amrs12.onmicrosoft.com; Password=Crm@1234;Url=https://amrs12.crm.dynamics.com/" />
</connectionStrings>
<appSettings>
<add key="LastYear" value="false" />
<add key="LogFilePath" value="C:\Users\Documents\Logs\" />
<add key="ClientSettingsProvider.ServiceUri" value="" />
</appSettings>
<system.web>
<membership defaultProvider="ClientAuthenticationMembershipProvider">
<providers>
<add name="ClientAuthenticationMembershipProvider" type="System.Web.ClientServices.Providers.ClientFormsAuthenticationMembershipProvider, System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" serviceUri="" />
</providers>
</membership>
<roleManager defaultProvider="ClientRoleProvider" enabled="true">
<providers>
<add name="ClientRoleProvider" type="System.Web.ClientServices.Providers.ClientRoleProvider, System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" serviceUri="" cacheTimeout="86400" />
</providers>
</roleManager>
</system.web>

</configuration>

3. Create a ConnectionService.cs class and add the below code to call the connectionStrings to connect to the CRM instance:

using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Tooling.Connector;
using System;
using System.Configuration;
using System.Net;

namespace ConsoleApplication
{
  public class ConnectionService
{
  static string connectionString = ConfigurationManager.ConnectionStrings["CRMConnection"].ConnectionString;
  public OrganizationServiceProxy CreateServiceProxy()
{
  try
{
ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;

  // Connect to the CRM web service using a connection string.
CrmServiceClient conn = new Microsoft.Xrm.Tooling.Connector.CrmServiceClient(connectionString);

  // Cast the proxy client to the IOrganizationService interface.
IOrganizationService _orgService = (IOrganizationService)conn.OrganizationWebProxyClient != null ? (IOrganizationService)conn.OrganizationWebProxyClient : (IOrganizationService)conn.OrganizationServiceProxy;
Guid orgId = ((WhoAmIResponse)_orgService.Execute(new WhoAmIRequest())).OrganizationId;
  return (OrganizationServiceProxy)_orgService;
}
  catch (Exception ex)
{
  throw;
}
}
}

}

4. Create a common entity Attribute class to store entities setter and getter methods e.g. Entity.cs, In this class, I have added ContactId, FirstName & LastName

using System;

using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace MyConsoleApp
{
  class EntityAttributes
  {
  public class Contacts
  {
  public string ContactId { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }

  }
  }
}

5. Create a Common.cs class in which add all your common methods.

-> Call connectionService inside InitiateService() to return the serviceProxy, with this service we can fetch and update the record in the CRM.

-> WriteCSVFile method to create CSV file in the mentioned path with a list of CRM records in raw wise in the sheet.

-> RetrieveRecords Function to Retrieve Records from the CRM and return a EntityCollection Response

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query;
using System.Configuration;

namespace ConsoleApplication
{
  class Common
  {
  static StringBuilder _log = new StringBuilder();
  public static string filepath = (ConfigurationManager.AppSettings["Logs"]).ToString();

  /// <summary>
  /// Initiate Connection Service function
  /// </summary>
  /// <returns></returns>
  public static OrganizationServiceProxy InitiateService()
  {
_log.AppendLine("Initiating CRM Service");
OrganizationServiceProxy serviceProxy = new ConnectionService().CreateServiceProxy();
serviceProxy.Timeout = new TimeSpan(2, 0, 0);
OrganizationServiceContext context = new OrganizationServiceContext(serviceProxy);
_log.AppendLine("Initiated CRM Service");

  return serviceProxy;
  }

  /// <summary>
  /// Function to Create CSV file with list of contact records
  /// </summary>
  /// <param name="lst"></param>
  /// <param name="filename"></param>
  public static void WriteCSVFile(List<MyConsoleApp.EntityAttributes.Contacts> lst, string filename)
  {
  string filepath = Common.filepath + filename + DateTime.Now.Date.ToString("dd-MM-yyyy").Replace('/', '-') + ".csv";

  String csv = String.Join(
  Environment.NewLine,
lst.Select(x => x.ContactId + "," + x.FirstName + "," + x.LastName)
  );
  System.IO.File.WriteAllText(filepath, csv);
Console.WriteLine("CSV File Generated");
    }

  /// <summary>
  /// Function to retrieve records from CRM using entity collection
  /// </summary>
  /// <param name="service"></param>
  /// <param name="Entity"></param>
  /// <param name="fe"></param>
  /// <param name="columns"></param>
  /// <param name="pageNumber"></param>
  /// <param name="PagingCookie"></param>
  /// <param name="ErrorMessage"></param>
  /// <returns></returns>
  public static EntityCollection RetrieveRecords(IOrganizationService service, string Entity, FilterExpression fe, ColumnSet columns, int pageNumber, string PagingCookie, out string ErrorMessage)
  {
ErrorMessage = "";
  try
  {
QueryExpression qe = new QueryExpression();
  qe.PageInfo.PageNumber = pageNumber;
  qe.EntityName = Entity;
  qe.Criteria.Filters.Add(fe);
  qe.ColumnSet = columns;

  qe.PageInfo.Count = 5000;

  if (!PagingCookie.Equals(string.Empty))
  {
  qe.PageInfo.PagingCookie = PagingCookie;
  }
  EntityCollection response = service.RetrieveMultiple(qe);
  return response;
  }
  catch (Exception ex)
  {
  ErrorMessage = ex.Message;
  return null;
  }
  }

public static string PascalCamelCasing(string text)

{

string firstLetterOfEachWord = string.Join(" ", text.Split(' ').ToList().ConvertAll(word =>word.Substring(0, 1).ToUpper() + word.Substring(1)));

return firstLetterOfEachWord;

}
}

}

6. Create a Contact.cs class. In this class add your business logic as required.

-> In RetrieveContactRecordsFromCRM function call RetrieveRecords method and store records in the entityCollection variable now get the records one by one from the entitycollection and store them into the list lstcontact.

-> Call the WriteCSVFile method passing 2 arguments (1. lstContact and 2.entity string contact)

-> Call UpdateContactRecords method, here in this method reading records one by one from the CSV file and storing the data into entityCollection contactRecords, checking record count if == 1000 (batchsize) then, calling BulkUpdate Method and also if < 1000 (means > 0)then calling BulkUpdate method to update remaining records into the CRM.

using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk.Messages;
using System.Globalization;
using ConsoleApplication;

namespace MyConsoleApp
{
  public class Contact
  {
  static StringBuilder _log = new StringBuilder();
  public static OrganizationServiceProxy service = Common.InitiateService();
  public static string filePath = Common.filepath + "Contact.txt";
  public static string csvFilePath = Common.filepath + "Contact";
  public static int batchSize = 1000; //This is the max batch size

  /// <summary>
  /// Function to retrieve contact records from CRM
  /// </summary>
  public static void RetrieveContactRecordsFromCRM()
  {
  try
  {
  string entityName = "contact";
  ColumnSet col = new ColumnSet();
  col.AddColumns("firstname", "lastname");
              int pageNumber = 1;
  string pagingCookie = "";
  bool moreRecords = true;
  string ErrorMessage = "";
  bool contactRetrieveFailed = false;
  FilterExpression fe = new FilterExpression(LogicalOperator.And);
  fe.AddCondition("statecode", ConditionOperator.Equal, 0);
  int retryCount = 0;
  List<EntityAttributes.Contacts> lstContacts = new List<EntityAttributes.Contacts>();

        while (moreRecords)
  {

//Retriving and storing records to entityCollection
EntityCollection entityCollection = Common.RetrieveRecords(service, entityName, fe, col, pageNumber, pagingCookie, out ErrorMessage);

//Check if entityCollection has record
  if (entityCollection != null && entityCollection.Entities.Count > 0 && ErrorMessage.Equals(string.Empty))
  {
  _log.AppendLine("Retrieved Page: " + pageNumber + " Total Records: " + entityCollection.Entities.Count);
  foreach (Entity contact in entityCollection.Entities)
  {
  lstContacts.Add(new EntityAttributes.Contacts
  {
  ContactId = Convert.ToString(contact.Id),
  FirstName = contact.Attributes.Contains("firstname") ? contact.GetAttributeValue<string>("firstname").Trim() : "",
  LastName = contact.Attributes.Contains("lastname") ? contact.GetAttributeValue<string>("lastname").Trim() : "",
  });
  }
  if (entityCollection.MoreRecords)
  {
                        pageNumber++;
  pagingCookie = entityCollection.PagingCookie;
  }
  else { moreRecords = false; }
  }
  else if (!(ErrorMessage.Equals(string.Empty)))
  {
  if (ErrorMessage.ToLower().Contains("timeout"))
  {
  service = Common.InitiateService();
  retryCount++;

                        _log.AppendLine("Timeout in UpdateContactFirstName&LastName - RetrieveRecords function, re-trying..." + retryCount);

  if (retryCount > 3)
  {
  contactRetrieveFailed = true;
  _log.AppendLine("Truncating operation as max no. of attempts exceeded");
  break;
  }
  }
  else
  {
  contactRetrieveFailed = true;
  break;
  }
  }
  File.AppendAllText(filePath, _log.ToString());
  _log = new StringBuilder();
  _log.AppendLine("-------------------------------------------------------------------");
  }

  if (!contactRetrieveFailed)
  {
          // Calling WriteCSVFile method with contactList records
  Common.WriteCSVFile(lstContacts, "Contact");

                  //Calling UpdateContactRecords Methods
  UpdateContactRecords();
  }
  }
  catch (Exception ex)
  {
            _log.AppendLine(" Exception at UpdateContactFirstNameLastName Method Exeception: " + ex.ToString());
  }
  finally
  {
  File.AppendAllText(filePath, _log.ToString());
  }
  }

  /// <summary>
  /// Method to read records from CSV file and update contact records into the CRM
  /// </summary>
  public static void UpdateContactRecords()
  {
  try
  {
  _log = new StringBuilder();
  int totalProcessedBatch = 0;
  EntityCollection contactRecords = new EntityCollection();
  string csvPath = csvFilePath + DateTime.Now.Date.ToString("dd-MM-yyyy").Replace('/', '-') + ".csv";
  var csvRows = File.ReadAllLines(csvPath);
  int startRow = 0;

  for (int i = startRow; i < csvRows.Length - 1; i++)
  {
  string currentRow = csvRows[i].Replace("\"", string.Empty);
  string[] columnValues = currentRow.Split(',');

  Entity contact = new Entity("contact");
  string firstName = columnValues[1];
  string lastName = columnValues[2];
  bool isFirstName, isLastName = false;
  isFirstName = isLowerCaseinString(firstName);
  isLastName = isLowerCaseinString(lastName);

  if (isFirstName || isLastName)
  {
  if (isFirstName)
  {

//firstName = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(firstName);
contact.Attributes["firstname"] = Common.PascalCamelCasing(firstName);
//contact.Attributes["firstname"] = firstName;

  }
  if (isLastName)
  {

//lastName = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(lastName);
contact.Attributes["lastname"] = Common.PascalCamelCasing(lastName);
//contact.Attributes["lastname"] = lastName;

  }
  contact.Id = Guid.Parse(columnValues[0]);
  contactRecords.Entities.Add(contact);
  }
  _log.AppendLine(Convert.ToString(i));

  if (contactRecords.Entities.Count == batchSize)
                {
  totalProcessedBatch++;
  BulkUpdate(contactRecords);
  _log.AppendLine("Total Batch Processed: " + totalProcessedBatch + "(" + contactRecords.Entities.Count + ")" + " | Total Number of Record Processed: " + i);
  contactRecords = new EntityCollection();
  File.AppendAllText(filePath, _log.ToString());
  _log = new StringBuilder();
  }
            }
  //Condition to check if records are less than 500
  if (contactRecords.Entities.Count > 0)
  {
  totalProcessedBatch++;
  BulkUpdate(contactRecords);
  _log.AppendLine("Total Batch Processed: " + totalProcessedBatch + "(" +contactRecords.Entities.Count +")" + " | Total Number of Record Processed: " + csvRows.Length);
  contactRecords = new EntityCollection();
              File.AppendAllText(filePath, _log.ToString());
  _log = new StringBuilder();
  }
  }
  catch (Exception ex)
  {
  _log.AppendLine("Error UpdateContactRecord: " + ex.Message);
  }
  finally
  {
  File.AppendAllText(filePath, _log.ToString());
  }
  }

  /// <summary>
  /// Function to BulkUpdate records in CRM
  /// </summary>
  /// <param name="entityCollection"></param>
  public static void BulkUpdate(EntityCollection entityCollection)
  {
  try
  {
  var multipleRequest = new ExecuteMultipleRequest()
  {
  Settings = new ExecuteMultipleSettings()
  {
  ContinueOnError = true,
  ReturnResponses = true
  },
  Requests = new OrganizationRequestCollection()
  };

  foreach (var entity in entityCollection.Entities)
  {
  UpdateRequest updateRequest = new UpdateRequest { Target = entity };
  multipleRequest.Requests.Add(updateRequest);
  }

  ExecuteMultipleResponse multipleResponse = (ExecuteMultipleResponse)service.Execute(multipleRequest);
  }
  catch (Exception ex)
          {
  _log.AppendLine("Error in BulkUpdate Method: " + ex.Message);
  }
  }
  /// <summary>
  /// Function to convert string into the camelcase
  /// </summary>
  /// <param name="value"></param>
  /// <returns></returns>
  public static bool isLowerCaseinString(string value)
  {
  string[] array = value.Split(' ');
  bool result = false;
  foreach (string s in array)
  {
  if (!String.IsNullOrEmpty(s))
  {
  string c = s.Substring(0, 1);
  if (char.IsLetter(Convert.ToChar(c)))
  {
  if (char.IsLower(Convert.ToChar(c)))
                        {
  result = true;
  break;
  }
  }
  }

  }
  return result;
  }
  }

}

7. In Program.cs class main method is written from where all the execution begins e.g. Program.cs,

-> Call RetrieveContactRecordsFromCRM

using System;
using System.Windows.Forms;
namespace MyConsoleApp
{
  class Program
  {
  static void Main(string[] args)
  {
  Console.WriteLine();
Console.WriteLine(" Please select the Console you want to run:");
  Console.WriteLine();
Console.WriteLine(" 1. Update Contact Name with Camel Casing.");
  Console.WriteLine();

  var input = Console.ReadLine();
  var inputValue = GetUtilityInfo(Convert.ToString(input));
  if (!inputValue.Equals("Invalid"))
  {
DialogResult ans = MessageBox.Show("Do you want to " + inputValue + "?", "Confirm", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

  if (ans == DialogResult.Yes)
  {
  switch (input.ToString())
  {
  case "1":
  Contact.RetrieveContactRecordsFromCRM();
  break;
  default:
Console.WriteLine("Invalid Input. Please enter a number between 1-11");
  break;
  }
  }
  }
  else
  {
MessageBox.Show("Invalid Input. Please enter a number between 1 - 11");
  }
  }

  public static string GetUtilityInfo(string input)
  {
  switch (input)
  {
  case "1": return "Update Contact Name with Camel Casing.";

  default: return "Invalid";
  }
  }
  }
}

8. Finally the console application solution will look like as shown below, Save and build the solution -> Run and Start the test.

Thank you!

Happy Learning!!!

THE CRM CAFÉ

D365 Pages

Search

Tuesday, May 31, 2022

Console application to create a .csv file and Add data and Retrieve, and update data into D365

Console application to create a .csv file and Retrieve, and update data into D365

No comments:

Post a Comment