SSB(SQLservice Service Broker) 入门实例详解
发布时间：2021-12-08 17:05:39 所属栏目：教程 来源：互联网
导读：step 1：perpar database data 复制代码 代码如下: use master go Create database Inventory go use Inventory go Create table inventory ( TitleId nvarchar(6) not null, Quantity int not null, primary key (TitleId,Quantity) ); go insert inventory
|
step 1：perpar database & data 复制代码 代码如下: use master go Create database Inventory go use Inventory go Create table inventory ( TitleId nvarchar(6) not null, Quantity int not null, primary key (TitleId,Quantity) ); go insert inventory values ('pc1001',100); insert inventory values ('pc1002',200); insert inventory values ('pc1003',300); insert inventory values ('pc1004',400); step 2: 创建整个SSB的基础框架. 首先创建信息类型 复制代码 代码如下: Create Message Type [//mark/v10/types/inventory] Create Message Type [//mark/v10/types/inventoryresponse] 创建合约 复制代码 代码如下: Create contracts [//mark/v10/contracts/inventorycontract] ( [//mark/v10/types/inventory] send by initiator, [//mark/v10/types/inventoryresponse] send by target ); 创建队列与服务 复制代码 代码如下: create queue [inventory client queue]; create service [//mark/v10/services/inventory client] on queue [inventory client queue] go --create inventory service create queue [inventory queue]; create service [//mark/v10/services/inventory] on queue [inventory queue] ( [//mark/v10/contracts/inventorycontract] ); step 3：发送消息到消息队列中 复制代码 代码如下: use Inventory go --send message begin transaction declare @dialog_id uniqueidentifier begin dialog conversation @dialog_id from service [//mark/v10/services/inventory client] to service '//mark/v10/services/inventory' on contract [//mark/v10/contracts/inventorycontract] with encryption = off; send on conversation @dialog_id message type [//mark/v10/types/inventory] ( '<InventoryUpdate> <TitleId>pc1001</TitleId> <Quantity>102</Quantity> </InventoryUpdate>' ); commit transaction; 发送成功后，可以查询 inventory_queue 复制代码 代码如下: select conversation_handle, cast(message_body as xml) as conversation_body from [Inventory queue] 这里只是查询了两列。 其中conversation_body 就是 我们发出的信息了。 setp 4: 从 inventory queue 中获取信息并更新数据 复制代码 代码如下: use inventory go begin transaction declare @dialog_id uniqueidentifier declare @message_body xml declare @amount int; declare @title nvarchar(128); receive @dialog_id = conversation_handle, @message_body = message_body from [dbo].[Inventory queue] print 'handle is' + cast(@dialog_id as nvarchar(90)) print 'message body is' + cast(@message_body as nvarchar(max) ) if(@dialog_id is not null) begin set @amount = @message_body.value('(/InventoryUpdate/Quantity)[1]','int'); set @title = @message_body.value('(/InventoryUpdate/TitleId)[1]','nvarchar(100)'); print 'a:'+cast(@amount as nvarchar(100)) print 'b:'+@title; Update inventory set Quantity = @amount where TitleId = @title; end end conversation @dialog_id; commit transaction; 操作成功后再去查询inventory queue的表 犹豫里面的数据已经被receive 出来 就为空了。 数据同时被更新了 step 5: 接下来做个自动监听功能，只要有信息发送到队列，相关队列，就可以自动执行功能. 首先建立个存储过程，（把receive 中的那段代码拿过来就可以了） 复制代码 代码如下: use Inventory go --create procedure to receive message and update table create proc InventoryProc as begin transaction declare @dialog_id uniqueidentifier declare @message_body xml declare @amount int; declare @title nvarchar(128); waitfor( receive @dialog_id = conversation_handle, @message_body = message_body from [dbo].[Inventory queue]),timeout 5000; print 'handle is' + cast(@dialog_id as nvarchar(90)) print 'message body is' + cast(@message_body as nvarchar(max) ) if(@dialog_id is not null) begin set @amount = @message_body.value('(/InventoryUpdate/Quantity)[1]','int'); set @title = @message_body.value('(/InventoryUpdate/TitleId)[1]','nvarchar(100)'); print 'a:'+cast(@amount as nvarchar(100)) print 'b:'+@title; Update inventory set Quantity = @amount where TitleId = @title; end end conversation @dialog_id; commit transaction; go 队列监听  （编辑：济南站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
