在 Dynamics CRM 中地理编码地址

robert_chang

5.00/5 (2投票s)

2016 年 12 月 26 日

CPOL

4分钟阅读

12237

250

了解如何连接 Dynamics CRM，自定义和查询客户地址实体，进行地址编码并更新 CRM 数据。

引言

在我上一篇关于“如何使用地理编码 Web 服务确定地址的国会选区”的文章中，代码可用于对 Dynamics CRM 中的地址进行地理编码。本文将展示如何连接 Dynamics CRM，自定义和查询客户地址实体，并使用控制台应用程序更新 CRM 数据。

背景

当前拥有系统管理员或系统自定义员权限的 Dynamics CRM 用户应该能够实现本文所述的更改并执行代码。不幸的是，如果您没有访问权限或无法获得足够的访问权限，我不知道有任何包含 Dynamics CRM 的虚拟实验室，并且 Microsoft 也不再提供 CRM Online 的免费试用。

CRM Online 目前提供地理编码功能，但不包含国会选区信息。

Dynamics CRM

默认情况下，Dynamics CRM 中的客户地址实体包含纬度和经度字段，因此如果您想为国会选区信息创建新字段，可以创建类似如下的字段：

国会选区名称（单行文本，最多 200 个字符）
国会选区编号（单行文本，最多 100 个字符）
国会届次（单行文本，最多 100 个字符）

CRM 发布者

对 Dynamics CRM 的自定义应包含在解决方案中，并且最好拥有一个合适的发布者。注意：本演示中使用的前缀是“xrmatic”。如果未使用国会选区字段，请确保将其从源代码中省略。

使用代码

本文中提到的所有源代码都已包含在可下载的材料中。代码已在 Dynamics CRM 2011、2013、2015、2016 和 CRM Online 上进行了测试。演示项目使用 Visual Studio 2015 SP3 构建和测试，但源代码在早期版本的 Visual Studio 和 .NET 4.5 中应该也能正常工作。

演示项目使用了以下 NuGet 包。注意：请注意您版本的 Dynamics CRM SDK 的 .NET Framework 依赖项。

需要在 Visual Studio 中添加对 CrmConnectors 和 GeocodeConsole 的引用。

源代码

Geocode.cs

添加了两个附加属性来存储国会选区编号和国会届次（例如 115）。

// Congressional District Number
public string CongressionalDistrictNumber { get; set; }
// Session of Congress
public int SessionOfCongress { get; set; }

Census.cs

如果将附加属性添加到 Geocode 类，则还应更新 GeocodeAddress() 方法。

public Geocode<Census> GeocodeAddress(string street, string city = "", string state = "", string zip = "")
{
    Census json = null;
    var geocode = new Geocodee<Census>();

    HttpResponseMessage response = geocode.GetResponse(this.apiUrl, this.GetUrlParameters(street, city, state, zip));
    using (response)
    {
        if (response.IsSuccessStatusCode)
        {
            var data = response.Content.ReadAsStringAsync().Result;
            json = JsonConvert.DeserializeObject<Census>(data);

            if (json != null && json.result.addressMatches.Length > 0)
            {
                geocode.Latitude = json.result.addressMatches[0].coordinates.y;
                geocode.Longitude = json.result.addressMatches[0].coordinates.x;
                geocode.CongressionalDistrictName = json.result.addressMatches[0].geographies.congressionalDistricts[0].NAME;
                        
                // Congressional District Number
                geocode.CongressionalDistrictNumber = json.result.addressMatches[0].geographies.congressionalDistricts[0].BASENAME;
                // Session of Congress
                geocode.SessionOfCongress = json.result.addressMatches[0].geographies.congressionalDistricts[0].CDSESSN;
            }
        }
        return geocode;
    }
}

Geocodio.cs

在 Geocodio 类中也需要对 GeocodeAddress() 方法进行类似的更改。

