Introduction
In my previous article on How to Use Geocoding Web Services to Determine the Congressional District for an Address, the code can be used to geocode addresses in Dynamics CRM. This article will show you how to connect to Dynamics CRM, customize and query the Customer Address entity, and update CRM data with a console application.
Background
Current Dynamics CRM users with System Administrator or System Customizer privileges should be able to implement the changes and execute the code described in this article. Unfortunately, if you do not have access or cannot be granted sufficient access, I do not know of any virtual labs with Dynamics CRM and Microsoft is no longer offering free trials of CRM Online.
CRM Online currently offers a Geocode feature but it does not include the Congressional District information.
Dynamics CRM
By default, the Customer Address entity in Dynamics CRM contain the fields for Latitude and Longitude so if you want to create new fields for Congressional District information, you can created fields that resemble these:
- Congressional District Name (Single Line of Text, 200 maximum length)
- Congressional District Number (Single Line of Text, 100 maximum length)
- Session of Congress (Single Line of Text, 100 maximum length)
CRM Publisher
Customizations to Dynamics CRM should be contained in a Solution and preferably with an appropriate Publisher. Note: the Prefix used in this demo is "xrmatic". If the Congressional District fields are not used, be sure to omit them from the source code.
Using the code
All of the source code mentioned in the article has been included in the downloadable materials. The code has been tested on Dynamics CRM 2011, 2013, 2015, 2016 and CRM Online. The demo project was built and tested using Visual Studio 2015 SP3 but there is no reason why the source code should not work in earlier versions of Visual Studio and .NET 4.5.
The following NuGet packages were used in the demo project. Note: Please be aware of the .NET Framework dependencies for your version of Dynamics CRM SDK.
References to the CrmConnectors and GeocodeConsole need to be added in Visual Studio.
Source Code
Geocode.cs
Two additional properties were added to store the Congressional District Number and the Session of Congress (e.g. 115).
public string CongressionalDistrictNumber { get; set; }
public int SessionOfCongress { get; set; }
Census.cs
If the additional properties are added to the Geocode class, the GeocodeAddress() method should to be updated as well.
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;
geocode.CongressionalDistrictNumber = json.result.addressMatches[0].geographies.congressionalDistricts[0].BASENAME;
geocode.SessionOfCongress = json.result.addressMatches[0].geographies.congressionalDistricts[0].CDSESSN;
}
}
return geocode;
}
}
Geocodio.cs
Similar changes to the GeocodeAddress() method need to be made in the Geocodio class.
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;
geocode.CongressionalDistrictNumber = json.results[0].fields.congressional_district.district_number;
geocode.SessionOfCongress = int.Parse(json.results[0].fields.congressional_district.congress_number);
}
}
return geocode;
}
}
LatLon.cs
Similar changes to the GeocodeAddress() method need to be made in the LatLon class.
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;
geocode.CongressionalDistrictNumber = json.congressional_district.district_number.ToString();
geocode.SessionOfCongress = int.Parse(json.congressional_district.congress_number);
}
}
return geocode;
}
}
Program.cs
The main program connects to the Dynamics CRM instance with methods provided by the CRM SDK. In the demo project provided for this article, I used a separate project named CrmConnectors to handle CRM connections to retrieve the OrganizationService object. See the section on CrmConnectors for more details.
The GetCrmAddresses() method to returns an EntityCollection containing CRM Addresses to geocode. The loop through the EntityCollection executes the GeocoodeAddress() method. Finally, the address Entity fields are updated in Dynamics CRM.
using GeocodeConsole;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using System;
namespace GeocodeCrmAddressesConsole
{
class Program
{
static void Main(string[] args)
{
var conn = new CrmConnectors.Crm.Connect();
IOrganizationService service = conn.Service(CrmConnectors.Environment.OnPrem);
EntityCollection addressEC = GetCrmAddresses(service);
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")
);
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
);
address["latitude"] = geocodeCensus.Latitude;
address["longitude"] = geocodeCensus.Longitude;
address["xrmatic_congressionaldistrictname"] = geocodeCensus.CongressionalDistrictName;
address["xrmatic_congressionaldistrictnumber"] = geocodeCensus.CongressionalDistrictNumber;
address["xrmatic_sessionofcongress"] = Int32.Parse(geocodeCensus.SessionOfCongress.ToString()).ToString();
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";
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 = {
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;
addressQE.NoLock = true;
addressQE.Distinct = false;
addressQE.TopCount = 3;
EntityCollection addressEC = service.RetrieveMultiple(addressQE);
return addressEC;
}
}
}
Alternatively, the EntityCollection in the GetCrmAddresses() method can be written with a FetchXml string. The FetchXml string can be exported from an Advanced Find search in Dynamics CRM.
Note: Before you copy and paste the exported version of FetchXml from Dynamics CRM, be sure to find/replace the double quotes with single quotes. Also, don't forget to add the "no-lock='true'" attribute to the root <fetch> node and adjust the "count='3'" attribute to limit the results during testing.
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;
}
The QueryExpression in the GetCrmAddresses() method can also be written as such:
internal static EntityCollection GetCrmAddresses(IOrganizationService service)
{
QueryExpression addressQE = new QueryExpression();
addressQE.EntityName = "customeraddress";
addressQE.ColumnSet = GetColumnSet();
FilterExpression addressFE = new FilterExpression();
addressFE.FilterOperator = LogicalOperator.And;
FilterExpression addressFE1 = new FilterExpression();
addressFE1.FilterOperator = LogicalOperator.And;
ConditionExpression addressCE1 = new ConditionExpression();
addressCE1.AttributeName = "line1";
addressCE1.Operator = ConditionOperator.NotNull;
addressFE1.Conditions.Add(addressCE1);
ConditionExpression addressCE2 = new ConditionExpression();
addressCE2.AttributeName = "city";
addressCE2.Operator = ConditionOperator.NotNull;
addressFE1.Conditions.Add(addressCE2);
ConditionExpression addressCE3 = new ConditionExpression();
addressCE3.AttributeName = "postalcode";
addressCE3.Operator = ConditionOperator.NotNull;
addressFE1.Conditions.Add(addressCE3);
ConditionExpression addressCE4 = new ConditionExpression();
addressCE4.AttributeName = "latitude";
addressCE4.Operator = ConditionOperator.Null;
addressFE1.Conditions.Add(addressCE1);
ConditionExpression addressCE5 = new ConditionExpression();
addressCE5.AttributeName = "longitude";
addressCE5.Operator = ConditionOperator.Null;
addressFE1.Conditions.Add(addressCE5);
FilterExpression addressFE2 = new FilterExpression();
addressFE2.FilterOperator = LogicalOperator.Or;
ConditionExpression addressCE6 = new ConditionExpression();
addressCE6.AttributeName = "xrmatic_congressionaldistrictname";
addressCE6.Operator = ConditionOperator.Null;
addressFE2.Conditions.Add(addressCE6);
ConditionExpression addressCE7 = new ConditionExpression();
addressCE7.AttributeName = "xrmatic_sessionofcongress";
addressCE7.Operator = ConditionOperator.BeginsWith;
addressCE7.Values.Add("114");
addressFE2.Conditions.Add(addressCE7);
ConditionExpression addressCE8 = new ConditionExpression();
addressCE8.AttributeName = "country";
addressCE8.Operator = ConditionOperator.Equal;
addressCE8.Values.Add("U.S.");
addressFE2.Conditions.Add(addressCE8);
ConditionExpression addressCE9 = new ConditionExpression();
addressCE9.AttributeName = "country";
addressCE9.Operator = ConditionOperator.Equal;
addressCE9.Values.Add("U.S.A.");
addressFE2.Conditions.Add(addressCE9);
ConditionExpression addressCE10 = new ConditionExpression();
addressCE10.AttributeName = "country";
addressCE10.Operator = ConditionOperator.Equal;
addressCE10.Values.Add("US");
addressFE2.Conditions.Add(addressCE10);
ConditionExpression addressCE11 = new ConditionExpression();
addressCE11.AttributeName = "country";
addressCE11.Operator = ConditionOperator.Equal;
addressCE11.Values.Add("USA");
addressFE2.Conditions.Add(addressCE11);
ConditionExpression addressCE12 = new ConditionExpression();
addressCE12.AttributeName = "country";
addressCE12.Operator = ConditionOperator.Equal;
addressCE12.Values.Add("United States");
addressFE2.Conditions.Add(addressCE12);
ConditionExpression addressCE13 = new ConditionExpression();
addressCE13.AttributeName = "country";
addressCE13.Operator = ConditionOperator.Equal;
addressCE13.Values.Add("United States of America");
addressFE2.Conditions.Add(addressCE13);
ConditionExpression addressCE14 = new ConditionExpression();
addressCE14.AttributeName = "createdon";
addressCE14.Operator = ConditionOperator.OlderThanXMonths;
addressCE14.Values.Add(3);
addressFE2.Conditions.Add(addressCE14);
ConditionExpression addressCE15 = new ConditionExpression();
addressCE15.AttributeName = "modifiedon";
addressCE15.Operator = ConditionOperator.OlderThanXMonths;
addressCE15.Values.Add(3);
addressFE2.Conditions.Add(addressCE15);
addressFE.AddFilter(addressFE1);
addressFE.AddFilter(addressFE2);
addressQE.Criteria = addressFE;
addressQE.NoLock = true;
addressQE.Distinct = false;
EntityCollection addressEC = service.RetrieveMultiple(addressQE);
return addressEC;
}
CrmConnectors
There are detailed instructions on MSDN, numerous blogs and walk throughs on how to connect to Dynamics CRM. For this article, the classes defined in the CrmConnectors namespace are for demonstration purposes only. There are certainly better methods to secure the service account credentials but this project can get you started.
Config.cs
The Config class contains the connection strings for each CRM instance. For added security, the credentials could be stored in app.config instead of being hard-coded in the ConnectionString() method.
namespace CrmConnectors
{
public enum Environment
{
CrmOnline,
OnPrem
}
public class Config
{
public string ConnectionString(Environment environment)
{
string connectionString = "";
if (environment == Environment.CrmOnline)
connectionString = @"Url=https://XXXXXX.crm.dynamics.com; Username=XXXXXX@XXXXXX.onmicrosoft.com; Password=XXXXXX;";
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)
{
var config = new Config();
string connectionString = config.ConnectionString(environment);
return Service(connectionString);
}
public IOrganizationService Service(string connectionString)
{
var conn = CrmConnection.Parse(connectionString);
IOrganizationService service = new OrganizationService(conn);
return service;
}
}
}
Now What?
In the next article on this topic, the final step is to create a Custom Workflow Activity in Dynamics CRM to geocode new and updated addresses.
History
- 26-Nov-2016: First draft
- 26-Dec-2016: Updated Source and Demo Project
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
Submit an article or tip
