Differences between SqlDependency and SqlCacheDependency

Question

I do have a question: I'm working on an ASP.Net Web Forms and C# app and I use a gridView in order to show the data from a table so I've decided to cache.

I did the

aspnet_regsql -ed -E -d Store
aspnet_regsql -et -E -d Store-t Customers

and the modifications in the web.config:

<caching>
      <sqlCacheDependency pollTime="2000" enabled="true">
        <databases>
          <add name="Store" connectionStringName="StoreConnectionString"/>
        </databases>
      </sqlCacheDependency>
    </caching>

But now I have to decide if use SqlDependency

<%@ OutputCache Duration=”600″ SqlDependency=”Store:Customers” VaryByParam=”none” %>

Or use the SqlCacheDependency

private void BindData() { 
  if (Cache["Users"] == null) {            
        SqlCacheDependency dep = new SqlCacheDependency("Store", "Customers");
        string connectionString = ConfigurationManager.ConnectionStrings[
                                        "ConnectionString"].ConnectionString;
        SqlConnection myConnection = new SqlConnection(connectionString);
        SqlDataAdapter ad = new SqlDataAdapter("SELECT FirstName, LastName " +
                                               "FROM Users", myConnection);
        DataSet ds = new DataSet();
        ad.Fill(ds);
        Cache.Insert("Cust", ds, dep);
    }
    gvUsers.DataSource = Cache["Cust"] as DataSet;
    gvUsers.DataBind();
}

Could you please tell me what are the differences between SqlDependency and SqlCacheDependency and which one is better for my code please?

Pablo Tobar · Accepted Answer · 2015-11-04T22:37:40.010

SqlDependency is likely to use in the page directive as property for outputcache, the most important aspect is that you have to specify your connectionstring in the Web.config(as you may know that is a security risk) and also use the polltime attribute in the tag .

SqlCacheDependency is a class and you will need to specify the data that want to add to the cache via cache.insert or cache.add, you don't need to specify the connectionstring in the Web.config but maybe you will likely use SERVICE_BROKER instead of aspnet_regsql and , also if you decided to use SERVICE_BROKER remember to add a global.asax in order to specify the:

Application_start(){
string connectionString = yourdatabaseconnection;
    System.Data.SqlClient.SqlDependency.Start(connectionString);
}

and the App_end()

Application_end(){
string connectionString = yourdatabaseconnection;
    System.Data.SqlClient.SqlDependency.Stop(connectionString);
}

since I' already use the aspnet_regsql command I would likely use outputchache directive page and SqlDependency but with a greater polltime, but my final recommendation would be to use SqlCacheDependency and enable SERVICE_BROKER via

ALTER DATABASE testdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

Differences between SqlDependency and SqlCacheDependency

1 Answers1