Share via

continuous processing of SQL Queue from C#

  • Article

Question

Saturday, August 6, 2016 4:41 PM

Hi All,

I would like to do continuous processing of SQL Queue from C# code.

Could someone please suggest window service code and how to deploy? 

Thanks!

All replies (10)

Saturday, August 6, 2016 9:39 PM âś…Answered

Here's a sample Windows Service that does just that

Service Broker Message Processor Service Sample

You can extend it or just steal the Service Broker interaction code.

>A SQL queue is simply queried using a SELECT {TOP n} * FROM QueueName;.

You typically want to remove items from the queue as you read them.  And you typcically want your request to block briefly waiting for a message if none is available.  This is also known as a "blocking, destructive read".  And in TSQL you use WAITFOR ... RECEIVE.  SELECT is just a "peek" operation.

RECEIVE is like SELECT, but it deletes the messages too, and a 5 or 10 second timeout is typical.  A call to RECEIVE can return multiple messages if you want (but only if they were sent in the same conversation group).

eg

  /// <summary>
    /// This is the method that actually receives Service Broker messages.
    /// </summary>
    /// <param name="timeout">Maximum time to wait for a message.  This is passed to the RECIEVE command, not used as a SqlCommand.CommandTimeout</param>
    /// <returns></returns>
    static SqlDataReader GetMessageBatch(string queueName, SqlConnection con, TimeSpan timeout, int maxMessages)
    {
      string SQL = string.Format(@"
            waitfor( 
                RECEIVE top (@count) conversation_handle,service_name,message_type_name,message_body,message_sequence_number 
                FROM [{0}] 
                    ), timeout @timeout", queueName);
      SqlCommand cmd = new SqlCommand(SQL, con);

      SqlParameter pCount = cmd.Parameters.Add("@count", SqlDbType.Int);
      pCount.Value = maxMessages;

      SqlParameter pTimeout = cmd.Parameters.Add("@timeout", SqlDbType.Int);

      if (timeout == TimeSpan.MaxValue)
      {
        pTimeout.Value = -1;
      }
      else
      {
        pTimeout.Value = (int)timeout.TotalMilliseconds;
      }

      cmd.CommandTimeout = 0; //honor the RECIEVE timeout, whatever it is.


      return cmd.ExecuteReader();
    }

David

David http://blogs.msdn.com/b/dbrowne/

Saturday, August 6, 2016 6:21 PM | 1 vote

A SQL queue is simply queried using a SELECT {TOP n} * FROM QueueName;.

Why do you think you need a Windows service?

Saturday, August 6, 2016 8:25 PM | 1 vote

I have a blog post I wrote about an easy way to create Windows Services and deploy them (with a batch file): http://geek-goddess-bonnie.blogspot.com/2013/10/easy-windows-services.html

OOPS! Hit the wrong button and accidentally marked my reply as an answer. I immediately unmarked it. Sorry about that!

~~Bonnie DeWitt [C# MVP]

http://geek-goddess-bonnie.blogspot.com

Saturday, August 6, 2016 9:03 PM

I have a blog post I wrote about an easy way to create Windows Services and deploy them (with a batch file): http://geek-goddess-bonnie.blogspot.com/2013/10/easy-windows-services.html

Oh, that is yours? Good to know, linked it a few times.

Saturday, August 6, 2016 10:14 PM

I have a blog post I wrote about an easy way to create Windows Services and deploy them (with a batch file): http://geek-goddess-bonnie.blogspot.com/2013/10/easy-windows-services.html

Oh, that is yours? Good to know, linked it a few times.

Thanks, Christopher!!  =0)

~~Bonnie DeWitt [C# MVP]

http://geek-goddess-bonnie.blogspot.com

Thursday, November 12, 2020 5:07 AM

I know this is a bit late to the conversation, but the resource you linked to is no longer at that location.  Is there a way to get this sample project?

-MBowles

Friday, November 13, 2020 4:20 PM

I know this is a bit late to the conversation, but the resource you linked to is no longer at that location.  Is there a way to get this sample project?

-MBowles

I can't help with that disappearing link, but depending on what part of that link you might have been interested in, you could start a new thread instead of tacking on to this one. Or, perhaps another link from one of my blog posts is relevant to what you want:

https://geek-goddess-bonnie.blogspot.com/2017/09/transactionscope-and-database-queues.html

~~Bonnie DeWitt [C# MVP]

http://geek-goddess-bonnie.blogspot.com

Friday, November 13, 2020 4:27 PM

Thanks, I may do that.  I was referencing the Microsoft link (https://code.msdn.microsoft.com/Service-Broker-Message-e81c4316) posted by the David from Microsoft.  The source/tutorial is no longer on the Microsoft site.   I am looking at writing a monitoring service to listen to a broker queue for crud messages.   I will take a look a the new link you reference.

-MBowles

Friday, November 13, 2020 4:34 PM

Thanks, I may do that.  I was referencing the Microsoft link (https://code.msdn.microsoft.com/Service-Broker-Message-e81c4316) posted by the David from Microsoft.  The source/tutorial is no longer on the Microsoft site.   I am looking at writing a monitoring service to listen to a broker queue for crud messages.   I will take a look a the new link you reference.

-MBowles

Yeah, I knew that's what you meant. I had poked around there on that Microsoft site also and never found anything relevant to that original link. Oh well.

My blog post probably won't help you ... it's about retrieving messages from a queue, but the queue is stored in a database table. 

~~Bonnie DeWitt [C# MVP]

http://geek-goddess-bonnie.blogspot.com

Friday, November 13, 2020 5:00 PM

Going to read you post anyway.   I think I have a snippet to directly query the live queue, was just curious to see how MS said to do it.  Thanks again.

-MBowles