Hi there,
Values in my database need to updated periodically. The code, upon starting the application, queries the database and stores the values in the Application collection. This is to avoid making a database call everytime the values are needed (increases performance). The drawback is that changes to the database values are not updated in the code.
How can I create a database trigger that will update the C# Application colllection whenever a table value is updated?
The following article gave me some good insight on how to integrate the .NET CLR with SQL Server 2005.
http://msdn2.microsoft.com/en-us/library/ms345136.aspx
I can now create triggers to run managed C# code. However, the CLR is limited because I cannot reference the System.Web.HttpApplicationState object or anything to do with HTTP requests; so I can't update my Application collection.
However, the CLR does let me access System.Web.Services. So I should be able to make a web service call to update the variables. More to come on this....
|||I solved my issue. Seems like I was going down the wrong path. The SqlCacheDependency class is what I needed. I can set up a cache dependency on a SQL table so that when values in that table change the associated cache value will be invalid.
Here's a good article on SqlCacheDependency ->http://www.ondotnet.com/pub/a/dotnet/2005/01/17/sqlcachedependency.html?page=last
This increases performance significantly! Awesome!
