c#本地缓存当数据库表更改时，缓存失效。

web.config

<?xml version="1.0" encoding="utf-8"?>
<!--
  有关如何配置 ASP.NET 应用程序的详细信息，请访问
  http://go.microsoft.com/fwlink/?LinkId=152368
  -->
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="TestMvcConnectionString" 
         connectionString="Data Source=LJJ-FFLJJ;Initial Catalog=TestMvc;User ID=sa;Password=111111;Max Pool Size=500;Min Pool Size=1;"
         providerName="System.Data.SqlClient" />
  </connectionStrings>
  <appSettings>
    <add key="webpages:Version" value="2.0.0.0" />
    <add key="webpages:Enabled" value="false" />
    <add key="PreserveLoginUrl" value="true" />
    <add key="ClientValidationEnabled" value="true" />
    <add key="UnobtrusiveJavaScriptEnabled" value="true" />
  </appSettings>
  <system.web>
    <caching>
      <sqlCacheDependency enabled="true" pollTime="5000">
        <databases>
          <add connectionStringName="TestMvcConnectionString" name="TestMvc"/>
        </databases>
      </sqlCacheDependency>
    </caching>
    <httpRuntime targetFramework="4.5" />
    <compilation debug="true" targetFramework="4.5" />
    <authentication mode="Forms">
      <forms loginUrl="~/Account/Login" timeout="2880" />
    </authentication>
    <pages>
      <namespaces>
        <add namespace="System.Web.Helpers" />
        <add namespace="System.Web.Mvc" />
        <add namespace="System.Web.Mvc.Ajax" />
        <add namespace="System.Web.Mvc.Html" />
        <add namespace="System.Web.Optimization" />
        <add namespace="System.Web.Routing" />
        <add namespace="System.Web.WebPages" />
      </namespaces>
    </pages>
    <profile defaultProvider="DefaultProfileProvider">
      <providers>
        <add name="DefaultProfileProvider" type="System.Web.Providers.DefaultProfileProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/" />
      </providers>
    </profile>
    <membership defaultProvider="DefaultMembershipProvider">
      <providers>
        <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" />
      </providers>
    </membership>
    <roleManager defaultProvider="DefaultRoleProvider">
      <providers>
        <add name="DefaultRoleProvider" type="System.Web.Providers.DefaultRoleProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" applicationName="/" />
      </providers>
    </roleManager>
    <!--
            If you are deploying to a cloud environment that has multiple web server instances,
            you should change session state mode from "InProc" to "Custom". In addition,
            change the connection string named "DefaultConnection" to connect to an instance
            of SQL Server (including SQL Azure and SQL  Compact) instead of to SQL Server Express.
      -->
    <sessionState mode="InProc" customProvider="DefaultSessionProvider">
      <providers>
        <add name="DefaultSessionProvider" type="System.Web.Providers.DefaultSessionStateProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" />
      </providers>
    </sessionState>
  </system.web>
  <system.webServer>
    <validation validateIntegratedModeConfiguration="false" />
    <handlers>
      <remove name="ExtensionlessUrlHandler-ISAPI-4.0_32bit" />
      <remove name="ExtensionlessUrlHandler-ISAPI-4.0_64bit" />
      <remove name="ExtensionlessUrlHandler-Integrated-4.0" />
      <add name="ExtensionlessUrlHandler-ISAPI-4.0_32bit" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" modules="IsapiModule" scriptProcessor="%windir%Microsoft.NETFrameworkv4.0.30319aspnet_isapi.dll" preCondition="classicMode,runtimeVersionv4.0,bitness32" responseBufferLimit="0" />
      <add name="ExtensionlessUrlHandler-ISAPI-4.0_64bit" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" modules="IsapiModule" scriptProcessor="%windir%Microsoft.NETFramework64v4.0.30319aspnet_isapi.dll" preCondition="classicMode,runtimeVersionv4.0,bitness64" responseBufferLimit="0" />
      <add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="GET,HEAD,POST,DEBUG,PUT,DELETE,PATCH,OPTIONS" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" />
    </handlers>
  </system.webServer>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="2.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-4.0.0.0" newVersion="4.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-2.0.0.0" newVersion="2.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="EntityFramework" publicKeyToken="b77a5c561934e089" />
        <bindingRedirect oldVersion="0.0.0.0-5.0.0.0" newVersion="5.0.0.0" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" />
        <bindingRedirect oldVersion="1.0.0.0-1.3.0.0" newVersion="1.3.0.0" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>
