Message Orientation
SQL Server 2005 Service Broker: Microsoft’s New Messaging Technology
Juan Carlos (John Charles) Olamendy Turruellas
In SQL Server 2005, Microsoft introduced the Service Broker (SSB) technology, which supports the Broker design pattern and the principles of message-oriented middleware (MOM). This technology, however, has been little used, despite the ability of SSB, in contrast to a traditional synchronous request/response approach, to enable developers to build reliable, scalable, distributed, and asynchronous messaging applications by implementing message-queuing mechanisms that are combined with the relational database capabilities of SQL Server.
A Broker is a software component that sits between enterprise applications that require integration and interacts with them. This middleware hides the complexity of the communication protocols and topologies when applications need to connect to each other to exchange data. It can also provide features such as transformation and message routing, managing the execution of business process logic in this way.
In this model, functionality is exposed as a service, and the exchange of data between services is accomplished through messages regardless of whether the applications involved are online at the same time. This model also enables loosely coupled composite applications and guarantees that messages are not lost. In this article, I will describe the main concepts of SSB by illustrating a real-world business scenario that shows the potential of message-based composite solutions.
Application of SSB in a Real-World Business Scenario
The main context in which you can apply SSB technology is in the development of an integration solution for enterprise applications. These applications usually support the management and automation of business processes that require reliable message-oriented middleware (no messages will be lost) for the exchange of information between them. In addition, these applications reside within a complex environment with a large number of systems and users. Not all of the applications are online at the same time, and their processing of requests in a loosely coupled way can take a long time.
In a traditional request-response model, communication between the back-end systems is synchronous and the business process logic is implemented (using a high-level language such as C# or Java) through components in the business layer of your solution and as a series of database transactions. In this approach, if one back-end system is down, the whole process passes to a standby status. For example, you cannot authorize a credit card if the payment system is down.
The main goal of SSB applications is to integrate enterprise systems through a common communication channel. Some external applications (ERP, supply chain management, CRM, and others) or internal modules (T-SQL code and CLR managed triggers and stored procedures) put messages into queues, while their counterparts receive notifications that messages are on the queues (and need to be retrieved and dequeued) through a trigger or a regular SEND/RECEIVE operation. The vision here is based on an enterprise system whose server-side components can accept requests immediately but defer the processing of the requests, returning an informative message to the client indicating that it should wait for the response later.
The sample asynchronous SSB solution illustrated in this article (see Figure 1 ) includes a portal site and store and inventory databases. The portal site has two Web pages: one for entering data associated with an order request and another for viewing the status of the order. The store database application receives order requests from the portal site, persists this data into the table tbOrder, and then posts the order request data in the form of messages in a queue at a common bus in the communication channel at which the inventory database application listens. The inventory database receives incoming request orders and handles the warehouse stocking and distribution delivery. It also creates an underlying order response and sends it, by using another queue, to the store database. Finally, the store database listens for order response messages and updates the status of the underlying order request row (OK or BAD status) in the table tbOrder, which lets users check the status of orders. In this way, the distributed solution delegates all the messaging, backup, administration, and failover procedures to the SSB infrastructure (applying the Broker design pattern) and concentrates its effort on the problem domain.
Figure 1 The eCommerce Scenario as a Composite Solution of Enterprise Systems
From the viewpoint of SSB principles, the store database application starts a conversation with the inventory database application by submitting an order request message to the queue of the inventory application through public interfaces based on services. Services are the logical endpoints in SSB technologies, which use queues as the message repository and specify their functionality using contracts. Contracts specify the direction of messages and their underlying message type in a given conversation. After a message is put into the queue (enqueued), the store application continues with other tasks. When the inventory application is ready (this might be at night or at other idle time), a service program takes the message from the queue and processes it, and the inventory database application sends a message confirming that the order was processed along with the end result. (See Figure 2 ).
Figure 2 The SSB Solution Architecture
As you analyze the SSB solution architecture, you might recognize that the underlying integration solution could be realized using other messaging technologies, such as MSMQ and Microsoft BizTalk Server. However, Microsoft SQL Server along with the Service Broker provides a reliable infrastructure on which to build composite applications and has a level of recoverability, maintainability, durability, performance, and scalability that can be found only in a database system. The quality-of-service features in SSB include the following:
- Transactions. If a message is taken out of the queue and an error occurs, the message is not lost.
- Recoverability. If the system fails because of media failures, problems in the processor or in the main memory, or the system undergoes an outage, the message must be durably persisted for the system to continue processing when it is restored.
- Scalability. Once the messages are posted into a queue, they are processed at a rate that does not introduce performance problems when more resources are added. The workload might increase to many thousands of messages per second, but the workload can be balanced by distributing the work across many participant SSB instances.
Implementing the Solution
In this section, I’ll walk through how to implement the SSB solution for the business scenario. The principal databases are the store and inventory databases, which are hosted on different servers. This means that you need to establish connections to these systems using SQL Server Management Studio.
The first step is to enable Service Broker and Transport Security features in both databases (see Figure 3) by configuring the underlying instances level in the system catalog (master database).
Figure 3 Enabling SSB and Transport Security Features in Participating Databases
--- Configure Instance1.
use master;
alter database Store set ENABLE_BROKER;
alter database Store set TRUSTWORTHY ON;
use Store;
create master key encryption by password = 'Store*-';
--- Configure Instance2.
use master;
alter database Inventory set ENABLE_BROKER;
alter database Inventory set TRUSTWORTHY ON;
use Inventory;
create master key encryption by password = 'Inventory*-';
Security Settings
The next step is to configure the communication infrastructure between the two SQL Server instances and databases in order to establish a secure channel involving authentication and, optionally, encryption of messages. You can use either Windows or certificate-based authentication.
Under the Windows mechanism, each SQL Server instance logs on to the other by using the credentials of the Windows user accounts under which the processes are running. It’s required that both SQL Server instances use a domain user account with privileges to log on as a service and send messages.
Under the certificate mechanism, each SQL Server instance authenticates the other by using a digital certificate. In SQL Server 2005, it’s required that you generate a public/private-key pair as the first step to set up a public-key infrastructure. The private key is protected by the database master key of the master database and is mainly used to establish the system’s identity by interchanging data encrypted with the private key and verified with the public key on the other side. Then each SSB endpoint is configured to use the generated certificates for authentication and encryption.
Message Structure and Validation
Next you need to specify the underlying communication objects, such as XML schemas, message types, queue, contracts, and services, that are used over this secured channel. A message is the content holder of the transmitted data, and each instance has an associated message type. To determine the structure of the interchanged messages, you use message types to specify the structure and constraints of the underlying message instances. This can be a simple binary object, a valid XML document, or a well-formed XML document. I used XML standards to represent and format the messages in our integration solution. In this business scenario, the first message represents the order sent from the store database to the inventory database using a well-formed XML document (described by the OrderRequestMsgType message type and the OrderRequestSchema schema and containing data about the order identifier, customer identifier, order date, and the requested item with its identifier, price, and quantity). The second message represents the correlated response sent by the target database using a well-formed XML document, too (described by the OrderResponseMsgType message type and the OrderResponseSchema schema and containing data about the correlated message request, process status, and description). Figure 4 defines the XML schemas used to specify and validate the structure of the Order Request and Order Response messages.
Figure 4 The XML Schemas for Order Request and Order Response messages
--- Create the schemas.
create xml schema collection OrderRequestSchema as
N'
<xs:schema xmlns="http://www.mycorporation.com/2007_schemas/"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.mycorporation.com/2007_schemas/"
elementFormDefault="qualified" attributeFormDefault="unqualified">
<xs:element name="Order">
<xs:complexType>
<xs:sequence>
<xs:element name="LineItem">
<xs:complexType>
<xs:attribute name="ItemNumber"
type="xs:int"/>
<xs:attribute name="Quantity"
type="xs:int"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="Id" type="xs:int"/>
<xs:attribute name="Customer" type="xs:int"/>
<xs:attribute name="Order_date" type="xs:dateTime"/>
</xs:complexType>
</xs:element>
</xs:schema>
'
go
create xml schema collection OrderResponseSchema as
N'
<xs:schema xmlns="http://www.mycorporation.com/2007_schemas/"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.mycorporation.com/2007_schemas/"
elementFormDefault="qualified" attributeFormDefault="unqualified">
<xs:element name="OrderConfirmation">
<xs:complexType>
<xs:sequence>
<xs:element name="Id" type="xs:int"/>
<xs:element name="Processing_Status" type="xs:int"/>
<xs:element name="Processing_Description"
type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
'
Go
The following code creates on both systems the message types based on the previous schemas, which are part of the interface of your SSB services.
create message type OrderRequestMsgType validation= VALID_XML
with schema collection OrderRequestSchema;
create message type OrderResponseMsgType validation=VALID_XML
with schema collection OrderResponseSchema;
go
The WITH SCHEMA COLLECTION clause indicates that the SSB has to validate the messages against the defined XML schemas. SSB performs validation as soon as the target service receives the messages. If the content doesn’t pass validation, SSB returns an error message. It’s worth pointing out that message validation can be fairly expensive. Thus, it’s recommended that message validation be enabled for processing messages from untrusted sources because bad messages can be discovered early; otherwise, this feature can be turned off. You can refer to SQL Server Books Online to better understand message-type creation syntax.
Definition of the Communication Infrastructure
After creating the message types, you need to define the contracts on both systems to specify what messages (and in what direction) the service can send or receive. A contract also determines whether a message is sent by the initiator of the conversation, by the target of the conversation, or by either the initiator or the target of the conversation. In this case, the initiator is the store database, and the target is the inventory database. The SENT BY clause specifies the endpoints involved and their underlying role. SSB ensures that only message types that are defined in a contract are handled and processed. If a service sends another message type, the message is rejected, and an error message is sent back.
create contract OrderProcessingContract
(OrderRequestMsgType sent by initiator,OrderResponseMsgType sent by
target)
go
You also need to create a table named tbOrder on the store database to record the data in the order request along with its processing status and description (returned from the inventory database) so that the client can check the order by using a Web page. The code that follows shows how to create the table. The OrderStatus field in the table can have one of three values:
- Value = 0. Order not processed; the OrderStatusDescription field is null.
- Value = 1. Order processed without errors; the OrderStatusDescription field is null.
- Value = 2. Order processed with errors; more details in the OrderStatusDescription field.
create table tbOrder
(
nOrderID int identity(1,1) primary key,
nCustomer int,
dtOrderDate datetime,
nItemNumber int,
nItemQuantity int,
nOrderStatus int,
vcOrderStatusDescription varchar(50)
);
To insert the order data that is entered in the Web portal front end into the table tbOrder, you can define a stored procedure, shown in Figure 5, as the main interface to submit the underlying data and return an order number. The order number is displayed at some other point in time and used by the user to request the underlying order status.
Figure 5 Creating the Stored Procedure spInsertOrder
create procedure spInsertOrder
@nOrderID int output,
@nCustomer int,
@dtOrderDate datetime,
@nItemNumber int,
@nItemQuantity int
as
begin
insert into tbOrder(nCustomer, dtOrderDate, nItemNumber, nItemQuantity,
nOrderStatus)
values(@nCustomer, @dtOrderDate, @nItemNumber, @nItemQuantity, 0);
set @nOrderID=@@IDENTITY;
end;
The next step is to create the queues for storing the received messages (either from the target service or the initiator server) that must be processed. When SSB receives and validates a message (as explained earlier), it inserts the message into the queue. (You can refer to SQL Server Books Online for more documentation of the CREATE QUEUE statement.)
A queue can be bound to a stored procedure that automatically handles the messages (whenever one is sent to the queue) and processes them. One important parameter in the creation of the queue is MAX_QUEUE_READERS, which specifies the maximum number of instances of the activation stored procedure that the queue starts at the same time. The value of MAX_QUEUE_READERS must be a number between 0 and 32,767. If you’re expecting order requests at a faster rate than one reader can process them, you can specify multiple queue readers to instantiate more readers as needed until the maximum number is reached. It’s worth noting that this stored procedure must be created before the queue, but for the sake of this article, the business logic of this stored procedure is discussed in the section “A Conversation Between Services in SSB Technology . ” In the example, we’re reading the queue and processing the messages automatically, but some business scenarios require the intervention of users, which means that the queue must be read by using the underlying logic exposed as a stored procedure to be invoked from a graphical user interface.
Up to this point, we have created for both systems the database objects that establish the bidirectional communication channel and its common understanding. Next you need to create artifacts for each system that provide the order processing functionality.
First you need to create a queue on the store database (the underlying stored procedure spStoreDB_QueueReader must be created first; see Figure 8) to hold and process incoming response order messages and update the order status in the table tbOrder.
--- Create the queue on the Store database
create queue StoreQueue with status=on,
activation (procedure_name=spStoreDB_QueueReader,max_queue_readers=5,
execute as 'dbo');
go
Next you create another queue on the inventory database (the underlying stored procedure spStoreDB_QueueReader must be created first; see Figure 7) to receive order request messages from the store database and process them.
--- In the Inventory database
create queue InventoryQueue with status=on,
activation (procedure_name=spInventoryDB_QueueReader,max_queue_readers=5,
execute as 'dbo');
go
Now you need to define the services, shown in the following code. A service represents an application-level endpoint in the SSB solution and can be both the initiator and the target of messages. A service can be bound to several queue and contract combinations. SSB can activate a service automatically whenever a new message arrives in a queue, and SSB can alternatively schedule an event for the activation or execute the service manually. The SSB services are created independently in both database systems.
--- In the Store database
create service StoreService on
queue StoreQueue(OrderProcessingContract);
go
--- In the Inventory database
create service InventoryService on
queue InventoryQueue(OrderProcessingContract);
go
And you also need to create a route on which to convey messages from the service to the remote endpoint. Routes are the communication channels that bind the services together.
--- Create route to InventoryService from Store database
create route Route2Inventory
with service_name = 'InventoryService',
address = 'tcp://Instance2:4037';
--- Create route to StoreService from Inventory database
create route Route2Store
with service_name = 'StoreService',
address = 'tcp://Instance1:4037';
At this point, you’re ready to create a remote service binding that maps credentials that are used to open a conversation with a remote SSB endpoint. Here’s the code for the bindings.
--- Create remote service binding on Store database
create remote service binding ToInventory_RSB
to service 'InventoryService'
with user = InventoryUser;
--- Create remote service binding on Inventory database
create remote service binding ToStore_RSB
to service 'StoreService'
with user = StoreUser;
A Conversation Between Services in SSB Technology
To establish a communication channel between services in SSB, all you need to do is to begin a conversation. This conversation is created by using the BEGIN DIALOG CONVERSATION statement, and each instance has a unique handle representing the channel for data exchanges. You can use the SEND statement to convey messages through specific open conversations by defining the message type and its content. Finally, to finish a conversation, the END CONVERSATION statement is used.
Figure 6 shows a stored procedure invoked by the user that will enter the order request data, record this information into the table tbOrder, start a transactional conversation between the store database and the inventory database through the contract and service created earlier, and then send an Order Request document instance of the XML schema OrderRequestSchema over this conversation. This stored procedure is invoked by the user through the Order Request data entry page in the Web portal.
Figure 6 The Stored Procedure EnterOrderRequest on the Store Database
--- Create the stored procedure EnterOrderRequest on the Store database
create procedure spEnterOrderRequest
@nOrderID int output,
@nCustomer int,
@nItemNumber int,
@nItemQuantity int
as
begin
declare @dialog_handle uniqueidentifier;
declare @Order_XDoc xml(OrderRequestSchema);
declare @dtNow datetime;
set @dtNow = getdate();
begin transaction
exec spInsertOrder @nOrderID
output,@nCustomer,@dtNow,@nItemNumber,@nItemQuantity;
set @Order_XDoc =
'<Order xmlns="http://www.mycorporation.com/2007_schemas/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.mycorporation.com/2007_schemas/"
Id="'+cast(@nOrderID as varchar(10))+
'" Customer="'+cast(@nCustomer as varchar(10))+'"
Order_date="'+convert(varchar,@dtNow,126)+'-05:00">
<LineItem ItemNumber="'+cast(@nItemNumber as varchar(10))+
'" Quantity="'+cast(@nItemQuantity as varchar(10))+'"/>
</Order>';
begin dialog conversation @dialog_handle
from service StoreService
to service 'InventoryService'
on contract OrderProcessingContract;
send on conversation @dialog_handle
message type OrderRequestMsgType(@Order_XDoc);
commit;
end;
You can check the status of the transmission by executing a query such as the following on the system table sys.transmission_queue and looking at the field transmission_status in the result set to discover error messages.
---- Check the transmission status on the Store database.
select *
from sys.transmission_queue;
Now you can also look at the message that arrives at the recipient queue (in this case the InventoryQueue queue) by using the SELECT statement over the underlying queue and analyzing the results set.
select message_type_name, cast(message_body as xml) message, queuing_order,
conversation_handle, conversation_group_id
from InventoryQueue;
Next, you need to process the incoming order request message and send an order response to the store database with the actual processing status. First we’ll create the stored procedure that implements the logic of the queue reader associated with the InventoryQueue queue. To start, you need to receive the order request message from the inventory queue using the statement RECEIVE TOP(1 ). You retrieve the order and item identifier using the XQuery value method of XML data types and then check whether the product exists in the test database AdventureWorks on the table Production.Product . Next you create the order response message with the proper processing status and description, send this message through the open conversation dialog using the statement SEND ON CONVERSATION, and finally end the conversation using the statement END CONVERSATION. The code is shown in Figure 7.
The RECEIVE statement is similar to the SQL SELECT statement in that you can query a database object (in this case a queue) and assign the result values to variables. The RECEIVE statement deletes the messages from the queue after receiving them, unlike the SELECT statement, which does not delete the records from the tables. XQuery is the emerging query language for XML data sources. It uses XPath expressions to address specific parts of documents. For example, the expression (/ns:Order/@Id)[1] allows you to select the attribute Id child of the first Order element in the transmitted XML document. Although XQuery was mainly conceived as a query language, now it’s enhanced to provide transformation features such as XSLT.
Figure 7 Creating the Stored Procedure spInventoryDB_QueueReader to Read Messages from the InventoryQueue Queue
--- Create stored procedure to process the incoming order request message
create procedure spInventoryDB_QueueReader
as
begin
declare @Conv_Dialog_Handle uniqueidentifier;
declare @Conv_Group_Handle uniqueidentifier;
declare @Order_XDoc xml(OrderRequestSchema);
declare @OrderConfirmation_Text varchar(8000);
declare @OrderConfirmation_XDoc xml(OrderResponseSchema);
declare @nOrderId int;
declare @nItemNumber int;
declare @nItemQuantity int;
declare @nItemNumberResult int;
declare @nProcessing_Status int;
declare @vcProcessing_Description varchar(120);
begin transaction;
--- Receive the message from the queue
receive top(1) @Order_XDoc = message_body,
@Conv_Dialog_Handle = conversation_handle,
@Conv_Group_Handle = conversation_group_id
from InventoryQueue;
--- Retrieve the order identifier
select @nOrderId = @Order_XDoc.value
('declare namespace ns="http://www.mycorporation.com/2007_schemas/";(/ns:Order/@Id)[1]', 'int');
select @nItemNumber = @Order_XDoc.value
('declare namespace ns="http://www.mycorporation.com/2007_schemas/";
(/ns:Order/ns:LineItem/@ItemNumber)[1]', 'int');
--- Process the incoming order request
select @nItemNumberResult=ProductID
from AdventureWorks.Production.Product
where ProductID=@nItemNumber;
if (@nItemNumberResult is null)
begin
set @nProcessing_Status = 2;
set @vcProcessing_Description = 'Processing transaction not
'successfully. No product in the
'Inventory database';
end
else
begin
set @nProcessing_Status = 1;
set @vcProcessing_Description = 'Processing transaction successfully.
'Found product in the Inventory
'database';
end;
--- Create the Order Confirmation message as a response
set @OrderConfirmation_Text =
'<OrderConfirmation xmlns="http://www.mycorporation.com/2007_schemas/"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.mycorporation.com/2007_schemas/">
<Id>'+cast(@nOrderId as varchar(10))+'</Id>
<Processing_Status>'+cast(@nProcessing_Status as varchar(10))+'</Processing_Status>
<Processing_Description>'+@vcProcessing_Description+'</Processing_Description>
</OrderConfirmation>';
set @OrderConfirmation_XDoc = @OrderConfirmation_Text;
---- Send the message through the open conversation dialog (@Dialog_Handler)
send on conversation @Conv_Dialog_Handle
message type OrderResponseMsgType(@OrderConfirmation_XDoc);
---- End the conversation
end conversation @Conv_Dialog_Handle;
commit;
end;
go
In the store database, you can check the confirmation response for the order request with the following code.
---- Check the messages on the StoreQueue on the Store database.
select message_type_name, cast(message_body as xml) message, queuing_order,
conversation_handle, conversation_group_id
from StoreQueue;
Now we’ll create the stored procedure spStoreDB_QueueReader , which implements the logic of the queue reader associated with the StoreQueue queue. It uses the same logic as spInventoryDB_QueueReader to get the messages from the StoreQueue and update the table tbOrder with the processing status and description. The code is shown in Figure 8.
Figure 8 Create the Stored Procedure spStoreDB_QueueReader to Read Messages from the StoreQueue Queue.
--- Create stored procedure to process the order response message
create procedure spStoreDB_QueueReader
as
begin
declare @Conv_Dialog_Handle uniqueidentifier;
declare @Conv_Group_Handle uniqueidentifier;
declare @Order_XDoc xml(OrderResponseSchema);
declare @nOrderId int;
declare @nProcessing_Status int;
declare @vcProcessing_Description varchar(50);
begin transaction;
--- Receive the message from the queue
receive top(1) @Order_XDoc = message_body,
@Conv_Dialog_Handle = conversation_handle,
@Conv_Group_Handle = conversation_group_id
from StoreQueue;
--- Retrieve the order identifier
select @nOrderId = @Order_XDoc.value('declare namespace
ns="http://www.mycorporation.com/2007_schemas/";(/ns:OrderConfirmation/ns:Id)[1]', 'int');
select @nProcessing_Status = @Order_XDoc.value('declare namespace
ns="http://www.mycorporation.com/2007_schemas/";(/ns:OrderConfirmation/ns:Processing_Status)[1]', 'int');
select @vcProcessing_Description = @Order_XDoc.value('declare namespace
ns="http://www.mycorporation.com/2007_schemas/";
(/ns:OrderConfirmation/ns:Processing_Description)[1]', 'varchar(50)');
update tbOrder
set nOrderStatus=@nProcessing_Status,
vcOrderStatusDescription=@vcProcessing_Description
where nOrderID=@nOrderId;
commit;
end;
go
Finally, you need to implement the components of the portal site, the front end of our solution. The portal site has two pages, the Order Request entry page and Order Status page. The Order Request entry page allows users to enter the data for the Order Request and invoke the stored procedure spEnterOrderRequest, which implements the main business logic associated with the order processing by using the Service Broker plumbing explained earlier. The order identifier is displayed to check order status later. The Order Status page enables users to check the order status by entering the order identifier.
I used Visual Studio to create a new Web site and added two Web forms. The first, OrderRequest_EntryPage.aspx , contains three labels to describe the fields to enter, three TextBox web controls ( m_tbItemQuantity , m_tbItemNumber , m_tbCustomerID ) to get data about the order request, and a Button web control ( m_btSubmit ) that enables the execution of the business logic of our solution and the display of the order identifier in the output label ( m_lbOutput ). Figure 9 shows the code for the page. The code behind associated with OrderRequest_EntryPage.aspx is shown in Figure 10.
Figure 9 The Layout of OrderRequest_EntryPage.aspx.
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="OrderRequest_EntryPage.aspx.cs" Inherits="OrderRequest_EntryPage" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Order Request Entry page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table border="0" width="100%" cellpadding="0" cellspacing="0">
<tr>
<td style="width:25%">
<asp:Label ID="Label1" runat="server" Text="Enter
CustomerID"></asp:Label>
</td>
<td style="width:75%">
<asp:TextBox ID="m_tbCustomerID"
runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td style="width:25%">
<asp:Label ID="Label2" runat="server" Text="Enter Item
Number"></asp:Label>
</td>
<td style="width:75%">
<asp:TextBox ID="m_tbItemNumber"
runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td style="width:25%">
<asp:Label ID="Label3" runat="server" Text="Enter Item
Quantity"></asp:Label>
</td>
<td style="width:75%">
<asp:TextBox ID="m_tbItemQuantity"
runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td style="width:25%">
<asp:Button ID="m_btSubmit" runat="server" Text="Submit" />
</td>
<td style="width:75%">
</td>
</tr>
<tr>
<td style="width:25%">
<asp:Label ID="m_lbOutput" runat="server"
Text=""></asp:Label>
</td>
<td style="width:75%">
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Figure 10 Code Behind for the OrderRequest_EntryPage.aspx page.
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class OrderRequest_EntryPage : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void m_btSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection objConnection = new SqlConnection())
{
objConnection.ConnectionString = "Data Source=localhost;Initial
Catalog=ServiceBrokerDB_Test;Integrated Security=True";
using (SqlCommand objCmd = new SqlCommand())
{
objCmd.Connection = objConnection;
objCmd.CommandType = CommandType.StoredProcedure;
objCmd.CommandText = "spEnterOrderRequest";
SqlParameter objOutputParam =
objCmd.Parameters.Add("@nOrderID",SqlDbType.Int);
objOutputParam.Direction = ParameterDirection.Output;
objCmd.Parameters.Add("@nCustomer",SqlDbType.Int).Value =
Convert.ToInt32(this.m_tbCustomerID.Text) ;
objCmd.Parameters.Add("@nItemNumber", SqlDbType.Int).Value =
Convert.ToInt32(this.m_tbItemNumber.Text);
objCmd.Parameters.Add("@nItemQuantity", SqlDbType.Int).Value
= Convert.ToInt32(this.m_tbItemQuantity.Text);
try
{
objConnection.Open();
objCmd.ExecuteNonQuery();
this.m_lbOutput.Text = "The Order Identifier is " +
objOutputParam.Value;
}
catch (SqlException ex)
{
this.m_lbOutput.Text = "Exception: " + ex.Message;
}
finally
{
objConnection.Close();
}
}
}
}
}
Next is the implementation of OrderStatus_Page.aspx, which contains one Label web control ( Label1 ) and one TextBox web control ( m_tbOrderID ) to get the order identifier, and a Button web control ( m_btSubmit ) that enables requesting the status of the order. The status is displayed using one last output Label web control ( m_lbOutput ). The code for the page is shown in Figure 11, and the code behind in Figure 12.
Figure 11 Layout of OrderStatus_Page.aspx.
<%@ Page Language="C#" AutoEventWireup="true"
CodeFile="OrderStatus_Page.aspx.cs" Inherits="OrderStatus_Page" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Order Status page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table border="0" width="100%" cellpadding="0" cellspacing="0">
<tr>
<td style="width:25%">
<asp:Label ID="Label1" runat="server" Text="Enter
OrderID"></asp:Label>
</td>
<td style="width:75%">
<asp:TextBox ID="m_tbOrderID" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td style="width:25%">
<asp:Button ID="m_btSubmit" runat="server" Text="Submit"
OnClick="m_btSubmit_Click" />
</td>
<td style="width:75%">
</td>
</tr>
<tr>
<td style="width:25%">
<asp:Label ID="m_lbOutput" runat="server"
Text=""></asp:Label>
</td>
<td style="width:75%">
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Figure 12 Code Behind for the OrderStatus_Page.aspx page
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class OrderStatus_Page : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void m_btSubmit_Click(object sender, EventArgs e)
{
using (SqlConnection objConnection = new SqlConnection())
{
objConnection.ConnectionString = "Data Source=localhost;
Initial Catalog=ServiceBrokerDB_Test;Integrated Security=True";
using (SqlCommand objCmd = new SqlCommand())
{
objCmd.Connection = objConnection;
objCmd.CommandType = CommandType.Text;
objCmd.CommandText = "select vcOrderStatusDescription " +
"from tbOrder " +
"where nOrderID=@nOrderID";
objCmd.Parameters.Add
("@nOrderID", SqlDbType.Int).Value = Convert.ToInt32(this.m_tbOrderID.Text);
try
{
objConnection.Open();
SqlDataReader objReader = objCmd.ExecuteReader();
objReader.Read();
string strStatusDescription = objReader.GetString(0);
this.m_lbOutput.Text = "The Order Status is " + strStatusDescription;
}
catch (SqlException ex)
{
this.m_lbOutput.Text = "Exception: " + ex.Message;
}
finally
{
objConnection.Close();
}
}
}
}
}
With the sample I’ve described, you now have a template for creating reliable, distributed messaging solutions using SQL Server 2005 Service Broker technologies,. You should be able to and adapt this article’s example to fit your own business scenario.
Juan Carlos Olamendy Turruellas is a senior integration solutions architect and consultant. His primary focus is object-oriented analysis and design, database design, enterprise application integration, Unified Modeling Language, design patterns, enterprise application architecture, and software development processes using agile methodologies. He was awarded Most Valuable Professional status by Microsoft in 2007, 2008, and 2009, as well as Oracle ACE status in 2008. You can contact Juan at johnx_olam@fastmail.fm.