Setup an Address Verification System in Dynamic Columns via a Web Service

This is an example showing how you can use a 3rd party system with Data Sync to verify or clean up your addresses in your CRM system or Database.

The principal idea is go through each address and pass the details to the 3rd party and then mark the record Valid or Invalid and set a Date value for the test to prevent too many API calls. If the service you use returns corrected address data you could then use this to update the address.

I am using a basic address validator service from http://www.address-validator.net/index.html, this will just return whether the address is valid or not.

I will use a simple SQL Table for this example however it would be easy to adapt to Dynamics CRM or SharePoint.

CREATE TABLE [dbo].[Addresses] (
    [ID] int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
    [UUID] uniqueidentifier DEFAULT(newid()) NOT NULL,
    [FirstName] nvarchar(255) NULL,
    [LastName] nvarchar(255) NULL,
    [Company] nvarchar(255) NULL,
    [Address] nvarchar(255) NULL,
    [City] nvarchar(255) NULL,
    [County] nvarchar(255) NULL,
    [State] nvarchar(255) NULL,
    [ZIP] nvarchar(255) NULL,
    [Country] nvarchar(255) NULL,
    [Phone] nvarchar(255) NULL,
    [Fax] nvarchar(255) NULL,
    [Email] nvarchar(255) NULL,
    [Web] nvarchar(255) NULL,
    [DateLastChecked] datetime NULL,
    [Valid] bit DEFAULT(0) NOT NULL
)

There are 2 fields in here that are going to control the process DateLastChecked and Valid, so the DateLastChecked will be updated with the Date we ran the check and Valid will be updated whether the address passed the validation.

First Step is to connect the Source and Target in Data Sync to the same Table as we want to update the Table based on the result of the Web Service.

Project

Next we want to drop into the code editor to write the code to call the service and update the values on the source. Go to View->Dynamic Columns and Enable Dynamic Columns.

First we write the code to provide us with the Dynamic Columns to map to the Target and a place to call the Web Service. We want to create virtual columns that we update with results from the Web Service. So the code below creates virtual/dynamic columns for the Address fields and calls them My* and we also make a check to ensure we only check addresses that are 90+ days old.

#region Usings
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using System.Linq;

using Simego.DataSync;
#endregion

partial class DataSourceRowOverride : Simego.DataSync.DynamicColumns.DataSourceRowInternal //Do Not Change This Line
{    
    public DateTime MyDateLastChecked { get; set; }
    public bool MyValid { get; set; }
        
    public string MyAddress { get; set; }
    public string MyCity { get; set; }
    public string MyCounty { get; set; }
    public string MyState { get; set; }
    public string MyZip { get; set; }
        
    public override bool BeginRow()
    {
        MyAddress = Address ?? string.Empty;
        MyCity = City ?? string.Empty;
        MyCounty = County ?? string.Empty;
        MyState = State ?? string.Empty;
        MyZip = ZIP;
        
        MyDateLastChecked = DateLastChecked ?? DateTime.Parse("2014-01-01");
             
        MyValid = Valid;
        
        //Check Addresses that are 90 Days old.
        if(MyDateLastChecked < DateTime.Today.AddDays(-90))
        {        
            //Check Address here
            
            MyDateLastChecked = DateTime.Today;
        }
        
        return true; // return false to skip row from results.
    }
}

Next we re-configure the Schema Map to map these My* columns to the Target.

Schema Map

Before we write the rest of the code we need to ensure that the .NET 4.0 System.Web.dll assembly is loaded into Data Sync, to do this go to File->Properties and add an Assembly reference for C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Web.dll (this path maybe different on your machine).

Project Properties

Now we configure the rest of the code to call the Web Service and set the Valid result.

#region Usings
using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using System.Linq;

using Simego.DataSync;
#endregion

using System.Web;
using System.Net;
using System.IO;

partial class DataSourceRowOverride : Simego.DataSync.DynamicColumns.DataSourceRowInternal //Do Not Change This Line
{
    const string APIURL = "http://api.address-validator.net/api/verify";
    const string APIKEY = "my-api-key";
    
    public DateTime MyDateLastChecked { get; set; }
    public bool MyValid { get; set; }
        
    public string MyAddress { get; set; }
    public string MyCity { get; set; }
    public string MyCounty { get; set; }
    public string MyState { get; set; }
    public string MyZip { get; set; }
        
    public override bool BeginRow()
    {
        MyAddress = Address ?? string.Empty;
        MyCity = City ?? string.Empty;
        MyCounty = County ?? string.Empty;
        MyState = State ?? string.Empty;
        MyZip = ZIP;
        
        MyDateLastChecked = DateLastChecked ?? DateTime.Parse("2014-01-01");
             
        MyValid = Valid;
        
        //Check Addresses that are 90 Days old.
        if(MyDateLastChecked < DateTime.Today.AddDays(-90))
        {        
            var postData = string.Format("StreetAddress={0}&amp;AdditionalAddressInfo={1}&amp;City={2}&amp;PostalCode={3}&amp;State={4}&amp;CountryCode={5}&amp;Locale=en&amp;APIKey={6}",                
                HttpUtility.UrlEncode(MyAddress),
                HttpUtility.UrlEncode(""),
                HttpUtility.UrlEncode(MyCity),
                HttpUtility.UrlEncode(MyZip),
                HttpUtility.UrlEncode(MyState),
                HttpUtility.UrlEncode(Country),
                APIKEY);
                        
            var postBytes = System.Text.Encoding.UTF8.GetBytes(postData);
            
            //Do the Address Lookup here...                    
            HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(APIURL);
            
            request.Method = "POST";
            request.ContentLength = postBytes.Length;
            request.ContentType = "application/x-www-form-urlencoded; charset=UTF-8";
            
            using(var requestStream = request.GetRequestStream())
            {
                requestStream.Write(postBytes, 0, postBytes.Length);
            }
            
            using(var response = request.GetResponse())
            {
                if(response != null)
                {
                    using(var responseStream = new StreamReader(response.GetResponseStream()))
                    {
                        var resultString = responseStream.ReadToEnd();
                        Trace.WriteLine(resultString);
                        
                        dynamic results = Json.Decode(resultString);
                        
                        MyValid = results["status"] == "VALID";
                    }
                }                
            }
            
            MyDateLastChecked = DateTime.Today;
        }
        
        return true; // return false to skip row from results.
    }    
}

That's all there is to it, if your service returns an updated address you can then use this result with the My* virtual/dynamic columns to update the Database with the corrected address details.

Compare

The same concept can be used to clean-up, fix or decorate your data with data from other systems via Web Service calls or other similar systems.