Click here to Skip to main content
Click here to Skip to main content

Advanced SQL 2005 SQLNotificationRequest Functionality with C#

, 24 Mar 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
Using the SQLNotificationRequest object and the Service Broker to get row and column change notifications, mimicking supposed SQLDependency capabilities

Introduction

SQL Server 2005 and ADO.NET 2.0 promised a powerful notification of database changes through the SQLDependency object. While SQL 2000 could only inform a client app of changes at the Table level, SQL 2005 promised the ability to notify of changes at the query level. If changes to the database table affect a query given to SQL 2005, a notification is given to the client app. If data is changed that does not affect a query given to SQL 2005, then no notification takes place.

Anyone that has tried using the SQLDependency object has found that this functionality does not actually exist. Notifications are still only at the table level, no matter what query is given to SQL 2005 to examine. I present here a workaround using SQL 2005 triggers and TSQL Service Broker Conversation to send a notification to a client app.

Using the Code

Just unzip the attached code, run the SQL script, and then run the C# application. Make changes to the SQLNotificationRequestTable (update, insert, delete), and see your changes be reported on the C# form...

The C# portion of this article is taken from this MSDN page with some slight modifications and one large modification: There is a location in the code where you can handle the Notification as an SQLDataReader and examine the message. I use this functionality to get the messages and display them.

The SQL portion of this article I wrote myself once I learned how the Service Broker and TSQL Triggers worked. The Service Broker is a complex concept, and I was thrilled to actually get my messages working. One other avenue I explored was the Microsoft.SQLServer.Samples namespace, which has some C# classes for the Service Broker, but I actually hit a dead-end using these classes. There may be a better way to do this if you are super knowledgeable in the Service Broker.

The majority of the SQL is devoted to examining the inserted and deleted tables to determine what caused the trigger to fire. The contents of the inserted or deleted tables are copied to a string variable which is then inserted into an XML variable because this Service Broker Message requires well formed XML. Then, in the last part of the SQL, I send the message.

Points of Interest

I have never tried this, but according to documentation Client and Database can be separate computers, and the IP address of the client app would replace 'LocalHost' in the SQL script attached when creating a Service Broker Route. Would love to hear back if anyone tries that.

I also do not know how production quality this is, and I haven't tried this on multiple tables.

I tried to copy snippets of my code into this article, but the formatting would not behave, so refer to the download.

History

  • 24th March, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Todd Wilder
Web Developer
United States United States
No Biography provided

Comments and Discussions

 
GeneralMy vote of 1 PinmemberMember 1079835224-Nov-14 22:05 
QuestionMultiple Clients PinmemberBendage27-Oct-11 6:57 
AnswerRe: Multiple Clients PinmemberTodd Wilder27-Oct-11 9:41 
GeneralRe: Multiple Clients Pinmemberaliahmadi13679-Mar-13 3:35 
QuestionDeployment PinmemberHumdy11-Jul-11 1:50 
QuestionDetermine change of a special column Pinmemberbeni27-Jan-09 3:37 
AnswerRe: Determine change of a special column PinmemberTodd Wilder10-Feb-09 17:34 
GeneralMy vote of 1 Pinmemberchenghezhang6-Dec-08 23:16 
GeneralRe: My vote of 1 PinmemberTodd Wilder10-Feb-09 17:29 
QuestionReplace Trigger Pinmemberdipak_narsinghani29-Apr-08 1:17 
GeneralTimeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Pinmembervineet1378019-Apr-08 0:15 
Generala code was not understanded Pinmemberjason_mf5-Mar-08 16:33 
GeneralSQLNotificationRequest Pinmembersssergei31-May-07 3:03 
GeneralRe: SQLNotificationRequest PinmemberTodd Wilder31-May-07 21:17 
As I understand the conversations in the service broker, they are 2 endpoints per conversation, so multiple endpoints = multiple conversations.
 
I have changed the SQL and created a Conversations table that manages the conversations with different clients.
I have also changed the C# so clients properly register themselves with the DB
 
