ASP.NET 應用程式中的 SqlDependencySqlDependency in an ASP.NET Application

本節中的範例將顯示如何藉由使用 ASP.NET SqlDependency 物件,間接使用 SqlCacheDependencyThe example in this section shows how to use SqlDependency indirectly by leveraging the ASP.NET SqlCacheDependency object. SqlCacheDependency 物件會使用 SqlDependency 來接聽通知並正確地更新快取。The SqlCacheDependency object uses a SqlDependency to listen for notifications and correctly update the cache.

注意

範例程式碼假設您已藉由執行啟用查詢通知中的腳本來啟用查詢通知。The sample code assumes that you have enabled query notifications by executing the scripts in Enabling Query Notifications.

關於範例應用程式About the Sample Application

範例應用程式會使用單一 ASP.NET 網頁,在GridView控制項中顯示AdventureWorks SQL Server 資料庫的產品資訊。The sample application uses a single ASP.NET Web page to display product information from the AdventureWorks SQL Server database in a GridView control. 載入頁面時,此程式碼會將目前時間寫入 Label 控制項。When the page loads, the code writes the current time to a Label control. 然後會定義 SqlCacheDependency 物件,並在 Cache 物件上設定屬性,以儲存快取資料長達三分鐘。It then defines a SqlCacheDependency object and sets properties on the Cache object to store the cache data for up to three minutes. 然後,此程式碼會連接到資料庫並擷取資料。The code then connects to the database and retrieves the data. 當頁面已載入而且應用程式正在執行時,ASP.NET 將從快取中擷取資料,而且您可以注意頁面上的時間沒有變更來確認這點。When the page is loaded and the application is running ASP.NET will retrieve data from the cache, which you can verify by noting that the time on the page does not change. 如果正在監視的資料有變更,ASP.NET 會讓快取無效並將新資料重新填入 GridView 控制項,而且更新 Label 控制項中顯示的時間。If the data being monitored changes, ASP.NET invalidates the cache and repopulate the GridView control with fresh data, updating the time displayed in the Label control.

建立範例應用程式Creating the Sample Application

