- 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:
- Example
-
- CREATE QUEUE MarketRateChangeMessage
- CREATE SERVICE MarketRateChangeNotificationService ON QUEUE MarketRateChangeMessage ([http:
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 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)
- {
-
- }
- public void StopNotification()
- {
- SqlDependency.Stop(this.ConnectionString,"QueueName");
- }
- }
- }
Summary