this is a total proof of concept, I don't even bother to unregister the conversation when the client closes.
 
Keep in mind this is beyond academic, there are all kinds of elephants in the room as far as maintaining the conversations and timeouts and should you even be using the service broker for this, but what follows works for me
 
totally replace your C# code so it reads
 
////////////////////////////////////////////////////////////////////////////
 
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Threading;
namespace WindowsApplication17
{
public partial class Form1 : Form
{
private SqlConnection connection = null;
private SqlCommand command = null;
// The Service Name is required to correctly
// register for notification.
// The Service Name must be already defined with
// Service Broker for the database you are querying.
private const string ServiceName = "ChangeNotifications";
// Spercify how long the notification request
// should wait before timing out.
// This value waits for 10 minutes.
private int NotificationTimeout = 600;
public Form1()
{
InitializeComponent();
}
private string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrive it from a configuration file.
// In general, client applications don't need to incur the
// overhead of connection pooling.
return "Data Source=localhost;Integrated Security=SSPI;" +
"Initial Catalog=SQLNotificationRequestDB;Pooling=False;";
}
private string GetSQL()
{
return "SELECT Name,Number from SQLNotificationRequestTable";
}
private string GetListenerSQL()
{
// Note that ChangeMessages is the name
// of the Service Broker queue that must
// be already defined.
return @"
DECLARE @NotificationDialog uniqueidentifier
SET QUOTED_IDENTIFIER ON
BEGIN DIALOG CONVERSATION @NotificationDialog
FROM SERVICE ChangeNotifications
TO SERVICE 'ChangeNotifications'
ON CONTRACT [http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
WITH ENCRYPTION = OFF;
delete from Conversations where NotificationHandle = '" + guid + @"'
insert into Conversations(ConversationHandle,NotificationHandle) values (@NotificationDialog,'" + guid + @"')
WAITFOR ( RECEIVE * FROM ChangeMessages);";
}
 
private void StartListener()
{
// A seperate listener thread is needed to
// monitor the queue for notifications.
Thread listener = new Thread(Listen);
listener.Name = "Query Notification Watcher";
listener.Start();
}
private void Listen()
{
using (SqlConnection connection =
new SqlConnection(GetConnectionString()))
{
using (SqlCommand command =
new SqlCommand(GetListenerSQL(), connection))
{
connection.Open();
// Make sure we don't time out before the
// notification request times out.
command.CommandTimeout = NotificationTimeout + 15;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
messageText = System.Text.ASCIIEncoding.ASCII.GetString((byte[])reader.GetValue(13)).ToString();
// Empty queue of messages.
// Application logic could parse
// the queue data and
// change its notification logic.
}
 
object[] args = { this, EventArgs.Empty };
EventHandler notify =
new EventHandler(OnNotificationComplete);
// Notify the UI thread that a notification
// has occurred.
this.BeginInvoke(notify, args);
}
}
}
private string messageText;
private void OnNotificationComplete(object sender, EventArgs e)
{
messageText = messageText.Replace("??", "");
label1.Text = messageText.Replace("\0","");
// The user can decide to register
// and request a new notification by
// checking the CheckBox on the form.
GetData();
}
 
private string guid = Guid.NewGuid().ToString();
private void button1_Click(object sender, EventArgs e)
{
if (connection == null)
{
connection = new SqlConnection(GetConnectionString());
}
if (command == null)
{
// GetSQL is a local procedure SQL string.
// You might want to use a stored procedure
// in your application.
command = new SqlCommand(GetSQL(), connection);
}

GetData();
}
private void GetData()
{
// Make sure the command object does not already have
// a notification object associated with it.
command.Notification = null;
SqlNotificationRequest snr =
new SqlNotificationRequest();
snr.UserData = guid;
snr.Options = "Service=" + ServiceName;
// If a time-out occurs, a notification
// will indicate that is the
// reason for the notification.
snr.Timeout = NotificationTimeout;
command.Notification = snr;
// Start the background listener.
StartListener();
}
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{

if (connection != null)
{
connection.Close();
 
}
Application.Exit();
}
}
}
 
