Web服务中有时会有需要频繁读取数据库的操作,若这个数据表中的内容不长变化,
为提高性能可对数据库进行缓存,只有数据库发生变化时才去读取数据库,否则直接从缓存中读取结果。
具体实现:
1.为数据库打开broker
ALTER DATABASE database_name SET TRUSTWORTHY ON WITH ROLLBACK IMMEDIATE
ALTER DATABASE database_name SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
ALTER AUTHORIZATION ON DATABASE::database_name TO sa//此句不确定是否必须
2.代码使用
private CurrentPath GetCurrentPath()
{
ObjectCache cache = MemoryCache.Default;
CurrentPath current =(CurrentPath)cache ["CurrentPath"] ;//从缓存中读取
if (current == null)
{//若缓存中没有则从数据库读取
SqlDataReader myreader = null;
CacheItemPolicy policy = new CacheItemPolicy();
SqlDependency.Start(dbs);
using (SqlConnection dbc = new SqlConnection(dbs))
{
using (SqlCommand command = new SqlCommand("select [RootID],[Folder],[Count] from dbo.CurrentPath", dbc))
{
command.Notification = null;
SqlDependency dep = new SqlDependency();
dep.AddCommandDependency(command);//对SQL语句创建依赖
dbc.Open();
myreader = command.ExecuteReader();
if(myreader .Read() )
{
current = new CurrentPath();
current.RootID = (int)myreader["RootID"];
current.Folder = (string)myreader["Folder"];
current.Current = (int)myreader["Count"];
}
myreader.Close();
dbc.Close();
SqlChangeMonitor monitor = new SqlChangeMonitor(dep);
policy.ChangeMonitors.Add(monitor);
}
}
cache.Set("CurrentPath", current , policy);
}
return current;
}
3.注意
必须先SqlDependency.Start(dbs);
Command语句中涉及表的地方不能直接用表名称而要加dbo.前缀如dbo.CurrentPath
Command语句至少执行一次
上述函数中只有缓存中没有读到数据时Command才真正执行。当SqlChangeMonitor 检测到Command执行结果发生了变化时,
缓存失效,这样再次读取时又会执行Command