public Geocode<Geocodio> GeocodeAddress(string address)
{
    Geocodio json = null;
    var geocode = new Geocode<Geocodio>();

    HttpResponseMessage response = geocode.GetResponse(this.apiUrl, this.GetUrlParameters(address));

    using (response)
    {
        if (response.IsSuccessStatusCode)
        {
            var data = response.Content.ReadAsStringAsync().Result;

            json = JsonConvert.DeserializeObject<Geocodio>(data);
            if (json != null)
            {
                geocode.Latitude = json.results[0].location.lat;
                geocode.Longitude = json.results[0].location.lng;
                geocode.CongressionalDistrictName = json.results[0].fields.congressional_district.name;

                // Congressional District Number
                geocode.CongressionalDistrictNumber = json.results[0].fields.congressional_district.district_number;
                // Session of Congress
                geocode.SessionOfCongress = int.Parse(json.results[0].fields.congressional_district.congress_number);
            }
        }
        return geocode;
    }
}

LatLon.cs

在 LatLon 类中也需要对 GeocodeAddress() 方法进行类似的更改。

public Geocode<LatLon> GeocodeAddress(string address)
{
    LatLon json = null;
    var geocode = new Geocode<LatLon>();

    HttpResponseMessage response = geocode.GetResponse(this.apiUrl, this.GetUrlParameters(address));

    using (response)
    {
        if (response.IsSuccessStatusCode)
        {
            var data = response.Content.ReadAsStringAsync().Result;

            json = JsonConvert.DeserializeObject<LatLon>(data);
            if (json != null)
            {
                geocode.Latitude = json.lat;
                geocode.Longitude = json.lon;
                geocode.CongressionalDistrictName = json.congressional_district.name;

                // Congressional District Number
                geocode.CongressionalDistrictNumber = json.congressional_district.district_number.ToString();
                // Session of Congress
                geocode.SessionOfCongress = int.Parse(json.congressional_district.congress_number);
            }
        }
        return geocode;
    }
}

Program.cs

主程序使用 CRM SDK 提供的方法连接到 Dynamics CRM 实例。在本文提供的演示项目中，我使用了名为 CrmConnectors 的单独项目来处理 CRM 连接以检索 OrganizationService 对象。有关更多详细信息，请参阅 CrmConnectors 部分。

GetCrmAddresses() 方法返回一个包含要进行地理编码的 CRM 地址的 EntityCollection。EntityCollection 中的循环执行 GeocoodeAddress() 方法。最后，在 Dynamics CRM 中更新地址实体字段。

using GeocodeConsole;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using System;

namespace GeocodeCrmAddressesConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            // Connect to CRM
            var conn = new CrmConnectors.Crm.Connect();

            // Get CRM Organization Service (On-Premise)
            IOrganizationService service = conn.Service(CrmConnectors.Environment.OnPrem);

            // Get CRM Addresses
            EntityCollection addressEC = GetCrmAddresses(service);

            // Census Bureau
            var census = new Geocode<Census>();
            census.Service = new Census();

            foreach (Entity address in addressEC.Entities)
            {
                Console.WriteLine(address.GetAttributeValue<string>("line1") + " " +
                    address.GetAttributeValue<string>("line2") + " " +
                    address.GetAttributeValue<string>("city") + " " +
                    address.GetAttributeValue<string>("county") + " " +
                    address.GetAttributeValue<string>("stateorprovince") + " " +
                    address.GetAttributeValue<string>("postalcode") + " " +
                    address.GetAttributeValue<string>("country")
                    );

                // Geocode Address
                var geocodeCensus = census.Service.GeocodeAddress(
                    address.GetAttributeValue<string>("line1") + " " + address.GetAttributeValue<string>("line2"),
                    address.GetAttributeValue<string>("city"),
                    address.GetAttributeValue<string>("stateorprovince"),
                    address.GetAttributeValue<string>("postalcode")
                    );

                if (geocodeCensus != null)
                {
                    Console.WriteLine(geocodeCensus.Latitude + " : " +
                        geocodeCensus.Longitude + " = " +
                        geocodeCensus.CongressionalDistrictName
                        );

                    // Update address Entity fields
                    address["latitude"] = geocodeCensus.Latitude;
                    address["longitude"] = geocodeCensus.Longitude;
                    //--- Custom fields for Congressional District ---//
                    address["xrmatic_congressionaldistrictname"] = geocodeCensus.CongressionalDistrictName;
                    address["xrmatic_congressionaldistrictnumber"] = geocodeCensus.CongressionalDistrictNumber;
                    address["xrmatic_sessionofcongress"] = Int32.Parse(geocodeCensus.SessionOfCongress.ToString()).ToString();

                    // Update Customer Address (Late Bound)
                    service.Update(address);
                }
            }
        }

        internal static ColumnSet GetColumnSet()
        {
            return new ColumnSet(
                "line1",
                "line2",
                "city",
                "stateorprovince",
                "postalcode",
                "country"
                );
        }

        internal static EntityCollection GetCrmAddresses(IOrganizationService service)
        {
            QueryExpression addressQE = new QueryExpression();
            addressQE.EntityName = "customeraddress";
            // Get the list of Customer Address fields (only needed for Console output)
            addressQE.ColumnSet = GetColumnSet();

            FilterExpression addressFE = new FilterExpression
            {
                FilterOperator = LogicalOperator.And,
                Filters =
                    {
                        new FilterExpression {
                            FilterOperator = LogicalOperator.And,
                            Conditions = {
                                new ConditionExpression("line1", ConditionOperator.NotNull),
                                new ConditionExpression("city", ConditionOperator.NotNull),
                                new ConditionExpression("postalcode", ConditionOperator.NotNull),
                                new ConditionExpression("latitude", ConditionOperator.Null),
                                new ConditionExpression("longitude", ConditionOperator.Null)
                            }
                        },
                        new FilterExpression {
                            FilterOperator = LogicalOperator.Or,
                            Conditions = {
                                //--- Custom fields for Congressional District ---//
                                new ConditionExpression("xrmatic_congressionaldistrictname", ConditionOperator.Null),
                                new ConditionExpression("xrmatic_sessionofcongress", ConditionOperator.BeginsWith, "114"),
                                new ConditionExpression("country", ConditionOperator.Equal, "U.S."),
                                new ConditionExpression("country", ConditionOperator.Equal, "U.S.A."),
                                new ConditionExpression("country", ConditionOperator.Equal, "US"),
                                new ConditionExpression("country", ConditionOperator.Equal, "USA"),
                                new ConditionExpression("country", ConditionOperator.Equal, "United States"),
                                new ConditionExpression("country", ConditionOperator.Equal, "United States of America"),
                                new ConditionExpression("createdon", ConditionOperator.OlderThanXMonths, 3),
                                new ConditionExpression("modifiedon", ConditionOperator.OlderThanXMonths, 3)
                            }
                        }
                    }
            };

            addressQE.Criteria = addressFE;
            // Prevent record locking
            addressQE.NoLock = true;
            addressQE.Distinct = false;

            // Limit the number of results (Testing)
            addressQE.TopCount = 3;
            
            EntityCollection addressEC = service.RetrieveMultiple(addressQE);

            return addressEC;
        }
    }
}

或者，GetCrmAddresses() 方法中的 EntityCollection 也可以使用 FetchXml 字符串编写。FetchXml 字符串可以从 Dynamics CRM 中的高级查找搜索导出。

注意：在从 Dynamics CRM 复制粘贴导出的 FetchXml 版本之前，请确保将双引号替换为单引号。另外，不要忘记在根节点中添加“no-lock='true'”属性，并调整“count='3'”属性以在测试期间限制结果。

internal static EntityCollection GetCrmAddresses(IOrganizationService service)
{
    string fetchXml = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' no-lock='true' count='3'>
    <entity name='customeraddress'>
    <attribute name='line1' />
    <attribute name='city' />
    <attribute name='postalcode' />
    <attribute name='customeraddressid' />
    <attribute name='stateorprovince' />
    <attribute name='xrmatic_sessionofcongress' />
    <attribute name='modifiedon' />
    <attribute name='longitude' />
    <attribute name='latitude' />
    <attribute name='createdon' />
    <attribute name='county' />
    <attribute name='country' />
    <!--//--- Custom fields for Congressional District ---//-->
    <attribute name='xrmatic_congressionaldistrictnumber' />
    <attribute name='xrmatic_congressionaldistrictname' />
    <order attribute='stateorprovince' descending='false' />
    <order attribute='city' descending='false' />
    <filter type='and'>
    <filter type='and'>
    <filter type='and'>
    <condition attribute='line1' operator='not-null' />
    <condition attribute='city' operator='not-null' />
    <condition attribute='postalcode' operator='not-null' />
    <condition attribute='latitude' operator='null' />
    <condition attribute='longitude' operator='null' />
    </filter>
    <filter type='or'>
    <!--//--- Custom fields for Congressional District ---//-->
    <condition attribute='xrmatic_congressionaldistrictname' operator='null' />
    <condition attribute='xrmatic_sessionofcongress' operator='like' value='114%' />
    <condition attribute='country' operator='eq' value='U.S.' />
    <condition attribute='country' operator='eq' value='U.S.A.' />
    <condition attribute='country' operator='eq' value='US' />
    <condition attribute='country' operator='eq' value='USA' />
    <condition attribute='country' operator='eq' value='United States' />
    <condition attribute='country' operator='eq' value='United States of America' />
    <condition attribute='createdon' operator='olderthan-x-months' value='3' />
    <condition attribute='modifiedon' operator='olderthan-x-months' value='3' />
    </filter>
    </filter>
    </filter>
    </entity>
    </fetch>";

    EntityCollection addressEC = service.RetrieveMultiple(new FetchExpression(fetchXml));

    return addressEC;
}

