Compartir a través de

Caching Data with the SqlDataSource Control

  • Artículo

The SqlDataSource control can cache data that it has retrieved, which can enhance the performance of your applications by avoiding the need to re-run resource-intensive queries. Caching is useful primarily in situations where the data does not change frequently.

Additionally, when using the SqlDataSource control with the System.Data.SqlClient provider, you can make use of the SqlCacheDependency object. This enables the SqlDataSource control to refresh the cache only if the data returned by the SelectCommand has been modified in the database.

Enabling Caching with the SqlDataSource Control

The SqlDataSource control can cache data when its DataSourceMode property is set to DataSet. Caching is not enabled by default, but you can enable it by setting the EnableCaching property to true.

Cached data is refreshed based on a time interval. You can set the CacheDuration property to the number of seconds to wait before the cache is refreshed. The SqlDataSource control maintains a separate cache item for each combination of ConnectionString, SelectCommand, and SelectParameters values.

You can further control the behavior of the SqlDataSource cache by setting the CacheExpirationPolicy property. A value of Absolute forces the cache to be refreshed when the CacheDuration value is exceeded. Setting the CacheExpirationPolicy property to Sliding refreshes the cache only if the CacheDuration value has been exceeded since the last time the cached item was accessed.

The following code example shows a SqlDataSource control configured to refresh data every 20 seconds:

<%@ Page language="VB" %>

<!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 runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
        <form id="form1" runat="server">

            <asp:SqlDataSource
                id="SqlDataSource1"
                runat="server"
                DataSourceMode="DataSet"
                ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
                EnableCaching="True"
                CacheDuration="20"
                SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees">
            </asp:SqlDataSource>

            <asp:GridView
                id="GridView1"
                runat="server"
                AutoGenerateColumns="False"
                DataSourceID="SqlDataSource1">
                <columns>
                    <asp:BoundField HeaderText="First Name" DataField="FirstName" />
                    <asp:BoundField HeaderText="Last Name" DataField="LastName" />
                    <asp:BoundField HeaderText="Title" DataField="Title" />
                </columns>
            </asp:GridView>

        </form>
    </body>
</html>

<%@ Page language="C#" %>

<!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 runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
        <form id="form1" runat="server">

            <asp:SqlDataSource
                id="SqlDataSource1"
                runat="server"
                DataSourceMode="DataSet"
                ConnectionString="<%$ ConnectionStrings:MyNorthwind%>"
                EnableCaching="True"
                CacheDuration="20"
                SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees">
            </asp:SqlDataSource>

            <asp:GridView
                id="GridView1"
                runat="server"
                AutoGenerateColumns="False"
                DataSourceID="SqlDataSource1">
                <columns>
                    <asp:BoundField HeaderText="First Name" DataField="FirstName" />
                    <asp:BoundField HeaderText="Last Name" DataField="LastName" />
                    <asp:BoundField HeaderText="Title" DataField="Title" />
                </columns>
            </asp:GridView>

        </form>
    </body>
</html>

<!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 runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
        <form id="form1" runat="server">

            <asp:SqlDataSource
                id="SqlDataSource1"
                runat="server"
                DataSourceMode="DataSet"
                ConnectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;"
                EnableCaching="True"
                CacheDuration="20"
                SelectCommand="SELECT EmployeeID,FirstName,LastName,Title FROM Employees">
            </asp:SqlDataSource>

            <asp:GridView
                id="GridView1"
                runat="server"
                AutoGenerateColumns="False"
                DataSourceID="SqlDataSource1">
                <Columns>
                    <asp:BoundField HeaderText="First Name" DataField="FirstName" />
                    <asp:BoundField HeaderText="Last Name" DataField="LastName" />
                    <asp:BoundField HeaderText="Title" DataField="Title" />
                </Columns>
            </asp:GridView>

        </form>
    </body>
</html>

Using the SqlCacheDependency Object

The SqlDataSource control supports an optional expiration policy based on a SqlCacheDependency object. You can use the SqlCacheDependency object to ensure that cached data is refreshed only when the source table in the database has been modified. In order to use the SqlCacheDependency object, the Microsoft SQL Server notification service must be running for the database server.

When your data source is SQL Server 7.0 or later, you can use the SqlCacheDependency object to poll the database for changes by setting the SqlDataSource control's SqlCacheDependency property to a string consisting of a connection string and table identifiers in the form "ConnectionString:Table". If the SqlCacheDependency involves more than one table, the connection string and table name pairs are separated by semicolons, as in the following example:

"ConnectionString1:Table1;ConnectionString2:Table2".

When your data source is Microsoft SQL Server 2005, you have the additional option to have SQL Server notify your application of changes instead of polling for changes. You can use the notification model by setting the SqlCacheDependency property to the string "CommandNotification". For more information on the SqlCacheDependency object, see Caching in ASP.NET with the SqlCacheDependency Class.

The following code example demonstrates how to create a Microsoft SQL Server cache dependency and set the SqlCacheDependency property of a SqlDataSource control. In the example, the database is polled every 120 seconds. If the data in the Northwind Employees table changes during that time, the data cached by the SqlDataSource control and displayed by the GridView control is refreshed the next time the database is polled.

<%@ 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 runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="Form1" method="post" runat="server">

        <asp:gridview
          id="GridView1"
          runat="server"
          datasourceid="SqlDataSource1" />

        <asp:sqldatasource
          id="SqlDataSource1"
          runat="server"
          connectionstring="<%$ ConnectionStrings:MyNorthwind%>"
          selectcommand="SELECT EmployeeID,FirstName,Lastname FROM Employees"
          enablecaching="True"
          cacheduration="300"
          cacheexpirationpolicy="Absolute"
          sqlcachedependency="Northwind:Employees" />

    </form>
  </body>
</html>

<%@ Page language="c#" %>

<!--

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 runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="Form1" method="post" runat="server">

        <asp:gridview
          id="GridView1"
          runat="server"
          datasourceid="SqlDataSource1" />

        <asp:sqldatasource
          id="SqlDataSource1"
          runat="server"
          connectionstring="<%$ ConnectionStrings:MyNorthwind%>"
          selectcommand="SELECT EmployeeID,FirstName,Lastname FROM Employees"
          enablecaching="True"
          cacheduration="300"
          cacheexpirationpolicy="Absolute"
          sqlcachedependency="Northwind:Employees" />

    </form>
  </body>
</html>

<%@ Page language="VJ#" %>

<!--

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

<?xml version="1.0"?>
<configuration>

  <connectionStrings>
    <add name = "NorthwindConnection" connectionString = "Data Source="localhost";Integrated Security="SSPI";Initial Catalog="Northwind"" />
  </connectionStrings>

  <system.web>

    ...

    <cache>
      <sqlCacheDependency enabled="true">
        <databases>
          <add
            name="Northwind_Remote"
            connectionStringName="NorthwindConnection"
            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 runat="server">
    <title>ASP.NET Example</title>
</head>
<body>
    <form id="Form1" method="post" runat="server">

        <asp:gridview
          id="GridView1"
          runat="server"
          datasourceid="SqlDataSource1" />

        <asp:sqldatasource
          id="SqlDataSource1"
          runat="server"
          connectionstring="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind"
          selectcommand="SELECT EmployeeID,FirstName,Lastname FROM Employees"
          enablecaching="True"
          cacheduration="300"
          cacheexpirationpolicy="Absolute"
          sqlcachedependency="Northwind_Remote:Employees" />

    </form>
  </body>
</html>

See Also

Other Resources

ASP.NET Caching
SqlDataSource Web Server Control
Data Source Web Server Controls