ObjectDataSource.SqlCacheDependency Property


The .NET API Reference documentation has a new home. Visit the .NET API Browser on docs.microsoft.com to see the new experience.

Gets or sets a semicolon-delimited string that indicates which databases and tables to use for the Microsoft SQL Server cache dependency.

Namespace:   System.Web.UI.WebControls
Assembly:  System.Web (in System.Web.dll)

public virtual string SqlCacheDependency { get; set; }

Property Value

Type: System.String

A 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.CS" Assembly="Samples.AspNet.CS" %>
<%@ Page language="c#" %>

The page uses an example configuration that includes
connection strings and a defined SqlCacheDependecy.

<?xml version="1.0"?>

    <add name="MyNorthwind"
         connectionString="Data Source="localhost";Integrated Security="SSPI";Initial Catalog="Northwind""
         providerName="System.Data.SqlClient" />

      <sqlCacheDependency enabled="true">
            pollTime="120000" />


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
<html xmlns="http://www.w3.org/1999/xhtml" >
    <title>ObjectDataSource - C# Example</title>
    <form id="Form1" method="post" runat="server">

          datasourceid="ObjectDataSource1" />

          sqlcachedependency="Northwind:Employees" />


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.

namespace Samples.AspNet.CS {

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
using System.Web.UI.WebControls;
  // 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 static ICollection GetAllEmployees () {
      ArrayList al = new ArrayList();

      ConnectionStringSettings cts = ConfigurationManager.ConnectionStrings["NorthwindConnection"];

      SqlDataSource sds
        = new SqlDataSource(cts.ConnectionString,
                            "SELECT EmployeeID FROM Employees");
      try {
        IEnumerable IDs = sds.Select(DataSourceSelectArguments.Empty);

        // Iterate through the Enumeration and create a
        // NorthwindEmployee object for each ID.
        IEnumerator enumerator = IDs.GetEnumerator();
        while (enumerator.MoveNext()) {
          // The IEnumerable contains DataRowView objects.
          DataRowView row = enumerator.Current as DataRowView;
          string id = row["EmployeeID"].ToString();
          NorthwindEmployee nwe = new NorthwindEmployee(id);
          // Add the NorthwindEmployee object to the collection.
      finally {
        // If anything strange happens, clean up.

      return al;

    // To support basic filtering, the employees cannot
    // be returned as an array of objects, rather as a
    // DataSet of the raw data values.
    public static DataSet GetAllEmployeesAsDataSet () {
      ICollection employees = GetAllEmployees();

      DataSet ds = new DataSet("Table");

      // Create the schema of the DataTable.
      DataTable dt = new DataTable();
      DataColumn dc;
      dc = new DataColumn("FirstName", typeof(string)); dt.Columns.Add(dc);
      dc = new DataColumn("LastName",  typeof(string)); dt.Columns.Add(dc);
      dc = new DataColumn("Title",     typeof(string)); dt.Columns.Add(dc);
      dc = new DataColumn("Courtesy",  typeof(string)); dt.Columns.Add(dc);
      dc = new DataColumn("Supervisor",typeof(Int32));  dt.Columns.Add(dc);

      // Add rows to the DataTable.
      IEnumerator emplEnum = employees.GetEnumerator();
      DataRow row;
      NorthwindEmployee ne;
      while (emplEnum.MoveNext()) {
        ne = emplEnum.Current as NorthwindEmployee;
        row = dt.NewRow();
        row["FirstName"]  = ne.FirstName;
        row["LastName"]   = ne.LastName;
        row["Title"]      = ne.Title;
        row["Courtesy"]   = ne.Courtesy;
        row["Supervisor"] = ne.Supervisor;
      // Add the complete DataTable to the DataSet.

      return ds;
    public static NorthwindEmployee GetEmployee(object anID) {
      if (anID.Equals("-1") ||
          anID.Equals(DBNull.Value) ) {
        return new NorthwindEmployee();
      else {
        return new NorthwindEmployee(anID);

    public static void UpdateEmployeeInfo(NorthwindEmployee ne) {
      bool retval = ne.Save();
      if (! retval) { throw new NorthwindDataException("UpdateEmployee failed."); }

    public static void DeleteEmployee(NorthwindEmployee ne) {
      bool retval = ne.Delete();
      if (! retval) { throw new NorthwindDataException("DeleteEmployee failed."); }

  public class NorthwindEmployee {

    public NorthwindEmployee () {
      ID = DBNull.Value;
      lastName = "";
      firstName = "";
      titleOfCourtesy = "";
      reportsTo = -1;

    public NorthwindEmployee (object anID) {
      this.ID = anID;

      SqlConnection conn
        = new SqlConnection (ConfigurationManager.ConnectionStrings["NorthwindConnection"].ConnectionString);
      SqlCommand sc =
        new SqlCommand(" SELECT FirstName,LastName,Title,TitleOfCourtesy,ReportsTo " +
                       " FROM Employees " +
                       " WHERE EmployeeID = @empId",
      // Add the employee ID parameter and set its value.
      sc.Parameters.Add(new SqlParameter("@empId",SqlDbType.Int)).Value = Int32.Parse(anID.ToString());
      SqlDataReader sdr = null;

      try {
        sdr = sc.ExecuteReader();

        // Only loop once.
        if (sdr != null && sdr.Read()) {
          // The IEnumerable contains DataRowView objects.
          this.firstName        = sdr["FirstName"].ToString();
          this.lastName         = sdr["LastName"].ToString();
          this.title            = sdr["Title"].ToString();
          this.titleOfCourtesy  = sdr["TitleOfCourtesy"].ToString();
          if (! sdr.IsDBNull(4)) {
            this.reportsTo        = sdr.GetInt32(4);
        else {
          throw new NorthwindDataException("Data not loaded for employee id.");
      finally {
        try {
          if (sdr != null) sdr.Close();
        catch (SqlException) {
          // Log an event in the Application Event Log.

    private object ID;
    public string EmpID {
      get { return ID.ToString();  }

    private string lastName;
    public string LastName {
      get { return lastName; }
      set { lastName = value; }

    private string firstName;
    public string FirstName {
      get { return firstName; }
      set { firstName = value;  }

    public string FullName {
      get { return FirstName + " " + LastName; }

    private string title;
    public String Title {
      get { return title; }
      set { title = value; }

    private string titleOfCourtesy;
    public string Courtesy {
      get { return titleOfCourtesy; }
      set { titleOfCourtesy = value; }

    private int    reportsTo;
    public int Supervisor {
      get { return reportsTo; }
      set { reportsTo = value; }

    public bool Save () {
      // Implement persistence logic.
      return true;

    public bool Delete () {
      // Implement delete logic.
      return true;

  internal class NorthwindDataException: Exception {
    public NorthwindDataException(string msg) : base (msg) { }

.NET Framework
Available since 2.0
Return to top