Advanced SQL 2005 SQLNotificationRequest Functionality with C#

code doesn't work

Really good stuff here! Thank you.

I noticed though that placing the client app running simultaneously on multiple machines, only the the client to hit 'start' last gets the notifications. How do I allow multiple clients to receive notifications simultaneously?

I won't be able to look at this until the weekend. Now that I know a little bit more about the .net / service broker I might take a slightly different tack.

i need this too
please help

What changes should I make to this project to use it in my projects?

Hi,
thanks for the great article, it works pretty fine for me!
Just one additional question: Your example returns the actual inserted / deleted / updated ROW. Do you know a possibility for monitoring the change of a special COLUMN in this row, e.g. the Number-column in your example?
What I mean is getting a message in the application which says: "column 'Number' of table 'SQLNotificationRequestTable' changed from '0' to '1'"?
Thank you,
beni

sorry for the late reply. Because we use a trigger we can get some good information about exactly what changed. A quick google for detecting column level changes in TSQL trigger shows...

http://msdn.microsoft.com/en-us/library/ms187326.aspx

apparently the Update() function, run on each column, will tell you which ones changed

Worse than the original article in the MSDN

Harsh - This was pretty impressive when I wrote this article, the service broker was kind of tough to figure out

Hi,
Can we replace trigger with any other code?
If yes, please tell how?

hi,
i read ur article and found its very informative.

while using code i am getting an error

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

<br />
command.CommandTimeout = NotificationTimeout + 15;<br />
                    SqlDataReader reader = command.ExecuteReader();<br />
                    <br />
                    while (reader.Read())<br />
                    {<br />

exception is occuring at "command.ExecuteReader();" call.
i am getting this error if application remain idle for say 10 minutes or more. i.e. if database remains unchanged for 10 minutes, then application throws this errror.

Hi,
private string GetListenerSQL()
{
// Note that ChangeMessages is the name
// of the Service Broker queue that must
// be already defined.
return "WAITFOR ( RECEIVE * FROM ChangeMessages);";
}

Is "WAITFOR ( RECEIVE * FROM ChangeMessages);" sql?
how to understand it?

Thanks.

Hello Todd,

Thanks for your article.

I have tried it. It works for me.
The problem is if many clients required to have the notifications - the notifications were sent only one.

Is it possible to send the notifications to many clients.

Thanks,

Sergei

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

////////////////////////////////////////////////////////////////////////////

Many Thanks Todd,

I am working with this code. It works.
I found - if many updates (using update sql) done and very often, sometimes it lost the data.(I will double check it)
I will continue and will send to you my results.

Thanks

Sergei

Advanced SQL 2005 SQLNotificationRequest Functionality with C#

Introduction

Using the Code

Points of Interest

History

License

Comments and Discussions