Section 17.2. Programming Service Broker

17.2. Programming Service Broker

SMO Service Broker classes are used to manage Service Broker objects programmatically. SMO Service Broker classes do not support creating conversations or sending and receiving messages. You send and receive streams of asynchronous messages using T-SQL DML statements, which are discussed in the "Implementing a Service Broker Service" section later in this chapter. Figure 17-2 shows the relationship between SMO classes for Service Broker programming .

Figure 17-1. Service Broker message flow

The remainder of this chapter contains examples that show how to use the SMO Service Broker classes and provides descriptions of the classes. You need a reference to the following assemblies to compile and run the examples:

• Microsoft.SqlServer.ConnectionInfo

• Microsoft.SqlServer.ServiceBrokerEnum

• Microsoft.SqlServer.Smo

The ServiceBroker object described in is the top-level class in the SMO Service Broker class hierarchy and represents the implementation of Service Broker on a SQL Server database. The ServiceBroker property of the Database class returns the Service Broker implementation on a database.

17.2.1. Enumerating Service Broker Objects

This example enumerates all Service Broker objectsmessage types, contracts, queues, services, routes, and remote service bindings:

     using System;     using Microsoft.SqlServer.Management.Smo;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo.Broker;     class Program 

Figure 17-2. Service Broker class relationships

     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["AdventureWorks"];             ServiceBroker   sb = db.ServiceBroker;             Console.WriteLine("MESSAGE TYPES:");             foreach (MessageType mt in sb.MessageTypes)                 Console.WriteLine("  " + mt.Name);             Console.WriteLine(Environment.NewLine + "CONTRACTS:");             foreach (ServiceContract sc in sb.ServiceContracts)                 Console.WriteLine(sc.Name);             Console.WriteLine(Environment.NewLine + "QUEUES:");             foreach (ServiceQueue sq in sb.Queues)                 Console.WriteLine("  " + sq.Name);             Console.WriteLine(Environment.NewLine + "SERVICES:");             foreach (BrokerService bs in sb.Services)                 Console.WriteLine("  " + bs.Name);             Console.WriteLine(Environment.NewLine + "ROUTES:");             foreach (ServiceRoute sr in sb.Routes)                 Console.WriteLine("  " + sr.Name);             Console.WriteLine(Environment.NewLine + "REMOTE SERVICE BINDINGS:");             foreach (RemoteServiceBinding rsb in sb.RemoteServiceBindings)                 Console.WriteLine("  " + rsb.Name);             Console.WriteLine(Environment.NewLine + "Press any key to continue.");             Console.ReadKey(  );         }     } 

Results are shown in Figure 17-3.

The ServiceBroker class exposes a set of collections of the following Service Broker objects: message types, contracts, queues, services, routes, and remote service bindings. A discussion of the classes used to programmatically manage these objects follows.

The classes used to manage Server Broker message types are described in Table 17-1.

Figure 17-3. Results for enumerating Service Broker objects example

Table 17-1. SMO classes for administering Service Broker message types

Class	Description
MessageType	Represents a message type.
MessageTypeCollection	Represents a collection of message types as MessageType objects. The MessageTypes property of the ServiceBroker class returns the message types defined on a Service Broker instance.
MessageTypeEvents	Represents the settings required for message type event notification. The BrokerServiceEvents object is obtained using the Events property of the MessageType object and cannot be created as a standalone object. The SubscribeToEvents( ) method of the BrokerServiceEvents class specifies the events to receive as an ObjectEventSet object.
MessageTypeMapping	Represents a relationship between a message type and a service contract.
MessageTypeMappingCollection	Represents a collection of message type mappings as MessageTypeMapping objects. The MessageTypeMappings property of the ServiceContract class returns the message types defined on a service contract.

The classes used to manage Server Broker service contracts programmatically are described in Table 17-2.

Table 17-2. SMO classes for administering Service Broker service contracts

Class	Description
ServiceContract	Represents a contract.
ServiceContractCollection	Represents a collection of contracts as ServiceContract objects. The ServiceContracts property of the ServiceBroker class returns the contracts defined on a Service Broker instance.
ServiceContractEvents	Represents the settings required for service contract event notification. The ServiceContractEvents object is obtained using the Events property of the ServiceContract object and cannot be created as a standalone object. The SubscribeToEvents( ) method of the ServiceContractEvents class specifies the events to receive as an ObjectEventSet object.
ServiceContractMapping	Represents a collection of contracts mapped to the Service Broker service.
ServiceContractMappingCollection	Represents a collection of service contract mappings as ServiceContractMapping objects. The ServiceContractMappings property of the BrokerService class returns the contracts mapped to the Service Broker instance.

