Distributed caching has become a popular way to enhance application performance and scalability. Developers are leveraging this approach to achieve significant improvements in their applications. However, this approach brings several challenges, one of the most important being ensuring that the cached data is always synchronized with the database. This synchronization is crucial because the cache stores a copy of the data in the database.
Moreover, the data is likely to become unsynchronized when multiple applications update the same database data, but not all of them have access to the distributed cache. This can lead to situations where the cached data becomes stale and differs from the database. While this might be acceptable for some reference data, it is not suitable for transactional data. Reference data, such as a product catalog, is read frequently but rarely modified. In contrast, transactional data, like customer or account information, is both read and modified frequently, necessitating accurate and up-to-date synchronization between the cache and the database.
How do you ensure that the distributed cache stays synchronized with the database?
The answer to such issues is SqlCacheDependency. It is part of ASP.NET Cache (System.Web.Caching) and allows you to specify a dataset in the database using an SQL statement. When the dataset is modified, you receive .NET event notifications from SQL Server 2005/2008. NCache has internally incorporated the SqlCacheDependency to synchronize the cache with SQL Server 2005/2008 or Oracle databases. NCache provides a similar interface called SqlDependency, allowing you to specify an SQL statement representing one or more rows in a given table for your cached item. NCache then uses SqlCacheDependency to link the database against these rows. Whenever that row changes, items associated with the row will be automatically removed from the cache. This ensures that your distributed cache stays synchronized with the database.
So, if your data is updated in the database by one of your applications, SQL Server fires a .NET event notification which NCache catches and removes the corresponding item from the distributed cache. This resolves the data inconsistency issue of having two different copies of the same data. This is because when your application wants the same data next time, it doesn’t find it in the cache and is forced to retrieve the latest copy from the database which is then cached again. This way, NCache ensures that the data in the cache is always consistent with the data in the database.
Add Data with SQL Server Dependency through API
The following example demonstrates how to add SQL Dependency to an item and then add the item into the cache using the Insert method. The Insert method not only adds a new item with the specified dependency but also overwrites any existing data for the same key in the cache:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
// Creating connection string to get connected with database. string connectionString = "your_connection_string_here"; // Getting products from database. List<Product> products = FetchProductFromDB(); foreach (Product product in products) { string productKey = $"Product: {product.ProductID}"; // Creating an SQL dependency on the UnitPrice of product. Whenever the UnitPrice changes, the product is removed from the cache. string query = $"SELECT UnitPrice FROM dbo.Products WHERE ProductID = {product.ProductID}"; // Creating dependency. SqlCacheDependency dependency = new SqlCacheDependency(connectionString, query); CacheItem productItem = new CacheItem(product); // Adding Dependency to product item productItem.Dependency = dependency; // Adding CacheItem in cache _cache.Add(productKey, productItem); } |
Add Data with SQL Dependency using Stored Procedure
Similarly, NCache also lets you add an SQL Dependency to an item using a stored procedure, including specifying parameters with the SqlCacheDependency method. The code below explains how you can add an item to the cache with SQL Dependency using a stored procedure with the help of the Insert method. The Insert method adds a new item with the dependency and overwrites existing data for the same key.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
// Creating connection string to get connected with database. string connectionString = "your_connection_string_here"; string spGetUnitPriceByProductID = "sp_GetUnitPriceByProductID"; // Getting products from database. List<Product> products = FetchProductFromDB(); // Creating dictionary of CacheItems. Dictionary<string, CacheItem> cacheItems = new Dictionary<string, CacheItem>(); foreach (Product product in products) { string productKey = $"Product: {product.ProductID}"; // Creating Param to be passed in stored procedure dictionary. SqlCmdParams paramProductID = new SqlCmdParams { Type = CmdParamsType.Int, Value = product.ProductID }; // Creating stored procedure params. Dictionary<string, SqlCmdParams> parameters = new Dictionary<string, SqlCmdParams>(); parameters.Add("@ProductID", paramProductID); CacheItem productItem = new CacheItem(product); // Creating an SQL dependency on the UnitPrice of product. Whenever the UnitPrice changes, the product is removed from the cache. SqlCacheDependency dependency = new SqlCacheDependency(connectionString, spGetUnitPriceByProductID, SqlCommandType.StoredProcedure, parameters); // Adding Dependency to product item productItem.Dependency = dependency; cacheItems.Add(productKey, productItem); } // Adding CacheItems in cache _cache.AddBulk(cacheItems); |
For more information, refer to the SQL Cache Dependency documentation.
Conclusion
In summary, NCache’s SqlDependency feature guarantees continuous synchronization between your cache and database, ensuring data integrity. You can now start caching all data without the fear of using stale data from the cache. And, of course, the more data you cache, the better your application performance and scalability become.
So, download NCache today and try it out for yourself.
