Cache sql server data in asp.net until row is modified

Question

I frequently run queries in the form of:

select * from SomeTable where id=12345

Page after page I may be running that same query because the user needs to values for that row. Normally I would fetch it once and cache it, but the user can change the row at any time and other users are also making changes in SomeTable at the same time so I can't cache against the whole table. I also have the problem that we have a cluster for webservers so I can't track internally on the webserver when the cache has become invalidated.

Is there any way to detect that a change has occurred in this row? Can SqlDependecyCache class help here for individual rows to multiple web servers? Is there some trigger or other paradigm that can notify my web applications?

I want the best of both worlds - real time cache updates on the webservers, but I don't want to run any queries to see if the row has changed because I could just as easily fetch the row again.

Answer 1

What you need is to enable SqlCacheDependency as @RenusRusanu mentioned.

I have several working examples on my Github site, feel free to browse the code under my LearningProjects (3rd party edit)

• Caching SQL queries

• Page OutputCache declaratively

• Page OutputCache in code

You need to configure the database first in order to use SqlCacheDependency:

In order to use a SQL Dependency, we need to configure basically two things in the database:

1. Create the database infrastructure which consists on a table, and store procedures, to configure it we use the aspnet_regsql.exe as follows:

   /* For SQL Server authentication... */
   aspnet_regsql.exe -S server -U user -P password -d database -ed
   /* For Windows Authentication... */
   aspnet_regsql.exe -S server -E -d database -ed
   

2. Create the polling information which consists to specify the table to monitor and a trigger to that table to populate the infrastructure table needed

       /* For SQL Server authentication... */
           aspnet_regsql.exe -S server
          -U user -P password -d database -t tableName -et
           /* For Windows Authentication... */
          aspnet_regsql.exe -S server
          -E -d database -t tableName -et
   

Code configuration

        var s = new SqlCacheDependency("AdventureWorks", "Product");

        HttpContext.Current.Cache.Insert(
            "products", 
            h, 
            s, 
            Cache.NoAbsoluteExpiration, 
            Cache.NoSlidingExpiration);

The AdventureWorks name is the name of the sql dependency configured in the web.config file

<caching>
  <sqlCacheDependency enabled="true" pollTime="30000" >
    <databases>
      <add 
           name="AdventureWorks"
           connectionStringName="AdventureWorksPolling" />
    </databases>
  </sqlCacheDependency>
</caching>

The pollTime can be configured in the <add ... element and will override the global polltime

Alternatively you can rely on the SqlDataSource control

In order to enable caching when using this control, you need to set the DataSourceMode property to DataSet. In this case, you do not need to configure the database as explained before

I just uploaded this example (full working example)

ASPX

    <asp:SqlDataSource runat="server" ID="sds"
        ConnectionString="<%$ConnectionStrings:pubsConnectionString %>"
        CacheDuration="30"
        EnableCaching="true"
        SelectCommand="select * from jobs"
        DataSourceMode="DataSet"
        OnSelecting="sds_Selecting">

    </asp:SqlDataSource>
    <div>
        <asp:Literal runat="server" ID="msg" Mode="Encode"></asp:Literal>
    </div>
    <asp:GridView runat="server" DataSourceID="sds"></asp:GridView>

Edit 1

In order to fire the trigger selectively when using SqlCacheDependency, you would need to update the triggers generated manually.

When you configure a table by using the aspnet_regsql -t -et... command, a trigger is added to the specified table. This trigger is in charge to populate the AspNet_SqlCacheTablesForChangeNotification table used by the SqlCacheDependency object to poll data from the database.

The trigger looks like:

ALTER TRIGGER [dbo].[jobs_AspNet_SqlCacheNotification_Trigger] ON [dbo].[jobs]
                   FOR INSERT, UPDATE, DELETE AS BEGIN
                   SET NOCOUNT ON
                   EXEC dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedure N'jobs'
                   END

You could update the trigger to just call the dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedureN'jobs' stored procedure when the rows you are interested in are updated

I'm not a SQL expert, but I think it would be easy to find a clean way to detect the rows that have changed, for example, googling I just found this link

Most efficient method to detect column change in MS SQL Server

Answer 2

SqlCacheDependency, as well as SqlDependency, can do exactly what you describe. Both are based on Query Notifications, a technology that allows your application to receive notifications from the server when a row was updated. You can even leverage it from LINQ.

But you need to be wary, caching of values that change frequently can do more damage than good. You should only do this with values that are fairly stable.