</configuration>

代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Caching;
using System.Web.Mvc;

namespace TestSystemWebCache.Controllers
{
    public class HomeController : Controller
    {
        //
        // GET: /Home/

        public ActionResult Index()
        {
            System.Web.Caching.Cache cache = System.Web.HttpContext.Current.Cache;

            //数据库连接字符串名字
            //启用更改通知
            SqlCacheDependencyAdmin.EnableNotifications(System.Configuration.ConfigurationManager.ConnectionStrings["TestMvcConnectionString"].ConnectionString);
            //数据库连接字符串名字和表的名字
            //连接到 SQL Server 数据库并为 SqlCacheDependency 更改通知准备数据库表
            SqlCacheDependencyAdmin.EnableTableForNotifications(System.Configuration.ConfigurationManager.ConnectionStrings["TestMvcConnectionString"].ConnectionString, "Student");

            //制定缓存策略
            SqlCacheDependency scd = new SqlCacheDependency("TestMvc", "Student");//数据库的名字和表名字

            string oldwCache = (string)cache.Get("SiteInfo");
            ViewBag.oldwCache = oldwCache;

            if (oldwCache == null)
            {

                //插入缓存
                cache.Insert("SiteInfo", "ljj", scd);
                string ss = (string)cache.Get("SiteInfo");
            }

            string newCache= (string)cache.Get("SiteInfo");
            ViewBag.newCache = newCache;



            return View();
        }

    }
}

我们对Student表启用缓存通知。

打开vs命令工具行，输入：aspnet_regsql -S LJJ-FFLJJ -U sa -P 111111 -ed -d TestMvc -et -t Student

这样当数据表更新的时候，缓存就会失效。

 public UserMenuEntityCollection GetUserMenuCollection(string language, int level)
        {
            
            UserMenuEntityCollection returnCollection = HttpContext.Current.Cache[className] as UserMenuEntityCollection;
            if (returnCollection != null)
            {
                return returnCollection;
            }
            returnCollection = GetUserMenuFromDB(language, level);
             
            if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(ConfigurationManager.ConnectionStrings["AARTOConnectionString"].ConnectionString).Contains(tableName))
            {
                SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings["AARTOConnectionString"].ConnectionString, tableName);
            }
            string dataBaseName = "";
            ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["AARTOConnectionString"];
            string[] connectParameterArray = connectionStringSettings.ConnectionString.Split(';');
            foreach (string connectParameter in connectParameterArray)
            {
                if (connectParameter.Substring(0, connectParameter.IndexOf("=")) == "Initial Catalog")
                {
                    //dataBaseName = connectParameter.Substring(connectParameter.IndexOf("="));
                    dataBaseName = connectParameter.Substring(connectParameter.IndexOf("=") + 1);
                    break;
                }
            }
            SqlCacheDependency sqlDependency = new SqlCacheDependency(dataBaseName, tableName);
            HttpContext.Current.Cache.Insert(className, returnCollection, sqlDependency);
            
            return returnCollection;
        }

利用反射取缓存代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Caching;
using System.Collections;
using System.Reflection;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using SIL.AARTO.DAL.Entities;

namespace SIL.AARTO.BLL.Utility.Cache
{
    public abstract class AARTOCache
    {
        public static System.Web.Caching.Cache Cache = HttpContext.Current.Cache;
        protected string className;
        protected string tableName;

