Caching with SQL server in ASP.NET 2.0

JustChiragPatel

Rate me:

1.42/5 (13 votes)

17 May 20072 min read

43.6K

use caching to reduce postback time in asp.net 2.0

Introduction

This type of SQL cacheing invalidation is working only with SQL server 7 or above.

Caching has been improved in ASP.NET 2.0. The most interesting feature is the introduction of database-triggered cache invalidation.

Three ways to do this SQL based invalidation.

1. Declarative Output caching by using the OutputCache directive.

2. Programmatic Output caching by using SqlCacheDependency object.

3. Cache API

In old framework 1.x there was polling mechanism for checking invalidity of data, but in Framework 2.0 and SQL server 2005 this process is completely reverse, means now sql server 2005 will notify asp pages about change in data bu using IIS posrt 80 by sending HTTP request.

You can configure SQL Server 2005 to notify your ASP.NET application whenever changes have been made to a database, a database table, or a database row.

To configure SQL server for cacheing we can use SqlCacheDependencyAdmin class,

The SqlCacheDependencyAdmin class has five important methods:

DisableNotifications—Disables SQL Cache Invalidation for a particular database.
DisableTableForNotifications—Disables SQL Cache Invalidation for a particular table in a database.
EnableNotifications—Enables SQL Cache Invalidation for a particular database.
EnableTableForNotifications—Enables SQL Cache Invalidation for a particular table in a database.
GetTablesEnabledForNotifications—Returns a list of all tables enabled for SQL Cache Invalidation.

In ASP.NET 2.0 we can also create custome cache dependency.

Using the code

//First you have add some lines into your Web.config file
<configuration>
      
  <connectionStrings>
    <add name="mySqlServer" 
      connectionString="Server=localhost;Database=Pubs" />
  </connectionStrings>
        
  <system.web>

    <caching>
      <sqlCacheDependency enabled="true">
      <databases>
      <add
            name="Pubs"
            connectionStringName="mySqlServer"
            pollTime="60000" />
      </databases>
      </sqlCacheDependency>
    </caching>
    </system.web>
</configuration>
//Within the <databases> subsection, you can list one or more databases that
//you want to poll for changes

Using SQL Cache Invalidation with Page Output Caching

 <%@ OutputCache SqlDependency="Pubs:Titles" 
    Duration="6000" VaryByParam="none" %>
<html>
<head runat="server">
    <title>Output Cache Titles</title>
</head>
<body>
    <form id="form1" runat="server">
    
    <%= DateTime.Now %>

    <asp:GridView 
      ID="grdTitles" 
      DataSourceID="SqlDataSource1" 
      Runat="Server" />    
    
    <asp:SqlDataSource
      ID="SqlDataSource1"
      SelectCommand="Select * FROM Titles"
      ConnectionString="<%$ ConnectionStrings:mySqlServer %>"
      Runat="Server" />
    
    </form>
</body>
</html>
//Notice that the SqlDependency attribute references the name of the 
//database defined within the Web configuration file.

Using SQL Cache Invalidation with the DataSource Control

//You should consider using SQL Cache Invalidation with the DataSource 
//controls when you need to work with the same database data in multiple 
//pages. The SqlDataSource, AccessDataSource, and ObjectDataSource controls 
//all support a SqlCacheDependency property.
 <html>
<head id="Head1" runat="server">
    <title>SqlDataSource Caching</title>
</head>
<body>
    <form id="form1" runat="server">

        <%= DateTime.Now %>

        <asp:GridView 
            ID="grdTitles" 
            DataSourceId="SqlDataSource1"
            Runat="server" />
            
        <asp:SqlDataSource 
            ID="SqlDataSource1" 
            EnableCaching="true"
            SqlCacheDependency="Pubs:Titles"
            SelectCommand="select * from titles"
            ConnectionString="<%$ ConnectionStrings:mySqlServer %>"
            Runat="server" />
   
    </form>
</body>
</html>

Caching has a dramatic impact on the performance of database-driven Web applications. Fortunately, the ASP.NET 2.0 framework includes a number of significant new enhancements that make it easier to take advantage of caching in your applications.

The new DataSource controls include properties that make it easy to cache database data in memory. By taking advantage of the DataSource controls, you can retrieve database data and cache the data without writing a single line of code.

Points of Interest

I like to Share my knowledge with guys like you, because i am also one of you, that if i dont know anything then i come to you...so keep exchange of knowledge...

History

keep attached with my simple way series articles....

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Written By

JustChiragPatel

Software Developer

India

Chirag Patel, a Programmer Analyst in a well known IT company working on .NET Technologies since last 2 years. He is interested in Pure business logic and fuzzy logic. his area of interest is in C#.NET, VB.NET and MSSQL 2005.

catch me on: http://groups.google.com/group/codewar

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.