Here’s a new exciting lesson I learned when working with my recent project. The challenge is to create a Price Screen for Commodity Trading that will update on real time. This should be done as optimize as possible for this will be shown over the Net. We usually save reusable data in Application variable or in Session variable for optimization purpose. Here is a sample code that we usually did to persist data.
public static DataTable ExecuteDataTable(persistIn persistence, string
storedProcedureName, params object[] parameterValues)
{
DataTable
returnTable = null;
switch (persistence)
{
case persistIn.Session:
returnTable = (DataTable)HttpContext.Current.Session
[storedProcedureName.ToUpper()];
if (returnTable == null)
{
returnTable = db.ExecuteDataSet(storedProcedureName,
parameterValues).Tables[0];
HttpContext.Current.Session
[storedProcedureName.ToUpper()] = returnTable;
}
break;
case
persistIn.Application:
returnTable =
(DataTable)HttpContext.Current.Application [storedProcedureName.ToUpper()];
if (returnTable == null)
{
returnTable =
db.ExecuteDataSet(storedProcedureName, parameterValues).Tables[0];
HttpContext.Current.Application [storedProcedureName.ToUpper()] =
returnTable;
}
break;
case persistIn.NoPersistence:
returnTable
= db.ExecuteDataSet(storedProcedureName, parameterValues).Tables[0];
break;
default:
break;
}
return returnTable;
}
Here, the result returned by a call to a stored procedure is saved in Server Memory, either in Session of Application. When the Data already exists there is no need to call for the stored procedure again, it just fetches the data in Server Memory and return it. The problem here is when there are changes on the data from the database the application won’t fetch the new item unless you force to change the value stored on the Server Variable, this can be done by calling this function and passing persistIn.NoPersistence as parameter.
The solution here is use SQLCacheDependency. SQLCacheDependency has the capability to detect changes on Database and delete specific data on memory that depends on it. What you need to do is to add dependency on table that will tell the application that its time to fetch a new data. So when someone updates that table the application will fetch a new data for the variable is lost on the memory.
But you need to configure your database and add some code for this to work. I listed below the steps to do to be able to use SQLCacheDependency:
1. Update your database by Running the script in SQL.
ALTER DATABASE YourDatabase SET ENABLE_BROKER; GO
This will enable Service Broker that will raise notification to ASP for changes on database
2. On your Global.asax, you should add one if you don’t have this yet, add the following line:
1: string connectionString = ConfigurationManager.
ConnectionStrings[0].ConnectionString;
2:
System.Data.SqlClient.SqlDependency.Start(connectionString);
3:
SqlCacheDependencyAdmin. EnableNotifications(connectionString);
4:
SqlCacheDependencyAdmin. EnableTableForNotifications (connectionString,
"TableName");
Line 1 will retrieve the connection string that you are using in your web app. The user you used on the Connection string should have the full access in your database.
Line 2 Starts the listening in the event
Line 3 Adds some stored procedure needed for notifications
Line 4 Adds Trigger to table that you are watching
3. Add the following on your web.config inside <system.web>:
<caching>
<sqlCacheDependency enabled = "true" pollTime
= "1000" >
<databases>
<add name="Database"
connectionStringName="MyConnection"
pollTime="1000"/>
</databases>
</sqlCacheDependency>
</caching>
The Name attribute will be used by the SQLCacheDependency to know how it will connect to the database.
The ConnectionStringName attribute will map the connection you are using in web.config.
4. Now you are ready to use SQLCacheDependency on your code as written bellow:
public static DataSet ExecuteDataTable(string storedProcedureName, params
object[] parameterValues)
{
DataSet myDataset = new DataSet();
if
(HttpContext.Current.Cache[storedProcedureName.ToUpper()] == null)
{
SqlCommand command = new SqlCommand();
command.CommandType =
CommandType.StoredProcedure;
command.CommandText = " MyStoredProcedure";
SqlCacheDependency dependency = new SqlCacheDependency("Database",
"TableName");
myDataset = db.ExecuteDataSet(storedProcedureName,
parameterValues);
HttpContext.Current.Cache.Insert(storedProcedureName.ToUpper(),
myDataset,
dependency);
}
return myDataset;
}
Then that’s it. You will experience the magic of it! If you will check your database once you have run your application, there are several new stored procedures and tables added by ASP. This is use for notification purpose, don’t erase those.
Now what I did on my Project is Add an AJAX Timer that will check if the Cache still exists, if not it will update the screen to display new data. It is a real-time base on web. Amazing!