GetCrmAddresses() 方法中的 QueryExpression 也可以这样写：

internal static EntityCollection GetCrmAddresses(IOrganizationService service)
{
    QueryExpression addressQE = new QueryExpression();

    addressQE.EntityName = "customeraddress";
    // Get the list of Customer Address fields (only needed for Console output)
    addressQE.ColumnSet = GetColumnSet();

    FilterExpression addressFE = new FilterExpression();
    addressFE.FilterOperator = LogicalOperator.And;

    // Child FilterExpression 1
    FilterExpression addressFE1 = new FilterExpression();
    addressFE1.FilterOperator = LogicalOperator.And;

    // line1
    ConditionExpression addressCE1 = new ConditionExpression();
    addressCE1.AttributeName = "line1";
    addressCE1.Operator = ConditionOperator.NotNull;
    addressFE1.Conditions.Add(addressCE1);

    // city
    ConditionExpression addressCE2 = new ConditionExpression();
    addressCE2.AttributeName = "city";
    addressCE2.Operator = ConditionOperator.NotNull;
    addressFE1.Conditions.Add(addressCE2);

    // postalcode
    ConditionExpression addressCE3 = new ConditionExpression();
    addressCE3.AttributeName = "postalcode";
    addressCE3.Operator = ConditionOperator.NotNull;
    addressFE1.Conditions.Add(addressCE3);

    // latitude
    ConditionExpression addressCE4 = new ConditionExpression();
    addressCE4.AttributeName = "latitude";
    addressCE4.Operator = ConditionOperator.Null;
    addressFE1.Conditions.Add(addressCE1);

    // longitude
    ConditionExpression addressCE5 = new ConditionExpression();
    addressCE5.AttributeName = "longitude";
    addressCE5.Operator = ConditionOperator.Null;
    addressFE1.Conditions.Add(addressCE5);

    // Child FilterExpression 2
    FilterExpression addressFE2 = new FilterExpression();
    addressFE2.FilterOperator = LogicalOperator.Or;

    // xrmatic_congressionaldistrictname -- Custom Field 
    ConditionExpression addressCE6 = new ConditionExpression();
    addressCE6.AttributeName = "xrmatic_congressionaldistrictname";
    addressCE6.Operator = ConditionOperator.Null;
    addressFE2.Conditions.Add(addressCE6);

    // xrmatic_sessionofcongress -- Custom Field
    ConditionExpression addressCE7 = new ConditionExpression();
    addressCE7.AttributeName = "xrmatic_sessionofcongress";
    addressCE7.Operator = ConditionOperator.BeginsWith;
    addressCE7.Values.Add("114");
    addressFE2.Conditions.Add(addressCE7);

    // country
    ConditionExpression addressCE8 = new ConditionExpression();
    addressCE8.AttributeName = "country";
    addressCE8.Operator = ConditionOperator.Equal;
    addressCE8.Values.Add("U.S.");
    addressFE2.Conditions.Add(addressCE8);

    // country
    ConditionExpression addressCE9 = new ConditionExpression();
    addressCE9.AttributeName = "country";
    addressCE9.Operator = ConditionOperator.Equal;
    addressCE9.Values.Add("U.S.A.");
    addressFE2.Conditions.Add(addressCE9);

    // country
    ConditionExpression addressCE10 = new ConditionExpression();
    addressCE10.AttributeName = "country";
    addressCE10.Operator = ConditionOperator.Equal;
    addressCE10.Values.Add("US");
    addressFE2.Conditions.Add(addressCE10);

    // country
    ConditionExpression addressCE11 = new ConditionExpression();
    addressCE11.AttributeName = "country";
    addressCE11.Operator = ConditionOperator.Equal;
    addressCE11.Values.Add("USA");
    addressFE2.Conditions.Add(addressCE11);

    // country
    ConditionExpression addressCE12 = new ConditionExpression();
    addressCE12.AttributeName = "country";
    addressCE12.Operator = ConditionOperator.Equal;
    addressCE12.Values.Add("United States");
    addressFE2.Conditions.Add(addressCE12);

    // country
    ConditionExpression addressCE13 = new ConditionExpression();
    addressCE13.AttributeName = "country";
    addressCE13.Operator = ConditionOperator.Equal;
    addressCE13.Values.Add("United States of America");
    addressFE2.Conditions.Add(addressCE13);

    // createdon
    ConditionExpression addressCE14 = new ConditionExpression();
    addressCE14.AttributeName = "createdon";
    addressCE14.Operator = ConditionOperator.OlderThanXMonths;
    addressCE14.Values.Add(3);
    addressFE2.Conditions.Add(addressCE14);

    // modifiedon
    ConditionExpression addressCE15 = new ConditionExpression();
    addressCE15.AttributeName = "modifiedon";
    addressCE15.Operator = ConditionOperator.OlderThanXMonths;
    addressCE15.Values.Add(3);
    addressFE2.Conditions.Add(addressCE15);

    // Add child filters
    addressFE.AddFilter(addressFE1);
    addressFE.AddFilter(addressFE2);

    addressQE.Criteria = addressFE;
    // Prevent record locking
    addressQE.NoLock = true;
    addressQE.Distinct = false;

    EntityCollection addressEC = service.RetrieveMultiple(addressQE);

    return addressEC;
}

