Example DAL for SPROCs, CACHE, and PUBSUB

appsettings.json

{
  "ConnectionStrings": {
    "SqlDb": "Server=(localdb)\\mssqllocaldb;Database=mydatabase;Trusted_Connection=True;MultipleActiveResultSets=true",
    "Redis": "localhost"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

exmapledal.cs

/// <summary>
/// DataAccessLayer is a class providing data access operations in a hybrid setup of SQL Server and Redis Cache.
/// It also provides methods to use the Redis Pub/Sub feature.
/// </summary>
public class DataAccessLayer
{
    private readonly IConfiguration _configuration;
    private readonly IDatabase _database;
    private readonly ISubscriber _subscriber;

    /// <summary>
    /// Initializes a new instance of the DataAccessLayer class.
    /// </summary>
    /// <param name="configuration">The application configuration, where key/value pair settings are stored.</param>
    public DataAccessLayer(IConfiguration configuration)
    {
        _configuration = configuration;
        var connectionMultiplexer = ConnectionMultiplexer.Connect(_configuration.GetConnectionString("Redis"));
        _database = connectionMultiplexer.GetDatabase();
        _subscriber = connectionMultiplexer.GetSubscriber();
    }

    /// <summary>
    /// Asynchronously retrieves data from Redis Cache or SQL Server.
    /// It checks Redis first, and if data does not exist, it fetches from SQL Server and then stores the result in Redis for future calls.
    /// </summary>
    /// <param name="cacheKey">The key used for Redis lookup.</param>
    /// <param name="storedProcedure">The name of the stored procedure to execute on SQL Server.</param>
    /// <param name="parameters">The parameters required by the stored procedure.</param>
    /// <returns>The requested data.</returns>
    public async Task<T> GetAsync<T>(string cacheKey, string storedProcedure, DynamicParameters parameters)
    {
        var cacheValue = _database.StringGet(cacheKey);
        if (cacheValue.HasValue)
        {
            return JsonSerializer.Deserialize<T>(cacheValue);
        }

        using (var sqlConnection = new SqlConnection(_configuration.GetConnectionString("SqlDb")))
        {
            var result = await sqlConnection.QueryFirstOrDefaultAsync<T>(storedProcedure, parameters, commandType: CommandType.StoredProcedure);

            _database.StringSet(cacheKey, JsonSerializer.Serialize(result));
            return result;
        }
    }

    /// <summary>
    /// Asynchronously saves data to SQL Server and invalidates the corresponding data in Redis Cache.
    /// </summary>
    /// <param name="cacheKey">The key of the Redis data to invalidate.</param>
    /// <param name="storedProcedure">The name of the stored procedure to execute on SQL Server.</param>
    /// <param name="parameters">The parameters required by the stored procedure.</param>
    /// <returns>A task representing the asynchronous operation.</returns>
    public async Task SetAsync(string cacheKey, string storedProcedure, DynamicParameters parameters)
    {
        using (var sqlConnection = new SqlConnection(_configuration.GetConnectionString("SqlDb")))
        {
            await sqlConnection.ExecuteAsync(storedProcedure, parameters, commandType: CommandType.StoredProcedure);
        }

        _database.KeyDelete(cacheKey);
    }

    /// <summary>
    /// Subscribes to a channel in Redis Pub/Sub.
    /// </summary>
    /// <param name="channel">The channel to subscribe to.</param>
    /// <param name="handler">The method to execute when a message is received on the channel.</param>
    public void Subscribe(string channel, Action<RedisChannel, RedisValue> handler)
    {
        _subscriber.Subscribe(channel, handler);
    }

    /// <summary>
    /// Publishes a message to a channel in Redis Pub/Sub.
    /// </summary>
    /// <param name="channel">The channel to publish the message to.</param>
    /// <param name="message">The message to publish.</param>
    public void Publish(string channel, string message)
    {
        _subscriber.Publish(channel, message);
    }
}

usage.cs

// Create an instance of DataAccessLayer class with the application configuration
var dataAccessLayer = new DataAccessLayer(configuration);

// Prepare parameters for the stored procedure
var parameters = new DynamicParameters();
parameters.Add("@UserId", 1);  // Assuming the stored procedure UpdateUser requires @UserId parameter
parameters.Add("@UserName", "newUserName");  // And some other parameters like @UserName

// Invoke the SetAsync method to update user data and refresh the corresponding cache
await dataAccessLayer.SetAsync("User:1", "UpdateUser", parameters);

// Prepare parameters for the stored procedure
var parameters = new DynamicParameters();
parameters.Add("@UserId", 1);  // Assuming the stored procedure GetUserById requires @UserId parameter

// Invoke the GetAsync method to fetch user data from cache or database
var user = await dataAccessLayer.GetAsync<User>("User:1", "GetUserById", parameters);

// Subscribe to a Redis Pub/Sub channel and specify the action to take when a message is published on that channel
dataAccessLayer.Subscribe("myChannel", (channel, message) =>
{
    // Print the message to the console
    Console.WriteLine($"Received {message} on {channel}");
});

// Publish a message to a Redis Pub/Sub channel
dataAccessLayer.Publish("myChannel", "Hello, world!");