Bewerken

Share via


SqlCacheDependency Constructors

Definition

Initializes a new instance of the SqlCacheDependency class.

Overloads

SqlCacheDependency(SqlCommand)

Initializes a new instance of the SqlCacheDependency class, using the supplied SqlCommand to create a cache-key dependency.

SqlCacheDependency(String, String)

Initializes a new instance of the SqlCacheDependency class, using the supplied parameters to create a cache-key dependency.

SqlCacheDependency(SqlCommand)

Initializes a new instance of the SqlCacheDependency class, using the supplied SqlCommand to create a cache-key dependency.

public:
 SqlCacheDependency(System::Data::SqlClient::SqlCommand ^ sqlCmd);
public SqlCacheDependency (System.Data.SqlClient.SqlCommand sqlCmd);
new System.Web.Caching.SqlCacheDependency : System.Data.SqlClient.SqlCommand -> System.Web.Caching.SqlCacheDependency
Public Sub New (sqlCmd As SqlCommand)

Parameters

sqlCmd
SqlCommand

A SqlCommand that is used to create a SqlCacheDependency object.

Exceptions

The sqlCmd parameter is null.

The SqlCommand instance has its NotificationAutoEnlist property set to true and there is an @ OutputCache directive on the page with the SqlDependency attribute set to CommandNotification.

Remarks

This constructor is used to create SqlCacheDependency objects that use the query-notification feature of SQL Server 2005 products.

SQL statements that are associated with the sqlCmd parameter must include the following:

  • Fully qualified table names, including the name of the table owner. For example, to refer to a table named Customers that is owned by the database owner, the SQL statement must refer to dbo.customers.

  • Explicit column names in the Select statement. You cannot use the asterisk (*) wildcard character to select all columns from a table. For example, instead of select * from dbo.customers, you must use select name, address, city, state from dbo.customers.

This constructor cannot be used to associate a SqlCommand instance with a SqlCacheDependency instance on a page using SQL Server 2005 query notifications with page-level output caching.

See also

Applies to

SqlCacheDependency(String, String)

Initializes a new instance of the SqlCacheDependency class, using the supplied parameters to create a cache-key dependency.

public:
 SqlCacheDependency(System::String ^ databaseEntryName, System::String ^ tableName);
public SqlCacheDependency (string databaseEntryName, string tableName);
new System.Web.Caching.SqlCacheDependency : string * string -> System.Web.Caching.SqlCacheDependency
Public Sub New (databaseEntryName As String, tableName As String)

Parameters

databaseEntryName
String

The name of a database defined in the databases element of the application's Web.config file.

tableName
String

The name of the database table that the SqlCacheDependency is associated with.

Exceptions

The internal check for SqlClientPermission failed.

-or-

The databaseEntryName was not found in the list of databases configured for table-based notifications.

-or-

The SqlCacheDependency object could not connect to the database during initialization.

-or-

The SqlCacheDependency object encountered a permission-denied error either on the database or on the database stored procedures that support the SqlCacheDependency object.

The tableName parameter is Empty.

Polling is not enabled for the SqlCacheDependency.

-or-

The polling interval is not correctly configured.

-or-

No connection string was specified in the application's configuration file.

-or-

The connection string specified in the application's configuration file could not be found.

-or-

The connection string specified in the application's configuration file is an empty string.

The database specified in the databaseEntryName parameter is not enabled for change notifications.

The database table specified in the tableName parameter is not enabled for change notifications.

databaseEntryName is null.

-or-

tableName is null.

Examples

The following code example uses this constructor to create an instance of the SqlCacheDependency class that is associated with a database table named Categories in a SQL Server database named Northwind.

