In many applications, the mutation of data starts with the user, flows through the application logic, and effects the underlying data store. In caching scenarios, we can see the flow of data reversed. Our data is updated by database administrators directly, and we want those changes to propagate back up through our application and to our users.

In this post, I’ll show you how to write a SQL polling mechanism that works with most relational databases. The solution works with Microsoft SQL Server, PostgreSQL, and Azure SQL.

The Solution

Let’s jump into the solution right away. We will write a SqlPollingListener class that takes the following parameters.

• connection string
• scalar SQL statement
• timespan frequency
• a Func<int,Task> as a callback

When implemented, our code should look like the following example.

using var polling = new SqlPollingListener(
    "server=localhost,11433;database=test;user=sa;password=Pass123!;",
    "select count(id) from persons",
    TimeSpan.FromSeconds(1),
    async result =>
    {
        Console.WriteLine($"Something Changed! Result is now {result}");
        // supports async calls
        await Task.CompletedTask;
    }
);

We will trigger the callback when the count of our Persons table changes.

Something Changed! Result is now 1

Want to see the code? Of course, you do!

public class SqlPollingListener : IDisposable
{
    private readonly string connectionString;
    private readonly string sql;
    private readonly TimeSpan frequency;
    private readonly Func<int, Task> callback;
    private CancellationTokenSource cancellation;
    private int currentValue;

    public SqlPollingListener(
        string connectionString,
        string sql,
        TimeSpan frequency,
        Func<int, Task> callback)
    {
        this.connectionString = connectionString;
        this.sql = sql;
        this.frequency = frequency;
        this.callback = callback;
    }

    public void Start()
    {
        if (cancellation != null)
        {
            Stop();
        }
        
        cancellation = new CancellationTokenSource();

        async Task Polling()
        {
            while (!cancellation.Token.IsCancellationRequested)
            {
                if (!string.IsNullOrEmpty(sql) && callback != null)
                {
                    await using var sqlConnection = new SqlConnection(connectionString);
                    await sqlConnection.OpenAsync();

                    var command = sqlConnection.CreateCommand();
                    command.CommandText = sql;
                    command.CommandType = CommandType.Text;
                    var result = (int) await command.ExecuteScalarAsync();

                    if (result != currentValue)
                    {
                        currentValue = result;
                        await callback(result);
                    }
                }

                await Task.Delay(frequency, cancellation.Token);
            }
        }

        Task.Run(Polling, cancellation.Token);
    }

    public void Stop()
    {
        if (cancellation.Token.CanBeCanceled)
        {
            cancellation.Cancel();
        }
        
        cancellation?.Dispose();
        cancellation = null;
    }

    public void Dispose()
    {
        Stop();
    }
}

Let’s see the SqlPollingListenerin action.

In the picture above, you see the SqlPollingListener triggering after we insert our first row. Then the listener triggers again after we truncate the Persons table. Our polling time is once every 1 second, but you may want to adjust the timespan for your use case.

Advantages

The advantages of this approach are as follows:

• Database Agnostic: It will work, as long as it is a SQL based that works with SqlConnection and SqlCommand.
• Easily testable
• Supports async callbacks
• Efficient since the result is a scalar of type int.

The code for the class can also be modified to work with any other kind of dependency. That said, let’s talk about the disadvantages.

Disadvantages

It isn’t all sunshine and rainbows. there are some disadvantages to this code as well:

• Long-lived Task means we consume a background thread for each polling listener.
• Inefficient because we are asking for changes rather than being told about changes.
• Utilizing more resources like threads, CPU, memory, database connection, and network traffic.

These advantages aren’t the worst thing in the world, but they are worth considering when implementing this solution.

Conclusion

As developers, we lose some functionality as we transition into a cloud-based ecosystem. We have to work around the limitations of our platform. With Azure SQL, we no longer can use SqlDependency as the SQL Server broker is no longer a thing. There are other services in Azure that can behave in the same way, but the SqlPollingListener I wrote above is a quick and straightforward way to account for data changes outside an application’s regular operation. I hope you found this post helpful, and let me know if you’re using it or what could be better.