SQL Service Broker and .NET Windows Service - Best practices?

Question

I currently have a database that gets updated from a legacy application. I'd like to utilize a SQL Service Broker queue so that when a record is updated, a message is placed in the queue (using a trigger or something).

I would then like to have a long running application (Windows service written in .NET) that is constantly "listening" to the queue to grab the messages and process them for another application.

I've found some sample code on the web, and just wanted to get some input on whether the code is solid or not. So, here's an abbreviated version of the Windows service class:

public class MyService
{
    public void StartService()
    {
        Thread listener = new Thread(Listen);
        listener.IsBackground = true;
        listener.Start();
    }

    private void Listen()
    {
        while (true)
        {
            using (SqlConnection connection = new SqlConnection(_connectionString))
            {
                string commandText = "WAITFOR ( RECEIVE * FROM MyQueue);";
                using (SqlCommand command = new SqlCommand(commandText, connection))
                {
                    connection.Open();
                    command.CommandTimeout = 0;
                    SqlDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        // Process message
                    }
                }
            }
        }
    }
}

What do you think? The code works exactly the way I want it to. But the idea of spinning off a new thread that contains a SQL command will never time out - inside an infinite loop - makes me a little nervous.

Answer 1

Your service will not shutdown cleanly if you are in a infinite loop. You should be checking for an exit condition, set on when the service gets the shutdown message. You can add a timeout to the WAITFOR so that you can check if you should be shutting down. You should also check this on each row processed.

I have used a 2 second sleep for the delay, but doing a 2 second timeout on the WAITFOR would accomplish the same thing.

A service has 30 seconds to shutdown or Windows will consider it stuck.