SQL Notification

Kailash Chandra Behera | Monday, August 08, 2016

Introduction

This article describe how to use SQL Notification in C# programming and how to make enable SQL Server for Notification with example.

Getting Started

Before starting how to use SQL Notification in C# programming, I would like to discuss little about SQL Notification. SQL Notification is used for applications that need to refresh displays or caches in response to changes in underlying data. Microsoft SQL Server allows .NET Framework applications to send a command to SQL Server and request notification if executing the same command would produce result sets different from those initially retrieved. Notifications generated at the server are sent through queues to be processed later.

To use Notification first, make sore database server be configured to support query notifications and the user must have the required and server-side permissions. Then enable a server broker of you database. Insure 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 the 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 insures that a user has complete security-level permission to access a data source. The following is the 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 the 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 SQLNotificationDemo   
 {   
   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

In the above of this article we have discussed how to use SQL Notification in C# programming and how to enable notification in SQL Server Database, Hope this article may helpful to you

Thanks

By at