The classes used to manage Server Broker message queues programmatically are described in Table 17-3.

Table 17-3. SMO classes for administering Service Broker message queues

Class	Description
ServiceQueue	Represents a message queue.
ServiceQueueCollection	Represents a collection of queues as ServiceQueue objects. The Queues property of the ServiceBroker class returns the queues defined on a Service Broker instance.
ServiceQueueEvents	Represents the settings required for service queue event notification. The ServiceQueueEvents object is obtained using the Events property of the ServiceQueue object and cannot be created as a standalone object. The SubscribeToEvents( ) method of the ServiceQueueEvents class specifies the events to receive as a ServiceEventSet object.
ServiceQueueEvent	Represents a service queue event that can be included in a ServiceQueueEventSet object.
ServiceQueueEventSet	Represents a set of service queue events as ServiceQueueEvent objects.

The classes used to programmatically manage Server Broker services are described in Table 17-4.

Table 17-4. SMO classes for managing Service Broker services

Class	Description
BrokerService	Represents a Service Broker service.
BrokerServiceCollection	Represents a collection of Service Broker services as BrokerService objects. The Services property of the ServiceBroker class returns the Service Broker services defined on a Service Broker implementation.
BrokerServiceEvents	Represents the settings required for Service Broker service event notification. The BrokerServiceEvents object is obtained using the Events property of the BrokerService object and cannot be created as a standalone object. The SubscribeToEvents( ) method of the BrokerServiceEvents class specifies the events to receive as an ObjectEventSet object.

The SMO classes used to manage Server Broker routes programmatically are described in Table 17-5.

Table 17-5. SMO classes for administering Service Broker routes

Class	Description
ServiceRoute	Represents a Service Broker route.
ServiceRouteCollection	Represents a collection of routes as ServiceRoute objects. The Routes property of the ServiceBroker class returns the routes defined on a Service Broker instance.
ServiceRouteEvents	Represents the settings required for service route event notification. The ServiceRouteEvents object is obtained using the Events property of the ServiceRoute object and cannot be created as a standalone object. The SubscribeToEvents( ) method of the ServiceRouteEvents class specifies the events to receive as an ObjectEventSet object.

The SMO classes used to manage Server Broker remote service bindings programmatically are described in Table 17-6.

Table 17-6. SMO classes for administering Service Broker remote service bindings

Class	Description
RemoteServiceBinding	Represents the settings that Service Broker uses for security and authentication when communicating with a remote service.
RemoteServiceBindingCollection	Represents a collection of remote service bindings as RemoteServiceBinding objects. The RemoteServiceBindings property of the ServiceBroker class returns the remote service bindings defined on a Service Broker instance.
RemoteServiceBindingEvents	Represents the settings required for remote service binding event notification. The RemoteServiceBindingEvents object is obtained using the Events property of the RemoteServiceBinding object and cannot be created as a standalone object. The SubscribeToEvents( ) method of the RemoteServiceBindingEvents class specifies the events to receive as an ObjectEventSet object.

17.2.2. Implementing a Service Broker Service

This example sets up Service Broker objects used in the examples later in this chapter. The following objects are created:

• Request and response message types, using the MessageType class.

• A contract that maps the request and response message types to initiator and target roles, using the ServiceContract and MessageTypeMapping classes.

• Initiator and target queues, using the ServiceQueue class.

• Request and response services, using the BrokerService class. These services are associated with the appropriate contract by using the ServiceContractMapping class.

