Introduction to SSB (SQLservice Service Broker) entry instances

Step 1: perpar database & data
Copy codeThe Code is as follows:
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: create the basic SSB framework.

First, create an information type.
Copy codeThe Code is as follows:
Create Message Type [// mark/v10/types/inventory]
Create Message Type [// mark/v10/types/inventoryresponse]

Create contract
Copy codeThe Code is as follows:
Create contracts [// mark/v10/contracts/inventorycontract]
(
[// Mark/v10/types/inventory] send by initiator,
[// Mark/v10/types/inventoryresponse] send by target
);


Create a queue and a service

Copy codeThe Code is as follows:
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: send a message to the Message Queue

Copy codeThe Code is as follows:
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;

After sending the message, you can query inventory_queue.
Copy codeThe Code is as follows:
Select conversation_handle, cast (message_body as xml) as conversation_body from [Inventory queue]

Only two columns are queried.
 
Conversation_body is the message we sent.

Setp 4: get information from inventory queue and update data

Copy codeThe Code is as follows:
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' + 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;

After the operation is successful, query the inventory queue table again. The data in the hesitant table has been receive and is empty.

The data is updated at the same time.

Step 5: perform an automatic listening function. If information is sent to the queue and related queues, the function can be automatically executed.

First, create a stored procedure. (You can get the code in receive)
Copy codeThe Code is as follows:
Use Inventory
Go
-- Create procedure to receive message and update table
Create proc InventoryProc

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' + 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

Queue listening
Copy codeThe Code is as follows:
Alter queue [inventory queue]
With activation (
Status = on,
Procedure_name = InventoryProc,
Max_queue_readers = 2,
Execute as self
);

Go

After sending the queue information, you can

PS: we have defined two queues and an inventory client queue.

The message "success" or "error" is saved in this queue. The receive method can also be used for extraction.
Copy codeThe Code is as follows:
Select message_type_name from [Inventory client queue]

 

The above message type service and other information are all here.

The above is an example of SSB, and I will write another article on practical application later (the above SQL code is purely manual input, and there may be errors (although it has been verified twice ), if an error occurs, sorry .)