Sending SQL Notification to C# Application using Service Broker
Posted by Sourabh Agarwal on September 14, 2012
On one of my earlier post on CDC, I was asked if we can send out CDC notifications to a C# application. The ensuing post is an attempt to answer that question. This post does not specifically talk about sending CDC notification, but a general guidance on how Service Broker can be leveraged to send out notifications to an External Application.
In order to send out the notification using Service Broker to an external application, we would need to make use of the SQL Server Service Broker External activator, which is distributed as part of the SQL Server Feature Pack (available for 2005/2008/2008R2/2012). The links for the respective SQL Versions (latest versions).
-
SQL 2008 R2 SP1:http://www.microsoft.com/en-us/download/details.aspx?id=26728
From a work flow perspective, the following events take place.
The Application event or service communicates with the Notification Service (Event Notification). The External Activator services is continuously listening on the notification queue. When it encounters a message in the Queue, it invokes the External Application, which can process the required operation.
Once the External Activator is installed, it works as a NT Service.
Before the External Activator can be configured, the Event Notification mechanism needs to be created in SQL. This event notification would be fired on the Queue Activation Event on the Application Queue. In this example, I have implement an Async Trigger, which uses Service Broker to activate an external application.
Setting up the Async Trigger Code
Use master
go
create database SSBEA
go
-- Enable Trustworthy on the database
Alter database SSBEA SET TRUSTWORTHY ON
go
Use SSBEA
go
/***************************** SET UP Application Service and Queue **********************************************/
--Create Message type for Communication
CREATE MESSAGE TYPE [MyMessage]
VALIDATION = NONE
-- Create a Contract for SSB communication
CREATE Contract [MyContract]
([MyMessage] Sent by ANY)
-- Create a Queue for the Notification
Create Queue MySSBEAQueue
With Status = ON,Retention = off, Poison_Message_Handling (Status = off)
go
-- Create a Service for the Notification
Create Service MySSBEAService
On Queue MySSBEAQueue
go
ALTER SERVICE MySSBEAService
(ADD CONTRACT [MyContract])
/****************** Create a Sample Table using which we would send message to be queue *****************/
--
Create table test_SSB(a int, b int)
GO
-- Create a Trigger on this table which would invoke the SSB Service and post messages to the Queue.
alter trigger SSB_trigger
on test_SSB
AFTER INSERT
as
declare @h as uniqueIdentifier
declare @message Varchar(1000)
declare @test1 int, @test2 int
select @test1=a, @test2=b from INSERTED with(nolock)
Set @message = ''
set @message = @message + 'The value Inserted in the table were '+ Cast(@test1 as varchar(10)) + ' and '+ Cast(@test2 as varchar(10));
BEGIN DIALOG CONVERSATION @h
FROM SERVICE MySSBEAService TO SERVICE 'MySSBEAService', 'CURRENT DATABASE'
ON CONTRACT [MyContract]
with encryption = off;
SEND ON CONVERSATION @h MESSAGE TYPE [MyMessage] (@message)
print @h
IF EXISTS(select * from sys.conversation_endpoints
where conversation_handle=@h and state='ER')
begin
RAISERROR ('Service Broker in error state',18,127)
rollback transaction
end
else
begin
END CONVERSATION @h WITH CLEANUP;
print 'Clean Up Completed'
end
GO
Next we need to setup the objects for Event Notification
--- Create the Queues for Notification
CREATE QUEUE MyNotificationQueue
GO
-- create event notification service
CREATE SERVICE MyNotificationService
ON QUEUE MyNotificationQueue
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
)
GO
-- Create a Event Notification to allow the EA to listen for Notification
CREATE EVENT NOTIFICATION MyNotificationEvent
ON QUEUE MySSBEAQueue
FOR QUEUE_ACTIVATION
TO SERVICE 'MyNotificationService' , 'current database'
GO
Once we have setup the Event notification, we need an create the External Application as per our requirement. In my case the External Application, takes the message from the Service and inserts it into a secondary table.
using System;
using System.Collections.Generic;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Diagnostics;
using System.IO;
namespace MyMessageApplication
{
class Program
{
/// <summary>
/// Application name used when connecting to Sql Server and writing Windows Event Log entries.
/// </summary>
private const string ApplicationName = "MyMessageApplication";
/// <summary>
/// Defines how many milliseconds the <c>RECEIVE</c> statement should wait for messages.
/// </summary>
private const int WaitforTimeout = 5000;
/// <summary>
/// Predefined Sql Server message type name for end dialog messages.
/// </summary>
private const string EndDialogMessageType = "http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog";
/// <summary>
/// Predefined Sql Server message type name for conversation error messages.
/// </summary>
private const string ErrorMessageType = "http://schemas.microsoft.com/SQL/ServiceBroker/Error";
/// <summary>
/// Services request messages by doing all the necessary computation and preparing the payload of a reply message.
/// </summary>
///
///Create a message logger function
///
public static void LogMessageToFile(string message)
{
StreamWriter sw = File.AppendText(@"D:\SQLDataFiles\MyMessageApplicationLog.txt");
string entry = String.Format("{0:G}: {1}.", System.DateTime.Now, message);
sw.WriteLine(entry);
sw.Close();
}
static void Main()
{
SqlConnectionStringBuilder csb = new SqlConnectionStringBuilder();
csb.ApplicationName = ApplicationName;
csb.DataSource = "<ServerName>";
csb.InitialCatalog = "SSBEA";
csb.UserID = "UserAccount";
csb.Password = "Password for the User Account above";
csb.MultipleActiveResultSets = true;
try
{
SqlConnection conn = new SqlConnection(csb.ToString());
conn.Open();
bool MsgRcv;
do
{
MsgRcv = false;
SqlTransaction tran = conn.BeginTransaction();
using (tran)
{
SqlCommand comm = conn.CreateCommand();
comm.Transaction = tran;
//LogMessageToFile("Querying from the Queue");
comm.CommandText = string.Format(
"WAITFOR (RECEIVE TOP (1) conversation_handle, message_type_name, convert(xml,message_body) FROM {0}), TIMEOUT {1}",
"MySSBEAQueue", WaitforTimeout);
SqlDataReader reader = comm.ExecuteReader();
while(reader.Read())
{
MsgRcv = true;
System.Data.SqlTypes.SqlGuid handle = reader.GetSqlGuid(0);
System.Data.SqlTypes.SqlString TypeName = reader.GetSqlString(1);
System.Data.SqlTypes.SqlXml message = reader.GetSqlXml(2);
if(TypeName == EndDialogMessageType || TypeName == ErrorMessageType)
{
if (TypeName == ErrorMessageType)
{
LogMessageToFile(message.ToString());
}
SqlCommand endconv = conn.CreateCommand();
endconv.Transaction = tran;
endconv.CommandText = "End Conversation @handle";
endconv.Parameters.Add("@handle", System.Data.SqlDbType.UniqueIdentifier);
endconv.Parameters["@handle"].Value = handle;
endconv.ExecuteNonQuery();
}
else
{
SqlCommand com = conn.CreateCommand();
//LogMessageToFile("Inserting into the table");
com.CommandText = "Insert into SSBEA.dbo.ReceievedMassage(RecievedMessage) values (convert(varchar(1000),@payload))";
com.Parameters.Add("@payload", System.Data.SqlDbType.Xml);
com.Parameters["@payload"].Value = message;
com.Transaction = tran;
com.ExecuteNonQuery();
//LogMessageToFile("Inserted this message to the table:- " + message.Value);
}
}
tran.Commit();
}
} while (MsgRcv);
}
catch (Exception e)
{
LogMessageToFile(e.Message.ToString());
}
}
}
}
Once the application has been created, the last step would be to configure the EA.
In order to configure the EA to listen to listen for notification it needs to be configured accordingly using the configuration file which is created as part of the installation.
The above snapshot of the Configuration File shows the important parameters which needs to be specified for the EA to function properly.
- Notification Service Name: Name of the Service which would be used for Notification.
- Connection String: Information about which server/database on which the Service and the queues are created.
- Application Name: Name of the application which would be invoked as part of External activation.
- On Notification: The four-part name of the queue on which to listen for notification.
- Launch Info: The executable path for the application which would be launched as part of the notification. The Concurrency section indication how many instance of the applications to be invoked in case multiple messages arrive in the queue.
In my case, there were some issues with launching the application from the External Activator which were resolved after setting some policies and UAC controls.
Hopefully this helps!!
jocker25 said
i try it but not understand application,
please source sample for me ?? i need it and tks you
Sourabh Agarwal said
All the source are in the blog itself.
jocker25 said
where is download it ???
I need run demo please help me!
ala said
“In my case, there were some issues with launching the application from the External Activator which were resolved after setting some policies and UAC controls. ”
Could you elaborate on the issues you had please, i tried the sample and it does not execute the program, it’s probably my 5th attempt at runinng samples and the Main not being called. I dont have any errors in event log.
Thanks.
ala said
In my case, it’s probably because nothing is being writtent to MyNotificationQueue.
I noticed that c# has a typo there, maybe there is a typo in sql as well?
Your help is much apreciated.
Thanks.