How can we apply Sql Dependency in Asp.Net MVC for cached objects?
+1
A:
To set up a dependency, you need to associate a SqlDependency object to one or more SqlCommand objects. To receive notifications, you need to subscribe to the OnChange event.
If you are using an ORM for your data model, I'm not sure how this applies.
More info here:
Robert Harvey
2010-02-03 14:43:35
Well, SqlDependency requires a SQL query which can only use features allowed in SQL Server query notifications. Since your ORM very likely does not know about this feature, it means you have to write a standard SQL query. Your ORM will very likely be able to give you the SQL query it will use to execute a certain ORM-style query. So your choices are to manually write the query which covers the data which is likely to change, or use the ORM's query, and hope that it falls within the rules of what is allowed for a SQL Server query notification.
Craig Stuntz
2010-02-03 14:47:13
@Craig: Thanks for the info.
Robert Harvey
2010-02-03 14:49:33
One other thing I forgot to add: Query notifications put a certain degree of load on the SQL Server. So you would prefer to use as few of them as possible. Therefore, if you are composing queries via your ORM, you probably don't want to create a query notification for every ORM query you compose. You would probably prefer to use a single, manually written query, which could provide appropriate notifications for a wide variety of ORM queries which touch the same data.
Craig Stuntz
2010-02-03 14:51:56
Current versions of SQL Server do not use polling to implement query notifications.
Craig Stuntz
2010-02-03 14:48:26