Queues with SQL Server 2005 Service Broker and C#

{

I recently had to set this up and didn't find all too many resources online. I don't have time for a full commentary but suffices to say this: SQL Server 2005 has some excellent reliable messaging capabilities through what's called Service Broker. If you, like me, are a complete skeptic when it comes to new products, this is what I think of as a compelling feature. In the following example, I'm setting up a queue for FileIDs - assume these are identifiers for files that need to be processed asynchronously. After I created my database in SQL 2005, I opened up the query tool and went ahead with the following TSQL:

-- you can attribute an hour to finding this requirement :(
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password1';
GO

-- message, contract, queue, service creation
CREATE MESSAGE TYPE FileIDForQueue
VALIDATION = NONE;
GO

CREATE CONTRACT FileQueueContract
(FileIDForQueue SENT BY INITIATOR)
GO

CREATE QUEUE dbo.FileIDReceiverQueue
GO

CREATE QUEUE dbo.FileIDSenderQueue
GO

CREATE SERVICE SenderService
ON QUEUE dbo.FileIDSenderQueue
GO

CREATE SERVICE ReceiverService
ON QUEUE dbo.FileIDReceiverQueue (FileQueueContract)
GO

In order to leverage the queue, I wrote the following stored procedures to enqueue, dequeue, and peek:

/*
STORED PROCEDURE RESPONSIBLE FOR INSERTING A QUEUE ITEM
*/
CREATE PROC spSendFileToQueue
@FileID INT
AS
BEGIN TRANSACTION;
DECLARE @conversationID UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @conversationID
FROM SERVICE SenderService
TO SERVICE 'ReceiverService'
ON CONTRACT FileQueueContract;
SEND ON CONVERSATION @conversationID
MESSAGE TYPE FileIDForQueue(@fileid);
--END CONVERSATION @conversationID;
COMMIT TRANSACTION;
GO

/*
STORED PROCEDURE RESPONSIBLE FOR RETRIEVING QUEUE ITEMS IN FIFO STYLE
*/
CREATE PROC spGetFileFromQueue
@FileID INT OUTPUT
AS
RECEIVE TOP(1) @FileID = CONVERT(INT, message_body) FROM FileIDReceiverQueue
GO

/*
STORED PROCEDURE RESPONSIBLE FOR "PEEKING" INTO QUEUE
*/
CREATE PROC spPeekFileQueue
@FileID INT OUTPUT
AS
SELECT TOP(1) @FileID = CONVERT(INT, message_body) FROM FileIDReceiverQueue
WHERE message_body IS NOT NULL
GO

Now that my stored procedures are in place, I can test in TSQL:

-- to send it to the queue:
spSendFileToQueue 45

-- peek into the queue
DECLARE @F INT
EXEC spPeekFileQueue @FileID=@F OUTPUT
PRINT @F

-- to get it back
DECLARE @F INT
EXEC spGetFileFromQueue @FileID=@F OUTPUT
PRINT @F

From here it's trivial to port the procedure calls to C#:

public static void RunActionProc(string procName, SqlParameter parm) {
SqlParameter[] parms = new SqlParameter[] { parm };
RunActionProc(procName, parms);
}

public static void RunActionProc(string procName, SqlParameter[] parms)
{
SqlCommand co = new SqlCommand(procName, GetConnection(true));
co.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parm in parms)
{
co.Parameters.Add(parm);
}
co.ExecuteNonQuery();
}

public static void Enqueue(int value)
{
DBHelper.RunActionProc("spSendFileToQueue", new SqlParameter("@FileID", value));
}

public static int Peek(){
SqlParameter fileIdParameter = new SqlParameter("@FileID", SqlDbType.Int);
fileIdParameter.Direction = ParameterDirection.Output;
DBHelper.RunActionProc("spPeekFileQueue", fileIdParameter);
return Convert.ToInt32(fileIdParameter.Value);
}

public static int Dequeue() {
SqlParameter fileIdParameter = new SqlParameter("@FileID", SqlDbType.Int);
fileIdParameter.Direction = ParameterDirection.Output;
DBHelper.RunActionProc("spGetFileFromQueue", fileIdParameter);
return Convert.ToInt32(fileIdParameter.Value);
}

A sample project in C# can be found here.

}