What everyone wants is cached data that is always current. This might sound like a contradiction or impossible dream, but the SqlDependency class can deliver this. The SqlDependency class provides a way for your application to be notified when the data in a Microsoft SQL Server database changes. You can use the notification to expire the cache and reload the data from the database server.
What to Cache
You should consider three factors when deciding what data to cache.
Frequency of change
Reuse
Size
Data that never changes can easily be cached. Because this data is read-only, you don't even need to use the SqlDependency class to refresh it. An example of read-only data is the list of states in the United States. Sure, the data could change, but in the unlikely event that it did, you could simply make the change and have users restart their application to get refreshed data. We'll look more closely at the SqlDependency class in a bit, but you can imagine that sending notifications to remote machines can be resource intensive for the database server, so adding a notification request for such read-only data would waste database resources.
Data that can be changed by a user but is not changed often is read-mostly data. You can cache read-mostly data and use the SqlDependency class to keep it fresh. An example of read-mostly data is a territory list for salespeople, or even a list of salespeople. Depending on where you work, this data can change several times a day, but it is still relatively stable.
Data should also be cached if you are likely to reuse it many times. The salesperson list might be cached for order entry people who need to assign a salesperson to each order received. Data that is relatively stable but not reused often should not be cached for example, the list of orders that a salesperson received.
Caching small lists, such as a territory list or a category list, makes sense; caching large lists, such as the complete customer list or order list, does not. The problem with caching a complete order list is that the list changes all day long and you probably don't need to see all orders at once. A traveling salesperson might want to cache the list of her customer's orders, though. Be careful about overloading the memory in your computer by caching large items that are not frequently used.
Is the SqlDependency Class for You?
Sending notifications to clients can be a resource-intensive task and the situation would be worse if the query notification were to result in 1000 clients re-querying a large table at the same time. When a notification is sent to the client, it is simply a message that states that the table changed, but you don't know what data changed. This means that you have to refresh the entire table. If you are writing an application that caches all of the sales orders for 1000 order entry people, you might end up generating large amounts of database activity because the clients will re-query the database for fresh data every time a new order is entered. Therefore, you should keep the client count and the data size to a minimum.
The SqlDependency class is best suited for Web applications or middle-tier components with a handful of clients (Web farm servers or component servers) that service hundreds or thousands of users at any given time. You can therefore cache large tables, but you might want to stagger the reload of these tables by simply expiring the cache when your application receives a change notification and reload the cache the next time a user references the data.
How does SqlDependency Work?
The SqlDependency object does not work until you execute the static (Visual Basic shared) Start method, which starts a client-side process to communicate with SQL Server. The Start method requires a valid connection string and needs to be executed only once at the beginning of your application. When the application is closing, you can execute the Stop method to discontinue the client-side process.
The SqlCommand object has a Notification property that you can set by passing the SqlCommand object to the constructor of a SqlDependency object. When the command is sent to the database server, additional packets are sent to SQL Server to request a query notification. You can see these packets by using the SQL Profiler and monitoring query notification events. Figure 12-1 shows the SQL Profiler trace when a SQL dependency is set.
Figure 12-1: The SQL Profiler trace when a SQL command is sent to the database that has its Notification property set
When SQL receives the request for query notification, it sends a subscription request to the query notification infrastructure and executes the SQL command, as shown in Figure 12-2. SQL Server then watches the DML (Data Manipulation Language) statements for any changes that might affect the query results. If a change to the data affects this query result, a message is sent to the Service Broker and the subscription is removed from the query notification infrastructure. With the SqlDependency object, this message fires back to the application through the sp_DispatcherProc stored procedure. The message is received by the client application in the form of the OnChange event on the SqlDependency object.
Figure 12-2: The SqlDependency object operation
When the OnChange event is fired, no data is sent that tells you what row was modified. You have to execute the SqlCommand object again to get the data, and you also have to re-create the SqlDependency object to be notified of more changes at the database.
Note that SQL Server is set up to be proactive about the notification you receive, which means that sometimes you might get a notification when no data has changed. For example, if the database server is restarted or if there is a schema change, you will receive a notification.
Query Considerations
For a query to work properly with SqlDependency, it must be written correctly. Here is a typical example of a Northwind database query that does not work.
Incorrect SQL Statement
SELECT * FROM [Order Details]
One problem with the preceding query is that you cannot use the asterisk (*) in it. Instead, you must spell out the field names. Another problem is that the table name is defined using a one-part name, but two-part table names are required. The corrected query looks like the following.
Correct SQL Query Statement
SELECT OrderID, ProductID, UnitPrice, Quantity, Discount FROM dbo.[Order Details]
SqlDependency Setup in SQL Server
To get the SqlDependency object to work, you must use SQL Server 2005. The SQL QueryNotificationService does not exist on SQL Server Express. Perform the following configuration changes using Microsoft SQL Server Management Studio with a New Query window.
Granting Permissions
You must grant send permissions to the Guest user on the QueryNotificationService in the MSDB database because the SqlDependency object uses the Service Broker to post messages to the QueryNotificationService when a data change occurs.
USE MSDB GRANT SEND ON SERVICE:: [http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService] TO GUEST
Note that the complete URL-like name for the QueryNotificationService is case sensitive, which database administrators might not be accustomed to.
Enabling CLR Execution on the Database Server
When a message arrives at the service queue, it is dispatched by a stored procedure with a queue named sp_DispatcherProc that contains .NET code, so you must have the common language runtime (CLR) enabled on this instance of SQL Server. The command to enable the CLR is as follows.
USE MASTER EXEC sp_configure 'clr enabled', 1 RECONFIGURE
Enabling the Service Broker
You must verify and possibly enable the Service Broker on your database. To verify that the Service Broker is enabled on your database, you can use the following command.
This command returns 1 for true and 0 for false (or null if you misspell the database name). To enable the Service Broker, you run this code:
USE MASTER ALTER DATABASE Northwind SET ENABLE_BROKER
Using the SqlDependency Object
The following code sample is a simple Windows Forms application with a DataGridView object on the form. When the form is loaded, the SqlDependency.Start method is executed, and then a call is made to the UpdateGrid method, which populates the grid. The UpdateGrid method creates SqlConnection, SqlCommand, and SqlDependency objects. The SqlCommand object is then executed to load a DataTable that is bound to the DataGridView object. Notice that the DataTable object is assigned using the DataGridView object's Invoke method because the UpdateGrid method is also called from the OnChange event of the SqlDependency object, which is executed on a different thread than the form's thread. When the form is closed and the application is ending, the SqlDependency.Stop method is executed to discontinue the client-side services.
Visual Basic
Imports System.Data Imports System.Data.SqlClient Public Class Form1 'requires Northwind to be installed 'on default SQL Server instance Dim connect As String = _ "Server=.;Database=Northwind;Integrated Security=True" Delegate Sub GridDelegate(ByVal table As DataTable) Dim WithEvents dep As SqlDependency Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load SqlDependency.Start(connect) UpdateGrid() End Sub Private Sub Form1_FormClosed(ByVal sender As System.Object, _ ByVal e As System.Windows.Forms.FormClosedEventArgs) _ Handles MyBase.FormClosed SqlDependency.Stop(connect) End Sub Private Sub UpdateGrid() Dim sql As String = _ "SELECT OrderID, ProductID, UnitPrice, Quantity, " _ + "Discount FROM dbo.[Order Details]" Dim dt As New DataTable() Using cn As New SqlConnection(connect) Using cmd As New SqlCommand(sql, cn) cn.Open() dep = New SqlDependency(cmd) Dim rdr As SqlDataReader = cmd.ExecuteReader() dt.Load(rdr) End Using End Using 'Use anonymous method to assure 're-assignment is done by the form's thread DataGridView1.Invoke(New GridDelegate(AddressOf assignDataSource), dt) End Sub Private Sub dep_OnChange(ByVal sender As Object, _ ByVal e As SqlNotificationEventArgs) Handles dep.OnChange System.Diagnostics.Debug.WriteLine("Received OnChange Event") If (e.Info = SqlNotificationInfo.Invalid) Then MessageBox.Show("Invalid Statement") Return End If UpdateGrid() End Sub Private Sub assignDataSource(ByVal dt As DataTable) DataGridView1.DataSource = dt End Sub End Class
C#
using System; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace SqlDependencyTest { public partial class Form1 : Form { //requires Northwind to be installed //on default SQL Server instance private string connect = @"Server=.;Database=Northwind;Integrated Security=True"; private delegate void GridDelegate(DataTable table); private SqlDependency dep; public Form1() { InitializeComponent(); } private void Form1_Load(object sender, EventArgs e) { SqlDependency.Start(connect); UpdateGrid(); } private void Form1_FormClosed(object sender, FormClosedEventArgs e) { SqlDependency.Stop(connect); } private void UpdateGrid() { string sql = "SELECT OrderID, ProductID, UnitPrice, Quantity, " + "Discount FROM dbo.[Order Details]"; DataTable dt = new DataTable(); using (SqlConnection cn = new SqlConnection(connect)) { using (SqlCommand cmd = new SqlCommand(sql, cn)) { cn.Open(); dep = new SqlDependency(cmd); dep.OnChange += dep_OnChange; using (SqlDataReader rdr = cmd.ExecuteReader()) { dt.Load(rdr); } } } //Use anonymous method to assure //reassignment is done by the form's thread dataGridView1.Invoke( (GridDelegate)delegate(DataTable table) { dataGridView1.DataSource = table; }, dt); } private void dep_OnChange(object sender, SqlNotificationEventArgs e) { System.Diagnostics.Debug.WriteLine("Received OnChange Event"); if (e.Info == SqlNotificationInfo.Invalid) { MessageBox.Show("Invalid Statement"); return; } UpdateGrid(); } } }
To test this code, start the application, which loads the DataGridView object with the OrderDetails table in the Northwind database. Next, run a program that is capable of modifying the data in the Order Details table, such as Microsoft SQL Management Studio (as shown in Figure 12-3). Try changing the quantity field in the Order Details table (be sure to navigate to a different row after you change the quantity), and you should see the DataGridView object update accordingly.
Figure 12-3: Changing data in Microsoft SQL Management Studio triggers the OnChange event, which refreshes the data.
Selecting the Communication Transport
The SqlDependency object constructor can have a parameter that sets the notification transport to a member of the SqlNotificationTransports enumeration. The default setting is SqlNotificationTransports.Any, which attempts to use the HTTP protocol by default if you have kernel mode HTTP support (which is available on Windows 2003 Server and Windows XP with SP2). If HTTP cannot be used, TCP is used. You can also try forcing the dependency to use TCP for notifications by using the following overload of the SqlDependency object constructor.
Visual Basic
Dim d As New SqlDependency(command, Nothing, _ SqlNotificationAuthType.None, SqlNotificationTransports.Tcp, 0)
C#
SqlDependency d = new SqlDependency(command, null, SqlNotificationAuthType.None, SqlNotificationTransports.Tcp, 0);
