CRM Dynamics 2013 SDK使用2个值更新当前帐户

Question

我在CRM中有一个场景，需要使用增值税和注册号更新现有帐户。系统中有超过3万个账号。我正在尝试使用CRM SDK API进行更新，但我正在努力弄清楚如何执行实际的更新。增值税编号和注册表已在电子表格中提供给我，并提供相应的编号，请注意，这些帐户已在CRM中，因此我只需使用其增值税和注册编号更新正确的帐户，我如何在CRM中执行此操作，请对我的代码提出以下建议：

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);
            }


        }

我如何确保更新正确的记录。我有一个电子表格中的帐户增值税和注册号，请参阅下面的示例图像。我能在这里得到建议吗。谢谢。

​

​

Answer 1

我会将增值税更新列表从电子表格加载到字典中，然后将CRM中的30k记录加载到内存中。然后我会将它们进行匹配，并使用ExecuteMultipleRequest进行更新。或者，您可以使用帐号查询CRM (如果列表足够小)。我假设你在30k的记录集中有数千次更新。请注意，如果帐户记录大小非常大，并且无法加载到内存中，则需要进行帐号查询。

以下是基本解决方案的粗略代码(我还没有测试过，方法应该被拆分，并且有最小的错误处理)：

    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);
    }

}

Answer 2

更新获取的实体必然会失败，因为它的实体状态不会为空。

要更新获取的实体，您需要新建实体：

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);
        }

Answer 3

下面的代码显示了我是如何做到正确的。福斯特，让我解释一下。我将我的记录导入到一个单独的SQL表中，在我的代码中，我将该表读取到内存中的列表中，然后查询需要更新的CRM帐户，然后循环每个帐户，检查CRM中的帐号是否与sql数据库中的帐号匹配，如果匹配，则更新相关的注册号和增值税号，请参见以下代码：

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);
                    }

                }
            }