CRM Dynamics 2013 SDK Update Current Accounts With

问题:

I have a scenario in CRM where I need to update existing accounts with their Vat and Registration number. There is well over 30 thousand accounts in the system. I am trying to update using the CRM SDK API but I am battling to figure out how to perform the actual update. The vat number and reg have been provided to me in a spreadsheet with their corresponding number, please note that the accounts are already in CRM so I just need to update the correct account with its Vat and Reg number, How can I do this in CRM, please advice on my code below:

public static void UpdateAllCRMAccountsWithVATAndRegistrationNumber(IOrganizationService service)
        {
            QueryExpression qe = new QueryExpression();
            qe.EntityName = "account";
            qe.ColumnSet = new ColumnSet("account", "new_vatno", "new_registrationnumber");
            qe.Criteria.AddCondition("accountnumber", ConditionOperator.In,"TA10024846", "TA10028471", "TA20014015", "TA4011652", "TA4011557");

            EntityCollection response = service.RetrieveMultiple(qe);

            foreach (var acc in response.Entities)
            {
                acc.Attributes["new_vatno"] = //this is where I am struggling to figure out how I am gong to match the records up,
                acc.Attributes["new_registrationnumber"] = //this is where I am struggling to figure out how I am gong to match the records up,

                service.Update(acc);
            }


        }

How am I going to ensure that I update the correct records. I have the vat and reg numbers for the accounts in a spreadsheet, please see example image below. Can I please get advised here. Thanks.

回答1:

I would load the list of VAT updates from the spreadsheet into a dictionary and then load the 30k record from CRM into memory. Then I would match them up and use ExecuteMultipleRequest to do the updates. Alternatively, you could query CRM using the account numbers (if the list is small enough.) I made the assumption you had thousands of updates to do across the record set of 30k. Note, if the Account record size was very large and couldn't be loaded into memory you would need to do account number queries.

Here is the rough code for the basic solution (I haven't tested, method should be split up, and there is minimal error handling):

    public class VatInfo
{
    public string RegistrationNumber;
    public string TaxNumber;

    public static Dictionary<string, VatInfo> GetVatList()
    {
        //TODO: Implement logic to load CSV file into a list. Dictionary key value should be Account Number        
        throw new NotImplementedException();
    }
}


public class UpdateVatDemo
{
    public const int maxBatchSize = 100;

    public static void RunVatUpdate(IOrganizationService conn)
    {

        var vats = VatInfo.GetVatList();

        var pagingQuery = new QueryExpression("account");
        pagingQuery.ColumnSet = new ColumnSet("accountnumber");

        Queue<Entity> allEnts = new Queue<Entity>();

        while (true)
        {

            var results = conn.RetrieveMultiple(pagingQuery);

            if (results.Entities != null && results.Entities.Any())
                results.Entities.ToList().ForEach(allEnts.Enqueue);

            if (!results.MoreRecords) break;

            pagingQuery.PageInfo.PageNumber++;
            pagingQuery.PageInfo.PagingCookie = results.PagingCookie;

        }

        ExecuteMultipleRequest emr = null;

        while (allEnts.Any())
        {
            if (emr == null)
                emr = new ExecuteMultipleRequest()
                {
                    Settings = new ExecuteMultipleSettings()
                    {
                        ContinueOnError = true,
                        ReturnResponses = true
                    },
                    Requests = new OrganizationRequestCollection()
                };

            var ent = allEnts.Dequeue();

            if (vats.ContainsKey(ent.GetAttributeValue<string>("accountnumber")))
            {
                var newEnt = new Entity("account", ent.Id);
                newEnt.Attributes.Add("new_vatno", vats[ent.GetAttributeValue<string>("accountnumber")].TaxNumber);
                newEnt.Attributes.Add("new_registrationnumber", vats[ent.GetAttributeValue<string>("accountnumber")].RegistrationNumber);
                emr.Requests.Add(new UpdateRequest() { Target = newEnt });
            }


            if (emr.Requests.Count >= maxBatchSize)
            {
                try
                {
                    var emResponse = (ExecuteMultipleResponse) conn.Execute(emr);

                    foreach (
                        var responseItem in emResponse.Responses.Where(responseItem => responseItem.Fault != null))
                        DisplayFault(emr.Requests[responseItem.RequestIndex],
                            responseItem.RequestIndex, responseItem.Fault);

                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Exception during ExecuteMultiple: {ex.Message}");
                    throw;
                }

                emr = null;
            }


        }

    }

    private static void DisplayFault(OrganizationRequest organizationRequest, int count,
        OrganizationServiceFault organizationServiceFault)
    {
        Console.WriteLine(
            "A fault occurred when processing {1} request, at index {0} in the request collection with a fault message: {2}",
            count + 1,
            organizationRequest.RequestName,
            organizationServiceFault.Message);
    }

}

回答2:

Updating the fetched entity is bound to fail because of its entity state, which would not be null.

To update the fetched entities, you need to new up the entity:

foreach (var acc in response.Entities)
        {
            var updateAccount = new Entity("account") { Id = acc.Id };
            updateAccount .Attributes["new_vatno"] = null; //using null as an example.
            updateAccount .Attributes["new_registrationnumber"] = null;
            service.Update(acc);
        }

回答3:

Code below shows how I managed to get it righy. forst let me explain. I imported my records into a seperate SQL table, in my code I read that table into a list in memory, I then query CRM accounts that need to be updated, I then loop though each account and check if the account number in CRM matches the account number from my sql database, if it matches, I then update the relevant Reg no and Vat no, See code below:

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

            //var crmAccount = db.Sheet1_.Select(x => x).ToList().Take(2);
            var crmAccounts = db.Sheet1_.Select(x => x).ToList();


            foreach (var dbAccount in crmAccounts)
            {
                CRMDataObject modelObject = new CRMDataObject()
                {
                    ID = dbAccount.ID,
                    Account_No = dbAccount.Account_No,
                    Tax_No = dbAccount.Tax_No.ToString(),
                    Reg_No = dbAccount.Reg_No
                    //Tarsus_Country = dbAccount.Main_Phone
                };

            }

            var officialDatabaseList = crmAccounts;

            foreach (var crmAcc in officialDatabaseList)
            {
                QueryExpression qe = new QueryExpression();
                qe.EntityName = "account";
                qe.ColumnSet = new ColumnSet("accountnumber", "new_vatno", "new_registrationnumber");
                qe.Criteria.AddCondition("accountnumber", ConditionOperator.In,'list of account numbers go here'

  EntityCollection response = service.RetrieveMultiple(qe);

                foreach (var acc in response.Entities)
                {
                    if (crmAcc.Account_No == acc.Attributes["accountnumber"].ToString())
                    {
                        //acc.Attributes["new_vatno"] = crmAcc.VAT_No.ToString();
                        acc.Attributes["new_registrationnumber"] = crmAcc.Reg_No.ToString();

                        service.Update(acc);
                    }

                }
            }