Member preview

Integrate Legacy System using SQL Dependency and Webhook

Integrating a legacy system can be a difficult task, especially when the system does not have a API available. While it is possible to integrate by polling the batch file process which most Legacy systems have, the polling batch file pattern is inherently error prone and not real time.

This article will describe a option that integrates a legacy system without an API, using SQL dependency and Webhook.

Scenario

To demonstrate a typical use case, we have a legacy system that captures user inquiry, the sales department wants to receive the new inquiry in realtime or near realtime. The legacy system does not have an API, and it uses a SQL database.

Solution

As illustrated by the diagram below, when a new inquiry is entered, a new data row will be inserted into the inquiry table. The Data observer service runs in the background, it uses the SQL Dependency to monitor and capture data change, and raise a event when data is added to the table.

Upon receiving the new inquiry event, the data observer will trigger the MVC Webhook controller with the inquiry data as parameter. The Webhook controller locates the registered Webhook event, then triggers the corresponding handler to send the inquiry to the sales department by posting a message to servicebus/auzre function/SMS or any other channel that fits the needs.

SQL Dependency

SQL dependency can detect the data change without the need to continuously query the database using timers or loops.
It is associated with a SqlCommand in order to detect when data change occurs, it can also be assigned with a delegate to the OnChange event, 
which will fire when the results change for the associated command.

To create a SQL dependency connection:

SqlDependency.Start(connectionString, queueName);

To Create and bind the SqlDependency object to the command object:

dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(DependencyOnChange);
// Execute the command.
using (SqlDataReader reader = command.ExecuteReader())
{
// Process the DataReader.
}

To terminate the SQL dependency:

SqlDependency.Stop(connectionString, queueName);

Create a Data Observer

The Data Observer is implemented as a window service, so it can run in the background.

Firstly we wrap up the SQL Dependency into a ImmediateNotification class as below:

Then we instantiate a ImmediateNotificationRegister with onChange event in the Data Observer window service:

iquery = from p in db.Inquiries 
where p.CreateDate >= lastUpdateDate
select p;
// use ImmediateNotificationRegister to register SqlDependency.
notification = new ImmediateNotificationRegister<Inquriy>(db, iquery);
notification.OnChanged += NotificationOnChanged;

In NotificationOnChange Event handler, the data captured can be posted to Webhook controller:

POST /api/webhooks/incoming/custom

Create a Webhook

Webhooks are an architectural pattern that enables developers to receive updates to data as they happen rather than polling for the latest updates. When an event happens, a notification is sent in the form of an HTTP POST request to registered subscribers. The POST request contains information about the event which makes it possible for the receiver to act accordingly.

Here we use Asp .Net Webhooks, as it provides a pluggable model for persisting WebHook registrations and out-of-box support for storing WebHook registrations in Azure Table Storage.

To use Asp .net Webhook, install the nuget packages first. The Nuget packages are named Microsoft.AspNet.WebHooks.Receivers.*, here we install the Microsoft.AspNet.WebHooks.Receivers.Custom which provides support for receiving WebHooks generated by ASP.NET WebHooks.

Next step is to create event filters:

Then we add a MVC Controller called NotifyController with a SubmitNewInquiry action that generates an event using the NotifyAsync method.

Add a Webhook handler to process incoming webhook request:

Don’t forget to configure the receiver with the following Application Setting added to Web.Config.

<appSettings>
<add key=”MS_WebHookReceiverSecret_Custom” value=”83699ec7c1d794c0c780e49a5c72972590571fd8" />
</appSettings>

All done! We should have two components ready,

  1. Data observer running as window service listening to database change.
  2. Asp .Net MVC Webhook controller that is ready to process the request from the data Observer.

Testing

The easiest way to test is to insert a row of data into the database table, to simulate the data entry in legacy application. If the data observer is running, you should see the new data entry being captured, and message being processed.

Conclusion

With SQL dependency, we save the risk of tapping into fragile and delayed batch process for integration, and save the effort of creating a new API for the legacy system.
Instant, real time notification is achieved by connecting the SQL dependency data observer to Webhook, which opens up unlimited opportunities for sharing the data to any application.