请教一个疑难问题

[skyaspnet]

不知道我是不是理解错误了，我设置一个Cache和数据库中表tb_cache关联，希望在这个表更改时会能时cache失效。但是，好像没有反映。
protected void Page_Load(object sender, EventArgs e)
{

DateTime storetime=new DateTime ();
if (Cache["storetime"] == null)
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["cachecon"].ConnectionString);
con.Open();
SqlCommand command = new SqlCommand("select id,sign from dbo.tb_cache where id=3",con);
SqlCacheDependency sp = new SqlCacheDependency(command);

storetime = DateTime.Now;
this.Cache.Insert("storetime", storetime,sp, DateTime.Now.AddSeconds(60), System.Web.Caching.Cache.NoSlidingExpiration,
System.Web.Caching.CacheItemPriority.High, null);
}
else
{
storetime=(DateTime)Cache["storetime"];
}
lit_time.Text = "storetime:"+storetime.ToString();
}


我在另一个页面中执行修改id=3的记录，
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["cachecon"].ConnectionString);
con.Open();
SqlCommand command = new SqlCommand("update tb_cache set sign='"+DateTime.Now.ToString ()+"' where id=3", con);
int returnvalue=command.ExecuteNonQuery();
this.Response.Write(returnvalue.ToString());

可当我修改后，刷新原来的页面还是缓存中的内容。
global.asax中也写了
void Application_Start(object sender, EventArgs e)
{
// 在应用程序启动时运行的代码
System.Data.SqlClient.SqlDependency.Start(ConfigurationManager.ConnectionStrings["cachecon"].ConnectionString);

}

void Application_End(object sender, EventArgs e)
{
// 在应用程序关闭时运行的代码
System.Data.SqlClient.SqlDependency.Stop(ConfigurationManager.ConnectionStrings["cachecon"].ConnectionString);

}

请问是不是我的数据库里设置的有问题，需要我开启什么，链接时我是使用的sa帐号进行数据库访问的。
通过SELECT * FROM sys.databases，查找到我用的那个库的is_broker_enabled=1,这个应该是已经开启了broker_service了吧？
那还有什么地方有可能有问题呢？谢谢

[freeflying1222]

sorry，你用的已经是全名了。

[skyaspnet]

感谢 freeflying1222，昨晚试了一下，加了一句表通知就可以了，

但是正如您所说，这种缓存方式使用很不方便，和SQL SERVER的结合度太高了，

不是太好控制，另外SQL SERVER会自动添加一些冗余的存储过程等，还是决定由程序控制

感谢！祝新年快乐！

[freeflying1222]

感觉你这是用的所谓的“拉”的sql2005新的sql缓存了(query-notification)。这种缓存依赖要求蛮高的：
一些参考：

For this class to work correctly when using table-based notifications, the database and any tables that you want to make dependencies on must have notifications enabled. You can enable notifications by calling methods of the SqlCacheDependencyAdmin class or by using the Aspnet_regsql.exe command-line tool. In addition, the proper configuration settings must be included in the application's Web.config file.

Using a SqlCacheDependency object with SQL Server 2005 query notification does not require any explicit configuration. Consult the SQL Server 2005 Books Online for information about restrictions on the types of Transact-SQL queries that are allowed when using query notification.

web.config
<configuration>
<connectionStrings>
<add name="Pubs" connectionString="Data Source=(local); Initial Catalog=pubs; Integrated Security=true"; providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<caching>
<sqlCacheDependency enabled = "true" pollTime = "60000" >
<databases>
<add name="pubs"
connectionStringName="pubs"
pollTime="9000000"
/>
</databases>
</sqlCacheDependency>
</caching>
</system.web>
</configuration>

SQL statements that are associated with the sqlCmd parameter must include the following:

Fully qualified table names, including the name of the table owner. For example, to refer to a table named Customersthat is owned by the database owner, the SQL statement must refer to dbo.customers.

Explicit column names in the Select statement. You cannot use the asterisk (*) wildcard character to select all columns from a table. For example, instead of select * from dbo.customers, you must use select name, address, city, state from dbo.customers.

最后，我的建议，除非特殊情况，不要用这种缓存依赖。很多问题。

[freeflying1222]

最主要的，你参考一下这句话，加上表的全名试试看

Fully qualified table names, including the name of the table owner.