Email Address Validation via ZeroBounce

We found this great SaaS service online to clean up email addresses https://www.zerobounce.net/ basically you can ask zerobounce if an email address is valid or not and then use that information to keep your database clean.

This is a simple HTTP Json type service where you send an email address and it will return a Json response with the result. Armed with this information it would be really easy to build an automated integration with Data Sync to continuously check the status of email addresses in your database.

For this example I created a simple SQL Table to hold some email addresses and the response from ZeroBounce. As your charged based on API Credits its a good idea to only call the API when you need to so in this example we store a last checked value and then only call the API again if 30 days have passed since the last check.

Note: You need to complete the sync to write the values to the SQL Table if you keep comparing then you will keep calling the API.

SQL Table

Here is a T-SQL definition for the SQL Table I used in this example.

CREATE TABLE [EmailValidation] (
	[ID] int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
	[EmailAddress] nvarchar(255) NOT NULL,
	[LastChecked] datetime NULL,
	[Status] nvarchar(100) NULL,
	[SubStatus] nvarchar(100) NULL,
	[FreeEmail] bit NULL
)

You then need to fill the EmailAddress column with email addresses. You could use a Data Sync project to import these from a CSV other SQL Table or other data source.

Data Sync Project

We now need to configure the Data Sync project as this project is to update the same data source i.e. the SQL table with information from ZeroBounce we need to map the SQL Table as both Source and Target. Then use Dynamic Columns in a Lookup type function to call the ZeroBounce API and get the result for each Email Address in the Table

To configure this Data Sync project

1. Load the SQL Table as both the Source and Target
2. Copy the Dynamic Columns Code add your ZeroBounce API key and check that it compiles (build button).
3. Map the columns as you see in the Screenshot below so that the data from ZeroBounce is mapped to the Target SQL Table
4. Compare and Sync

Dynamic Columns Code

partial class DataSourceRowOverride : Simego.DataSync.DynamicColumns.DataSourceRowInternal
{
    private const string API_KEY = "YOUR_API_KEY";
    private const string API_URL = "https://api.zerobounce.net/v2/validate?api_key={0}&email={1}&ip_address=";
	
    private HttpWebRequestHelper helper = new HttpWebRequestHelper();
	
    public DateTime? Fx_LastChecked { get; set; }
    public string Fx_Status { get; set; }
    public string Fx_SubStatus { get; set; }
    public bool? Fx_FreeEmail { get; set; }
			
    public override bool BeginRow()
    {
	    Fx_LastChecked = LastChecked;
	    Fx_Status = Status;
	    Fx_SubStatus = SubStatus;
	    Fx_FreeEmail = FreeEmail;
	
	    if(!Fx_LastChecked.HasValue || DateTime.Today.AddDays(-30) > Fx_LastChecked.Value)
	    {
		    Fx_LastChecked = DateTime.UtcNow;

		    try 
		    {			
			    var result = helper.GetRequestAsJson(API_URL, API_KEY, EmailAddress);
			
			    Fx_Status = DataSchemaTypeConverter.
				    ConvertTo<string>(result["status"].ToObject<object>());
				
			    Fx_SubStatus = DataSchemaTypeConverter.
				    ConvertTo<string>(result["sub_status"].ToObject<object>());
				
			    Fx_FreeEmail = DataSchemaTypeConverter.
				    ConvertTo<bool>(result["free_email"].ToObject<object>());				
			
		    } 
		    catch(Exception e)
		    {
			    Fx_Status = "Error";
			    Trace.TraceError(e.ToString());
		    }				
	    }
		
        return true;
    }    
}

Email Validation Results

After you run the sync you will then see the results of the email validation in the SQL Table. If you add more email addresses to the Table only those new email addresses added will be checked next time.