請遵循下列步驟來建立並執行範例應用程式:Follow these steps to create and run the sample application:

  1. 建立新的 ASP.NET 網站。Create a new ASP.NET Web site.

  2. LabelGridView 控制項加入至 Default.aspx 頁面。Add a Label and a GridView control to the Default.aspx page.

  3. 開啟頁面的類別模組,並加入下列指示詞:Open the page's class module and add the following directives:

    Option Strict On  
    Option Explicit On  
    
    Imports System.Data.SqlClient  
    
    using System.Data.SqlClient;  
    using System.Web.Caching;  
    
  4. 將下列程式碼加入至頁面的 Page_Load 事件:Add the following code in the page's Page_Load event:

    protected void Page_Load(object sender, EventArgs e)
    {
        Label1.Text = "Cache Refresh: " +
        DateTime.Now.ToLongTimeString();
    
        // Create a dependency connection to the database.
        SqlDependency.Start(GetConnectionString());
    
        using (SqlConnection connection =
            new SqlConnection(GetConnectionString()))
        {
            using (SqlCommand command =
                new SqlCommand(GetSQL(), connection))
            {
                SqlCacheDependency dependency =
                    new SqlCacheDependency(command);
                // Refresh the cache after the number of minutes
                // listed below if a change does not occur.
                // This value could be stored in a configuration file.
                int numberOfMinutes = 3;
                DateTime expires =
                    DateTime.Now.AddMinutes(numberOfMinutes);
    
                Response.Cache.SetExpires(expires);
                Response.Cache.SetCacheability(HttpCacheability.Public);
                Response.Cache.SetValidUntilExpires(true);
    
                Response.AddCacheDependency(dependency);
    
                connection.Open();
    
                GridView1.DataSource = command.ExecuteReader();
                GridView1.DataBind();
            }
        }
    }
    
    Protected Sub Page_Load(ByVal sender As Object, _
       ByVal e As System.EventArgs) Handles Me.Load
    
        Label1.Text = "Cache Refresh: " & _
           Date.Now.ToLongTimeString()
    
        ' Create a dependency connection to the database
        SqlDependency.Start(GetConnectionString())
    
        Using connection As New SqlConnection(GetConnectionString())
            Using command As New SqlCommand(GetSQL(), connection)
                Dim dependency As New SqlCacheDependency(command)
    
                ' Refresh the cache after the number of minutes
                ' listed below if a change does not occur.
                ' This value could be stored in a configuration file.
                Dim numberOfMinutes As Integer = 3
                Dim expires As Date = _
                    DateTime.Now.AddMinutes(numberOfMinutes)
    
                Response.Cache.SetExpires(expires)
                Response.Cache.SetCacheability(HttpCacheability.Public)
                Response.Cache.SetValidUntilExpires(True)
    
                Response.AddCacheDependency(dependency)
    
                connection.Open()
    
                GridView1.DataSource = command.ExecuteReader()
                GridView1.DataBind()
            End Using
        End Using
    End Sub
    
  5. 加入兩個 Helper 方法:GetConnectionStringGetSQLAdd two helper methods, GetConnectionString and GetSQL. 定義的連接字串會使用整合安全性。The connection string defined uses integrated security. 您必須確認所使用的帳戶具有必要的資料庫許可權,而且範例資料庫AdventureWorks已啟用通知。You will need to verify that the account you are using has the necessary database permissions and that the sample database, AdventureWorks, has notifications enabled.

    private string GetConnectionString()
    {
        // To avoid storing the connection string in your code,
        // you can retrieve it from a configuration file.
        return "Data Source=(local);Integrated Security=true;" +
          "Initial Catalog=AdventureWorks;";
    }
    private string GetSQL()
    {
        return "SELECT Production.Product.ProductID, " +
        "Production.Product.Name, " +
        "Production.Location.Name AS Location, " +
        "Production.ProductInventory.Quantity " +
        "FROM Production.Product INNER JOIN " +
        "Production.ProductInventory " +
        "ON Production.Product.ProductID = " +
        "Production.ProductInventory.ProductID " +
        "INNER JOIN Production.Location " +
        "ON Production.ProductInventory.LocationID = " +
        "Production.Location.LocationID " +
        "WHERE ( Production.ProductInventory.Quantity <= 100 ) " +
        "ORDER BY Production.ProductInventory.Quantity, " +
        "Production.Product.Name;";
    }
    
    Private Function GetConnectionString() As String
        ' To avoid storing the connection string in your code,
        ' you can retrive it from a configuration file.
    
        Return "Data Source=(local);Integrated Security=true;" & _
         "Initial Catalog=AdventureWorks;"
    End Function
    
    Private Function GetSQL() As String
        Return "SELECT Production.Product.ProductID, " & _
        "Production.Product.Name, " & _
        "Production.Location.Name AS Location, " & _
        "Production.ProductInventory.Quantity " & _
        "FROM Production.Product INNER JOIN " & _
        "Production.ProductInventory " & _
        "ON Production.Product.ProductID = " & _
        "Production.ProductInventory.ProductID " & _
        "INNER JOIN Production.Location " & _
        "ON Production.ProductInventory.LocationID = " & _
        "Production.Location.LocationID " & _
        "WHERE ( Production.ProductInventory.Quantity <= 100) " & _
        "ORDER BY Production.ProductInventory.Quantity, " & _
        "Production.Product.Name;"
    End Function
    

測試應用程式Testing the Application

應用程式會快取顯示在 Web Form 上的資料,並每隔三分鐘重新整理它一次 (如果沒有任何活動的話)。The application caches the data displayed on the Web form and refreshes it every three minutes if there is no activity. 如果資料庫發生變更,就會立即重新整理快取。If a change occurs to the database, the cache is refreshed immediately. 從 Visual Studio 執行應用程式,以便將頁面載入瀏覽器。Run the application from Visual Studio, which loads the page into the browser. 顯示的快取重新整理時間表示上次重新整理快取的時間。The cache refresh time displayed indicates when the cache was last refreshed. 等候三分鐘,然後重新整理頁面,以便發生回傳事件。Wait three minutes, and then refresh the page, causing a postback event to occur. 請注意,顯示在頁面上的時間已經變更。Note that the time displayed on the page has changed. 如果您在三分鐘以內便重新整理頁面,顯示在頁面上的時間將維持不變。If you refresh the page in less than three minutes, the time displayed on the page will remain the same.

現在,請使用 Transact-SQL UPDATE 命令來更新資料庫中的資料,並重新整理頁面。Now update the data in the database, using a Transact-SQL UPDATE command and refresh the page. 此時顯示的時間表示已使用資料庫的新資料來重新整理快取。The time displayed now indicates that the cache was refreshed with the new data from the database. 請注意,雖然快取已更新,但是顯示在頁面上的時間要等到發生回傳事件之後才會變更。Note that although the cache is updated, the time displayed on the page does not change until a postback event occurs.

另請參閱See also