Working With SQL Notification

Introduction

 

This article explains how SQL Notification can be enabled in SQL Server to build an application that uses notification. An application that involves SQL Notification requires a common set of tasks to be performed. The data source must be configured to support query notifications and the user must have the required and server-side permissions.

 

To use notifications, first, you need to enable a server broker of your database. Ensure that your user id should have the necessary permission. A SQL Server database does not have a Service Broker enabled by default. You need to enable a Service Broker. A Service Broker can be enabled using a SQL statement. The following code enables the Service Broker of a database:

 

Syntax: 

 

ALTER DATABASE DATABASE_NAME SET ENABLE_BROKER  

 

Example:

ALTER DATABASE MyDatabase SET ENABLE_BROKER

 

The SQL keyword "ENABLE_BROKER" activates the Service Broker of the database you have given. After activating the Service Broker on your database, you need to create a queue for storing messages and a service for delivering messages to the correct queue. A queue is primary storage for messages that are transferred between two services. The following SQL statements create a queue and service in your database.

 

Syntax  

CREATE QUEUE QUEUE_NAME
CREATE SERVICE SERVICE_NAME ON QUEUE QUEUE_NAME
  ([http: //schemas.microsoft.com/SQL/Notification/PostQueryNotification])
      Example

      CREATE QUEUE MarketRateChangeMessage
      CREATE SERVICE MarketRateChangeNotificationService ON QUEUE MarketRateChangeMessage([http: //schemas.microsoft.com/SQL/Notification/PostQueryNotification])

Now set the permissions for the query notifications, the client-side code requires necessary permission to execute SQL Notification. This can be done by the SqlClientPermission class. The following SQL statement grants permissions for query notification.

 

SQL Statement: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO DATABASE_PRINCIPAL

 

The SqlClientPermission ensures that a user has complete security-level permission to access a data source. The following is an example of the SqlClientPermission class.

private bool HasPermission() {
    SqlClientPermission sqlClientPermission = new SqlClientPermission(PermissionState.Unrestricted);
    try {
        sqlClientPermission.Demand();
        return true;
    } catch {
        return false;
    }
}

The preceding code creates an object of the SqlClientPremission class, there are two types of permission states available in the .Net framework, one is None and the is Unrestricted. The value of None gives no access and Unrestricted gives full access. The method Demand forces a SecurityException at runtime if all callers higher in the call stack have not been granted the permission. PermissionState is an enumeration and is available in the System.Security.Permissions namespace.

 

Now we will execute the notification. The SqlDependency class is used to process notifications; this class automatically starts a worker thread to process the notifications as they are posted to the queue and also parses the Service Broker message and exposes the message as event argument data.

 

The SqlDependency class has two static classes, Start and Stop, that take a measure role. The SqlDependency class initializes by calling the start method. The start method is a static method, it only needs to be called once for each database collection.

 

The following code snippet contains the full of code for SQL Notification:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace ConsoleDemo {
    public class Program {
        static void Main(string[] args) {
            NotificationExample ne = new NotificationExample();
            ne.StartNotification();
            ne.StopNotification();
        }
    }
    public class NotificationExample {
        private delegate void RateChangeNotification(DataTable table);
        private SqlDependency dependency;
        string ConnectionString = "database sonnection string";

        public void StartNotification() {
            SqlDependency.Start(this.ConnectionString, "QueueName");
            SqlConnection connection = new SqlConnection(this.ConnectionString);
            connection.Open();

            SqlCommand command = new SqlCommand();
            command.CommandText = "SQL Statement";
            command.Connection = connection;
            command.CommandType = CommandType.Text;

            this.dependency = new SqlDependency(command);
            dependency.OnChange += new OnChangeEventHandler(OnRateChange);

        }
        private void OnRateChange(object s, SqlNotificationEventArgs e) {
            //Write code for you task
        }
        public void StopNotification() {
            SqlDependency.Stop(this.ConnectionString, "QueueName");
        }
    }
}

Summary

 

This article is just a quick introduction to enabling Notification Services and how it may be used in an application.