Generate Microsoft SQL Database diagram

Raghubir Singh — Fri, 21 Apr 2023 11:23:00 +0000

There is a tool available in Visual Studio market place to generate better form of DB diagram than the existing features available in SQL itself. Tool Url is provided below.

https://marketplace.visualstudio.com/items?itemName=ErikEJ.EFCorePowerTools

Please use it. I wish I could help here.

SQL query check Azure Data Sync processing status

Raghubir Singh — Fri, 21 Apr 2023 11:17:23 +0000

As you aware that Azure data sync service syncs database changes to one database to another(source to destination).First time when this sync process is established successfully, system creates one extra database somewhere called as metadata db in destination server. If user was to check using SQL query that if last process is completed successfully or there is any failure than it can execute following query.

SELECT top 1 sg.[name] as GroupName, ud.[database] as DatabaseName, uh.completionTime, uh.detailEnumId as SyncStatus, taskType.Name taskType--, -- uh.recordType FROM [dss].[syncgroupmember] sgm join [dss].[syncgroup] sg on sg.Id = sgm.syncgroupid join [dss].[userdatabase] ud on ud.id = sgm.databaseid join [dss].[UIHistory] uh on uh.databaseid = ud.id and sg.id= uh.syncgroupid join [dss].[EnumType] taskType on taskType.EnumId = uh.taskType and taskType.type ='TaskType' WHERE sg.name = 'groupname' and cast(uh.completionTime as date) = Cast(DATE() as date) order by uh.completionTime desc

I wish this could help someone.

Distributed Caching in ASP.Net Core using Azure Cache for Redis

Raghubir Singh — Mon, 02 May 2022 14:39:11 +0000

Caching is a technique to store the data in memory to avoid multiple round-trip of data from remote calls/database. It reduces the remote calls and provides data in quick way. Caching plays a vital role in order to optimise the time and resources utilisation. Moveover I am explaining Caching by below image.

As you can see in the image when user clicks on UI to get the data, a request goes to server and retrieves the data from the database. Every time when user clicks on same link, the same amount of time is consumed by the resources to provide the data. But wait, now we have cache server in between client and database server. This cache server stores the data after first request returning the data from the database. On second request client is able to fetch data from cache server rather on database. The data will come very quick as there is no calculation or logic needs to execute.

Majorly, Caching is used for two reasons:

Lookup data
High calculation

Cache constructs the data in keys and values. Key is used to retrieve/delete data inside cache. Values contains the actual data.

Before jumping into Code part. Let's us first setup Azure Cache for redis service on Azure portal. Here are the following steps:

Click on Create link highlighted as above image

The latest version of redis is 6.

Cache Implementation

First of all, install Azure redis Nuget package into the asp.net core project. Find package name as Microsoft.Extensions.Caching.StackExchangeRedis on Nuget repository. Create ResponseCacheService class and its interface by implementing three main methods.

CacheResponseAsync - It is used to store the cache.
GetCachedResponseAsync - It is used to get stored cache data through cache key. Here, Key is your method name.
RemoveCacheAsync - This is used to remove cache based on keys passed as parameters. Remember, there are two ways to expire/remove cache. One is automated way where cached expiration time is defined at the time of cached data. Another one is manually removed cache on performing any CRUD operations.

