This will be (at least) a two part series on how to use NServiceBus and ServiceBroker.net, where I walk you through how we (Frontwalker) used those techniques in a recent project. The first part will be how to set up the SQL Server Service Broker and the next part I’ll use the ServiceBroker.net and NServiceBus.

ServiceBroker.net was created by Joseph Daigle and it’s a simple wrapper API for the SQL Service Broker. It also has a transport for NServiceBus that uses the SSSB. This transport is now part of the official NServiceBus-contrib over at github.

Our scenario was:

The client had a rather old application and they wanted that some of the changes made to that applications underlying data would be processed and sent to another application. In other words, pretty simple, one-way, integration. The old application didn’t publish any events when the client did something, it just inserted/updated/deleted rows in a SQL Server 2005 database. Just for the record, the application itself was a-not-so-task-based MS Access application…

To solve this we had to listen to changes in the columns we were interested in and then notify our integration service to take action. We are using NServiceBus in our projects and decided to use the ServiceBroker.net to solve this scenario. In this series I will be using the Chinook database and a simple NServiceBus sample to illustrate how we did this.

Setting up the Service Broker in SQL Server

First, make sure that the Service Broker is enabled on your database. Right click your database in Microsoft SQL Server Management Studio and select Properties –> Options. Set “Broker Enabled” to True.

image

The next thing we need to do is to define a message type and a message contract that the Service Broker will use. We do that by running the following SQL:

CREATE MESSAGE TYPE NServiceBusTransportMessage
    VALIDATION = NONE ;
GO

CREATE CONTRACT NServiceBusTransportMessageContract
    ( NServiceBusTransportMessage SENT BY ANY);
GO

After that, we create our service queue, our service and also an error service queue and an error service.

CREATE QUEUE [dbo].[ChinookEventServiceQueue];
GO

CREATE SERVICE ChinookEventService
    ON QUEUE [dbo].[ChinookEventServiceQueue]
    (NServiceBusTransportMessageContract);
GO

-- Error service
CREATE QUEUE [dbo].[ErrorServiceQueue];
GO

CREATE SERVICE ErrorService
    ON QUEUE [dbo].[ErrorServiceQueue]
    (NServiceBusTransportMessageContract);
GO

Run these scripts and refresh your database and you should see the following:

image

It’s in the ChinookEventServiceQueue all your messages from your SQL server will end up before being picked up by ServiceBroker.net and NServiceBus.

Now we need something in our database to actually send messages to our newly created service queue. For this, we will use a Store Procedure. You can copy and paste this from below but make sure the @MessageContract and the @MessageType variables have the same name as the message type and message contract you created earlier.

