In SQL Server if you need to add a column to all tables which don’t have it already I found the below script on Stack Exchange the other day which helps create the ALTER statements needed…
SELECT 'ALTER TABLE ' + QUOTENAME(ss.name) + '.' + QUOTENAME(st.name) + ' ADD created_date DATETIME NULL;'
FROM sys.tables st
INNER JOIN sys.schemas ss on st.[schema_id] = ss.[schema_id]
WHERE st.is_ms_shipped = 0
AND NOT EXISTS (
FROM sys.columns sc
WHERE sc.[object_id] = st.[object_id]
AND sc.name = 'created_date'
The example below shows that the created_date column is not in three tables. The generated ALTER statements can be easily reviewed before executing them.
We could also easily modify lines 4 and 8 to create drop statements for a column for all tables in which it exists.
If you need a distributed cache and are planning on deploying your .NET Core web app to Azure, using Azure Cache for Redis as your cache provider is a good choice.
The steps below show you how to get started…
Create an Azure Cache for Redis instance using the portal
Search for ‘azure cache for redis’ on the portal and on the create screen fill in details such as resource group, DNS name, location and cache type.
Choose the same location as whatever app will be consuming your cache to minimise latency. When you’re just getting setup I’d recommend choosing the Basic C0 cache as this is the cheapest and you can always upgrade later. For all the other tabs just accept the defaults for the moment.
After Azure has finished creating your new cache, navigate into it and go to the Access keys page as shown below.
Copy the primary connection string somewhere as you’ll need to put this into your appsettings.json file in the next step.
Setup and configure your app
Install Microsoft.Extensions.Caching.StackExchangeRedis from NuGet.
Add a connection string pointing to your Redis instance on Azure into your appsettings.json file. Remember you can get this from the Access keys page as shown above. In our real apps we’d likely store this value in Azure Key Vault.
Create a cache wrapper or helper class
At this stage your app should be able to set and read items in Azure Redis by instantiating a concrete instance of the IDistributedCache instance. The next step is to create a simple thin wrapper around this interface. It’s not 100% needed but it helps keep code that is using the cache a little bit neater.
Since .NET Core 3.0 Microsofts default JSON deserializer has been System.Text.Json, however it doesn’t support circular references so in the below example I’ve fallen back to using Newtonsoft.
Inject IDistributedCache into your controller or service class
Since dependency injection is built into .NET Core and because we’ve registered the Redis Cache service in our Startup.cs file we can inject an instance of the IDistributedCache into our controller class…
Check the cache when retrieving data
Finally to use the cache when getting data… we can implement the cache-aside pattern. In this pattern we…
First check the cache for a particular key. If it does not exist we read data from the DB and store it in the cache. If it does exist in cache we use the cached version of the data.
Below I’m checking the cache for a list of products…
In this case I’ve not set any DistributedCacheEntryOptions meaning items will not expire. If you need to set absolute or sliding expirations on items you can set the relevant options when adding them to the cache.
Remove items from cache after they’ve been updated
When some data change occurs which means previously cached items are now stale we can remove items from the cache as below…
In this case the list of products I previously cached is no longer valid as I’ve deleted one of them.
Depending on the situation we might not have to explicitly remove certain items from the cache like above. In many cases some staleness is acceptable so in this case we might set a sliding expiration of 1 hour or depending on the data just re-cache certain items overnight.
How to test your app is storing items in and reading from Azure Cache for Redis
When you create an Azure SQL Database with the Serverless (pay per second of usage) pricing model there’s an auto-pause option which will pause the DB after a period of inactivity. This option is intended to save you money. One problem with enabling it is connection failures when activity re-starts again as the DB has to go through a cold start.
When you’re connecting to a paused DB from Entity Framework Core you’ll often see an error like below…
An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding ‘EnableRetryOnFailure()’ to the ‘UseSqlServer’ call.
As suggested by the error we can handle this by including EnableRetryOnFailure in our DbContext configuration as shown below…
When the above options are set and the DB is in a paused state the first request of the app will take longer than normal but the user won’t see an error as the DB will be accessible after one or two connect retries.
Testing EnableRetryOnFailure when Azure SQL DB is paused
Unfortunately at the moment there doesn’t seem to be a way to simulate the paused state for Azure SQL Databases so you’ll need to wait an hour (min auto-pause time) to test. You can confirm the DB is in a paused state from the portal…
… and after hitting your app if you’re logging debug and info messages you should see some failed connection attempts (first yellow box below) and finally a successful connection and query execution (second yellow box below) in your logs.
The sample log output below (click for a larger view in a new window) is taken from the App Service Log stream page which allows you to view your application logs in real-time direct from the portal.
A lot of time the first thing we do when we startup SQL Server Management Studio is to open a new query window. To save time we can have SSMS do this for us by changing the environment startup options as shown below…
If we want SQL Server Management Studio to create a DDL change script for us when we create or change a table we can configure this in the Options -> Designers -> Tables and Database Designers screen as shown below…
When we have the ‘Auto generate change scripts’ option selected above SMSS will popup a window with the DDL change script each time we create or edit a table.
The example below shows the change script shown after I’ve added a new column called Fee to the Courses table…
Unfortunately it seems this functionality only works on tables.