Using caching with an editable gridview

[dba56]

In an ASP.Net 2.0 page using SQL Server 2K, I have a editable gridview
that is bound to a SQLDataSource using stored procedures for its select
and update queries. The grid works fine but is slow due to rebinding
on postbacks when the edit, save or cancel buttons are clicked. So I
set "enablecache = True" for the SQLDataSource and it is much,much
faster. Clicking on edit or cancel (when in edit mode) refreshes the
grid much faster. But the problem I can't seem to solve is after
editing a row and saving it, the cache does not get updated and the
grid shows the old value from the cache. Instead of using the
enablecache property, I was thinking of saving the data to the cache
manually and rebinding only when the save button is clicked. I used to
do this by saving the dataset to cache. But I don't see a way to save
the results of a SQLDataSource "select" into a cache. Is a dataset even
generated? Is there some way to refresh the cache when the save button
is clicked without using the SQL dependency feature, which I would not
rather use at this point? I don't know if the SQL dependency feature
would update the cache that quickly anyway. Any suggestions would be
greatly appreciated. Thanks in advance for any help.

[dba56]

I found a property that is supposed to work to expire the cache but of
course it does not work. I have found others that have not been able
to get this to work.

Set the CacheKeyDependency property to something like "MyKey"

Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
Cache(SqlDataSource1.CacheKeyDependency) = someData
End If
End Sub

Then removing it from the cache, supposedly causes the cache to get
refreshed.

Sub OnItemUpdated(sender As Object, e As DetailsViewUpdatedEventArgs)
Cache.Remove(SqlDataSource1.CacheKeyDependency)
GridView1.DataBind()
End Sub

Anyone have any luck getting this to work?

dba56 wrote:

In an ASP.Net 2.0 page using SQL Server 2K, I have a editable gridview
that is bound to a SQLDataSource using stored procedures for its select
and update queries. The grid works fine but is slow due to rebinding
on postbacks when the edit, save or cancel buttons are clicked. So I
set "enablecache = True" for the SQLDataSource and it is much,much
faster. Clicking on edit or cancel (when in edit mode) refreshes the
grid much faster. But the problem I can't seem to solve is after
editing a row and saving it, the cache does not get updated and the
grid shows the old value from the cache. Instead of using the
enablecache property, I was thinking of saving the data to the cache
manually and rebinding only when the save button is clicked. I used to
do this by saving the dataset to cache. But I don't see a way to save
the results of a SQLDataSource "select" into a cache. Is a dataset even
generated? Is there some way to refresh the cache when the save button
is clicked without using the SQL dependency feature, which I would not
rather use at this point? I don't know if the SQL dependency feature
would update the cache that quickly anyway. Any suggestions would be
greatly appreciated. Thanks in advance for any help.

[dba56]

I tried so many different variations of setting the CacheKeyDependency
and nothing worked. I was able to use a little trick to get the cache
cleared. Its not a great way to do things, but this is a small
intranet web page that will be used by 2 people in our marketing
department. So it will suffice for us. In case anyone else out there
needs a quick and dirty asp.net 2.0 page, here's what worked for me:

It seems that when caching is enabled for a SqlDatasource object, a key
is generated that includes the select statement, any parameters,
CacheDuration value, CacheExpirationPolicy value and some other items.
If any of these items change, the key is changed and the cache is
expired causing the select statement to execute and regenerate the
data.

I initially have the CacheDuration set to 3600. In the
SqlDatasource_Updated event, I increment or decrement the CacheDuration
value by 5 seconds depending on the last value and then rebind the
gridview. I also store this value in a session variable. When a page
reloads, I found that the CacheDuration is automatically reset to the
original default value (3600 in my case), which would in turn cause the
cache to expire again. But since at that point I do want to use cache,
I use the session variable in the page load event to reset it back to
the last incremented/decremented value. Works like a charm and the
page is only slower when updating a row but at least you will see the
updated row, not the old cached row.

dba56 wrote:

I found a property that is supposed to work to expire the cache but of
course it does not work. I have found others that have not been able
to get this to work.

Set the CacheKeyDependency property to something like "MyKey"

Sub Page_Load(sender As Object, e As EventArgs)
If Not IsPostBack Then
Cache(SqlDataSource1.CacheKeyDependency) = someData
End If
End Sub

Then removing it from the cache, supposedly causes the cache to get
refreshed.

Sub OnItemUpdated(sender As Object, e As DetailsViewUpdatedEventArgs)
Cache.Remove(SqlDataSource1.CacheKeyDependency)
GridView1.DataBind()
End Sub

Anyone have any luck getting this to work?