SQL 2005 and SQL2008 Enabling Notifications. SQL Chache Dependancy Part-I

Posted: February 18, 2008 in ASP.NET, C#, SQL
Tags: , ,

The only configuration step you need to perform is to make sure your database has the ENABLE_BROKER flag set.
Use Northwind
ALTER DATABASE Northwind SET ENABLE_BROKER

Notifications work with SELECT queries and stored procedures. However, some restrictions
exist for the SELECT syntax you can use. To properly support notifications, your command must
adhere to the following rules:
 • You must fully qualify table names in the form [Owner].table, as in dbo.Employees (not just Employees).
 • Your query cannot use an aggregate function, such as COUNT(), MAX(), MIN(), or AVERAGE().
 • You cannot select all columns with the wildcard * (as in SELECT * FROM Employees). Instead,
you must specifically name each column so that SQL Server can properly track changes that
do and do not affect the results of your query.
Here’s an acceptable command: SELECT EmployeeID, FirstName, LastName, City FROM dbo.Employees

Creating the Cache Dependency:
You use a different syntax to use SQL cache dependencies with SQL Server 2005 (and 2008) than you do
with SQL Server 2000. That’s because it’s not enough to simply identify the database name and table—
instead, SQL Server needs to know the exact database command you’re using to retrieve your data.
If you use programmatic caching, you must create the SqlCacheDependency using the constructor
that accepts a SqlCommand object. Here’s an example:

// Create the ADO.NET objects.
string connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
SqlConnection con = new SqlConnection(connectionString);
string query ="SELECT EmployeeID, FirstName, LastName, City FROM dbo.Employees";
SqlCommand cmd = new SqlCommand(query, con);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
// Fill the DataSet.
DataSet ds = new DataSet();
adapter.Fill(ds, "Employees");
// Create the dependency.
SqlCacheDependency empDependency = new SqlCacheDependency(cmd);


// Add a cache item that will be invalidated if one of its records changes
// (or a new record is added in the same range).
Cache.Insert("Employees", ds, empDependency);

You also need to call the static SqlDependency.Start() method to initialize the listening service
on the web server. This needs to be performed only once for each database connection. One place
you can call the Start() method is in the Application_Start() method of the global.asax file.
SqlDependency.Start(connectionString);
This method opens a new, nonpooled connection to the database. ASP.NET checks the queue
for notifications using this connection. The first time you call Start(), a new queue is created with a
unique, automatically generated name, and a new notification service is created for that queue.
Then, the listening begins. When a notification is received, the web server pulls it from the queue,
raises the SqlDependency.OnChange event, and invalidates the cached item.
Even if you have dependencies on several different tables, the same queue is used for all of
them. That means you need only a single call to SqlDependency.Start(). If you inadvertently call the
Start() method more than once, nothing happens.
Finally, you can use the following code to detach the listener:
SqlDependency.Stop(connectionString);
Typically, you’ll use this when the Application_End() method is called to detach the listener and
release all resources.

 N.B: The content of the book is mostly taken from Pro ASP.NET 3.5 from APRESS(Second Edition).

And This Blog is for My Personal documentation. Next Post Will be on CUSTOM CACHE DEPENDENCY- SQL Chache Dependancy Part-II and the Next one will be on A Custom Cache Dependency Using Message Queues- SQL Chache Dependancy Part-III.

Advertisements
Comments
  1. chinmayv84 says:

    HI,
    I want to implement sql cache dependency with sql server 2008 and iis7.
    it work fine with sql server 2008 and iis6 but not with iis7.
    any specific configurations that we need to do?

  2. dennisgorelik says:

    I don’t think the issue is with iis7.
    Most likely the problem is with your server database permissions being a little bit out of whack (because you restored the database).

    Run:
    sp_changedbowner [YourServerName\YourUsername]
    in SSMS for your database.

    If it didn’t help, read more:
    Troubleshooting SqlCacheDependency in SQL Server 2008 and SQL Server 2005

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s