Implementing SQL Server 2005 Query Notifications in C# Windows Application

Query notification is a feature included in Microsoft SQL Server 2005 that allows applications to be notified when data has changed. It is mainly used for applications that stores cache of data from a database and get refreshed and notified in the client application everytime the data changes in the database.

Developers in ASP.NET 2.0 are already familiar with the use the SqlCacheDependency classes in their web applications to buffer SQL Data. This article deals with the high-level implementation as provided by the SqlDependency class in System.Data.SqlClient Namespace. It provides a simple and elegant notification functionality between the windows application and SQL Server, enabling you to use a dependency to detect changes in the server. It effectively leverages the SQL Server 2005 notifications capability to the managed client applications using ADO.NET.

SQL Server 2005 allows ADO.NET Windows applications to send a command to SQL Server and request that a notification be generated if executing the same SQL command would produce result sets different from those initially retrieved. Query Notification is supported by the database’s Service Broker Event and Queueing Mechanisms.

Step-By-Step approach in implementing Query Notifications

The whole process of request-receive notification process between the client windows application and the SQL Server 2005 is done in five little steps as follows:

1) The database that is in question must be configured to enable query notification services.

For security reasons, SQL Server 2005 databases do not have Service Broker enabled by default. To enable query notifications for your database, in the SQL SERVER 2005 MAnagement Studio, Query window, run the following command:

ALTER DATABASE SET ENABLE_BROKER;

In this article, I uses the database named AdvtDB

ALTER DATABASE AdvtDB SET ENABLE_BROKER;

2) The user must have the correct client and server side permissions to request and receive notifications.

Users who execute commands requesting notification must have SUBSCRIBE QUERY NOTIFICATIONS database permission on the server.Client-side code that runs in a partial trust situation requires the SqlClientPermission.

<br /> private bool EnoughPermission()<br /> {<br /> SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);<br /> try<br /> {<br /> perm.Demand();<br /> return true;<br /> }<br /> catch (System.Exception)<br /> {<br /> return false;<br /> }<br /> }</p> <p>

3) Use a SqlCommand object to execute a valid SELECT statement with an associated notification object—SqlDependency.

SqlDependency Object The query notifications API provides SqlDependency object to process notifications. When using SqlDependency, Service Broker objects, such as the notification queue, are predefined. It automatically launches a worker thread to process notifications as they are posted to the queue; it also parses the Service Broker message, exposing the information as event argument data. SqlDependency must be initialized by calling the Start method to establish a dependency to the database. This is a static method that need be called only once during application initialization for each database connection required. The Stop method should be called at application termination for each dependency connection that was made.

On Click of a button, database connection is established, SqlDependency is started to listen the notification service and a DataGridView Control is displayed with current results returned from a query executed by the command object.

</p> <p>private void button1_Click(object sender, EventArgs e)<br /> {</p> <p>// Remove any existing dependency connection, then create a new one.<br /> connstr = “Data Source=localhost;Integrated Security=SSPI;Initial Catalog=AdvtDB;”;<br /> string ssql = “select * from advt “;</p> <p>SqlDependency.Stop(connstr);<br /> SqlDependency.Start(connstr);<br /> if (connection == null)<br /> connection = new SqlConnection(connstr);<br /> if (command == null)<br /> command = new SqlCommand(ssql , connection);<br /> if (myDataSet == null)<br /> myDataSet = new DataSet();<br /> GetAdvtData();<br /> }</p> <p>

GetAdvtData() is a helper function that creates the command object instance, associate it with the SqlDependency object. Note that the SqlDependency object has an OnChange event notifying the client that changes has been done in the database and its eventhandler dependency_OnChange will take care of the receiving part of the notification.

</p> <p>private void GetAdvtData()<br /> {<br /> myDataSet.Clear();<br /> // Make sure the command object does not already have a notification object associated with it.<br /> command.Notification = null;<br /> // Create and bind the SqlDependency object to the command object.<br /> SqlDependency dependency =new SqlDependency(command);<br /> dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);</p> <p>using (SqlDataAdapter adapter = new SqlDataAdapter(command))<br /> {<br /> adapter.Fill(myDataSet, “Advt”);<br /> dataGridView1.DataSource = myDataSet;<br /> dataGridView1.DataMember = “Advt”;<br /> }<br /> }</p> <p>

4) Provide code to process the notification when and if the data being monitored changes.

The worker thread process encapsulates the OnChange Event handler and hence, the UI changes(updating the datagrid, displaying the status message) available in the Main thread might not be accessible here. Create another delegate thread UIDelegate that does these tasks and let the
worker thread removes just the handler from the OnChange event of the SqlDependency object.

<br /> delegate void UIDelegate();<br /> private void dependency_OnChange(object sender, SqlNotificationEventArgs e)<br /> {<br /> UIDelegate uidel = new UIDelegate(RefreshData);<br /> this.Invoke(uidel, null);</p> <p>//Remove the handler as it is used for a single notification.<br /> SqlDependency dependency =(SqlDependency)sender;<br /> dependency.OnChange -= dependency_OnChange;<br /> }<br /> private void RefreshData()<br /> {<br /> // At this point, the code is executing on the UI thread, so it is safe to update the UI.</p> <p>label1.Text = “Database had some changes and are applied in the Grid”;</p> <p>// Reload the dataset that is bound to the grid.<br /> GetAdvtData();<br /> }</p> <p>

5) Stop the SqlDependency Notification Services while quitting the application.

In the form_closing event, you include the code for stopping the SqlDepenency notification listener service.

</p> <p>private void Form1_FormClosing(object sender, FormClosingEventArgs e)<br /> {<br /> SqlDependency.Stop(connstr);<br /> if (connection != null)<br /> connection.Close();<br /> }</p> <p>

Following are the declarations used in this program.

</p> <p>private const string statusMessage;<br /> private DataSet myDataSet = null;<br /> private SqlConnection connection = null;<br /> private SqlCommand command = null;<br /> private string connstr;</p> <p>

Testing the application

Run the C# Windows application you have just created following the above steps. When the form shows up, Click the button and notice that your datagridview control is populated.

Now, Open the Query window of the SQL Server Management studio, and insert rows or update columns in the table concerned. When you switch back to your C# running application, you could see the changes appeared in the datagrid view.

Copyright @ Balamurali Balaji 2007