[Study] SqlCacheDependency using DAAB and SQL Server 2005 - Enterprise Library 2.0

SqlCacheDependency using DAAB and SQL Server 2005 - Enterprise Library 2.0

SqlCacheDependency using DAAB and SQL Server 2005

by David Hayden ( .NET Developer )

 

I didn't think to include this in my previous post, SqlCacheDependency using ASP.NET 2.0 and SQL Server 2005, but with only a few minor changes you can use the Enterprise Library 2.0 DAAB with SqlCacheDependency and SQL Server 2005. For completeness I will include all the previous information, but the only code that changes is the code to create the command object and get the DataSet. The code is reduced by about 5 or 6 lines and there is no concern of connection management.

Here is now the previous article using the Enterprise Library 2.0 DAAB instead of SqlClient, except for the fact that we need to cast DbCommand to SqlCommand for use in the constructor of the SqlCacheDependency object.

 

SqlCacheDependency using ASP.NET 2.0 and SQL Server 2005 is a beautiful thing :) Although getting SqlCacheDependency to work with SQL Server 2000 is not rocket science, there are a few extra moving parts that need to be set-up in your web.config and on SQL Server 2000. When using SQL Server 2005, all of that goes away :)

 

Enable Service Broker

Before SqlCacheDependency will work with SQL Server 2005, you first have to enable Service Broker, which is reponsible for the notification services that let the web cache know a change has been made to the underlying database and that the item in the cache must be removed.

ALTER DATABASE Store SET ENABLE_BROKER;
GO

SqlCacheDependency.Start() in Global.asax

In ASP.NET, you need to run SqlCacheDependency.Start(connectionString) in the Global.asax:

void Application_Start(object sender, EventArgs e) 
{
    string connectionString = WebConfigurationManager.
        ConnectionStrings["Catalog"].ConnectionString;
    SqlDependency.Start(connectionString);
}

SqlCacheDependency in ASP.NET 2.0 Example

Now you can just create your SqlCacheDependency as normal in your ASP.NET 2.0 page. Here is a simple example:

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable categories = (DataTable)Cache.Get("Categories");

        if (categories == null)
        {
            categories = GetCategories();
            Label1.Text = System.DateTime.Now.ToString();
        }

        GridView1.DataSource = categories.DefaultView;
        GridView1.DataBind();
    }

    private DataTable GetCategories()
    {
        Database db = DatabaseFactory.CreateDatabase();

        SqlCommand command = (SqlCommand)db.
              GetSqlStringCommand("SELECT CategoryID,Code,
                             Title FROM dbo.Categories");

        SqlCacheDependency dependency =
                new SqlCacheDependency(command);

        DataSet dataset = db.ExecuteDataSet(command);

        DataTable categories = dataset.Tables[0];

        Cache.Insert("Categories", categories, dependency);

        return categories;    }
}

Since I am using a normal SELECT statement above, there are a number of rules one needs to follow, such as

• You cannot use SELECT * - use individual fields
• Must use fully qualified name of table, e.g. dbo.Categories

and a whole bunch of other rules outlined here on MSDN.

There are other ways to use SqlCacheDependency as well, such as with Ouput Caching. I can show those at another time.

Source: David Hayden ( .NET Developer )

Filed: ASP.NET 2.0, SQL Server 2005, Enterprise Library 2.0

posted on Sunday, April 30, 2006 4:09 PM