.net2.0緩衝小例測試

緩衝是.net2.0引入的一個新東西,藉助SqlCacheDependency(存在於System.Web.Cacheing下)可以添加快取項目對資料庫表或查詢的依賴,這個東西用在sql2005下可以不用設定因為sql2005已經提供了緩衝依賴的條件,用在sql2000下用有兩種辦法都可以實現:

一.要對資料庫進行下命令操作

使用aspnet_regsql命令列工具,aspnet_regsql工具位於Windows\Microsoft.NET\Framework\[版本]檔案夾中,如果要配置SqlCacheDependency，則需要以命令列的方式執行。例如:
aspnet_regsql -S localhost -E -d test -ed  //test為資料庫名稱
aspnet_regsql -S localhost -E -d test -t news -et  //test為資料庫名字,news為表名

二.或者使用SqlCacheDependencyAdmin類

方法一:
在web.config檔案<configuration>節中添加:

<connectionStrings>
  <add name="testConn" connectionString="server=.;database=test;uid=sa;pwd="/>
 </connectionStrings>

在接下來的<system.web>節中添加緩衝策略如下:
<caching >
      <sqlCacheDependency  enabled="true" pollTime="600000">
        <databases>
          <add name="test" connectionStringName="testConn" pollTime="600000"/>
        </databases>
      </sqlCacheDependency>
    </caching>
注意:這裡的<add>中的name是必須的,是要添加到設定集合中的SqlCacheDependencyDatabase對象的名稱,此名稱用作 @ OutputCache 指令上 SqlDependency 屬性的一部分。pollTime:設定 SqlCacheDependency 輪詢資料庫表以查看是否發生更改的頻率（以毫秒計算）。

在aspx頁面放一個GridView,代碼檔案可以如下:
protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            string key = "TableCache";
            DataSet data = (DataSet)HttpRuntime.Cache[key];

            if (data == null)
            {
                data = GetDataSource();
                AggregateCacheDependency cd = TableDependency();
                HttpRuntime.Cache.Add(key, data, cd, DateTime.Now.AddHours(1), System.Web.Caching.Cache.NoSlidingExpiration, CacheItemPriority.High, null);
            }
            this.GridView1.DataSource = data;
            this.GridView1.DataBind();
        }
    }

    private AggregateCacheDependency TableDependency()
    {
        AggregateCacheDependency dependency = new AggregateCacheDependency();
        dependency.Add(new SqlCacheDependency("test", "news"));
        return dependency;
    }
    private DataSet GetDataSource()
    {
        DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetAllNews");
        return ds;
    }

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        this.GridView1.PageIndex = e.NewPageIndex;
        this.GridView1.DataSource = GetDataSource();
        this.GridView1.DataBind();
    }

方法二:使用SqlCacheDependencyAdmin類,web.config檔案配置和方法一一樣

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            string key = "TableCache";
            DataSet data = (DataSet)HttpRuntime.Cache[key];

            if (data == null)
            {
                data = GetDataSource();
                //啟用指定資料庫的更改通知
                SqlCacheDependencyAdmin.EnableNotifications(ConfigurationManager.ConnectionStrings["testConn"].ConnectionString);
                //串連到sqlserver資料庫並為SqlCacheDependency更改通知準備資料庫表
                SqlCacheDependencyAdmin.EnableTableForNotifications(ConfigurationManager.ConnectionStrings["testConn"].ConnectionString, "news");
                //指定緩衝策略
                SqlCacheDependency scd = new SqlCacheDependency("test", "news");
                //插入緩衝
                HttpRuntime.Cache.Insert(key, data, scd);
            }
            this.GridView1.DataSource = data;
            this.GridView1.DataBind();
        }
    }

    private DataSet GetDataSource()
    {
        DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "GetAllNews");
        return ds;
    }

    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        this.GridView1.PageIndex = e.NewPageIndex;
        this.GridView1.DataSource = GetDataSource();
        this.GridView1.DataBind();
    }