////////////////////////////////////////////////////////////////////////////
 
totally replace your sql script so it reads
 
////////////////////////////////////////////////////////////////////////////
 
USE [master]
GO
/****** Object: Database [SQLNotificationRequestDB] Script Date: 03/24/2007 20:36:36 ******/
CREATE DATABASE [SQLNotificationRequestDB]
 
GO
ALTER DATABASE [SQLNotificationRequestDB] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [SQLNotificationRequestDB] SET ANSI_NULLS OFF
GO
ALTER DATABASE [SQLNotificationRequestDB] SET ANSI_PADDING OFF
GO
ALTER DATABASE [SQLNotificationRequestDB] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [SQLNotificationRequestDB] SET ARITHABORT OFF
GO
ALTER DATABASE [SQLNotificationRequestDB] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [SQLNotificationRequestDB] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [SQLNotificationRequestDB] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [SQLNotificationRequestDB] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [SQLNotificationRequestDB] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [SQLNotificationRequestDB] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [SQLNotificationRequestDB] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [SQLNotificationRequestDB] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [SQLNotificationRequestDB] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [SQLNotificationRequestDB] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [SQLNotificationRequestDB] SET ENABLE_BROKER
GO
ALTER DATABASE [SQLNotificationRequestDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [SQLNotificationRequestDB] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [SQLNotificationRequestDB] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [SQLNotificationRequestDB] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [SQLNotificationRequestDB] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [SQLNotificationRequestDB] SET READ_WRITE
GO
ALTER DATABASE [SQLNotificationRequestDB] SET RECOVERY FULL
GO
ALTER DATABASE [SQLNotificationRequestDB] SET MULTI_USER
GO
ALTER DATABASE [SQLNotificationRequestDB] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [SQLNotificationRequestDB] SET DB_CHAINING OFF
 

USE [SQLNotificationRequestDB] ;
 
GO
 
CREATE QUEUE ChangeMessages ;
 
CREATE SERVICE ChangeNotifications
 
ON QUEUE ChangeMessages
 
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]) ;
 
CREATE ROUTE
 
ChangeRoute
 
WITH SERVICE_NAME = 'ChangeNotifications',
 
ADDRESS = 'LOCAL' ;
 
GO
 