CrmConnectors

MSDN 上有关于如何连接到 Dynamics CRM 的详细说明，MSDN、大量博客和演练。本文中的 CrmConnectors 命名空间中的类仅用于演示目的。肯定有更好的方法来保护服务帐户凭据，但此项目可以帮助您入门。

Config.cs

Config 类包含每个 CRM 实例的连接字符串。为增加安全性，可以将凭据存储在 app.config 中，而不是硬编码在 ConnectionString() 方法中。

namespace CrmConnectors
{
    public enum Environment
    {
        CrmOnline,
        OnPrem
    }

    public class Config
    {
        public string ConnectionString(Environment environment)
        {
            string connectionString = "";

            // CRM Connection String - CRM Online
            if (environment == Environment.CrmOnline)
                connectionString = @"Url=https://XXXXXX.crm.dynamics.com; Username=XXXXXX@XXXXXX.onmicrosoft.com; Password=XXXXXX;";
                
            // CRM Connection String - On-Premise CRM
            if (environment == Environment.OnPrem)
                connectionString = @"Url=https://crm/XXXXXX/XRMServices/2011/Organization.svc; Domain=XXXXXX; Username=XXXXXX; Password=XXXXXX; authtype=AD";
            
            return connectionString;
        }
    }
}

Connect.cs

using Microsoft.Xrm.Client;
using Microsoft.Xrm.Client.Services;
using Microsoft.Xrm.Sdk;

namespace CrmConnectors.Crm
{
    public class Connect
    {

        public IOrganizationService Service(Environment environment)
        {
            // Get Connection String
            var config = new Config();
            string connectionString = config.ConnectionString(environment);

            return Service(connectionString);
        }

        public IOrganizationService Service(string connectionString)
        {
            // Connect to CRM
            var conn = CrmConnection.Parse(connectionString);

            IOrganizationService service = new OrganizationService(conn);

            return service;
        }
    }
}

接下来呢？

在下一篇关于此主题的文章中，最后一步是在 Dynamics CRM 中创建自定义工作流活动，以对新地址和更新地址进行地理编码。

历史

2016年11月26日：初稿
2016年12月26日：更新了源代码和演示项目