The source code follows:

     using System;     using Microsoft.SqlServer.Management.Smo;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo.Broker;     class Program     {         static void Main(string[] args)         {             Server server = new Server("localhost");             Database db = server.Databases["ProgrammingSqlServer2005"];             ServiceBroker sb = db.ServiceBroker;             // create the request and response message types             MessageType requestMessage = new MessageType(sb, "HelloWorldRequest");             requestMessage.MessageTypeValidation = MessageTypeValidation.Xml;             requestMessage.Create(  );             MessageType responseMessage = new MessageType(sb, "HelloWorldResponse");             responseMessage.MessageTypeValidation = MessageTypeValidation.Xml;             responseMessage.Create(  );             // create the service contract             ServiceContract contract = new ServiceContract(sb, "HelloWorldContract");             contract.MessageTypeMappings.Add(new MessageTypeMapping(                 contract, "HelloWorldRequest", MessageSource.Initiator));             contract.MessageTypeMappings.Add(new MessageTypeMapping(                 contract, "HelloWorldResponse", MessageSource.Target));             contract.Create(  );             // create the queues             ServiceQueue initiatorQueue = new ServiceQueue(sb, "HelloWorldInitiator");             initiatorQueue.Create(  );             ServiceQueue targetQueue = new ServiceQueue(sb, "HelloWorldTarget");             targetQueue.Create(  );             // create the services             BrokerService requestService =                 new BrokerService(sb, "HelloWorldRequestService");             requestService.QueueName = "HelloWorldTarget";             requestService.ServiceContractMappings.Add(                 new ServiceContractMapping(requestService, "HelloWorldContract"));             requestService.Create(  );             BrokerService responseService =                 new BrokerService(sb, "HelloWorldResponseService");             responseService.QueueName = "HelloWorldInitiator";             responseService.ServiceContractMappings.Add(                 new ServiceContractMapping(responseService, "HelloWorldContract"));             responseService.Create(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

You can view the newly created objects in Object Explorer in SQL Server Management Studio by opening the Databases ProgrammingSqlServer2005 Service Broker node.

This example does not specify all of the possible properties when creating the objects. For example, the message owner is not specified and defaults to the Windows account. The rest of the discussion for this example describes a generic approach for creating the different objects.

Creating a message type defines the name of the message, the owner of the message type as a database user or role, and how the message is validated as a value from the MessageTypeValidation enumeration, as described in Table 17-7.

Table 17-7. MessageTypeValidation enumeration

Value	Description
Empty	Message body must be null.
None	Validation is not performed.
Xml	Message body must contain well-formed XML.
XmlSchemaCollection	Message body must contain XML that validates against the XML schema collection specified for the message type by the ValidationXmlSchemaCollection property.

Creating a contract defines the name of the contract, the owner of the contract as a database user or role, message types included in the contract, and which endpoints (MessageSource.Initiator, MessageSource.InitiatorAndTarget, or MessageSource.Target) can send each message type.

Creating a queue defines the name of the queue, the status (indicating whether the queue is available for use), the retention (specifying whether messages are removed from the queue once retrieved), and, optionally, a stored procedure that runs when a message arrives on the queue, to process the message automatically.

Creating a service defines the name of the service, the owner of the service as a database user or role, the queue that receives messages for the service, and the name of one or more contracts for which the service is a target. The service can initiate conversations only if no contracts are specified.

Creating a route defines the name of the route, the owner of the route as a database user or role, the name of the remote service that the route points to, the database that hosts the target service, the length of time that SQL Server retains the route in the routing table, and network addresses for the route.

Additionally, the other object you can create is a remote service binding. Creating a remote service binding defines the name of the remote service binding, the owner of the binding as a database user or role, the database principal that owns the certificate associated with the remote service, and the remote service to bind to the user.

The preceding example corresponds to the following T-SQL DML batch:

     USE [ProgrammingSqlServer2005]     GO     CREATE MESSAGE TYPE [HelloWorldRequest] VALIDATION = WELL_FORMED_XML     CREATE MESSAGE TYPE [HelloWorldResponse] VALIDATION = WELL_FORMED_XML     CREATE CONTRACT [HelloWorldContract]     (         [HelloWorldRequest] SENT BY INITIATOR,         [HelloWorldResponse] SENT BY TARGET     )     CREATE QUEUE [HelloWorldInitiator]     CREATE QUEUE [HelloWorldTarget]     CREATE SERVICE [HelloWorldRequestService] ON QUEUE [HelloWorldTarget]     (        [HelloWorldContract]     )     CREATE SERVICE [HelloWorldResponseService] ON QUEUE [HelloWorldInitiator]     (        [HelloWorldContract]     ) 

The following T-SQL batch starts a conversation from the HelloWorldRequestService service to the HelloWorldResponseService service by using the HelloWorldContract and sends a message of type HelloWorldRequest by using the conversation:

     BEGIN TRANSACTION     DECLARE @conversationHandle uniqueidentifier     BEGIN DIALOG @conversationHandle        FROM SERVICE [HelloWorldRequestService]        TO SERVICE 'HelloWorldResponseService'        ON CONTRACT [HelloWorldContract]        WITH ENCRYPTION = OFF;     SEND ON CONVERSATION @conversationHandle        MESSAGE TYPE [HelloWorldRequest]        (           CAST(N'<Request>Hello world request</Request>' AS XML)        )     COMMIT 

The BEGIN DIALOG T-SQL statement starts a conversation between two services. The new converstation is assigned a system-generated conversation handle with a data type of uniqueidentifier. All messages are part of a conversation. You can specify a conversation group when starting a conversation. If one is not specified, SQL Server automatically creates a new conversation group for the new conversation.

The SEND T-SQL statement sends a message to a service using an existing conversationthe conversation that the message belongs to is identified by a conversation handle such as the one returned by the BEGIN DIALOG statement in the preceding example. If the SEND statement is not the first statement in a batch, you must terminate the preceding T-SQL statement with a semicolon (;).

The following T-SQL batch retrieves the first message from the HelloWorldInitiator queue. If the message type name is HelloWorldRequest, a response is sent (again using a SEND T-SQL statement) as part of the conversation initiated in the preceding example and the conversation is ended.

     DECLARE @conversationHandle uniqueidentifier     DECLARE @message_body nvarchar(MAX)     DECLARE @message_type_name sysname;     BEGIN TRANSACTION;     RECEIVE TOP(1)         @message_type_name = message_type_name,         @conversationHandle = conversation_handle,         @message_body = message_body     FROM [HelloWorldInitiator]     IF @message_type_name = 'HelloWorldRequest'     BEGIN         SEND ON CONVERSATION @conversationHandle             MESSAGE TYPE [HelloWorldResponse]             (                 CAST(N'<Response>Hello world response</Response>' AS XML)             );         END CONVERSATION @conversationHandle;     END     COMMIT 

The RECEIVE T-SQL statement retrieves one or more messages from a message queue. A RECEIVE statement can specify a conversation handle or conversation group ID to retrieve specific messages. The RECEIVE statement has an optional WAITFOR clause that specifies the length of time to wait for a message. The RECEIVE statement removes the message from the queue unless the RETENTION property of the queue is set to on. If the RECEIVE statement is not the first statement in a batch, you must terminate the preceding statement with a semicolon.

The END CONVERSATION T-SQL statement ends one side of an existing conversation. A conversation ends when initiator and target both end the conversation or when the conversation expires, specified by the LIFETIME argument in the BEGIN DIALOG T-SQL statement. When a conversation ends, Service Broker removes all messages for the conversation from the service queue.

You can use the BEGIN CONVERSATION TIMER T-SQL statement to start a timer. When the timer expires, a message of the type http://schemas.Microsoft.com/SQL/ServiceBroker/Messages/DialogTimer is put on the local queue for the conversation. Each side of the conversation has its own conversation timer.

You can also use the GET trANSMISSION STATUS T-SQL statement to return a description of the last transmission error for one side of a conversation. An empty string is returned if the last transmission succeeded.

You can see the message by querying the HelloWorldTarget queue, using a SELECT T-SQL statement:

     SELECT * FROM HelloWorldTarget 

Service Broker manages the contents of the queue. So, although you can issue SELECT statements to query the contents of the queue, the queue cannot be the target of an INSERT, UPDATE, or DELETE statement.

This T-SQL batch retrieves a response message from the HelloWorldTarget queue, outputs the response, and ends the conversation:

     DECLARE @conversationHandle uniqueidentifier     DECLARE @message_body nvarchar(MAX)     DECLARE @message_type_name sysname;     BEGIN TRANSACTION;     RECEIVE TOP(1)        @message_type_name = message_type_name,        @conversationHandle = conversation_handle,        @message_body = message_body     FROM [HelloWorldTarget]     IF @message_type_name = 'HelloWorldResponse'     BEGIN         PRINT @message_type_name;         PRINT @conversationHandle;         PRINT @message_body;         END CONVERSATION @conversationHandle;     END     COMMIT 

Results are shown in Figure 17-4.

Figure 17-4. Results for retrieve response example