首先,确保目标数据库的is_broker_enabled已经enabled。
SELECT name, is_broker_enabled FROM sys.databases
如果不是enabled,使用以下语句
ALTER DATABASE DB_Name SET ENABLE_BROKER
GO
使用以下语句授权指定用户订阅Query Notifications
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO "domain name\user name"
以下是c# 源码
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Caching;
public class WebCacheHelper<T> where T : class
{
public event FetchDataFromDBHandler<T> OnFetchDataFromDB;
public T GetFromCache(string connectionString, string tableName, string cacheKey)
{
var cache = HttpRuntime.Cache;
T data = null;
if (cache[cacheKey] != null)
{
return cache[cacheKey] as T;
}
using (SqlConnection con = new SqlConnection(connectionString))
using (SqlCommand cmd =
new SqlCommand(string.Format("select 1 from dbo.{0}", tableName)
, con))
{
bool started = SqlDependency.Start(connectionString);
con.Open();
cmd.Notification = null;
cmd.NotificationAutoEnlist = true;
SqlCacheDependencyAdmin.EnableNotifications(connectionString);
if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(
connectionString).Contains(tableName))
{
SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString, tableName);
}
SqlCacheDependency dependency = new SqlCacheDependency(cmd);
if (OnFetchDataFromDB != null)
data = OnFetchDataFromDB(started);
cache.Insert(cacheKey, data, dependency);
cmd.ExecuteNonQuery();
}
return data;
}
}
public delegate T FetchDataFromDBHandler<T>(bool IsSqlDependecyStarted);
Sample
public class Movie
{
public int ID { get; set; }
public string Title { get; set; }
[Display(Name = "Release Date")]
[DataType(DataType.Date)]
[DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}", ApplyFormatInEditMode = true)]
public DateTime ReleaseDate { get; set; }
public string Genre { get; set; }
public decimal Price { get; set; }
}
public class MovieDBContext : DbContext
{
public DbSet<Movie> Movies { get; set; }
}
public class MovieController : Controller
{
private MovieDBContext db = new MovieDBContext();
public ActionResult List()
{
WebCacheHelper<IEnumerable<Movie>> helper = new WebCacheHelper<IEnumerable<Movie>>();
helper.OnFetchDataFromDB += x =>
{
var query = from p in db.Movies
select p;
ViewBag.DataInitilized = "Cache data initialized at " + DateTime.Now.ToLongTimeString();
ViewBag.DependencyStarted = x;
return query.ToList();
};
var model = helper.GetFromCache(db.Database.Connection.ConnectionString, "Movies", "Movie");
return View(model);
}
}
SQL Server2008实测运行成功。 C#代码执行以后,数据库中将自动生成一个名为AspNet_SqlCacheTablesForChangeNotification的表。
原创博文,欢迎转载。转载请注明出处。