How SQLCacheDependency Synchronizes Distributed Cache with Database?

Distributed Caching has become a popular way of improving .NET application performance and scalability. That is why developers are caching more and more data in distributed cache. However along with this come a few challenges. One important challenge is to ensure that data in the cache is always synchronized with the database. This is because the cache is keeping a copy of the data that already exists in the database.

If you have multiple applications updating the same data in the database but not all of them have access to the distributed cache, you’ll end up with a situation where data in the cache is older and different than its counterpart in the database. And, while this may be okay for some reference type of data, it is definitely not acceptable for transactional data. Reference data is one that you read a lot but don’t modify very frequently (e.g. product catalog) while transactional data is something you read and modify frequently (e.g. customer or account data).

How do you ensure that the distributed cache stays synchronized with the database?

The answer is SqlCacheDependency. SqlCacheDependency is part of ASP.NET Cache (System.Web.Caching) and allows you to specify a dataset in the database with an SQL statement and then receive .NET event notifications from SQL Server 2005/2008 whenever your dataset is modified in the database.

NCache has internally incorporated SqlCacheDependency for the purpose of synchronizing cache with SQL Server 2005/2008 or Oracle database. To you, NCache provides a similar interface called SqlDependency that allows you to specify an SQL statement representing one or more rows in a given table that make up your cached item. NCache then internally uses SqlCacheDependency to establish a link with the database against these rows.

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 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 it then caches as well. This way, NCache ensures that the data in the cache is always consistent with the data in the database.

Here is a source code example of using SqlDependency of NCache that internally uses SqlCacheDependency:

public class Program {

    // A standard Load method that loads a single row from database
    public Customer LoadCustomer(Customer cust)
	String key = "Customer:CustomerID:" + cust.CustomerID;

	Customer cust2 = (Customer)NCache.Cache.Get(key);
	if (cust2 != null)
	   return cust2;

	CustomerFactory custFactory = new CustomerFactory();

	// Load a single customer from the database
	// SELECT * FROM Customers WHERE CustomerID = 'ALFKI'

	// Create a SqlCacheDependency for this item
	CacheItem item = new CacheItem(cust);
	item.Dependency = SqlDependencyFactory(connectionString,
	    "SELECT CustomerID FROM Customers WHERE CustomerID = '"
		+ cust.CustomerID + "'");

	// Store item in the cache along with SqlCacheDependency
	NCache.Cache.Insert(key, item);
	return cust;

    // A query method
    public List<Customer> FindCustomers(String city)
	String key = "List<Customer>:City:" + city;
	List<Customer> custList = (List<Customer>)NCache.Cache.Get(key);
	if (custList != null)
	    return custList;

	CustomerFactory custFactory = new CustomerFactory();

	// Load a list of customers from database based on a criteria
	// SELECT * FROM Customers WHERE City = 'San Francisco'
	custList = custFactory.FindByCity(city);

	// Create a SqlCacheDependency for this list of customers
	CacheItem item = new CacheItem(custList);
	item.Dependency = SqlDependencyFactory.(connectionString,
	    "SELECT CustomerID FROM Customers WHERE City = '" + city + "'");

	// Store list of customers in the cache along with SqlCacheDependency
	NCache.Cache.Insert (key, item);
	return custList;

In summary, SqlDependency feature of NCache allows you to synchronize cache with the database and maintain 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 becomes.

So, download a fully working 60-day trial of NCache Enterprise and try it out for yourself.

Download NCache Trial | NCache Details

Avatar for Iqbal Khan

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *