CRM Dynamics 2013 How to Update Multiple Records From External Source Using ExecuteMultipleRequest

Question

I am having a scenario in CRM where I need to update multiple accounts values(text fields and option sets) with values from an external sql database table. How can I go about doing this using the execute multiple request. The requirement is to sync all the account data in CRM with our ERP data which comes from a sql table. This process needs to be automated so i opted to use a windows service that runs daily to update accounts that ar flagged for update in the external sql table. I am struggling to find the best approach for this,I tested this idea in a console application on DEV and here is my solution code below. My question is how can I do this better using ExecuteMultipleRequest request.

public static void UpdateAllCRMAccountsWithEmbraceAccountStatus(IOrganizationService service, CRM_Embrace_IntegrationEntities3 db)
        {


            List<C_Tarsus_Account_Active_Seven__> crmAccountList = new List<C_Tarsus_Account_Active_Seven__>();

            //Here I get the list from Staging table
            var crmAccounts = db.C_Tarsus_Account_Active_Seven__.Select(x => x).ToList();



            foreach (var dbAccount in crmAccounts)
            {
                CRMDataObjectFour modelObject = new CRMDataObjectFour()
                {
                    ID = dbAccount.ID,
                    Account_No = dbAccount.Account_No,
                    Account_Name = dbAccount.Account_Name,
                    Account_Status = Int32.Parse(dbAccount.Account_Status.ToString()),
                    Country = dbAccount.Country,
                    Terms = dbAccount.Terms
                };
            }


            var officialDatabaseList = crmAccounts;

            //Here I query CRM to
            foreach (var crmAcc in officialDatabaseList)
            {
                QueryExpression qe = new QueryExpression();
                qe.EntityName = "account";
                qe.ColumnSet = new ColumnSet("accountnumber", "new_embraceaccountstatus");
                qe.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
                qe.Criteria.AddCondition("accountnumber", ConditionOperator.NotIn, "List of acconts for example"


);

                EntityCollection response = service.RetrieveMultiple(qe);

                //Here I update the optionset value
                foreach (var acc in response.Entities)
                {
                    if (acc.Attributes["accountnumber"].ToString() == crmAcc.Account_No)
                    {
                        if (acc.Contains("new_embraceaccountstatus"))
                        {
                            continue;
                        }
                        else
                        {
                            acc.Attributes["new_embraceaccountstatus"] = new OptionSetValue(Int32.Parse(crmAcc.Account_Status.ToString()));
                        }
                        service.Update(acc);
                    }

                }
            }


        }

I know this might not be the right approach, please advise me how to use ExecuteMultipleRequest or perhaps a different solution altogether.

Answer 1

Because it is 2013, and you need to sync records, you'll need to know if some previous records were already in CRM, because depending on that you'll need to send a bunch of Create's or Update's. I would do it in 2 batches of ExecuteMultiple:

1) One batch to execute a query to find which accounts need to be created / updated in CRM, depending on some matching field there.

2) Another batch which will use the previous one to generate all Create / Update operations in one go, depending on the responses you got from 1).

The issue is that they won't run in the same transaction, and that's something which was improved in 2016, as @Daryl said. There is also a new request in 2016 which might improve things even further, because you could merge the 2 batches into one: Upsert, therefore avoiding unnecessary roundtrips to the server.

Maybe this was inspired on Mongo Db's upsert concept which existed long time before? Who knows :)

Answer 2

Here is some helper methods I've used before to handle this:

public static ExecuteMultipleRequest MultipleRequest { get; set; }
private const int BatchSize = 250;
public static long LastBatchTime { get; set; }

private static void Batch(IOrganizationService service, OrganizationRequest request)
{
    if (MultipleRequest.Requests.Count == BatchSize)
    {
        ExecuteBatch(service);
    }
    MultipleRequest.Requests.Add(request);
}

private static void ExecuteBatch(IOrganizationService service)
{
    if (!MultipleRequest.Requests.Any())
    {
        return;
    }
    Log("Executing Batch size {0}.  Last Batch was executed in {1}",MultipleRequest.Requests.Count, LastBatchTime);
    var watch = new System.Diagnostics.Stopwatch();
    watch.Start();
    var response = (ExecuteMultipleResponse)service.Execute(MultipleRequest);
    watch.Stop();
    LastBatchTime = watch.ElapsedMilliseconds;
    Log("Completed Executing Batch in " + watch.ElapsedMilliseconds);
    WriteLogsToConsole();


    var errors = new List<string>();
    // Display the results returned in the responses.
    foreach (var responseItem in response.Responses)
    {
        // A valid response.
        if (responseItem.Fault != null)
        {
            errors.Add(string.Format(
                "Error: Execute Multiple Response Fault.  Error Code: {0} Message {1} Trace Text: {2} Error Keys: {3} Error Values: {4} ",
                responseItem.Fault.ErrorCode,
                responseItem.Fault.Message,
                responseItem.Fault.TraceText,
                responseItem.Fault.ErrorDetails.Keys,
                responseItem.Fault.ErrorDetails.Values));
        }
    }
    MultipleRequest.Requests.Clear();
    if (errors.Any())
    {
        throw new Exception(string.Join(Environment.NewLine, errors));
    }
}

You can then call this from your normal logic like so:

public static void UpdateAllCRMAccountsWithEmbraceAccountStatus(IOrganizationService service, CRM_Embrace_IntegrationEntities3 db)
{
    List<C_Tarsus_Account_Active_Seven__> crmAccountList = new List<C_Tarsus_Account_Active_Seven__>();

    //Here I get the list from Staging table
    var crmAccounts = db.C_Tarsus_Account_Active_Seven__.Select(x => x).ToList();

    foreach (var dbAccount in crmAccounts)
    {
        CRMDataObjectFour modelObject = new CRMDataObjectFour()
        {
            ID = dbAccount.ID,
            Account_No = dbAccount.Account_No,
            Account_Name = dbAccount.Account_Name,
            Account_Status = Int32.Parse(dbAccount.Account_Status.ToString()),
            Country = dbAccount.Country,
            Terms = dbAccount.Terms
        };
    }

    var officialDatabaseList = crmAccounts;

    //Here I query CRM to
    foreach (var crmAcc in officialDatabaseList)
    {
        QueryExpression qe = new QueryExpression();
        qe.EntityName = "account";
        qe.ColumnSet = new ColumnSet("accountnumber", "new_embraceaccountstatus");
        qe.Criteria.AddCondition("statecode", ConditionOperator.Equal, 0);
        qe.Criteria.AddCondition("accountnumber", ConditionOperator.NotIn, "List of acconts for example");

        EntityCollection response = service.RetrieveMultiple(qe);

        //Here I update the optionset value
        foreach (var acc in response.Entities)
        {
            if (acc.Attributes["accountnumber"].ToString() == crmAcc.Account_No)
            {
                if (acc.Contains("new_embraceaccountstatus"))
                {
                    continue;
                }
                else
                {
                    acc.Attributes["new_embraceaccountstatus"] = new OptionSetValue(Int32.Parse(crmAcc.Account_Status.ToString()));
                }
                Batch(service, new UpdateRequest { Target = acc });
            }

        }
    }

    // Call ExecuteBatch to ensure that any batched requests, get executed.
    ExeucteBatch(service)
}

Answer 3

If you just need to now how to perform an ExecuteMultipleRequest there are samples on the MSDN. Sample: Execute multiple requests.