SQLDependency in 3 tier application

Posted by Neerajsinghmail under ASP.NET on 8/7/2015 | Points: 10 | Views : 837 | 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;

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.




Responses

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

Up
1
Down
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