首先,确保目标数据库的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 nameuser 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的表。
原创博文,欢迎转载。转载请注明出处。