Service Broker基础应用(上）

-- Enabling Databases for Service Broker Activity

USE master
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'BookStore')
CREATE DATABASE BookStore
GO
IF NOT EXISTS (SELECT name FROM sys.databases WHERE name = 'BookDistribution')
CREATE DATABASE BookDistribution
GO
ALTER DATABASE BookStore SET ENABLE_BROKER
GO
ALTER DATABASE BookStore SET TRUSTWORTHY ON
GO
ALTER DATABASE BookDistribution SET ENABLE_BROKER
GO
ALTER DATABASE BookDistribution SET TRUSTWORTHY ON
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.

-- Creating the DatabaseMaster Key for Encryption

USE BookStore
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'I5Q7w1d3'
GO

USE BookDistribution
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'D1J3q5z8X6y4'
GO
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

-- Managing Message Types

Use BookStore
GO
-- 发送图书订单的消息类型
CREATE MESSAGE TYPE [//SackConsulting/SendBookOrder]
VALIDATION = WELL_FORMED_XML
GO

--目标数据库发送的消息类型
CREATE MESSAGE TYPE [//SackConsulting/BookOrderReceived]
VALIDATION = WELL_FORMED_XML
GO

--执行同样的定义
Use BookDistribution
GO
-- 发送图书订单的消息类型
CREATE MESSAGE TYPE [//SackConsulting/SendBookOrder]
VALIDATION = WELL_FORMED_XML
GO

--目标数据库发送的消息类型
CREATE MESSAGE TYPE [//SackConsulting/BookOrderReceived]
VALIDATION = WELL_FORMED_XML
GO 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.

-- Creating Contracts

Use BookStore
GO
CREATE CONTRACT
[//SackConsulting/BookOrderContract]
( [//SackConsulting/SendBookOrder]
SENT BY INITIATOR,
[//SackConsulting/BookOrderReceived]
SENT BY TARGET
)
GO

USE BookDistribution
GO
CREATE CONTRACT
[//SackConsulting/BookOrderContract]
( [//SackConsulting/SendBookOrder]
SENT BY INITIATOR,
[//SackConsulting/BookOrderReceived]
SENT BY TARGET
)
GO
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.

-- Creating Queues

Use BookStore
GO
--保存BookDistribution过来的消息
CREATE QUEUE BookStoreQueue
WITH STATUS=ON
GO

USE BookDistribution
GO
--保存BookStore过来的消息
CREATE QUEUE BookDistributionQueue
WITH STATUS=ON
GO
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

-- Creating Services

Use BookStore
GO
CREATE SERVICE [//SackConsulting/BookOrderService]
ON QUEUE dbo.BookStoreQueue--指定的队列绑定到契约
([//SackConsulting/BookOrderContract])
GO

USE BookDistribution
GO
CREATE SERVICE [//SackConsulting/BookDistributionService]
ON QUEUE dbo.BookDistributionQueue--指定的队列绑定到契约
([//SackConsulting/BookOrderContract])
GO
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

-- Initiating a Dialog

Use BookStore
GO

--保存会话句柄和订单信息
DECLARE @Conv_Handler uniqueidentifier
DECLARE @OrderMsg xml;

BEGIN DIALOG CONVERSATION @Conv_Handler--创建会话
FROM SERVICE [//SackConsulting/BookOrderService]
TO SERVICE '//SackConsulting/BookDistributionService'
ON CONTRACT [//SackConsulting/BookOrderContract];
SET @OrderMsg =
'<order id="3439" customer="22" orderdate="2/15/2011">
<LineItem ItemNumber="1" ISBN="1-59059-592-0" Quantity="1" />
</order>';
SEND ON CONVERSATION @Conv_Handler--发送到BookDistribution数据库的队列中
MESSAGE TYPE [//SackConsulting/SendBookOrder]
(@OrderMsg);
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.

-- Querying the Queue for IncomingMessages

USE BookDistribution
GO
SELECT message_type_name, CAST(message_body as xml) message,
queuing_order, conversation_handle, conversation_group_id
FROM dbo.BookDistributionQueue 
1.
2.
3.
4.
5.
6.
7.

-- Receiving and Responding to aMessage

USE BookDistribution
GO
--创建一个表存放接收到的订单信息
CREATE TABLE dbo.BookOrderReceived
(BookOrderReceivedID int IDENTITY (1,1) NOT NULL,
conversation_handle uniqueidentifier NOT NULL,
conversation_group_id uniqueidentifier NOT NULL,
message_body xml NOT NULL)
GO

-- 声明变量
DECLARE @Conv_Handler uniqueidentifier
DECLARE @Conv_Group uniqueidentifier
DECLARE @OrderMsg xml
DECLARE @TextResponseMsg varchar(8000)
DECLARE @ResponseMsg xml
DECLARE @OrderID int;

--从队列中获取消息，将接收值赋于局部变量
RECEIVE TOP(1) @OrderMsg = message_body,--TOP指定最多一条消息
@Conv_Handler = conversation_handle,
@Conv_Group = conversation_group_id
FROM dbo.BookDistributionQueue;

-- 将变量值插入表中
INSERT dbo.BookOrderReceived
(conversation_handle, conversation_group_id, message_body)
VALUES
(@Conv_Handler,@Conv_Group, @OrderMsg )

-- 使用XQuery进行抽取以响应消息订单
SELECT @OrderID = @OrderMsg.value('(/order/@id)[1]', 'int' )
SELECT @TextResponseMsg =
'<orderreceived id= "' +
CAST(@OrderID as varchar(10)) +
'"/>';
SELECT @ResponseMsg = CAST(@TextResponseMsg as xml);

-- 使用既有的会话句柄，发送响应消息到发起方
SEND ON CONVERSATION @Conv_Handler
MESSAGE TYPE [//SackConsulting/BookOrderReceived] 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.

-- Ending a Conversation

USE BookStore
GO
-- 创建订单确认表
CREATE TABLE dbo.BookOrderConfirmation
(BookOrderConfirmationID int IDENTITY (1,1) NOT NULL,
conversation_handle uniqueidentifier NOT NULL,
DateReceived datetime NOT NULL DEFAULT GETDATE(),
message_body xml NOT NULL)

DECLARE @Conv_Handler uniqueidentifier
DECLARE @Conv_Group uniqueidentifier
DECLARE @OrderMsg xml
DECLARE @TextResponseMsg varchar(8000);

RECEIVE TOP(1) @Conv_Handler = conversation_handle,
@OrderMsg = message_body
FROM dbo.BookStoreQueue

INSERT dbo.BookOrderConfirmation
(conversation_handle, message_body)
VALUES (@Conv_Handler,@OrderMsg );

END CONVERSATION @Conv_Handler;
GO

USE BookDistribution
GO
DECLARE @Conv_Handler uniqueidentifier
DECLARE @Conv_Group uniqueidentifier
DECLARE @OrderMsg xml
DECLARE @message_type_name nvarchar(256);

RECEIVE TOP(1) @Conv_Handler = conversation_handle,
@OrderMsg = message_body,
@message_type_name = message_type_name
FROM dbo.BookDistributionQueue

-- 双方必须都结束会话
IF
@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
BEGIN
END CONVERSATION @Conv_Handler;
END 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.

SELECT state_desc, conversation_handle
FROM sys.conversation_endpoints 
1.
2.