USE [SQLNotificationRequestDB]
GO
/****** Object: Table [dbo].[NotificationTable] Script Date: 03/24/2007 20:37:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SQLNotificationRequestTable](
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Number] [int] NOT NULL,
CONSTRAINT [PK_SQLNotificationRequestTable] PRIMARY KEY CLUSTERED
(
[Number] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
CREATE TABLE [dbo].[Conversations](
[Identity] [int] IDENTITY(1,1) NOT NULL,
[ConversationHandle] [uniqueidentifier] NOT NULL,
[NotificationHandle] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
go
 
CREATE TRIGGER [dbo].[Triggers]
ON [dbo].[SQLNotificationRequestTable]
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
 
--Strategy: look at the Inserted and Deleted tables available in the trigger to determine which rows were inserted, updated, or deleted
--Once we know that, build a string that contains information we want client app to know about. I go as far as to store all the data of the inserted/deleted tables
--into the string by looping through each row of the temporary table I make
 
DECLARE @Message xml
set @Message = N''
 
declare @tempTable Table
(
rowNumber int IDENTITY(0,1) NOT NULL,
Number int,
Name nvarchar(50)
)
 
declare @iterator int
set @iterator = 0
declare @count int
declare @string nvarchar(1024)
set @string = 'Values '
 
-- If a delete occurred
if Exists(Select Number from Deleted) and not Exists(Select Number from Inserted)
Begin
insert into @tempTable (Name, Number) select Name, Number from Deleted
set @count = (select count(Name) from @tempTable)
while @iterator < @count
Begin
if @count > 1
begin
if(@iterator != @count-1)
begin
set @string = @string + Cast((select Number from @tempTable where rowNumber = @iterator) as nvarchar) + ', '
set @string = @string + Cast((select Name from @tempTable where rowNumber = @iterator) as nvarchar) + ',
'
end
else
begin
set @string = @string + Cast((select Number from @tempTable where rowNumber = @iterator) as nvarchar) + ', '
set @string = @string + Cast((select Name from @tempTable where rowNumber = @iterator) as nvarchar) + ' were deleted.'
end
end
else
begin
set @string = @string + Cast((select Number from @tempTable where rowNumber = @iterator) as nvarchar) + ', '
set @string = @string + Cast((select Name from @tempTable where rowNumber = @iterator) as nvarchar) + ' was deleted.'
end
set @iterator = @iterator + 1
end
 
set @Message = @string
End
-- If an Insert occurred
if Exists(Select Number from Inserted) and not Exists(Select Number from Deleted)
Begin
insert into @tempTable (Name, Number) select Name, Number from Inserted
set @count = (select count(Name) from @tempTable)
while @iterator < @count
Begin
if @count > 1
begin
if(@iterator != @count-1)
begin
set @string = @string + Cast((select Number from @tempTable where rowNumber = @iterator) as nvarchar) + ', '
set @string = @string + Cast((select Name from @tempTable where rowNumber = @iterator) as nvarchar) + ',
'
end
else
begin
set @string = @string + Cast((select Number from @tempTable where rowNumber = @iterator) as nvarchar) + ', '
set @string = @string + Cast((select Name from @tempTable where rowNumber = @iterator) as nvarchar) + ' were Inserted.'
end
end
else
begin
set @string = @string + Cast((select Number from @tempTable where rowNumber = @iterator) as nvarchar) + ', '
set @string = @string + Cast((select Name from @tempTable where rowNumber = @iterator) as nvarchar) + ' was inserted.'
end
set @iterator = @iterator + 1
end
 
set @Message = @string
end
if Exists(Select Number from Deleted) and Exists(Select Number from Inserted)
--Update Occurred
begin
insert into @tempTable (Name, Number) select Name, Number from Inserted
set @count = (select count(Name) from @tempTable)
while @iterator < @count
Begin
if @count > 1
begin
if(@iterator != @count-1)
begin
set @string = @string + Cast((select Number from @tempTable where rowNumber = @iterator) as nvarchar) + ', '
set @string = @string + Cast((select Name from @tempTable where rowNumber = @iterator) as nvarchar) + ',
'
end
else
begin
set @string = @string + Cast((select Number from @tempTable where rowNumber = @iterator) as nvarchar) + ', '
set @string = @string + Cast((select Name from @tempTable where rowNumber = @iterator) as nvarchar) + ' were Updated.'
end
end
else
begin
set @string = @string + Cast((select Number from @tempTable where rowNumber = @iterator) as nvarchar) + ', '
set @string = @string + Cast((select Name from @tempTable where rowNumber = @iterator) as nvarchar) + ' was Updated.'
end
set @iterator = @iterator + 1
end
 
set @Message = @string
end
 
--By using the PostQueryNotification Contract and QueryNotification Message Type, These messages will be caught by the SQLNotificationRequest
 
declare @conversations int
set @conversations = (select count(*) from Conversations);
 
set @iterator = 1;
 
declare @key int
 
declare @conversationsTable table
(
[Identity] [int] Identity not null,
[Key] [int]
)
 
insert into @conversationsTable([Key]) select [Identity] from Conversations
 
while @iterator <= @conversations
begin
DECLARE @NotificationDialog uniqueidentifier
set @NotificationDialog = (select [ConversationHandle] from [Conversations] where [Identity] = (select [Key] from @conversationsTable where [Identity] = @iterator));
SEND ON CONVERSATION @NotificationDialog
MESSAGE TYPE [http://schemas.microsoft.com/SQL/Notifications/QueryNotification] (@Message);
set @iterator = @iterator + 1;
end
 
END
 
////////////////////////////////////////////////////////////////////////////
GeneralRe: SQLNotificationRequest Pinmembersssergei4-Jun-07 6:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 24 Mar 2007
Article Copyright 2007 by Todd Wilder
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid