SSB (SQLService Service Broker) Introduction _mssql

Step 1:perpar Database & Data

Copy Code code 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 an entire SSB infrastructure.

First create the type of information

Copy Code code as follows:

Create message Type [//mark/v10/types/inventory]
Create message Type [//mark/v10/types/inventoryresponse]

Create a contract

Copy Code code as follows:

Create contracts [//mark/v10/contracts/inventorycontract]
(
[//mark/v10/types/inventory] Send by initiator,
[//mark/v10/types/inventoryresponse] Send by Target
);

Creating Queues and services

Copy Code code 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 messages to message queues

Copy Code code 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 successfully, you can query Inventory_queue

Copy Code code as follows:

Select Conversation_handle, CAST (message_body as XML) as conversation_body from [Inventory queue]

Only two columns are queried here.

Where Conversation_body is the message we send out.

SETP 4: Get information from inventory queue and update data

Copy Code code 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 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;

The operation is successful and then go to query Inventory queue's table hesitation inside the data has been receive out of the empty.

The data was updated at the same time

Step 5: Next to do an automatic monitoring function, as long as there is information sent to the queue, related queues, you can automatically perform functions.

First set up a stored procedure, (take the code in the receive to come over it)

Copy Code code as follows:

Use Inventory
Go
--create procedure to receive 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

Queue Monitoring

Copy Code code as follows:

Alter queue [inventory queue]
With Activation (
Status = ON,
Procedure_name= Inventoryproc,
max_queue_readers=2,
EXECUTE AS Self
);

Go

Once you're done, just send the queue information.

PS: We have defined two queues and a Inventory client queue.

This queue holds a success or error prompt. It can also be extracted using the Receive method.

Copy Code code as follows:

Select Message_type_name from [Inventory client queue]

The above message type service information , as shown in the picture, is here.

The above is SSB example, will later write a practical application (the above SQL code is pure manual input, there may be errors (although the checksum is two times), if there is a mistake, I'm sorry everyone. ）