public void Page_Load(object Src, EventArgs E) 
{ 
    // Declare the SqlCacheDependency instance, SqlDep. 
    SqlCacheDependency SqlDep = null; 
    
    // Check the Cache for the SqlSource key. 
    // If it isn't there, create it with a dependency 
    // on a SQL Server table using the SqlCacheDependency class. 
    if (Cache["SqlSource"] == 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("Northwind", "Categories"); 
        } 
        
        // Handle the DatabaseNotEnabledForNotificationException with 
        // a call to the SqlCacheDependencyAdmin.EnableNotifications method. 
        catch (DatabaseNotEnabledForNotificationException exDBDis) { 
            try { 
                SqlCacheDependencyAdmin.EnableNotifications("Northwind"); 
            } 
            
            // If the database does not have permissions set for creating tables, 
            // the UnauthorizedAccessException is thrown. Handle it by redirecting 
            // to an error page. 
            catch (UnauthorizedAccessException exPerm) { 
                Response.Redirect(".\\ErrorPage.htm"); 
            } 
        } 
        
        // Handle the TableNotEnabledForNotificationException with 
        // a call to the SqlCacheDependencyAdmin.EnableTableForNotifications method. 
        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"); 
            } 
        } 
        
        // If all the other code is successful, add MySource to the Cache 
        // with a dependency on SqlDep. If the Categories table changes, 
        // MySource will be removed from the Cache. Then generate a message 
        // that the data is newly created and added to the cache. 
        finally { 
            Cache.Insert("SqlSource", Source1, SqlDep); 
            CacheMsg.Text = "The data object was created explicitly."; 
            
        } 
    } 
    
    else { 
        CacheMsg.Text = "The data was retrieved from the Cache."; 
    } 
} 
Sub Page_Load(Src As Object, E As EventArgs)
   ' Declare the SqlCacheDependency instance, SqlDep.
   Dim SqlDep As SqlCacheDependency

   ' Check the Cache for the SqlSource key.
   ' If it isn't there, create it with a dependency
   ' on a SQL Server table using the SqlCacheDependency class.
   If Cache("SqlSource") Is Nothing

      ' Because of possible exceptions thrown when this
      ' code runs, use Try...Catch...Finally syntax.
      Try
         ' Instantiate SqlDep using the SqlCacheDependency constructor.
         SqlDep = New SqlCacheDependency("Northwind", "Categories")

      ' Handle the DatabaseNotEnabledForNotificationException with
      ' a call to the SqlCacheDependencyAdmin.EnableNotifications method.
      Catch exDBDis As DatabaseNotEnabledForNotificationException
         Try
            SqlCacheDependencyAdmin.EnableNotifications("Northwind")

         ' If the database does not have permissions set for creating tables,
         ' the UnauthorizedAccessException is thrown. Handle it by redirecting
         ' to an error page.
         Catch exPerm As UnauthorizedAccessException
             Response.Redirect(".\ErrorPage.htm")
         End Try

      ' Handle the TableNotEnabledForNotificationException with
            ' a call to the SqlCacheDependencyAdmin.EnableTableForNotifications method.
      Catch exTabDis As TableNotEnabledForNotificationException
         Try
            SqlCacheDependencyAdmin.EnableTableForNotifications( _
             "Northwind", "Categories")

         ' If a SqlException is thrown, redirect to an error page.
         Catch exc As SqlException
             Response.Redirect(".\ErrorPage.htm")
         End Try

      ' If all the other code is successful, add MySource to the Cache
      ' with a dependency on SqlDep. If the Categories table changes,
      ' MySource will be removed from the Cache. Then generate a message
            ' that the data is newly created and added to the cache.
      Finally
         Cache.Insert("SqlSource", Source1, SqlDep)
            CacheMsg.Text = "The data object was created explicitly."

      End Try

    Else
       CacheMsg.Text = "The data was retrieved from the Cache."
    End If
End Sub

Remarks

This constructor is used to create SqlCacheDependency objects for SQL Server 7.0 and SQL Server 2000 products.

The database name passed to the database parameter must be defined in the application's Web.config file. For example, the following Web.config file defines a database named pubs for SqlCacheDependency change notifications.

<configuration>
  <connectionStrings>
    <add name="Pubs" connectionString="Data Source=(local); Initial Catalog=pubs; Integrated Security=true"; providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.web>
    <caching>
      <sqlCacheDependency enabled = "true" pollTime = "60000" >
        <databases>
          <add name="pubs"
            connectionStringName="pubs"
            pollTime="9000000"
            />
        </databases>
      </sqlCacheDependency>
    </caching>
  </system.web>
</configuration>

Two exceptions are commonly thrown when this constructor is used: DatabaseNotEnabledForNotificationException and TableNotEnabledForNotificationException. If a DatabaseNotEnabledForNotificationException is thrown, you can call the SqlCacheDependencyAdmin.EnableNotifications method in exception-handling code, or use the aspnet_regsql.exe command-line tool to set up the database for notifications. If a TableNotEnabledForNotificationException is thrown, you can call the SqlCacheDependencyAdmin.EnableTableForNotifications method or use aspnet_regsql.exe to set up the table for notifications.

See also

Applies to