namespace AzureCachePOC.Services
{
     using Microsoft.Extensions.Caching.Distributed;
     using Newtonsoft.Json;
     using StackExchange.Redis;
     using System;
     using System.Collections.Generic;
     using System.Linq;
     using System.Threading.Tasks;
     using AzureCachePOC.Cache;
     /// <summary>
     /// Defines the <see cref="ResponseCacheService" />
     /// </summary>
     public class ResponseCacheService : IResponseCacheService
     {
         /// <summary>
         /// Defines the _distributedCache
         /// </summary>
         private readonly IDistributedCache _distributedCache;
         /// <summary>
         /// Defines the _redisSettings
         /// </summary>
         private readonly RedisCacheSettings _redisSettings;
         /// <summary>
         /// Initializes a new instance of the <see cref="ResponseCacheService"/> class.
         /// </summary>
         /// <param name="distributedCache">The distributedCache<see cref="IDistributedCache"/></param>
         /// <param name="redisSettings"></param>
         public ResponseCacheService(IDistributedCache distributedCache, RedisCacheSettings redisSettings)
         {
             _distributedCache = distributedCache;
             _redisSettings = redisSettings;
         }
         /// <summary>
         /// The CacheResponseAsync
         /// </summary>
         /// <param name="cacheKey">The cacheKey<see cref="string"/></param>
         /// <param name="response">The response<see cref="object"/></param>
         /// <param name="timeToLive">The timeToLive<see cref="TimeSpan"/></param>
         /// <returns>The <see cref="Task"/></returns>
         public async Task CacheResponseAsync(string cacheKey, object response, TimeSpan timeToLive)
         {
             if (response == null)
             {
                 return;
             }
             var serializedResoponse = JsonConvert.SerializeObject(response);
             await _distributedCache.SetStringAsync(cacheKey, serializedResoponse, new DistributedCacheEntryOptions
             {
                 AbsoluteExpirationRelativeToNow = timeToLive
             });
         }
         /// <summary>
         /// The GetCachedResponseAsync
         /// </summary>
         /// <param name="cacheKey">The cacheKey<see cref="string"/></param>
         /// <returns>The <see cref="string"/></returns>
         public async Task<string> GetCachedResponseAsync(string cacheKey)
         {
             var cachedResponse = await _distributedCache.GetStringAsync(cacheKey);
             return string.IsNullOrEmpty(cachedResponse) ? null : cachedResponse;
         }
         /// <summary>
         /// The RemoveCacheAsync
         /// </summary>
         /// <param name="cacheKeys"></param>
         /// <returns></returns>
         public async Task RemoveCacheAsync(List<string> cacheKeys)
         {
             // show all keys in database 0 that include key in their name
             ConfigurationOptions options = ConfigurationOptions.Parse(_redisSettings.ConnectionString);
             ConnectionMultiplexer connection = ConnectionMultiplexer.Connect(options);
             IDatabase db = connection.GetDatabase();
             var endPoint = connection.GetEndPoints().First();
             var server = connection.GetServer(endPoint);
             if (server != null)
             {
                 foreach (var cacheKey in cacheKeys)
                 {
                     foreach (var key in server.Keys(pattern: cacheKey + "*"))
                     {
                         // Remove Keys as per requirement
                         await _distributedCache.RemoveAsync(key);
                     }
                 }
             }
         }
     }
 }
 namespace AzureCachePOC.Services
 {
     using System;
     using System.Collections.Generic;
     using System.Threading.Tasks;
     /// <summary>
     /// Defines the <see cref="IResponseCacheService" />
     /// </summary>
     public interface IResponseCacheService
     {
         /// <summary>
         /// The CacheResponseAsync
         /// </summary>
         /// <param name="cacheKey">The cacheKey<see cref="string"/></param>
         /// <param name="response">The response<see cref="object"/></param>
         /// <param name="timeToLive">The timeToLive<see cref="TimeSpan"/></param>
         /// <returns>The <see cref="Task"/></returns>
         Task CacheResponseAsync(string cacheKey, object response, TimeSpan timeToLive);
         /// <summary>
         /// The GetCachedResponseAsync
         /// </summary>
         /// <param name="cacheKey">The cacheKey<see cref="string"/></param>
         /// <returns>The <see cref="string"/></returns>
         Task<string> GetCachedResponseAsync(string cacheKey);
        /// <summary>
         /// RemoveCacheAsync 
         /// </summary>
         /// <param name="cacheKey"></param>
         /// <returns></returns>
         Task RemoveCacheAsync(List<string> cacheKeys);
     }
 }

Attribute - Store Cache

