SSB(SQLservice Service Broker) 入門實(shí)例介紹_Mssql數(shù)據(jù)庫教程

推薦：高效的SQLSERVER分頁查詢(推薦)
Sqlserver數(shù)據(jù)庫分頁查詢一直是Sqlserver的短板，閑來無事，想出幾種方法，假設(shè)有表ARTICLE,字段ID、YEAR...(其他省略)，數(shù)據(jù)53210條(客戶真實(shí)數(shù)據(jù)，量不大)，分頁查詢每頁30條，查詢第1500頁（即第45001-45030條數(shù)據(jù)），字段ID聚集索引，YEAR無索引，Sqlserver版本：20

step 1：perpar database & data

復(fù)制代碼代碼如下:www.wf0088.com

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: 創(chuàng)建整個(gè)SSB的基礎(chǔ)框架.

首先創(chuàng)建信息類型

復(fù)制代碼代碼如下:www.wf0088.com

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

創(chuàng)建合約

復(fù)制代碼代碼如下:www.wf0088.com

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

創(chuàng)建隊(duì)列與服務(wù)

復(fù)制代碼代碼如下:www.wf0088.com

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：發(fā)送消息到消息隊(duì)列中

復(fù)制代碼代碼如下:www.wf0088.com

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;

發(fā)送成功后，可以查詢 inventory_queue

復(fù)制代碼代碼如下:www.wf0088.com

select conversation_handle, cast(message_body as xml) as conversation_body from [Inventory queue]

這里只是查詢了兩列。

其中conversation_body 就是我們發(fā)出的信息了。

setp 4: 從 inventory queue 中獲取信息并更新數(shù)據(jù)

復(fù)制代碼代碼如下:www.wf0088.com

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的表猶豫里面的數(shù)據(jù)已經(jīng)被receive 出來就為空了。

數(shù)據(jù)同時(shí)被更新了

step 5: 接下來做個(gè)自動(dòng)監(jiān)聽功能，只要有信息發(fā)送到隊(duì)列，相關(guān)隊(duì)列，就可以自動(dòng)執(zhí)行功能.

首先建立個(gè)存儲(chǔ)過程，（把receive 中的那段代碼拿過來就可以了）

復(fù)制代碼代碼如下:www.wf0088.com

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

隊(duì)列監(jiān)聽

復(fù)制代碼代碼如下:www.wf0088.com

alter queue [inventory queue]
        with activation(
            status = on,
            procedure_name=    InventoryProc,
            max_queue_readers=2,
            execute as self
        );

        go

完成后，只要發(fā)送隊(duì)列信息后，就可以了

PS：我們定義了兩個(gè)隊(duì)列還有個(gè) inventory client queue

這個(gè)隊(duì)列保存的是成功或者error 提示。用receive 方法也可以提取出來。

復(fù)制代碼代碼如下:www.wf0088.com

select message_type_name from [Inventory client queue]

以上的 message type service 等信息如圖所示，都在這里.

以上是SSB的入門實(shí)例，以后會(huì)再寫一篇實(shí)際應(yīng)用上的（以上的sql code 都是純手工輸入，可能還存在錯(cuò)誤（雖然校驗(yàn)了兩遍）,如果發(fā)生錯(cuò)誤，對(duì)不起大家了。）

分享：SQL Server2012在開發(fā)中的一些新特性
SQL Server 2012已經(jīng)發(fā)布一段時(shí)間了，最近在新的機(jī)器上安裝了最新的SQL Server 2012 SP1，體檢下感覺良好。官方給出了一大堆SQL2012相對(duì)于SQL2008R2的新特性，但是大多數(shù)對(duì)于普通開發(fā)人員來說都是浮云，根本用不到，下面就說說一些對(duì)于開發(fā)人員來說比較有用的新特性。

來源：模板無憂/所屬分類：Mssql數(shù)據(jù)庫教程/更新時(shí)間：2013-04-23

SSB(SQLservice Service Broker) 入門實(shí)例介紹_Mssql數(shù)據(jù)庫教程

Mssql數(shù)據(jù)庫教程Rss訂閱編程教程搜索

Mssql數(shù)據(jù)庫教程推薦

猜你也喜歡看這些