        protected static object GetCacheData(string className, string tableName)
        {

            Type typeEntity = Type.GetType("SIL.AARTO.DAL.Entities." + className + ",SIL.AARTO.DAL.Entities");
            Type typeService = Type.GetType("SIL.AARTO.DAL.Services." + className + "Service,SIL.AARTO.DAL.Services");
            object objEntity = Activator.CreateInstance(typeEntity);
            object objService = Activator.CreateInstance(typeService);

            object result = Cache[tableName];

            if (result == null)
            {
                string dataBaseName = "";
                ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["AARTOConnectionString"];
                string[] connectParameterArray = connectionStringSettings.ConnectionString.Split(';');
                foreach (string connectParameter in connectParameterArray)
                {
                    if (connectParameter.Substring(0, connectParameter.IndexOf("=")) == "Initial Catalog")
                    {
                        //dataBaseName = connectParameter.Substring(connectParameter.IndexOf("="));
                        dataBaseName = connectParameter.Substring(connectParameter.IndexOf("=") + 1);
                        break;
                    }
                }

                foreach (MethodInfo info in typeService.GetMethods())
                {
                    if (info.Name == "GetAll")
                    {
                        result = info.Invoke(objService, null);
                        //if (result != null)
                        //{
                        //    SqlCacheDependencyAdmin.EnableNotifications(connectionStringSettings.ConnectionString);
                        //    if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connectionStringSettings.ConnectionString).Contains(tableName))
                        //    {
                        //        SqlCacheDependencyAdmin.EnableTableForNotifications(connectionStringSettings.ConnectionString, tableName);
                        //    }
                        //    SqlCacheDependency sqlCacheDependency = new SqlCacheDependency(dataBaseName, tableName);
                        //    HttpContext.Current.Cache.Insert(tableName, result, sqlCacheDependency);
                        //}
                        break;
                    }
                }

            }

            return result;

        }

