Consuming Event Notifications from CLR

April 11th, 2007

A question was asked on the newsgroups: how can a C# program be notified when the schema of a table was modified (i.e. a column was added)? The short answer is to use Event Notifications, see

But an example would help J. First thing, we need to set up a queue and service that will receive the notifications:

USE [myapplicationdatabase];


CREATE QUEUE [NotificationsQueue];

CREATE SERVICE [MyNotificationsService]

ON QUEUE [NotificationsQueue]



Next we create an Event Notification for the ALTER_TABLE event, asking the notifications to be sent to our newly created service:

USE [myapplicationdatabase];





TO SERVICE ‘MyNotificationsService’, ‘current database’;


With this, we had set in place the infrastructure to be notified when a table is altered. We could had choose any other event from the many notification types supported by Event Notifications, like other DDL events (CREATE, DROP) or even trace events (e.g. LOCK_DEADLOCK to be notified when a deadlock occurs). Whenever the notification is fired, a message will be enqueued in the [NotificationsQueue] queue.

How does a C# application consume those messages? Using the RECEIVE verb. This verb produces a result set just like a SELECT, so messages are being consumed by creating and iterating a data reader. Here is a dummy example of a C# program that will receive our notifications and display them on the console:

using System;

using System.Collections.Generic;

using System.Text;

using System.Data.SqlClient;

using System.Data;

using System.Data.SqlTypes;

using System.Xml;

namespace ReceiveEventNotifications


class Program


const string _sqlReceive =




CAST(message_body as XML)

FROM [NotificationsQueue]);”;

const string _sqlEndDialog = @”END CONVERSATION @conversationHandle”;

const string _errorMessageType = @””;

const string _endDialogMessageType = @””;

static void Main(string[] args)


SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();

scsb.DataSource = “.”;

scsb.IntegratedSecurity = true;

scsb.InitialCatalog = “target”;

scsb.MultipleActiveResultSets = true;

using (SqlConnection conn = new SqlConnection(scsb.ConnectionString))



SqlCommand cmdReceive = new SqlCommand(_sqlReceive, conn);

// Set the command timeout to infinite

cmdReceive.CommandTimeout = 0;

while (true)


using (SqlTransaction trn = conn.BeginTransaction())


cmdReceive.Transaction = trn;

using (SqlDataReader rdr = cmdReceive.ExecuteReader(CommandBehavior.SequentialAccess))


while (rdr.Read())


Guid conversationHandle = rdr.GetGuid(0);

string messageType = rdr.GetString(1);

if (messageType == _endDialogMessageType ||

messageType == _errorMessageType)


SqlCommand cmdEndDialog = new SqlCommand(_sqlEndDialog, conn, trn);

cmdEndDialog.Parameters.AddWithValue(“@conversationHandle”, conversationHandle);





SqlXml xmlNotification = rdr.GetSqlXml(2);

XmlDocument doc = new XmlDocument();













Things worthy of note:

the infinite CommandTimeout (since WAITFOR will block until a message arrives)

the use of SqlXml.CreateReader to get the message payload as an XmlReader

the use of a SequentialAccess command behavior to optimize the XmlReader stream

the need for MultipleActiveResultSets (aka MARS) to be able to respond with END CONVERSATION to incomming error and end messages while the current connection has an active result set

the processing of EndDialog and Error message types by ending our side of the conversation

One thing to keep in mind is that the Event Notifications are going to be fired even when the application is not running. So the application might start up and find a number of notifications pending from the period was not running and has to be prepared to deal with this case.

Comments are closed.