sql2005下运用 SqlDependency 实现实时缓存其效果比aspnet_regsql快很多

本文链接：https://blog.csdn.net/hei25hei/article/details/5744627

缓存调用类

public class SQLCacheUtil
{
private static SqlConnection con = new SqlConnection("server=.;database=SLWCF;uid=sa;pwd=123456");
private static DataTable dt = new DataTable();

    public static void insertSqlCache()
    {
        dt.Clear();
        SqlCommand com = new SqlCommand("SELECT [Id],[Name],[PassWord] FROM dbo.Users", con);
        SqlDependency.Start("server=.;database=SLWCF;uid=sa;pwd=123456");
        System.Web.Caching.SqlCacheDependency sqldep = new System.Web.Caching.SqlCacheDependency(com);
        con.Open();
        SqlDataReader dr = com.ExecuteReader();
        dt.Load(dr);
        HttpContext.Current.Response.Write(dt.Rows.Count);
        HttpContext.Current.Cache.Insert("insertSQL", dt, sqldep, DateTime.Now.AddHours(1), TimeSpan.Zero, System.Web.Caching.CacheItemPriority.High, null);
        dr.Close();
        com.Clone();
        con.Close();

    }
}

测试页面

        if (Cache["insertSQL"] == null)
        {
            SQLCacheUtil.insertSqlCache();
        }
        DataTable dt2 = (DataTable)Cache["insertSQL"];
        this.GridView2.DataSource = dt2;
        this.GridView2.DataBind();

重点强调：

        SqlCommand com = new SqlCommand("SELECT [Id],[Name],[PassWord] FROM dbo.Users", con);
        SqlDependency.Start("server=.;database=SLWCF;uid=sa;pwd=123456");
        System.Web.Caching.SqlCacheDependency sqldep = new System.Web.Caching.SqlCacheDependency(com);
顺序要 com->SqlDependency.Start->new SqlCacheDependency

2. SqlCacheDependency(com)
在声明SqlCacheDependency对象时候有两种方式在sql2005的情况下一定要用 SqlCommand 做为参数