SQLDependency in 3 tier application

Posted by Neerajsinghmail under ASP.NET on 8/7/2015 | Points: 10 | Views : 2861 | Status : [Member] | Replies : 1
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;


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.

// Create a new monitor.
_monitor = new SqlChangeMonitor(_dependency);

// Create a policy.
policy = new CacheItemPolicy();
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)

// 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.


Posted by: Neerajsinghmail on: 8/12/2015 [Member] Starter | Points: 25

Finally I am able to achieve SQLDependency. I created a new class file in UI layer and created SQL connection with dependency. After by passing layering concept, I am able to get the notification.

I also tested same on my team mate dev box and it was providing change notification to me.

But now when I created a build and deployed on a new IIS server ,Its not working. I am using same SQL server box.

Any suggestion, please let me know in case I need to do any setting with new IIS server.

Neerajsinghmail, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response