public static void EnableSqlCacheDependency <TEntity>(this Table <TEntity> table) where TEntity : class { Type entityType = typeof(TEntity); MetaTable metaTable = table.Context.Mapping.GetTable(entityType); SqlCacheDependencyAdmin.EnableTableForNotifications(table.Context.Connection.ConnectionString, metaTable.TableName); }
/// <summary> /// 数据依赖缓存 /// </summary> /// <param name="key"></param> /// <param name="valueInserted">对象</param> /// <param name="connectionString"></param> /// <param name="dependencyTableName">与 SqlCacheDependency 关联的数据库表的名称</param> /// <param name="databaseEntryName">在应用程序的 Web.config 文件的 caching 的 sqlCacheDependency 的 databases 元素(ASP.NET 设置架构)元素中定义的数据库的名称。 </param> public static void Insert( string key, object valueInserted, string connectionString, string dependencyTableName, string databaseEntryName) { SqlCacheDependencyAdmin.EnableNotifications(connectionString); SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString, dependencyTableName); Insert(key, valueInserted, new SqlCacheDependency(databaseEntryName, dependencyTableName)); }
protected void enableTable_Click(object sender, EventArgs e) { try { if (tableName.Text.Contains(";")) { string[] tables = tableName.Text.Split(new Char[] { ';' }); for (int i = 0; i < tables.Length; i++) { tables[i] = tables[i].Trim(); } SqlCacheDependencyAdmin.EnableTableForNotifications(ConnString.SqlServer, tables); } else { SqlCacheDependencyAdmin.EnableTableForNotifications(ConnString.SqlServer, tableName.Text); } } catch (HttpException ex) { enableTableErrorMsg.Text = "<br />" + "An error occured enabling a table.<br />" + "The error message was: " + ex.Message; enableTableErrorMsg.Visible = true; } }
/// <summary> /// 启动应用程序 /// </summary> protected void Application_Start() { NFine.Application.AutoMapper.Configuration.Configure();//注册DTO映射(AutoMapper) AreaRegistration.RegisterAllAreas(); GlobalConfiguration.Configure(WebApiConfig.Register); FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters); RouteConfig.RegisterRoutes(RouteTable.Routes); //DBConnection.Encrypt = true;//开启数据库连接字符串加密功能 var connectionString = DBConnection.connectionString; //启动数据库的数据缓存依赖功能 SqlCacheDependencyAdmin.EnableNotifications(connectionString); string[] tables = { "Web_Attachment", "Web_Content", "Web_Node", "Web_FriendLinks", "Sys_Items", "Sys_ItemsDetail", "Sys_Module", "Sys_ModuleButton", "Sys_ModuleForm", "Sys_Role", "Sys_RoleAuthorize", "Sys_User", "Sys_UserLogOn", "Sys_Area", "Sys_Log", "Sys_Organize", "Sys_BackupServer", "Sys_FileBackup", "Web_Picture" }; //启用数据表缓存 SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString, tables); //注册缓存依赖的服务代码:在vs命令行输入:aspnet_regsql -S . -U sa -P 123 -ed -d NFineBase -et -t Web_Content //-S localhost:数据库地址 //-U sa:数据库登录名 //-P 123456:数据库登录密码 //-d wcfDemo:数据库的名称 //-t user:表名称(小写) //消息队列处理,需要服务器部署redis //MessageQueueConfig.RegisterExceptionLogQueue(); }
public static void InitialSqlDependencyDataBase() { var conStr = ConfigurationManager.ConnectionStrings["WorkDbContext"].ConnectionString; SqlCacheDependencyAdmin.EnableNotifications(conStr); SqlCacheDependencyAdmin.EnableTableForNotifications(conStr, "Employee"); }
protected void Page_Load(object sender, EventArgs e) { string CS = @"Data Source=DESKTOP-661ODQ1;Initial Catalog=ASPDOTNETDB;Integrated Security=True"; SqlCacheDependencyAdmin.EnableNotifications(CS); SqlCacheDependencyAdmin.EnableTableForNotifications(CS, "tblProducts"); }
public static List<KeyValuePair<string,string>> GetData() { var cache = System.Web.HttpContext.Current.Cache; SqlCacheDependency SqlDep = null; var modules = Cache["Modules"] as List<KeyValuePair<string,string>>; if (modules == null) { // Because of possible exceptions thrown when this // code runs, use Try...Catch...Finally syntax. try { // Instantiate SqlDep using the SqlCacheDependency constructor. SqlDep = new SqlCacheDependency("Test", "SD_TABLES"); } // Handle the DatabaseNotEnabledForNotificationException with // a call to the SqlCacheDependencyAdmin.EnableNotifications method. catch (DatabaseNotEnabledForNotificationException exDBDis) { SqlCacheDependencyAdmin.EnableNotifications("Test"); } // Handle the TableNotEnabledForNotificationException with // a call to the SqlCacheDependencyAdmin.EnableTableForNotifications method. catch (TableNotEnabledForNotificationException exTabDis) { SqlCacheDependencyAdmin.EnableTableForNotifications("Test", "SD_TABLES"); } finally { // Assign a value to modules here before calling the next line Cache.Insert("Modules", modules, SqlDep); } } return modules; }
protected AggregateCacheDependency GetAggregateCacheDependency <T>() { AggregateCacheDependency acd = new AggregateCacheDependency(); IEnumerable <string> tables = GetTables <T>(); foreach (string table in tables) { SqlCacheDependency cd = null; try { cd = new SqlCacheDependency(DatabaseEntryName, table); } catch (DatabaseNotEnabledForNotificationException ex) { SqlCacheDependencyAdmin.EnableNotifications(ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString); SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString, table); } catch (TableNotEnabledForNotificationException ex) { SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings[ConnectionStringName].ConnectionString, table); } if (cd == null) { cd = new SqlCacheDependency(DatabaseEntryName, table); } acd.Add(cd); } return(acd); }
/// <summary> /// Caches Linq query´s that is created for LinqToSql. /// Limitations are the same as SqlCacheDependency /// </summary> /// <typeparam name="T"></typeparam> /// <param name="q">The linq query</param> /// <param name="dc">Your LinqToSql DataContext</param> /// <param name="CacheId">The unique Id for the cache</param> /// <returns></returns> public static List <T> LinqCache <T>(this IQueryable <T> q, DataContext dc, string CacheId) { try { List <T> objCache = (List <T>)HttpRuntime.Cache.Get(CacheId); if (objCache == null) { /////////No cache... implement new SqlCacheDependeny////////// //1. Get connstring from DataContext var connStr = Properties.Settings.Default.ioschoolsConnectionString; //2. Get SqlCommand from DataContext and the LinqQuery string sqlCmd = dc.GetCommand(q).CommandText; //3. Create Conn to use in SqlCacheDependency using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); //4. Create Command to use in SqlCacheDependency using (SqlCommand cmd = new SqlCommand(sqlCmd, conn)) { //5.0 Add all parameters provided by the Linq Query foreach (System.Data.Common.DbParameter dbp in dc.GetCommand(q).Parameters) { cmd.Parameters.Add(new SqlParameter(dbp.ParameterName, dbp.Value)); } //5.1 Enable DB for Notifications... Only needed once per DB... SqlCacheDependencyAdmin.EnableNotifications(connStr); //5.2 Get ElementType for the query var attrib = (TableAttribute)Attribute.GetCustomAttribute(q.ElementType, typeof(TableAttribute)); string notificationTable = attrib.Name; //5.3 Enable the elementtype for notification (if not done!) if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(notificationTable)) { SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, notificationTable); } //6. Create SqlCacheDependency SqlCacheDependency sqldep = new SqlCacheDependency(cmd); // - removed 090506 - 7. Refresh the LinqQuery from DB so that we will not use the current Linq cache // - removed 090506 - dc.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, q); //8. Execute SqlCacheDepency query... cmd.ExecuteNonQuery(); //9. Execute LINQ-query to have something to cache... objCache = q.ToList(); //10. Cache the result but use the already created objectCache. Or else the Linq-query will be executed once more... HttpRuntime.Cache.Insert(CacheId, objCache, sqldep); } } } //Return the created (or cached) List return(objCache); } catch (Exception ex) { Syslog.Write(ex); throw; } }
public static List <T> FromCache <T>(this IQueryable <T> q, ICache currentCache, string sqlConnStr) { var queryObject = (ObjectQuery)(q); string sqlCmd = queryObject.ToTraceString(); var cacheKey = string.Format("{0}__{1}", sqlCmd.GetMd5Sum(), string.Join(",", queryObject.Parameters.OrderBy(a => a.Name).Select(a => a.Value).ToArray())); List <T> objCache = (List <T>)(currentCache.Get(cacheKey)); if (objCache == null) { using (SqlConnection conn = new SqlConnection(sqlConnStr)) { conn.Open(); SqlCommand cmd = new SqlCommand(sqlCmd, conn); foreach (var param in queryObject.Parameters) { cmd.Parameters.AddWithValue(param.Name, param.Value); } foreach (SqlParameter param in cmd.Parameters) { sqlCmd = sqlCmd.Replace("@" + param.ParameterName, "'{0}'".FormatWith(param.Value.ToString().Replace("'", "''"))); } cmd = new SqlCommand(sqlCmd, conn); using (cmd) { SqlCacheDependency sqlDep = null; CreateDependency: try { sqlDep = new SqlCacheDependency(cmd); } catch (DatabaseNotEnabledForNotificationException) { SqlCacheDependencyAdmin.EnableNotifications(sqlConnStr); goto CreateDependency; } catch (TableNotEnabledForNotificationException ex) { ((string[])(ex.Data["Tables"])) .Where(table => !SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(sqlConnStr).Contains(table)).ToList() .ForEach(table => SqlCacheDependencyAdmin.EnableTableForNotifications(sqlConnStr, table)); goto CreateDependency; } var reader = cmd.ExecuteReader(); string results = string.Empty; while (reader.Read()) { } objCache = q.ToList(); currentCache.Insert(cacheKey, objCache, sqlDep); } } } return(objCache); }
/// <summary> /// SQL缓存依赖 用于SQL2000数据库 sql2005不需要 /// </summary> /// <param name="table"></param> /// <returns></returns> public static SqlCacheDependency GetDependency(string ConnectionString_SQL2000, string table) { SqlCacheDependencyAdmin.EnableNotifications(ConnectionString_SQL2000); //启动数据库的数据缓存依赖功能 SqlCacheDependencyAdmin.EnableTableForNotifications(ConnectionString_SQL2000, table); //启用数据表缓存 SqlCacheDependency dependency = new SqlCacheDependency( new System.Data.SqlClient.SqlConnectionStringBuilder( ConnectionString_SQL2000 ).InitialCatalog, table); return(dependency); }
protected void Application_Start() { DbConfig.InitializeDatabase(); AreaRegistration.RegisterAllAreas(); FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters); RouteConfig.RegisterRoutes(RouteTable.Routes); BundleConfig.RegisterBundles(BundleTable.Bundles); AutoMapperConfig.Config(Assembly.Load(ServerModelsAssembly)); SqlCacheDependencyAdmin.EnableNotifications(ConnectionString); SqlCacheDependencyAdmin.EnableTableForNotifications(ConnectionString, CountriesTable); }
public void PrepareCachingMode(CachingMode mode) { if (mode == CachingMode.Polling) { SqlCacheDependencyAdmin.EnableNotifications(ConnectionString); SqlCacheDependencyAdmin.EnableTableForNotifications(ConnectionString, "Production.Product"); } else if (mode == CachingMode.Notification) { SqlDependency.Start(ConnectionString); } }
private static void SetAuth() { lock (typeof(AuthCacheConfig)) { SqlCacheDependency dep = null; try { dep = new SqlCacheDependency("BlueCarGps", "User"); } catch (DatabaseNotEnabledForNotificationException exDBDis) { LogUtil.Logger.Error(exDBDis.Message, exDBDis); try { SqlCacheDependencyAdmin.EnableNotifications(Settings.Default.db); } catch (UnauthorizedAccessException ex) { LogUtil.Logger.Error(ex.Message, ex); } } catch (TableNotEnabledForNotificationException exTabDis) { LogUtil.Logger.Error(exTabDis.Message, exTabDis); try { SqlCacheDependencyAdmin.EnableTableForNotifications(Settings.Default.db, "User"); } catch (SqlException ex) { LogUtil.Logger.Error(ex.Message, ex); } } finally { //HttpRuntime.Cache.Insert( // cacheKey, // new UserService(Settings.Default.db).All(), // dep, // Cache.NoAbsoluteExpiration, // Cache.NoSlidingExpiration, // CacheItemPriority.High, // null); HttpRuntime.Cache.Insert( cacheKey, new UserService(Settings.Default.db).All(), dep); } } }
public static void SetDependency() { string conn = ConfigurationManager.AppSettings["Whiskey-Database-String"]; string connString = ConfigurationManager.ConnectionStrings[conn].ConnectionString; string[] tables = new string[] { "W_Storage", "A_Department", "S_Store", "P_Product", "P_Brand", "P_Category", "P_Size", "P_Color", "P_Season", "W_Storage", "W_OrderBlank_Item", "W_OrderBlank", "A_Administrator", "M_Member", "F_Factorys", //"A_MemberRole_MemberModule_Relation","A_Member_MemberRole_Relation","A_MemberModule","M_MemberRole","A_Administrator_Type", "A_Module", "A_Permission", "A_Role", "T_Template", "P_ProductOrigNumber", "P_Product_Attribute", "T_TemplateTheme", "A_QrLogin", "O_JobPosition", "S_StoreType", "P_ProductCrowd" }; SqlCacheDependencyAdmin.EnableNotifications(connString); SqlCacheDependencyAdmin.EnableTableForNotifications(connString, tables); }
private static void SetCacheData(string cacheItemName, object dataSet, string connString, string tableName) { string cacheEntryname = "NorthwindCache"; //Many articles online state that the following statement should be run only once at the start of the application. //Few articles say the object get replaced if called again with same connection string. SqlDependency.Start(connString); //The following statements needs to be run only once against the connection string and the database table, hence may also be moved to an appropriate place in code. SqlCacheDependencyAdmin.EnableNotifications(connString); SqlCacheDependencyAdmin.EnableTableForNotifications(connString, tableName); System.Web.Caching.SqlCacheDependency dependency = new System.Web.Caching.SqlCacheDependency(cacheEntryname, tableName); HostingEnvironment.Cache.Insert(cacheItemName, dataSet, dependency); }
void Application_Start(object sender, EventArgs e) { // 在应用程序启动时运行的代码 AreaRegistration.RegisterAllAreas(); GlobalConfiguration.Configure(WebApiConfig.Register); RouteConfig.RegisterRoutes(RouteTable.Routes); //配缓存数据库依赖 string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ToString(); //启动数据库的数据缓存依赖功能 SqlCacheDependencyAdmin.EnableNotifications(connectionString); //启用数据表缓存 SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString, "Person"); //第二个参数可以是单个表名或表名的数组 }
protected TableDependency(string strTableConfigKey, string strDataBaseConfigKey) { string dbName = ConfigurationManager.AppSettings[strDataBaseConfigKey]; string tableConfig = ConfigurationManager.AppSettings[strTableConfigKey]; string[] tables = tableConfig.Split(configurationSeparator); string strDNS = ConfigurationManager.ConnectionStrings[dbName].ConnectionString; SqlCacheDependencyAdmin.EnableNotifications(strDNS); SqlCacheDependencyAdmin.EnableTableForNotifications(strDNS, tables); foreach (string tableName in tables) { dependency.Add(new SqlCacheDependency(dbName, tableName)); } }
//<script runat = "server" > protected void Page_Load(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString; SqlCacheDependency SqlDep = null; if (Cache["SqlSource"] == null) { try { SqlDep = new SqlCacheDependency("Northwind", "Categories"); } catch (DatabaseNotEnabledForNotificationException exDBDis) { try { SqlCacheDependencyAdmin.EnableNotifications(connectionString); } catch (UnauthorizedAccessException exPerm) { Response.Redirect(".\\ErrorPage.html"); } } catch (TableNotEnabledForNotificationException exTabDis) { try { SqlCacheDependencyAdmin.EnableTableForNotifications("Northwind", "Categories"); } // If a SqlException is thrown, redirect to an error page. catch (SqlException exc) { Response.Redirect(".\\ErrorPage.htm"); } } finally { Cache.Insert("SqlSource", Source1, SqlDep); CacheMsg.Text = "The data object was created explicitly."; } } else { CacheMsg.Text = "The data was retrieved from the Cache."; } }
private void PrepareDatabase() { var connectionString = ConfigurationManager.ConnectionStrings["AdventureWorks"] .ConnectionString; try { SqlCacheDependencyAdmin.EnableNotifications(connectionString); SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString, "Person.Person"); } catch (Exception ex) { Console.WriteLine(ex.StackTrace); } }
public static void SqlCacheDependency_Insert(string[] tableName, string keyCache, object data) { try { var aggCacheDep = new AggregateCacheDependency(); var sqlDepGroup = new SqlCacheDependency[tableName.Length]; for (var i = 0; i < tableName.Length; i++) { sqlDepGroup[i] = new SqlCacheDependency(DatabaseName, tableName[i]); } aggCacheDep.Add(sqlDepGroup); if (data != null) { HttpRuntime.Cache.Insert(keyCache, data, aggCacheDep); } } catch (Exception ex) { var strErr = ex.Message; var exErr = string.Format("is not enabled for SQL cache notification.", tableName, DatabaseName); if (strErr.Contains(exErr)) { try { SqlCacheDependencyAdmin.EnableNotifications(StrCn); SqlCacheDependencyAdmin.EnableTableForNotifications(StrCn, tableName); var aggCacheDep = new AggregateCacheDependency(); var sqlDepGroup = new SqlCacheDependency[tableName.Length]; for (int i = 0; i < tableName.Length; i++) { sqlDepGroup[i] = new SqlCacheDependency(DatabaseName, tableName[i]); } aggCacheDep.Add(sqlDepGroup); if (data != null) { HttpRuntime.Cache.Insert(keyCache, data, aggCacheDep); } } catch (Exception ex1) { } } } }
private void EnableDependency(params Type[] DependencyTablesType) { foreach (Type _tableType in DependencyTablesType) { String _table = Loader.GetClassMap(_tableType).GetTableName(); if (Loader.HasClassMap(_tableType)) { if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(Config.DataSource).Contains(_table)) { SqlCacheDependencyAdmin.EnableTableForNotifications(Config.DataSource, _table); logger.Info("Enabling Notification For: " + _table); } else if (!MutableDependencyTablesType.Exists(m => m == _tableType)) { logger.Info("Already Enabled Notification For: " + _table); MutableDependencyTablesType.Add(_tableType); } } } }
/// <summary> /// 启动应用程序 /// </summary> protected void Application_Start() { NFine.Application.AutoMapper.Configuration.Configure();//注册DTO映射(AutoMapper) AreaRegistration.RegisterAllAreas(); FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters); RouteConfig.RegisterRoutes(RouteTable.Routes); var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["NFineDbContext"].ToString(); //启动数据库的数据缓存依赖功能 SqlCacheDependencyAdmin.EnableNotifications(connectionString); string[] tables = { "Web_Attachment", "Web_Content", "Web_Node", "Web_FriendLinks", "Sys_Items", "Sys_ItemsDetail", "Sys_Module", "Sys_ModuleButton", "Sys_ModuleForm", "Sys_Role", "Sys_RoleAuthorize", "Sys_User", "Sys_UserLogOn", "Sys_Area", "Sys_Log", "Sys_Organize" }; //启用数据表缓存 SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString, tables); //注册缓存依赖的服务代码:在vs命令行输入:aspnet_regsql -S . -U sa -P 123 -ed -d NFineBase -et -t Web_Content //-S localhost:数据库地址 //-U sa:数据库登录名 //-P 123456:数据库登录密码 //-d wcfDemo:数据库的名称 //-t user:表名称(小写) }
protected void BtnGetProduct_Click(object sender, EventArgs e) { if (Cache["ProductDetails"] != null) { Grdroducts.DataSource = Cache["ProductDetails"]; Grdroducts.DataBind(); LblMsg.Text = "ProductList From Cache :" + DateTime.Now.ToString(); } else { SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString()); SqlCommand cmd = new SqlCommand("sp_getTblEmployee", con); cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter adp = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); adp.Fill(ds); SqlCacheDependencyAdmin.EnableNotifications("data source=admin-pc;initial catalog=Del_Scheduling_Tool; integrated security=SSPI"); SqlCacheDependencyAdmin.EnableTableForNotifications("data source=admin-pc;initial catalog=Del_Scheduling_Tool; integrated security=SSPI", "TblEmployee"); CacheItemRemovedCallback OncacheItemRemoved = new CacheItemRemovedCallback(CacheItemRemovedCallbackMethod); SqlCacheDependency cacheDep = new SqlCacheDependency("Del_Scheduling_Tool", "TblEmployee"); Cache.Insert("ProductDetails", ds, cacheDep, DateTime.Now.AddDays(1), Cache.NoSlidingExpiration, CacheItemPriority.Default, OncacheItemRemoved); // Cache.Insert("ProductDetails", ds, null, DateTime.Now.AddSeconds(5), Cache.NoSlidingExpiration, CacheItemPriority.Default, null); Grdroducts.DataSource = ds; Grdroducts.DataBind(); LblMsg.Text = "ProductList From Database :" + DateTime.Now.ToString(); } }
public static void FetchFromDb <T>(string key, IList <T> values) { SqlCacheDependency SqlDep = null; string conn = ConfigurationManager.ConnectionStrings["PersonalDemoContext"].ConnectionString; SqlDependency.Start(conn); try { SqlDep = new SqlCacheDependency("PersonalDemo", key); } catch (DatabaseNotEnabledForNotificationException) { try { SqlCacheDependencyAdmin.EnableNotifications(conn); } catch (UnauthorizedAccessException) { //Response.Redirect("ErrorPage.htm"); } } catch (TableNotEnabledForNotificationException) { try { SqlCacheDependencyAdmin.EnableTableForNotifications(conn, "dbo." + key); } catch (SqlException) { //Response.Redirect("ErrorPage.htm"); } } finally { HttpRuntime.Cache.Insert(key, values, SqlDep); } }
protected void Button1_Click(object sender, EventArgs e) { if (Cache["ProductsData"] != null) { GridView1.DataSource = Cache["ProductsData"]; GridView1.DataBind(); Label1.Text = "Data retrived from cache @ " + DateTime.Now.ToString(); } else { string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString; SqlCacheDependencyAdmin.EnableNotifications(cs); SqlCacheDependencyAdmin.EnableTableForNotifications(cs, "tblProducts"); using (SqlConnection con = new SqlConnection(cs)) { SqlDataAdapter da = new SqlDataAdapter("spGetProducts", con); da.SelectCommand.CommandType = CommandType.StoredProcedure; DataSet ds = new DataSet(); da.Fill(ds); CacheItemRemovedCallback onCacheItemRemoved = new CacheItemRemovedCallback(CacheItemRemovedCallbackMethod); SqlCacheDependency sqlDependency = new SqlCacheDependency("Sample1", "tblProducts"); Cache.Insert("ProductsData", ds, sqlDependency, DateTime.Now.AddHours(24), Cache.NoSlidingExpiration, CacheItemPriority.Default, onCacheItemRemoved); GridView1.DataSource = ds; GridView1.DataBind(); Label1.Text = "Data retrived from database @ " + DateTime.Now.ToString(); } } }
static AspNetCacheService() { var cnnStr = ConfigurationManager.ConnectionStrings["WebFormsChat"].ConnectionString; SqlCacheDependencyAdmin.EnableTableForNotifications(cnnStr, "ChatMessages"); }
/// <summary> /// 快取 LINQ Query 的結果(僅適用於 LINQ to SQL 環境) /// 使用的的限制跟使用 SqlCacheDependency 的限制一樣 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="q"></param> /// <param name="dc">你的 LINQ to SQL DataContext</param> /// <param name="CacheId">Cache ID,需為每一個不同的 IQueryable 物件設定一個唯一的 ID</param> /// <returns></returns> public static List <T> LinqCache <T>(this IQueryable <T> q, DataContext dc, string CacheId) { List <T> objCache = (List <T>)System.Web.HttpRuntime.Cache.Get(CacheId); if (objCache == null) { #region 从数据库查找 ///////// 尚未快取,實做 new SqlCacheDependeny ////////// // 1. 透過 DataContext 取得連線字串 string connStr = dc.Connection.ConnectionString; // 2. 透過 DataContext 與 IQueryable 物件取得 SqlCommand 物件 SqlCommand sqlCmd = dc.GetCommand(q) as SqlCommand; // 3. 建立要給 SqlCacheDependency 使用的 SqlConnection 物件 using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); // 4. 建立要給 SqlCacheDependency 使用的 SqlCommand 物件 using (SqlCommand cmd = new SqlCommand(sqlCmd.CommandText, conn)) { // 5.0 將 sqlCmd 中的所有參數傳遞給 cmd 物件 foreach (System.Data.Common.DbParameter dbp in sqlCmd.Parameters) { cmd.Parameters.Add(new SqlParameter(dbp.ParameterName, dbp.Value)); } // 5.1 啟用資料庫的 Query notifications 功能 SqlCacheDependencyAdmin.EnableNotifications(connStr); // 5.2 取得要進行異動通知的表格名稱(ElementType) string NotificationTable = q.ElementType.Name; // 5.3 將取得的 NotificationTable 啟用通知功能 if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connStr).Contains(NotificationTable)) { SqlCacheDependencyAdmin.EnableTableForNotifications(connStr, NotificationTable); } // 6. 建立 SqlCacheDependency物件 SqlCacheDependency sqlDep = new SqlCacheDependency(cmd); // 7. 刷新 LINQ to SQL 的值(取得資料庫中的最新資料) dc.Refresh(RefreshMode.OverwriteCurrentValues, q); // 8. 執行 SqlCacheDepency 查詢 cmd.ExecuteNonQuery(); // 9. 執行 LINQ to SQL 的查詢,並將結果轉成 List<T> 型別,避免延遲查詢(Delayed Query)立即將資料取回 objCache = q.ToList(); //10. 將結果插入到 System.Web.HttpRuntime.Cache 物件中,並且指定 SqlCacheDependency 物件 System.Web.HttpRuntime.Cache.Insert(CacheId, objCache, sqlDep); } } #endregion } // 回傳查詢結果(或快取的結果) return(objCache); }
/// <summary> /// 数据依赖缓存 /// </summary> /// <param name="Key">键值</param> /// <param name="obj">对象</param> /// <param name="ConnectionStringName">数据库联接字符串</param> /// <param name="DependencyTableName">与 SqlCacheDependency 关联的数据库表的名称</param> /// <param name="DatabaseEntryName">在应用程序的 Web.config 文件的 caching 的 sqlCacheDependency 的 databases 元素(ASP.NET 设置架构)元素中定义的数据库的名称。 </param> public static void Insert(string Key, object obj, string ConnectionString, string DependencyTableName, string DatabaseEntryName) { SqlCacheDependencyAdmin.EnableNotifications(ConnectionString); SqlCacheDependencyAdmin.EnableTableForNotifications(ConnectionString, DependencyTableName); CacheHelper.Insert(Key, obj, new System.Web.Caching.SqlCacheDependency(DatabaseEntryName, DependencyTableName)); }
/** * 一般该方法会放在Application_Start中初始化 * **/ public static void Add(string[] tableNames) { SqlCacheDependencyAdmin.EnableNotifications(ConnectionString); SqlCacheDependencyAdmin.EnableTableForNotifications(ConnectionString, tableNames); }