        private static void OnRemoveQuotesCollection(string key, object val,
              CacheItemRemovedReason r)
        {
            // Do something about the dependency Change
            if (r == CacheItemRemovedReason.DependencyChanged)
            {
                HttpRuntime.Cache.Remove(key);
            }
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using SIL.AARTO.DAL.Entities;

using SIL.AARTO.DAL.Services;
using System.Web.UI.WebControls;
using SIL.AARTO.BLL.Utility.UserMenu;
using System.Web;
using System.Web.Caching;
using System.Configuration;

namespace SIL.AARTO.BLL.Utility.Cache
{
    public class AARTOMenuLookUpCache : AARTOCache
    {
        //public readonly string className="AartoMenu";
        //public readonly string tableName = "AARTOMenu";

        public TList<AartoMenuLookup> GetAll()
        {
            className = "AartoMenuLookup";
            tableName = "AARTOMenuLookup";

            return GetCacheData(className, tableName) as TList<AartoMenuLookup>;

        }
    }

    public class AARTOMenuCache : AARTOCache
    {
        public AARTOMenuCache()
        {
            className = "GenerateUserMenuByLanguageAndLevel";
            tableName = "AARTOMenu";
        }
        private static AartoMenuService menuService = new AartoMenuService();
        public TList<AartoMenu> GetAll()
        {
            className = "AartoMenu";
            tableName = "AARTOMenu";

            return GetCacheData(className, tableName) as TList<AartoMenu>;

        }

        public UserMenuEntityCollection GetUserMenuFromDB(string language, int level)
        {
            UserMenuEntityCollection returnCollection = new UserMenuEntityCollection();
            UserMenuEntityCollection containerCollection = new UserMenuEntityCollection();
            UserMenuEntityCollection currentCollection = new UserMenuEntityCollection();
            //int currentLevel = 0;
            int oldlevel = 2;

            using (IDataReader reader = menuService.GenerateUserMenuByLanguageAndLevel(language, level))
            {
                while (reader.Read())
                {
                    UserMenuEntity menu = GetUserMenuEntityFromDataReader(reader);

                    if (menu.Level == 1)
                    {
                        returnCollection.Add(menu);
                        containerCollection.Add(menu);
                    }
                    else
                    {
                        if (oldlevel != menu.Level)
                        {
                            containerCollection.Clear();

                            foreach (UserMenuEntity menuEntity in currentCollection)
                            {
                                containerCollection.Add(menuEntity);
                            }
                            currentCollection.Clear();
                            oldlevel = menu.Level;
                        }

                        currentCollection.Add(menu);

                        CreateUserMenu(containerCollection, menu);
                    }
                }
            }
            return returnCollection;
        }

        public UserMenuEntityCollection GetUserMenuCollection(string language, int level)
        {
            
            UserMenuEntityCollection returnCollection = HttpContext.Current.Cache[className] as UserMenuEntityCollection;
            if (returnCollection != null)
            {
                return returnCollection;
            }
            returnCollection = GetUserMenuFromDB(language, level);
             
            if (!SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(ConfigurationManager.ConnectionStrings["AARTOConnectionString"].ConnectionString).Contains(tableName))
            {
                SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings["AARTOConnectionString"].ConnectionString, tableName);
            }
            string dataBaseName = "";
            ConnectionStringSettings connectionStringSettings = ConfigurationManager.ConnectionStrings["AARTOConnectionString"];
            string[] connectParameterArray = connectionStringSettings.ConnectionString.Split(';');
            foreach (string connectParameter in connectParameterArray)
            {
                if (connectParameter.Substring(0, connectParameter.IndexOf("=")) == "Initial Catalog")
                {
                    //dataBaseName = connectParameter.Substring(connectParameter.IndexOf("="));
                    dataBaseName = connectParameter.Substring(connectParameter.IndexOf("=") + 1);
                    break;
                }
            }
            SqlCacheDependency sqlDependency = new SqlCacheDependency(dataBaseName, tableName);
            HttpContext.Current.Cache.Insert(className, returnCollection, sqlDependency);
            
            return returnCollection;
        }

        private void CreateUserMenu(UserMenuEntityCollection container, UserMenuEntity menu)
        {
            foreach (UserMenuEntity menuEntity in container)
            {
                if (menuEntity.AaMeID == menu.ParentAaMeID)
                {
                    menu.ParentMenuEntity = menuEntity;
                    menuEntity.UserMenuList.Add(menu);
                    break;
                }
            }
        }

        private UserMenuEntity GetUserMenuEntityFromDataReader(IDataReader reader)
        {
            UserMenuEntity userMenu = new UserMenuEntity();
            userMenu.AaMeID = Convert.ToDecimal(reader["AaMeID"]);
            userMenu.ParentAaMeID = reader["AaParentMeIDMeID"] == DBNull.Value ? 0 : Convert.ToDecimal(reader["AaParentMeIDMeID"]);
            userMenu.AMLMenuItemName = reader["AaMLMenuItemName"].ToString();
            userMenu.PageID = reader["AaPageID"].ToString();
            userMenu.UserRole = reader["AaUserRoleID"] == DBNull.Value ? 0 : Convert.ToInt32(reader["AaUserRoleID"]);
            userMenu.Level = Convert.ToInt32(reader["Level"]);
            userMenu.AaMeOrderNo = Convert.ToInt32(reader["AaMeOrderNo"]);
            userMenu.IsVisable = true;
            userMenu.IsAarto = reader["IsAARTO"] == DBNull.Value ? false : Convert.ToBoolean(reader["IsAARTO"]);
            userMenu.AaMePageURL = reader["AaPageURL"].ToString();
            userMenu.IsNewWindow = reader["IsNewWindow"] == DBNull.Value ? false : Convert.ToBoolean(reader["IsNewWindow"]);
            return userMenu;
        }

    }
}