首页 > 代码库 > SqlCacheDependency的使用
SqlCacheDependency的使用
最近项目需要几秒就获取一次数据,查数据库服务器压力会很大,因此用缓存技术来缓解服务器压力。
使用SqlCacheDependency采用轮询的方式来获取缓存,SqlDependency查询通知的方式来更新缓存应该更好,先说轮询的方式吧。
配置文件如下:
<configuration>
<system.web>
<compilation debug="true" targetFramework="4.5" />
<httpRuntime targetFramework="4.5" />
<caching>
<sqlCacheDependency enabled="true" pollTime="10000">
<databases>
<add name="msBaby" connectionStringName="conn"/>
</databases>
</sqlCacheDependency>
</caching>
</system.web>
<connectionStrings>
<add name="conn" connectionString="data source=SH-05\SA;initial catalog=Baby;user id=sa;password=123456;Asynchronous Processing=true;MultipleActiveResultSets=true;Integrated Security=True"/>
</connectionStrings>
</configuration>
其中 connectionStringName="conn"是connectionStrings的name,name="msBaby" 中,msBaby就是数据库名称。
以下是代码示例:
基类
public class BaseCache
{
private string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
/// <summary>
/// 获取缓存数据
/// </summary>
/// <param name="cacheName">缓存名</param>
/// <param name="dataBaseName">数据库名</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public DataTable GetCacheData(string cacheName, string dataBaseName, string tableName)
{
DataTable data = http://www.mamicode.com/(DataTable)HttpRuntime.Cache[cacheName]; //获取缓存
// 判断缓存数据为空
if (data =http://www.mamicode.com/= null)
{
// 获取数据
data = http://www.mamicode.com/GetDataSource();
SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString, tableName);
SqlCacheDependency depend = new SqlCacheDependency(dataBaseName, tableName);
HttpContext.Current.Cache.Insert(cacheName, data, depend);
}
return data;
}
public virtual DataTable GetDataSource()
{
return null;
}
}
数据库帮助类
public class DBHelper
{
private static string connectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
public static DataTable GetData(string sql, params SqlParameter[] parameters)
{
DataTable data;
try
{
SqlConnection conn = new SqlConnection(connectionString);
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddRange(parameters);
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
data = http://www.mamicode.com/new DataTable();
dataAdapter.Fill(data);
}
}
catch (Exception)
{
throw;
}
return data;
}
}
子类重写GetDataSource方法
public class Job : BaseCache
{
public override DataTable GetDataSource()
{
return DBHelper.GetData("SELECT TOP 50 * FROM job");
}
}
外部类中调用
Job job = new Job();
DataTable dt = job.GetCacheData("jobCache", "msBaby", "job");
这里的msBaby与webconfig中相同,job是数据库表名称。
<databases>
<add name="msBaby" connectionStringName="conn"/>
</databases>
SqlCacheDependency的使用