Dynamics Community Forum Thread Details

Hi All,

I am using below code to create large no of case records like 10K+ & after that one more entity record like interview & then Action record.

But due to timeout issue not sure its sometimes skipping creating cases & going to different method to create interview entity records & Action records.

What could be the cause its going out from the loop in executemultiplerequests method batchwise it will create 1000 records from the requested list.

getting issue in logg file The remote server returned an unexpected response 502 Bad gateway. & its skipping creation of rest case records for some duration & going for create Interviews & so on.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using Microsoft.Xrm.Sdk;

using Microsoft.Xrm.Sdk.Client;

using Microsoft.Xrm.Sdk.Messages;

using Microsoft.Xrm.Sdk.Metadata;

using Microsoft.Xrm.Tooling.Connector;

using System.ServiceModel.Description;

using System.Configuration;

using Microsoft.Xrm.Sdk.Query;

using Microsoft.Crm.Sdk.Messages;

using Microsoft.Office.Interop.Excel;

using System.Net;

using NLog;

using System.IO;

namespace ConsoleApplication1

{

class Program

{

public const int BATCH_SIZE = 300;// Controls how many items get passed into an ExecuteMultipleRequest

private static Logger logger = LogManager.GetCurrentClassLogger();

public static string fileName = DateTime.Now.ToString("dd-MM-yyyy-HH-mm-ss") + "Prod";

static void Main(string[] args)

{

IOrganizationService _crmService = ConnectToCRM();

if (_crmService != null)

{

Guid userid = ((WhoAmIResponse)_crmService.Execute(new WhoAmIRequest())).UserId;

if (userid != Guid.Empty)

{

Console.WriteLine("Connection Established Successfully");

Get_and_CreateCRMRecord(_crmService);

//CreateInterviews(_crmService); //Creating Interview Records

//CreateActions(_crmService); //Creating Action Records

}

public static void Get_and_CreateCRMRecord(IOrganizationService Service)

{

Application excelApp = new Application();

if (excelApp == null)

{

Console.WriteLine("Excel is not installed!!");

return;

}

Workbook excelBook = excelApp.Workbooks.Open(@"C:\_DO_NOT_BACKUP\MigrationCCMToPP1GC_8thSep2021\9thSep\CCM- Case MigrationSepFinal.xlsx");

_Worksheet excelSheet = excelBook.Sheets[1];

Range excelRange = excelSheet.UsedRange;

int rows = excelRange.Rows.Count;

int cols = excelRange.Columns.Count;

ExecuteMultipleRequest executeMultipleRequest = CreateExecuteMultipleRequest();

// List<Entity> listEntity = new List<Entity>();

for (int i = 1; i <= rows; i++)

{
Entity incident = new Entity("incident");

if (i >= 2)

{

for (int j = 1; j <= cols; j++)

{

incident["mdc_ismigrated"] = new OptionSetValue(755040000); //Yes

try

{

switch (j)

{

case 1:

string Casecode = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["ticketnumber"] = !string.IsNullOrEmpty(Casecode) ? Casecode : string.Empty;

break;

//2 CreatedOn NA

case 3:

string Casefindings = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_casefindings"] = !string.IsNullOrEmpty(Casefindings) ? Casefindings : string.Empty;

break;

case 4:

//incident["description"] = string.Empty;

string CaseSummary = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["description"] = !string.IsNullOrEmpty(CaseSummary) ? CaseSummary : string.Empty;

break;

//5 Industry/Sector need to look

case 5:

//incident["description"] = string.Empty;

incident["mdc_industrysector"] = null;

string industrysector = Convert.ToString(excelRange.Cells[i, j].Value2);//.ToString();

if (!string.IsNullOrEmpty(industrysector))

{

switch (industrysector.ToLower())

{

case "aerospace":

incident["mdc_industrysector"] = new OptionSetValue(755040000);

break;

case "banking":

incident["mdc_industrysector"] = new OptionSetValue(755040001);

break;

case "charity":

incident["mdc_industrysector"] = new OptionSetValue(755040002);

break;

case "construction":

incident["mdc_industrysector"] = new OptionSetValue(755040003);

break;

case "defense":

incident["mdc_industrysector"] = new OptionSetValue(755040004);

break;

case "education":

incident["mdc_industrysector"] = new OptionSetValue(755040005);

break;

case "energy":

incident["mdc_industrysector"] = new OptionSetValue(755040006);

break;

case "financial services":

incident["mdc_industrysector"] = new OptionSetValue(755040007);

break;

case "government entity":

incident["mdc_industrysector"] = new OptionSetValue(755040008);

break;

case "healthcare services":

incident["mdc_industrysector"] = new OptionSetValue(755040009);

break;

case "investment":

incident["mdc_industrysector"] = new OptionSetValue(755040010);

break;

case "manufacturing":

incident["mdc_industrysector"] = new OptionSetValue(755040011);

break;

case "media":

incident["mdc_industrysector"] = new OptionSetValue(755040012);

break;

case "other":

incident["mdc_industrysector"] = new OptionSetValue(755040013);

break;

case "product supply":

incident["mdc_industrysector"] = new OptionSetValue(755040014);

break;

case "professional services":

incident["mdc_industrysector"] = new OptionSetValue(755040015);

break;

case "real estate":

incident["mdc_industrysector"] = new OptionSetValue(755040016);

break;

default:

break;

}

break;

case 6:

string IntakeSummary = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["description"] += !string.IsNullOrEmpty(IntakeSummary) ? " " + IntakeSummary : string.Empty;

break;

case 9:

string Title = Convert.ToString(excelRange.Cells[i, j].Value2);

incident["mdc_reportertitle"] = !string.IsNullOrEmpty(Title) ? Title : string.Empty;

break;

case 10:

incident["mdc_reportername"] = null;

string Email = Convert.ToString(excelRange.Cells[i, j].Value2);

if (!string.IsNullOrEmpty(Email))

{

// incident["emailaddress"] = Email;

QueryExpression query = new QueryExpression("contact");

query.ColumnSet = new ColumnSet("emailaddress1", "mdc_title", "mobilephone", "mdc_company", "mdc_department", "mdc_linemanager");

query.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, Email);

try

{

//EntityCollection col = Service.RetrieveMultiple(query);

var col = RetrieveAllRecords(Service, query);

if (col.Count > 0)

{

foreach (Entity Eninci in col)

{

incident["mdc_reportername"] = new EntityReference("contact", Eninci.Id);

incident["customerid"] = new EntityReference("contact", Eninci.Id);

var conEmailAdd = Eninci.Attributes.Contains("emailaddress1") ? Eninci.GetAttributeValue<string>("emailaddress1") : string.Empty;

incident["mdc_reporteremailaddress"] = !string.IsNullOrEmpty(conEmailAdd) ? conEmailAdd : string.Empty;

var contitle = Eninci.Attributes.Contains("mdc_title") ? Eninci.GetAttributeValue<string>("mdc_title") : string.Empty;

incident["mdc_reportertitle"] = !string.IsNullOrEmpty(contitle) ? contitle : string.Empty;

var conMobilePhone = Eninci.Attributes.Contains("mobilephone") ? Eninci.GetAttributeValue<string>("mobilephone") : string.Empty;

incident["mdc_contactnumber"] = !string.IsNullOrEmpty(conMobilePhone) ? conMobilePhone : string.Empty;

incident["mdc_reporterphonenumber"] = !string.IsNullOrEmpty(conMobilePhone) ? conMobilePhone : string.Empty;

var conCompany = Eninci.Attributes.Contains("mdc_company") ? Eninci.GetAttributeValue<string>("mdc_company") : string.Empty;

incident["mdc_reportercompany"] = !string.IsNullOrEmpty(conCompany) ? conCompany : string.Empty;

var conDept = Eninci.Attributes.Contains("mdc_department") ? Eninci.GetAttributeValue<string>("mdc_department") : string.Empty;

incident["mdc_reporterdepartment"] = !string.IsNullOrEmpty(conDept) ? conDept : string.Empty;

var conLineManager = Eninci.Attributes.Contains("mdc_linemanager") ? Eninci.GetAttributeValue<string>("mdc_linemanager") : string.Empty;

incident["mdc_linemanagername"] = !string.IsNullOrEmpty(conLineManager) ? conLineManager : string.Empty;

}

else

{

string ReporterName = Convert.ToString(excelRange.Cells[i, 7].Value2);

string ExReporterName = Convert.ToString(excelRange.Cells[i, 8].Value2);

if (!string.IsNullOrEmpty(ReporterName))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.ColumnSet = new ColumnSet("fullname", "emailaddress1", "mdc_title", "mobilephone", "mdc_company", "mdc_department");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, ReporterName);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1.Count > 0)

{

foreach (Entity Eninci1 in contactCol1)

{

incident["mdc_reportername"] = new EntityReference("contact", Eninci1.Id);

incident["customerid"] = new EntityReference("contact", Eninci1.Id);

var conEmailAdd = Eninci1.Attributes.Contains("emailaddress1") ? Eninci1.GetAttributeValue<string>("emailaddress1") : string.Empty;

incident["mdc_reporteremailaddress"] = !string.IsNullOrEmpty(conEmailAdd) ? conEmailAdd : string.Empty;

var contitle = Eninci1.Attributes.Contains("mdc_title") ? Eninci1.GetAttributeValue<string>("mdc_title") : string.Empty;

incident["mdc_reportertitle"] = !string.IsNullOrEmpty(contitle) ? contitle : string.Empty;

var conMobilePhone = Eninci1.Attributes.Contains("mobilephone") ? Eninci1.GetAttributeValue<string>("mobilephone") : string.Empty;

incident["mdc_contactnumber"] = !string.IsNullOrEmpty(conMobilePhone) ? conMobilePhone : string.Empty;

incident["mdc_reporterphonenumber"] = !string.IsNullOrEmpty(conMobilePhone) ? conMobilePhone : string.Empty;

var conCompany = Eninci1.Attributes.Contains("mdc_company") ? Eninci1.GetAttributeValue<string>("mdc_company") : string.Empty;

incident["mdc_reportercompany"] = !string.IsNullOrEmpty(conCompany) ? conCompany : string.Empty;

var conDept = Eninci1.Attributes.Contains("mdc_department") ? Eninci1.GetAttributeValue<string>("mdc_department") : string.Empty;

incident["mdc_reporterdepartment"] = !string.IsNullOrEmpty(conDept) ? conDept : string.Empty;

}

else

{

var newRN = ReporterName.Split(' ');

string firstName = newRN[0];

string lastName = ReporterName.Remove(0, ReporterName.IndexOf(' ') + 1);

// string lastName = exInterview[1];

Entity reporter = new Entity("contact");

reporter["firstname"] = !string.IsNullOrEmpty(firstName) ? firstName : string.Empty;

reporter["lastname"] = !string.IsNullOrEmpty(lastName) ? lastName : string.Empty;

reporter["emailaddress1"] = !string.IsNullOrEmpty(Email) ? Email : string.Empty;

Service.Create(reporter);

QueryExpression qe = new QueryExpression();

qe.EntityName = "contact";

qe.ColumnSet = new ColumnSet("emailaddress1");

qe.Criteria = new FilterExpression();

qe.Criteria.AddCondition("emailaddress1", ConditionOperator.Equal, Email);

//EntityCollection ec = Service.RetrieveMultiple(qe);

var ec = RetrieveAllRecords(Service, qe);

if (ec.Count > 0)

{

foreach (var con in ec)

{

incident["customerid"] = new EntityReference("contact", con.Id);

incident["mdc_reportername"] = new EntityReference("contact", con.Id);

incident["mdc_reporteremailaddress"] = Email;

}

else if (!string.IsNullOrEmpty(ExReporterName))

{

QueryExpression conQuery = new QueryExpression("contact");

conQuery.ColumnSet = new ColumnSet("fullname", "emailaddress1", "mdc_title", "mobilephone", "mdc_company", "mdc_department");

conQuery.Criteria.AddCondition("fullname", ConditionOperator.Equal, ExReporterName);

//EntityCollection contactCol1 = Service.RetrieveMultiple(conQuery);

var contactCol1 = RetrieveAllRecords(Service, conQuery);

if (contactCol1?.Count > 0)