A wrapper class is defined on top of Cache class (ResponseCacheService) as an attribute. It is named as CachedAttribute. This attribute is used on the top of the controller method where data or response is cached if there is no cached data available yet. This attribute is executed before and after method execution. If method is executed successfully and returns OK then it stored the cache on server.

  [AttributeUsage(validOn: AttributeTargets.Class | AttributeTargets.Method)]
     public class CachedAttribute : Attribute, IAsyncActionFilter
     {
         /// <summary>
         /// Defines the _timeToLiveSeconds
         /// </summary>
         private readonly int _timeToLiveSeconds;
         /// <summary>
         /// Initializes a new instance of the <see cref="CachedAttribute"/> class.
         /// </summary>
         /// <param name="timeToLiveSeconds">The timeToLiveSeconds<see cref="int"/></param>
         public CachedAttribute(int timeToLiveSeconds)
         {
             _timeToLiveSeconds = timeToLiveSeconds;
         }
         /// <summary>
         /// The OnActionExecutionAsync
         /// </summary>
         /// <param name="context">The context<see cref="ActionExecutingContext"/></param>
         /// <param name="next">The next<see cref="ActionExecutionDelegate"/></param>
         /// <returns>The <see cref="Task"/></returns>
         public async Task OnActionExecutionAsync(ActionExecutingContext context, ActionExecutionDelegate next)
         {
             //check if requests is cached
             //if true return
             var cacheSettings = context.HttpContext.RequestServices.GetRequiredService<RedisCacheSettings>();
             if (!cacheSettings.Enabled)
             {
                 await next();
                 return;
             }
             var cacheService = context.HttpContext.RequestServices.GetRequiredService<IResponseCacheService>();
             var cacheKey = GenerateCacheKeyFromRequest(context.HttpContext.Request);
             var cachedResponse = await cacheService.GetCachedResponseAsync(cacheKey: cacheKey);
             if (!string.IsNullOrEmpty(cachedResponse))
             {
                 var contentResult = new ContentResult
                 {
                     Content = cachedResponse,
                     ContentType = "application/json",
                     StatusCode = 200
                 };
                 context.Result = contentResult;
                 return;
             }
             var executedContext = await next();
             if (executedContext.Result is OkObjectResult okObjectResult)
             {
                 await cacheService.CacheResponseAsync(cacheKey: cacheKey, okObjectResult.Value, timeToLive: TimeSpan.FromSeconds(_timeToLiveSeconds));
             }
         }
         private static string GenerateCacheKeyFromRequest(HttpRequest request)
         {
             var keyBuilder = new StringBuilder();
             keyBuilder.Append($"{request.Path}");
             foreach (var (key, value) in request.Query.OrderBy(x => x.Key))
             {
                 keyBuilder.Append($"{key}-{value}");
             }
             return keyBuilder.ToString();
         }
     }

Cached implementation on controller method.

Here you can see cache implementation on method. The code is written in a generic way. Output/Response caching is performed or stored on memory using attribute (Cached) in C# for example.

         [HttpGet]
         [Route("GetEmpData")]
         [Cached(120)]
         public async Task<ActionResult> GetEmpData([FromQuery] GetAllEmployeeQuery model)
         {
             model = UtilityFunctions.HandleSpecialCharacter(model);
             var empFilter = _mapper.Map<EmployeeFilter>(model);
             var result = await _employeeService.GetEmployeeData(empFilter);
             return Ok(new ResponseWrapper<List<EmployeeListResponse>>
             {
                 Status = result.Any() ? Constants.MessageStatus.Success : Constants.MessageStatus.NotFound,
                 Data = _mapper.Map<List<EmployeeListResponse>>(result),
                 Code = result.Any() ? (int)Constants.StatusCode.Ok : (int)Constants.StatusCode.BadRequest,
             });
         }

Remove Cache

Data is cached for a specific period of time. The reason for that is user should always get the latest data. Otherwise user can have previously modified data. In order to deal with this situation, cache is deleted time to time. As I previously explained that caching can be removed in two ways.

Set the expiration time when cache is created.
Remove cache through key on any CRUD operation. There is also a generic implemention to remove cache and that is through again wrapper class as an attribute. This attribute is named as RemoveCacheAttribute. It also runs before and after method execution and remove cache mentioned inside the method. Which dependent cache keys need to removed on the method those can be defined inside ManageCacheKeys.json file. This attribute reads those keys as well to remove cache. You can see the complete code on GitHub repository. This attribute can be defined on top of the method as given image below. Removal of cached data is also done in the same way by implementing another attribute (RemoveCache).

public async Task RemoveCacheAsync(List<string> cacheKeys)
         {
             // show all keys in database 0 that include key in their name
             ConfigurationOptions options = ConfigurationOptions.Parse(_redisSettings.ConnectionString);
             ConnectionMultiplexer connection = ConnectionMultiplexer.Connect(options);
             IDatabase db = connection.GetDatabase();
             var endPoint = connection.GetEndPoints().First();
             var server = connection.GetServer(endPoint);
             if (server != null)
             {
                 foreach (var cacheKey in cacheKeys)
                 {
                     foreach (var key in server.Keys(pattern: cacheKey + "*"))
                     {
                         // Remove Keys as per requirement
                         await _distributedCache.RemoveAsync(key);
                     }
                 }
             }
         }

Conclusion

In conclusion, caching is a very simple way to improve the performance of your services. For the scenario above, without caching, the request gets handled in about 40ms on my machine. Once I enabled caching that number dropped down to around 10ms.

The source material for this post can be found on GitHub. This is an application I am actively developing, so if the source code for this post is not there please bear with me as it probably has not been merge yet.

DEV Community: Raghubir Singh