need help with SqlCacheDependency

[benmartin101]

I have this code:

        protected void Button3_Click(object sender, EventArgs e)
        {
            DataView dv2 = (System.Data.DataView)Cache["SqlSource"];
            if (dv2 == null)
            {
             
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleDBConnectionString"].ConnectionString);
                SqlCommand cmd = new SqlCommand("Select id, product, warehouse, quantity from inventory", con);
                SqlCacheDependency sqlDep = new SqlCacheDependency(cmd);

                SqlDataSource1.SelectCommand = "Select id, product, warehouse, quantity from inventory";
                dv2 = (System.Data.DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);

                Cache.Insert("SqlSource", dv2, sqlDep, System.Web.Caching.Cache.NoAbsoluteExpiration,new TimeSpan(0, 0, 0, 5));
            }
            int x = dv2.Table.Rows.Count;
            Label2.Text = x.ToString();
        }

For some reason, the Cache is not expiring. What is wrong with this code? I am aware that I could do this using the SqlDataSource1.SqlCacheDependency property, but I'd like to know how to do it using the SqlCacheDependency object. Could someone tell me why this code is not working?

[danasegarane]

You are using the NoAbsoluteExpiration, means

Public Shared ReadOnly NoAbsoluteExpiration As Date
Member of System.Web.Caching.Cache
Summary:
Used in the absoluteExpiration parameter in an System.Web.Caching.Cache.Insert(System.String,System.Object) method call to indicate the item should never expire. This field is read-only.

Use this one

                Cache.Insert("SqlSource", dv2, sqlDep,,new TimeSpan(0, 0, 0, 5),System.Web.Caching.Cache.NoSlidingExpiration);

[benmartin101]

Thanks. I'll give it a try, and let you know.

[gep13]

Hello Ben,

Did this work out for you, or are you still having issues?

Thanks

Gary

[benmartin101]

Hello Ben,

Did this work out for you, or are you still having issues?

Thanks

Gary

Hey gep, sorry for the late response. Well, good news is that it is now expiring. I had to change it to this:

Cache.Insert("SqlSource", dv2, sqlDep, System.DateTime.UtcNow.Add(new TimeSpan(0,0,60)), System.Web.Caching.Cache.NoSlidingExpiration); //the 4th parameter had to be a DateTime type

So the above code basically causes the cache to expire. Bad news is, it is always expiring regardless of whether or not I make changes to the table.

Basically what I am trying to accomplish (using the methods above) is that when there are no changes to the result based on the select command, it should not expire. If there are changes, then it should expire on the given datetime/timespan.

[benmartin101]

Actually, shouldn't it also become invalidated (or null) when there changes made to the table (affecting the select statement)?

[benmartin101]

When I change:

SqlCommand cmd = new SqlCommand("Select id, product, warehouse, quantity from inventory", con);
                SqlCacheDependency sqlDep = new SqlCacheDependency(cmd);

to

SqlCacheDependency sqlDep = new SqlCacheDependency("db1", "inventory") //db1 is from the webconfig

<caching>
      <sqlCacheDependency enabled="true">
        <databases>
          <add name="db1" connectionStringName="SampleDBConnectionString" pollTime="10000"/>
        </databases>
      </sqlCacheDependency>
    </caching> ;

It works fine. The cache["SqlSource"] become invalidated/null. But how come it does not work with the original code (the one with the Select statement)?

[gep13]

Hello,

In all honesty, I am not sure

Sorry!

Gary