Hello All,
I have created a MVC project with below architecture-
1. UI Layer
2. Business Layer
3. Data Access layer
I am using Memory Caching (System.Runtime.Caching) and SQLDependency to auto refresh my cache once any change happen in database table. I did my all SQLDependency & Cache code on my data access layer page. But My cache is not getting updated on database change.
Code Sample -
****************************************************************************************
Start by declaring a few global variables.
private static MemoryCache _cache;
private SqlChangeMonitor _monitor;
private SqlDependency _dependency;
private bool _hasDataChanged;
At the constructor of the class, initialize the _cache and call the SqlDependency.Start() method.
_cache = MemoryCache.Default;
SqlDependency.Start(CONNECTION_STRING);
Next, create the main function that loads the data into a CacheItem:
private CacheItem LoadData(out CacheItemPolicy policy)
{
const string SQL_STATEMENT = "SELECT [ID], [Data] FROM dbo.TestData";
var data = new List<TestData>();
var db = new SqlDatabase(CONNECTION_STRING);
using (DbCommand cmd = db.GetSqlStringCommand(SQL_STATEMENT))
{
// Initialize SqlDependency
_dependency = new SqlDependency(cmd as SqlCommand);
_dependency.OnChange += dependency_OnChange;
using (IDataReader dr = db.ExecuteReader(cmd))
{
while (dr.Read())
{
// Create a new TestData
var testData = new TestData();
// Read values.
testData.ID = GetDataValue<int>(dr, "ID");
testData.Data = GetDataValue<string>(dr, "Data");
// Add to List.
data.Add(testData);
}
}
}
// Create a new monitor.
_monitor = new SqlChangeMonitor(_dependency);
// Create a policy.
policy = new CacheItemPolicy();
policy.ChangeMonitors.Add(_monitor);
policy.UpdateCallback = CacheUpdateCallback;
// Put results into Cache Item.
var item = new CacheItem("TestData", data);
// Reset the data changed flag.
_hasDataChanged = false;
return item;
}
void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
// DataChange Detection
_hasDataChanged = true;
}
private void CacheUpdateCallback(CacheEntryUpdateArguments args)
{
// Dispose of monitor
if (_monitor != null)
_monitor.Dispose();
// Disconnect event to prevent recursion.
_dependency.OnChange -= dependency_OnChange;
// Refresh the cache if tracking data changes.
if (_hasDataChanged)
{
// Refresh the cache item.
CacheItemPolicy policy;
args.UpdatedCacheItem = LoadData(out policy);
args.UpdatedCacheItemPolicy = policy;
}
}
Finally, the method that we want to expose out to callers to call will be like the following:
public List<TestData> Select()
{
if (_cache["TestData"] == null)
{
// Create a policy.
CacheItemPolicy policy = null;
// Load data into Cache Item.
var item = LoadData(out policy);
// Set Cache Item into cache with the policy.
_cache.Set(item, policy);
}
return _cache["TestData"] as List<TestData>;
}
******************************************************************************************
For a POC I did similar code on a single page web application and its working fine and getting refreshed data from database once any change happening in database table. Please let me know in case I need to move my code on any other layer.
I will be very thankful if any body can help me to resolve this problem as soon as possible.