Automatically Refresh Cached Web Page Data from SQL Server or Oracle

By: Pablo Echeverria | Updated: 2023-06-22 | Comments | Related: > Application Development

Problem

As we've shown in Cache SQL Server Data in a .NET Web Application, caching data improves your application performance by several orders of magnitude. However, it is mandatory that the cached data is updated when a change occurs in the database. I've had web farms where all nodes need to update their cached information when one of them modifies it. How can this be accomplished in Oracle or in SQL Server, and what are the differences?

Solution

We need to make the code generic to work with both Oracle and SQL Server. I will show you how this is accomplished using reflection, generic types, casting, and inheritance. Once we have the code in place, we will use SqlDependency/OracleDependency to get notified when a change occurs at the database level. This way, you can cache slowly changing data which is expensive to retrieve from the database, and reload it only when a change occurs. Note: This is not the only option. You can also investigate how to do it using SqlCacheDependency or SqlNotificationRequest.

The code below is from the abovementioned article and is our starting point.

using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Configuration;
using System.Data.Common;
using Microsoft.Data.SqlClient;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using Microsoft.Extensions.Caching.Memory;
namespace WebApplication2.Pages
{
    public class IndexModel : PageModel
    {
        private readonly ILogger<IndexModel> _logger;
        public object Message { get; private set; } = "PageModel in C#<br/>"; 
        private IConfiguration _config;
        private readonly IMemoryCache _memoryCache;
        public IndexModel(ILogger<IndexModel> logger, IConfiguration config, IMemoryCache memoryCache)
        {
            _logger = logger;
            _config = config;
            _memoryCache = memoryCache;
        }
        public void OnGet()
        {
            Message += $"Server time is { System.DateTime.Now }<br/>";
            System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch();
            timer.Start();
            if (!_memoryCache.TryGetValue("Data", out DataSet ds))
            {
                ds = new DataSet();
                using (DbConnection connection = new SqlConnection(_config["ConnectionStrings:SqlServerConnection"]))
                {
                    DbDataAdapter da = new SqlDataAdapter("select * from T1", (SqlConnection)connection);
                    da.Fill(ds);
                }
                _memoryCache.Set("Data", ds, new MemoryCacheEntryOptions());
            }
            timer.Stop();
            Message += $"{ ds.Tables[0].Rows.Count } records found in { timer.ElapsedMilliseconds } ms<br/>";
        }
    }
}

We will make the following changes to the above code, allowing us to expire the web application cached data using Query Notifications.

using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Configuration;
using System.Data.Common;
using Microsoft.Data.SqlClient;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using Microsoft.Extensions.Caching.Memory;
using System;
namespace WebApplication2.Pages
{
    public class IndexModel : PageModel
    {
        private readonly ILogger<IndexModel> _logger;
        public object Message { get; private set; } = "PageModel in C#<br/>"; 
        private IConfiguration _config;
        private readonly IMemoryCache _memoryCache;
        Type DbConnectionType;
        string DbConnString;
        string DbCommand;
        Type DbDependencyType;
        Type DbDataAdapterType;
        public IndexModel(ILogger<IndexModel> logger, IConfiguration config, IMemoryCache memoryCache)
        {
            _logger = logger;
            _config = config;
            _memoryCache = memoryCache;
            DbConnectionType = typeof(OracleConnection);
            DbConnString = _config["ConnectionStrings:OracleConnection"];
            DbCommand = "SELECT ROUND(KEY, 8) \"KEY\", ROUND(RANDOM_INT, 8) \"RANDOM_INT\", ROUND(RANDOM_FLOAT, 8) \"RANDOM_FLOAT\" FROM T1";
            DbDependencyType = typeof(OracleDependency);
            DbDataAdapterType = typeof(OracleDataAdapter);
        }
        public void OnGet()
        {
            Message += $"Server time is { System.DateTime.Now }<br/>";
            System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch();
            timer.Start();
            if (!_memoryCache.TryGetValue("Data", out DataSet ds))
            {
                ds = new DataSet();
                if (DbConnectionType.Equals(typeof(SqlConnection)))
                    SqlDependency.Start(DbConnString);
                using (DbConnection connection = (DbConnection)Activator.CreateInstance(DbConnectionType, DbConnString))
                {
                    DbCommand cmd = connection.CreateCommand();
                    cmd.CommandText = DbCommand;
                    dynamic dependency = Activator.CreateInstance(DbDependencyType, cmd);
                    DbDependencyType.GetEvent("OnChange").AddEventHandler(
                        dependency,
                        Delegate.CreateDelegate(
                            DbDependencyType.GetEvent("OnChange").EventHandlerType,
                            ((EventHandler)dbChangeNotification).Target,
                            ((EventHandler)dbChangeNotification).Method));
                    DbDataAdapter da = (DbDataAdapter)Activator.CreateInstance(DbDataAdapterType, cmd);
                    da.Fill(ds);
                }
                _memoryCache.Set("Data", ds, new MemoryCacheEntryOptions());
            }
            timer.Stop();
            Message += $"{ ds.Tables[0].Rows.Count } records found in { timer.ElapsedMilliseconds } ms<br/>";
        }
        private void dbChangeNotification(object sender, EventArgs e)
        {
            _memoryCache.Remove("Data");
            if (DbConnectionType.Equals(typeof(SqlConnection)))
                SqlDependency.Stop(DbConnString);
        }
    }
}

The code below is the same as above, but with line numbers, so I can explain the changes.

Note: We have declared three type variables: DbConnectionType (line 28), DbDependencyType (line 31), and DbDataAdapterType (line 32), in which we're going to store the type of the following classes: OracleConnection, OracleDependency, and OracleDataAdapter. We have also declared two string variables: DbConnString (line 29) and DbCommand (line 30). This allows us to only change these five lines when switching to SQL Server, and everything else will function properly.

The DbConnString is assigned the value in the configuration named OracleConnection, and the DbCommand is assigned the query to retrieve the information. Note: The double quotes need to be escaped, and the columns need to be rounded to avoid the exception "Specified cast is not valid" due to the incompatibility between database types and .Net types:

SELECT ROUND(KEY, 8) \"KEY\", ROUND(RANDOM_INT, 8) \"RANDOM_INT\", ROUND(RANDOM_FLOAT, 8) \"RANDOM_FLOAT\" FROM T1

For SQL Server, we will store the type of the following classes: SqlConnection, SqlDependency, and SqlDataAdapter. The connection string is the one in the configuration named SqlServerConnection, and the query to retrieve the information is this. Note the explicit column names and owner as there are several restrictions on the query as explained in Query Notification in SQL Server:

SELECT [KEY], [RANDOMINT], [RANDOMFLOAT] FROM [dbo].[T1]

In the data retrieval part, for SQL Server, we need to start a listener (line 43) on the connection string using SqlDependency.Start. Otherwise, you'll get the following exception when filling the dataset:

"When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency."

For Oracle, this is not needed. We use reflection to create the appropriate database connection type (line 44), either SqlConnection or OracleConnection and store it in the DbConnection superclass. We also use reflection to create the dependency (line 48), but because SqlDependency comes from Microsoft.Data.SqlClient and OracleDependency comes from Oracle.ManagedDataAccess.Client. They don't have a superclass. We need to subscribe to the "OnChange" event of this dependency class (line 49), for which we use reflection, type casting, and delegates. Finally, we use reflection to create the appropriate Data Adapter type (line 55), either SqlDataAdapter or OracleDataAdapter, and store it in the DbDataAdapter superclass.

Note: We also need a method to get notified when the database changes. It is named "dbChangeNotification" (line 63), and instead of SqlNotificationEventArgs or OracleNotificationEventArgs, we use the superclass, EventArgs.

The first thing we will do is remove the cached data (line 65), so the next time the data is needed, it will be retrieved from the database and cached again. Other more complex options involve getting the data immediately, caching it, and updating the displayed data using SignalR, which you can research online. For SQL Server, we also need to stop the listener (line 67), as it needs to be stopped every time it is started after it has notified the application. If dbChangeNotification gets fired immediately without changing the database, you need to inspect the EventArgs to determine the cause. For example, if in SQL Server you get the exception "Error – Object" or "Invalid – Statement," most probably it's because the query doesn't conform to the standards allowed for it. See the previously referenced article on Query Notification in SQL Server.

Now that we've shown the code changes, we need to explain the database changes enabling this functionality.

Oracle Changes

In Oracle, there is only one: grant change notification to the user. Otherwise, you will get the error message:

"ORA-29972: user does not have privilege to change/ create registration."

It is done with this command:

GRANT CHANGE NOTIFICATION TO MyUser;

SQL Server Changes

In SQL Server, you need to make the following ten changes:

1 - Point to the database where this functionality will be enabled for:

USE [MyDb];

2 -Grant create procedure:

GRANT CREATE PROCEDURE TO [MyUser];