Implementing SQL Server 2005 Query Notifications in C# Windows Application

Author:

Balamurali Balaji MVP

COMMUNITY SOLUTIONS CONTENT DISCLAIMER

MICROSOFT CORPORATION AND/OR ITS RESPECTIVE SUPPLIERS MAKE NO REPRESENTATIONS ABOUT THE SUITABILITY, RELIABILITY, OR ACCURACY OF THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN. ALL SUCH INFORMATION AND RELATED GRAPHICS ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT AND/OR ITS RESPECTIVE SUPPLIERS HEREBY DISCLAIM ALL WARRANTIES AND CONDITIONS WITH REGARD TO THIS INFORMATION AND RELATED GRAPHICS, INCLUDING ALL IMPLIED WARRANTIES AND CONDITIONS OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, WORKMANLIKE EFFORT, TITLE AND NON-INFRINGEMENT. YOU SPECIFICALLY AGREE THAT IN NO EVENT SHALL MICROSOFT AND/OR ITS SUPPLIERS BE LIABLE FOR ANY DIRECT, INDIRECT, PUNITIVE, INCIDENTAL, SPECIAL, CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER INCLUDING, WITHOUT LIMITATION, DAMAGES FOR LOSS OF USE, DATA OR PROFITS, ARISING OUT OF OR IN ANY WAY CONNECTED WITH THE USE OF OR INABILITY TO USE THE INFORMATION AND RELATED GRAPHICS CONTAINED HEREIN, WHETHER BASED ON CONTRACT, TORT, NEGLIGENCE, STRICT LIABILITY OR OTHERWISE, EVEN IF MICROSOFT OR ANY OF ITS SUPPLIERS HAS BEEN ADVISED OF THE POSSIBILITY OF DAMAGES.

SUMMARY

This article explains how to implement SQL SERVER 2005 Query Notification Service in your C# windows application. The sample code given in this article works with Visual Studio 2005 and SQL Server 2005 Management Studio.

SYMPTOMS

Introduction

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.

CAUSE

Step-By-Step approach in implementing Query NotificationsThe 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.



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

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


SqlDependency ObjectThe 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.


        private void button1_Click(object sender, EventArgs e)
        {

            // Remove any existing dependency connection, then create a new one.
            connstr = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=AdvtDB;";
            string ssql = "select * from advt ";

            SqlDependency.Stop(connstr);
            SqlDependency.Start(connstr);
            if (connection == null)
                connection = new SqlConnection(connstr);
            if (command == null)
                command = new SqlCommand(ssql , connection);
            if (myDataSet == null)
                myDataSet = new DataSet();
            GetAdvtData();
        }

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.

        private void GetAdvtData()
        {
            myDataSet.Clear();
            // Ensure the command object does not have a notification object.
            command.Notification = null;
            // Create and bind the SqlDependency object to the command object.
            SqlDependency dependency =new SqlDependency(command);
            dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

            using (SqlDataAdapter adapter =  new SqlDataAdapter(command))
            {
                adapter.Fill(myDataSet, "Advt");
                dataGridView1.DataSource = myDataSet;
                dataGridView1.DataMember = "Advt";
            }
        }

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.

       delegate void UIDelegate();
       private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
        {
            UIDelegate uidel = new UIDelegate(RefreshData);
            this.Invoke(uidel, null);

            //Remove the handler as it is used for a single notification.
            SqlDependency dependency =(SqlDependency)sender;
            dependency.OnChange -= dependency_OnChange;
        }
        private void RefreshData()
        {
            // Since the code is executing on the UI thread,it is safe to update the UI.

            label1.Text = "Database had some changes and are applied in the Grid";

            // Reload the dataset that is bound to the grid.
            GetAdvtData();
        }

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.


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

Following are the declarations used in this program.

        private const string statusMessage; 
        private DataSet myDataSet = null;
        private SqlConnection connection = null;
        private SqlCommand command = null;
        private string connstr;



RESOLUTION

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.


MORE INFORMATION

Summary
 
SQL Server 2005 Brokerage Services combined with ADO.NET Query Notification objects provide you an effective way of notifying the changes in database in user applications. More details can be obtained at the following url:
Properties

Article ID: 555893 - Last Review: 14 Feb 2017 - Revision: 1

Microsoft SQL Server Notification Services 2.0, Microsoft Visual C# .NET 2003 Standard Edition

Feedback