Consuming Event Notifications from CLR

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 http://msdn2.microsoft.com/en-us/library/ms189453.aspx

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

USE [myapplicationdatabase];

GO

CREATE QUEUE [NotificationsQueue];

CREATE SERVICE [MyNotificationsService]

ON QUEUE [NotificationsQueue]

([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

GO

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

USE [myapplicationdatabase];

GO

CREATE EVENT NOTIFICATION [NotificationAlterTable]

ON DATABASE

FOR ALTER_TABLE

TO SERVICE ‘MyNotificationsService’, ‘current database’;

GO

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 =

@”WAITFOR (RECEIVE

conversation_handle,

message_type_name,

CAST(message_body as XML)

FROM [NotificationsQueue]);”;

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

const string _errorMessageType = @”http://schemas.microsoft.com/SQL/ServiceBroker/Error”;

const string _endDialogMessageType = @”http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog”;

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))

{

conn.Open();

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);

cmdEndDialog.ExecuteNonQuery();

}

else

{

SqlXml xmlNotification = rdr.GetSqlXml(2);

XmlDocument doc = new XmlDocument();

doc.Load(xmlNotification.CreateReader());

Console.WriteLine(doc.OuterXml);

}

}

}

trn.Commit();

}

}

}

}

}

}

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.