ObjectDataSource.SqlCacheDependency Property
Gets or sets a semicolon-delimited string that indicates which databases and tables to use for the Microsoft SQL Server cache dependency.
Assembly: System.Web (in System.Web.dll)
Property Value
Type: System.StringA string that indicates which databases and tables to use for the SQL Server cache dependency.
The ObjectDataSource control supports an optional expiration policy that is based on the SqlCacheDependency object for the data cache (the service must be configured for the database server).
SQL Server supports two mechanisms for cache invalidation: polling and notification. Each mechanism has a different syntax for the SqlCacheDependency object.
The ObjectDataSource supports only polling. The SqlCacheDependency string is used to create a SqlDependency object that is passed to the DbCommand constructor before the Select method is executed. The SqlCacheDependency string identifies databases and tables according to the same format that is used by the @ Page directive, where the first part of the string is a connection string to a SQL Server database, followed by a colon delimiter, and finally the name of the database table (for example, "connectionstring1:table1"). If the SqlCacheDependency property depends on more than one table, the connection string and table name pairs are separated by semicolons (for example, "connectionstring1:table1";connectionstring2:table2").
To support notification, you must write the cache logic in the implementation of your SelectMethod property and handle the construction of the SqlCacheDependency object in your code.
This section contains two code examples. The first code example demonstrates how to create a SQL Server cache dependency and set the SqlCacheDependency property of a ObjectDataSource object. The second code example demonstrates how to implement a wrapper method that returns a DataSet object to enable caching and filtering with an ObjectDataSource object.
The following code example demonstrates how to create a SQL Server cache dependency and set the SqlCacheDependency property of a ObjectDataSource control. In this example, the database is polled every 120 seconds. If the data in the Northwind Traders Employees table changes during that time, any data that is cached by the ObjectDataSource control and displayed by the GridView control is expired and a new set of data is retrieved on the next request for the page.
<%@ Register TagPrefix="aspSample" Namespace="Samples.AspNet.VB" Assembly="Samples.AspNet.VB" %> <%@ Page language="vb" %> <!-- The page uses an example configuration that includes connection strings and a defined SqlCacheDependecy. <?xml version="1.0"?> <configuration> <connectionStrings> <add name="MyNorthwind" connectionString="Data Source="localhost";Integrated Security="SSPI";Initial Catalog="Northwind"" providerName="System.Data.SqlClient" /> </connectionStrings> <system.web> <cache> <sqlCacheDependency enabled="true"> <databases> <add name="Northwind" connectionStringName="MyNorthwind" pollTime="120000" /> </databases> </sqlCacheDependency> </cache> </system.web> </configuration> --> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head> <title>ObjectDataSource - VB Example</title> </head> <body> <form id="Form1" method="post" runat="server"> <asp:gridview id="GridView1" runat="server" datasourceid="ObjectDataSource1" /> <asp:objectdatasource id="ObjectDataSource1" runat="server" typename="Samples.AspNet.VB.EmployeeLogic" selectmethod="GetAllEmployeesAsDataSet" enablecaching="True" cacheduration="300" cacheexpirationpolicy="Absolute" sqlcachedependency="Northwind:Employees" /> </form> </body> </html>
The following code example demonstrates how to implement a wrapper method that returns a DataSet object to enable caching and filtering with an ObjectDataSource control. In the base implementation of the EmployeeLogic class, the GetAllEmployees method returns an ArrayList object. Instead of refactoring the object completely to work with the ObjectDataSource on a Web Forms page, a wrapper method named GetAllEmployeesAsDataSet is added that returns a set of NorthwindEmployee data as a DataSet.
Imports System Imports System.Collections Imports System.Configuration Imports System.Data Imports System.Data.SqlClient Imports System.Web.UI Imports System.Web.UI.WebControls Namespace Samples.AspNet.VB ' EmployeeLogic is a stateless business object that encapsulates ' the operations one can perform on a NorthwindEmployee object. ' Public Class EmployeeLogic ' Returns a collection of NorthwindEmployee objects. Public Shared Function GetAllEmployees() As ICollection Dim al As New ArrayList() Dim cts As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("NorthwindConnection") Dim sds As New SqlDataSource(cts.ConnectionString, "SELECT EmployeeID FROM Employees") Try Dim IDs As IEnumerable = sds.Select(DataSourceSelectArguments.Empty) ' Iterate through the Enumeration and create a ' NorthwindEmployee object for each ID. Dim enumerator As IEnumerator = IDs.GetEnumerator() While enumerator.MoveNext() ' The IEnumerable contains DataRowView objects. Dim row As DataRowView = CType(enumerator.Current,DataRowView) Dim id As String = row("EmployeeID").ToString() Dim nwe As New NorthwindEmployee(id) ' Add the NorthwindEmployee object to the collection. al.Add(nwe) End While Finally ' If anything strange happens, clean up. sds.Dispose() End Try Return al End Function 'GetAllEmployees ' To support basic filtering, the employees cannot ' be returned as an array of objects, rather as a ' DataSet of the raw data values. Public Shared Function GetAllEmployeesAsDataSet() As DataSet Dim employees As ICollection = GetAllEmployees() Dim ds As New DataSet("Table") ' Create the schema of the DataTable. Dim dt As New DataTable() Dim dc As DataColumn dc = New DataColumn("FirstName", GetType(String)) dt.Columns.Add(dc) dc = New DataColumn("LastName", GetType(String)) dt.Columns.Add(dc) dc = New DataColumn("Title", GetType(String)) dt.Columns.Add(dc) dc = New DataColumn("Courtesy", GetType(String)) dt.Columns.Add(dc) dc = New DataColumn("Supervisor", GetType(Int32)) dt.Columns.Add(dc) ' Add rows to the DataTable. Dim emplEnum As IEnumerator = employees.GetEnumerator() Dim row As DataRow Dim ne As NorthwindEmployee While emplEnum.MoveNext() ne = CType(emplEnum.Current, NorthwindEmployee) row = dt.NewRow() row("FirstName") = ne.FirstName row("LastName") = ne.LastName row("Title") = ne.Title row("Courtesy") = ne.Courtesy row("Supervisor") = ne.Supervisor dt.Rows.Add(row) End While ' Add the complete DataTable to the DataSet. ds.Tables.Add(dt) Return ds End Function 'GetAllEmployeesAsDataSet Public Shared Function GetEmployee(anID As Object) As NorthwindEmployee If anID.Equals("-1") OrElse anID.Equals(DBNull.Value) Then Return New NorthwindEmployee() Else Return New NorthwindEmployee(anID) End If End Function 'GetEmployee Public Shared Sub UpdateEmployeeInfo(ne As NorthwindEmployee) Dim retval As Boolean = ne.Save() If Not retval Then Throw New NorthwindDataException("UpdateEmployee failed.") End If End Sub 'UpdateEmployeeInfo Public Shared Sub DeleteEmployee(ne As NorthwindEmployee) Dim retval As Boolean = ne.Delete() If Not retval Then Throw New NorthwindDataException("DeleteEmployee failed.") End If End Sub 'DeleteEmployee End Class 'EmployeeLogic Public Class NorthwindEmployee Public Sub New() ID = DBNull.Value aLastName = "" aFirstName = "" aTitle = "" titleOfCourtesy = "" reportsTo = - 1 End Sub 'NewNew Public Sub New(anID As Object) Me.ID = anID Dim cts As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("NorthwindConnection") Dim conn As New SqlConnection(cts.ConnectionString) Dim sc As New SqlCommand(" SELECT FirstName,LastName,Title,TitleOfCourtesy,ReportsTo " & _ " FROM Employees " & _ " WHERE EmployeeID = @empId", conn) ' Add the employee ID parameter and set its value. sc.Parameters.Add(New SqlParameter("@empId", SqlDbType.Int)).Value = Int32.Parse(anID.ToString()) Dim sdr As SqlDataReader = Nothing Try conn.Open() sdr = sc.ExecuteReader() ' Only loop once. If Not (sdr Is Nothing) AndAlso sdr.Read() Then ' The IEnumerable contains DataRowView objects. Me.aFirstName = sdr("FirstName").ToString() Me.aLastName = sdr("LastName").ToString() Me.aTitle = sdr("Title").ToString() Me.titleOfCourtesy = sdr("TitleOfCourtesy").ToString() If Not sdr.IsDBNull(4) Then Me.reportsTo = sdr.GetInt32(4) End If Else Throw New NorthwindDataException("Data not loaded for employee id.") End If Finally Try If Not (sdr Is Nothing) Then sdr.Close() End If conn.Close() Catch se As SqlException ' Log an event in the Application Event Log. Throw End Try End Try End Sub 'NewNew Private ID As Object Public ReadOnly Property EmpID() As String Get Return ID.ToString() End Get End Property Private aLastName As String Public Property LastName() As String Get Return aLastName End Get Set aLastName = value End Set End Property Private aFirstName As String Public Property FirstName() As String Get Return aFirstName End Get Set aFirstName = value End Set End Property Public ReadOnly Property FullName() As String Get Return FirstName & " " & LastName End Get End Property Private aTitle As String Public Property Title() As String Get Return aTitle End Get Set aTitle = value End Set End Property Private titleOfCourtesy As String Public Property Courtesy() As String Get Return titleOfCourtesy End Get Set titleOfCourtesy = value End Set End Property Private reportsTo As Integer Public Property Supervisor() As Integer Get Return reportsTo End Get Set reportsTo = value End Set End Property Public Function Save() As Boolean ' Implement persistence logic. Return True End Function 'Save Public Function Delete() As Boolean ' Implement delete logic. Return True End Function 'Delete End Class 'NorthwindEmployee Friend Class NorthwindDataException Inherits Exception Public Sub New(msg As String) MyBase.New(msg) End Sub 'NewNew End Class 'NorthwindDataException End Namespace
Available since 2.0