Another very important part is that the @TransportMessage variable get the correct xmlns set (the <Messages xmlns=”http://tempuri.net/Namespace.of_your.Events”>). The xmlns need to be set to tempuri.net/ and then the correct namespace of where your events resides. (If you don’t know what your namespace will be, just alter this store procedure later).

Note! The http://tempuri.net/Namespace.of_your.Events is the default namespace of NServiceBus XmlSerializer. You can change this when configuring NServiceBus.

CREATE PROCEDURE [dbo].[SendNServiceBusMessage]
 @TargetService NVARCHAR(200),
 @MessageName NVARCHAR(200),
 @MessageContent NVARCHAR(4000)
AS

BEGIN
-- Sending a Service Broker Message
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @MessageContract NVARCHAR(200);
DECLARE @MessageType NVARCHAR(200);
DECLARE @TransportMessage NVARCHAR(4000);

SET NOCOUNT ON

SET  @MessageContract = 'NServiceBusTransportMessageContract';
SET  @MessageType = 'NServiceBusTransportMessage';

BEGIN TRANSACTION;

 BEGIN DIALOG @InitDlgHandle
   FROM SERVICE @TargetService
   TO SERVICE @TargetService
   ON CONTRACT @MessageContract
   WITH ENCRYPTION = OFF;

SET @TransportMessage ='<TransportMessage><Body><![CDATA[<Messages xmlns="http://tempuri.net/ServiceBrokerNetSample.Events"><'+@MessageName+'>' +
    @MessageContent +'</'+@MessageName+'></Messages>]]></Body></TransportMessage>';

 SEND ON CONVERSATION @InitDlgHandle
   MESSAGE TYPE @MessageType
    (@TransportMessage);

COMMIT TRANSACTION;

END

GO

Now we need something to actually use this Store Procedure to create messages for our service queue. This is the scary part, we will need to use a trigger on the table we’re interested in!

Now you have a choice. You can either create one trigger per change you’re interested in or create one “global” trigger that listens to all changes to a given table and then use an event handler in NServiceBus to later decide what was changed. We used the latter in our project, but in this sample I’m just going to listen for changes in one column.

Let’s say we have the following scenario for this example:

When a customer changes his email address, we want to notify some other service…

The Chinook database have a table named “Customer” and it contains a column named “Email”. Let’s create the trigger that listens for changes to that column.

Here’s the SQL for creating the trigger:

CREATE TRIGGER [dbo].[TRG_EmailChanged]
   ON  [dbo].[Customer]
   FOR UPDATE
AS
IF UPDATE(Email)
BEGIN
	SET NOCOUNT ON;

	DECLARE @MESSAGENAME NVARCHAR(255)

	-- This is what event your application will use later
	SET @MESSAGENAME = 'CustomerEmailChangedEvent'

	DECLARE @CustomerId int
	DECLARE @OldEmail NVARCHAR(60)
	DECLARE @NewEmail NVARCHAR(60)

	SELECT @OldEmail = Email FROM deleted
	SELECT @NewEmail = Email, @CustomerId = CustomerId FROM inserted

	IF @OldEmail <> @NewEmail
	BEGIN

		DECLARE @CustomerIdXml XML
		DECLARE @PreviousEmailAddressXml XML
		DECLARE @NewEmailAddressXml XML
		DECLARE @XmlText NVARCHAR(MAX)

		-- I'm using select for xml because otherwise special characters might cause problems
		SET @CustomerIdXml = (select @CustomerId for xml path('CustomerId'))
		SET @PreviousEmailAddressXml = (select @OldEmail for xml path ('PreviousEmailAddress'))
		SET @NewEmailAddressXml = (select @NewEmail for xml path ('NewEmailAddress'))

		-- This is the text that we will use in our message
		SET @XmlText = cast(@CustomerIdXml as NVARCHAR(MAX))
			+ cast(@PreviousEmailAddressXml as NVARCHAR(MAX))
			+ cast(@NewEmailAddressXml as NVARCHAR(MAX))

		exec SendNServiceBusMessage
			@TargetService = N'ChinookEventService',
			@MessageName = @MESSAGENAME,
			@MessageContent = @XmlText;
	END
END
GO

Updated the trigger and added CustomerId too. Kind of useless to send an event like this without saying wich customer it affects…

I’m by no means a SQL expert, so I’ve kept the trigger as simple as possible. Basically, what’s going on is that I check if the old value differs from the new value and if so I create a message body in XML and pass that and the name of the message to the store procedure we created earlier.

Now we have a trigger that, if a customers email address is changed, creates a message and hands that to the Service Broker. You can try it out by running a simple update on a customer:

update Customer set Email = 'jens.pettersson@local.loc' where CustomerId = 60

Now, under your Service Broker > Queues, right click the ChinookEventServiceQueue and select “Select top 1000 rows” and you should see one row. Look in the last column, called “casted_message_body”. There you should see the XML your trigger passed on to your store procedure and more that the sp added for this message to work with NServiceBus.

image

That’s it for the first part! It’s quite a lot of scary SQL stuff going on, but when you’ve set this up you really only need to worry about new triggers later on.

In the next part, I will build a simple NServiceBus sample that actually uses the ServiceBroker.net. This post didn’t really say anything about that or NServiceBus, but this stuff is needed to get something up and running.

//J