Cache refreshment approaches

 

If you cache a SQL Server table in .NET Core, you need a way to refresh the cache when data changes. There are several approaches to achieve this:

---

1. Using SQL Server Query Notifications (SqlDependency)

Best for: Applications that need real-time cache updates with minimal overhead.

How It Works

• SQL Server notifies your application when data changes, so you can refresh the cache.
• Uses SqlDependency to listen for changes.
• Requires Service Broker to be enabled in SQL Server.

Implementation Steps

Step 1: Enable Service Broker in SQL Server

Run this SQL command:

ALTER DATABASE YourDatabase SET ENABLE_BROKER;

Step 2: Configure SQL Dependency

Install the necessary package:

dotnet add package Microsoft.Data.SqlClient

Step 3: Implement Caching with Notifications

using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Caching.Memory;

public class ProductCacheService
{
    private readonly IMemoryCache _cache;
    private readonly string _connectionString;
    
    public ProductCacheService(IMemoryCache cache, IConfiguration config)
    {
        _cache = cache;
        _connectionString = config.GetConnectionString("DefaultConnection");

        StartSqlDependency();
    }

    public IEnumerable<Product> GetProducts()
    {
        if (!_cache.TryGetValue("ProductsCache", out IEnumerable<Product> products))
        {
            products = LoadProductsFromDatabase();
            _cache.Set("ProductsCache", products, TimeSpan.FromMinutes(10));
        }
        return products;
    }

    private void StartSqlDependency()
    {
        SqlDependency.Start(_connectionString);
        RefreshCache();
    }

    private void RefreshCache()
    {
        using var connection = new SqlConnection(_connectionString);
        using var command = new SqlCommand("SELECT Id, Name, Price FROM dbo.Products", connection);

        var dependency = new SqlDependency(command);
        dependency.OnChange += (sender, e) =>  
        {
            _cache.Remove("ProductsCache");
            GetProducts(); // Reload cache
        };

        connection.Open();
        command.ExecuteReader(); // Start listening
    }

    private IEnumerable<Product> LoadProductsFromDatabase()
    {
        using var connection = new SqlConnection(_connectionString);
        using var command = new SqlCommand("SELECT Id, Name, Price FROM dbo.Products", connection);
        connection.Open();

        using var reader = command.ExecuteReader();
        var products = new List<Product>();

        while (reader.Read())
        {
            products.Add(new Product
            {
                Id = reader.GetInt32(0),
                Name = reader.GetString(1),
                Price = reader.GetDecimal(2)
            });
        }

        return products;
    }
}

Pros:
✅ Automatic cache invalidation.
✅ No need to poll the database.

Cons:
❌ Requires Service Broker.
❌ Limited to queries that SQL Server can monitor.

---

2. Using Change Tracking (Polling)

Best for: When SqlDependency is not feasible (e.g., cloud databases like Azure SQL).

How It Works

• Add a LastModified column to the table.
• Use a background job (e.g., IHostedService) to check for changes periodically.
• Refresh the cache if data has changed.

Implementation Steps

Step 1: Add LastModified Column in SQL Server

ALTER TABLE Products ADD LastModified DATETIME DEFAULT GETDATE();

Step 2: Implement Polling in .NET Core

using Microsoft.Extensions.Caching.Memory;
using Microsoft.Extensions.Hosting;
using System.Data.SqlClient;

public class CacheRefresherService : BackgroundService
{
    private readonly IMemoryCache _cache;
    private readonly string _connectionString;
    private DateTime _lastUpdated = DateTime.MinValue;

    public CacheRefresherService(IMemoryCache cache, IConfiguration config)
    {
        _cache = cache;
        _connectionString = config.GetConnectionString("DefaultConnection");
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            await RefreshCacheIfNeeded();
            await Task.Delay(TimeSpan.FromMinutes(1), stoppingToken);
        }
    }

    private async Task RefreshCacheIfNeeded()
    {
        using var connection = new SqlConnection(_connectionString);
        using var command = new SqlCommand("SELECT MAX(LastModified) FROM Products", connection);
        connection.Open();
        
        var lastModified = (DateTime?)await command.ExecuteScalarAsync();
        if (lastModified > _lastUpdated)
        {
            _lastUpdated = lastModified ?? DateTime.MinValue;
            _cache.Remove("ProductsCache");
        }
    }
}

Step 3: Register Background Service in Program.cs

builder.Services.AddHostedService<CacheRefresherService>();

Pros:
✅ Works on any SQL Server version, including Azure SQL.
✅ No need for Service Broker.

Cons:
❌ Requires polling, which may increase database load.
❌ Not real-time (depends on the polling interval).

---

3. Using a Message Queue (Event-Driven)

Best for: High-performance distributed applications using multiple services.

How It Works

• Use SQL Server triggers to publish changes to a message queue (e.g., RabbitMQ, Azure Service Bus).
• A background worker listens for messages and refreshes the cache.

Implementation Steps

Step 1: Create a Trigger in SQL Server

CREATE TRIGGER ProductChangeTrigger
ON Products
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    INSERT INTO ChangeLog (Entity, ChangeTime) VALUES ('Products', GETDATE());
END

Step 2: Implement a Message Listener in .NET Core

public class CacheUpdateListener : BackgroundService
{
    private readonly IMemoryCache _cache;

    public CacheUpdateListener(IMemoryCache cache)
    {
        _cache = cache;
    }

    protected override async Task ExecuteAsync(CancellationToken stoppingToken)
    {
        while (!stoppingToken.IsCancellationRequested)
        {
            if (CheckForUpdates())
            {
                _cache.Remove("ProductsCache");
            }
            await Task.Delay(TimeSpan.FromSeconds(30), stoppingToken);
        }
    }

    private bool CheckForUpdates()
    {
        using var connection = new SqlConnection("YourConnectionString");
        using var command = new SqlCommand("SELECT TOP 1 ChangeTime FROM ChangeLog ORDER BY ChangeTime DESC", connection);
        connection.Open();

        var lastChange = (DateTime?)command.ExecuteScalar();
        return lastChange.HasValue && lastChange > DateTime.UtcNow.AddMinutes(-1);
    }
}

Step 3: Register the Background Service

builder.Services.AddHostedService<CacheUpdateListener>();

Pros:
✅ Scalable – Works well in distributed environments.
✅ Efficient – Only refreshes when needed.

Cons:
❌ Requires message queue setup.
❌ More complex than polling.

---

Which Approach Should You Choose?

---

Conclusion

• Use SqlDependency for real-time cache invalidation, but only if Service Broker is available.
• Use polling with a LastModified column if your database does not support Service Broker.
• Use a message queue (e.g., RabbitMQ, Azure Service Bus) for distributed applications.