The Applications Engineering team at Service Objects prides itself on being integration experts. We are here to assist our clients and prospects in getting the most out of our services. Whether it is dealing with the actual coding, integration, and call to our web services or if it is intelligently dealing with the response from the web service, we’re here to help. Part of that means putting out guides and helpful information for our clients to highlight typical uses cases.

Validate records in a database

A common scenario that our clients run into is the need to clean, validate or scrub a list of records that may be in a database. From this, arises another question of what to do with the validated data afterward. There are a lot of different technology stacks that can be used to do the implementation but in this example, we’ll use SQL Server, C# and the .NET framework.

Connecting and pulling records from a database

There are a few ways to retrieve records from a database and put them back into C#, but arguably one of the easiest ways, in C# and the .NET framework, is through the Entity framework. Adding an entity connection is super simple! The entity framework is a source object relational mapper that allows developers to work with a database while using .NET objects. The steps can vary slightly depending on the type of database you are connecting to. In this example, we’re connecting to a Microsoft SQL Server database, so the connection details and processing may be a bit different if you use another database.

For this example, we’ll create a simple console app. After the project has successfully created, right-click the project, Select Add, then New Item and then select the ADO.NET Entity Data Model item to add the entity connection. Input your specific connection details and add the connection.

Screenshot 1

The database that we’re connecting to in this example has two tables in it. The first holds the input addresses that we will be pulling out to validate against the DOTS Address Validation – US web service. The image on the left (screenshot 2) is the design of our DB and the image on the right (screenshot3) are two address examples that we’ll be validating. One is our office address, and the other is clearly a bad address that will not return validated address information from the service.

 

   
Screenshot 2 and 3

We also have a table that will hold some of the validated output information from the validation service. For this example, we’re just using a few fields that are returned: the validated address information that is returned from the service, (Address1, Address2, City, State, Zip) DPV, Corrections, and the error description from the service.

Below is a screenshot of the table design for the output table. But you can use any other fields described in the developer guides.

Screenshot 4

Now that the entity connection is set up, we can add the code that will actually retrieve the records from the DB, process them through our Address Validation – US service.

Adding code to retrieve records from the database

To start this, we’ll add the service reference to the project so we can easily call the Address Validation – US service and validate our addresses.

To do this, right click the project, select Add, then “Service Reference…”. On the dialogue box that appears enter in the WSDL for the Address Validation – US service and add an appropriate name into the Namespace field. Then select OK.

Screenshot 5

Instantiating three objects

We’ll instantiate three objects: the entity connection we’ll use to connect to the database, a list of addresses we’ll pull back from the database, and a list of objects that we will use to update the database with the validated address information. After this, we will use the entity connection to fill our list of unvalidated addresses that we’ll pull back from the database. The objects we’re creating are based on the table structure from our entity connection.

Screenshot 6

Screenshot 7

Now that we have the unvalidated addresses, we’ll want to process them through the Address Validation – US service and update the database.

Below is the method we will add and call.

Screenshot 8

As seen in the screenshot above, we use the Address Validation – US service reference that we’ve made to validate the input addresses. We do this by looping through the list of tblAddress objects, calling the Address Validation – US service, and then adding an tblValidatedAddress objects to the table that will hold the validated data with the entity connection.

Parallel processing

In our example, we’re only processing two addresses, but the code still uses a Parallel.ForEach statement, as typical use cases may want to process large volumes of addresses in a shorter time. The Parallel.ForEach statements in C# can have a max degree of parallelism set to ensure that the processing power of the machine isn’t exceeded. Use the max degree of parallelism option to best suit the needs of your specific application.

An important thing to note is that the ID for the input addresses is assigned as the ID for the objects being put into the tblValidatedAddresses table so that the non-validated address record and the validated address record can be linked by ID. Your system may vary in how you want to link your address records so modify it accordingly.

Below is a screenshot of the validated data in our tblValidatedAddresses table

Screenshot 9

Results

Note that for record #1, the fields have the validated address information, as well as provided the DPV_Out value and Corrections_Out notes for the input address. For record #2, all the fields are blank except for the Error_Out field, which indicates that the input address was not able to be validated.

Hopefully, this tutorial helped provide an idea of how to retrieve records, process them through a validation service and then update a database with the validated records. We’re always happy to help provide any assistance or recommendations on your specific use case so please reach out to us and we’ll help you get the best out of your Service Objects service.