Geek asks and answers

Sql server caching data in asp.net before row change

I often run queries in the form:

select * from SomeTable where id=12345

Page by page I can run the same query because the user needs values ​​for this row. I usually got it once and cached it, but the user can change the row at any time, and other users also make changes to SomeTable at the same time, so I can not cache the whole table. I also have a problem that we have a cluster for web servers, so I cannot track internally on the web server when the cache has become invalid.

Is there any way to detect that a change has occurred on this line? Can the SqlDependecyCache class help here for single lines on multiple web servers? Is there some kind of trigger or other paradigm that can notify my web apps?

I want the best of both worlds - updating the cache in real time in web servers, but I do not want to run any queries to see if the line has changed, since I could just as easily get the line again.

+5
source share
2 answers

You need to enable SqlCacheDependency as indicated in @RenusRusanu.

I have some working examples on my Github site , feel free to view the code under my training projects (third-party editing)

, SqlCacheDependency:

SQL Dependency, :

  • , , aspnet_regsql.exe :

    /* 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

  • , , ,

        /* 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


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

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

AdventureWorks - sql, web.config

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

pollTime <add ... pollTime

SqlDataSource

, DataSourceMode DataSet. ,

( )

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>

1

SqlCacheDependency, , .

aspnet_regsql -t -et..., . AspNet_SqlCacheTablesForChangeNotification, SqlCacheDependency, .

:

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

, dbo.AspNet_SqlCacheUpdateChangeIdStoredProcedureN'jobs',

SQL, , , , , googling. .

MS SQL Server

+3

SqlCacheDependency, SqlDependency, , . , . LINQ.

, , , , . .

+2

More articles:


All Articles