Using the SqlDependency Class


Using the SqlDependency Class

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.

image from book
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.

image from book
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.

image from book

Incorrect SQL Statement

SELECT * FROM [Order Details] 
image from book

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.

image from book

Correct SQL Query Statement

SELECT OrderID, ProductID, UnitPrice, Quantity, Discount FROM dbo.[Order Details] 
image from book

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.


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.

SELECT DATABASEPROPERTYEX('Northwind', 'IsBrokerEnabled') 

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:


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.

image from book

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 
image from book

image from book


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();       }    } } 
image from book

To test this code, start the application, which loads the DataGridView object with the Order Details 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.

image from book
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.

image from book

Visual Basic

Dim d As New SqlDependency(command, Nothing, _    SqlNotificationAuthType.None, SqlNotificationTransports.Tcp, 0) 
image from book

image from book


SqlDependency d = new SqlDependency(command, null,    SqlNotificationAuthType.None, SqlNotificationTransports.Tcp, 0); 
image from book


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan

Similar book on Amazon
Beginning Linux Programming
Beginning Linux Programming
Advanced Programming in the UNIX(R) Environment (Addison-Wesley Professional Computing Series)
Advanced Programming in the UNIX(R) Environment (Addison-Wesley Professional Computing Series)
Tom Swan's GNU C++ for Linux (Professional Dev. Guide)
Tom Swan's GNU C++ for Linux (Professional Dev. Guide) © 2008-2017.
If you may any questions please contact us: