–Service broker示例
–上面将创设三个向数据库添加员工的不难示例。
–必要是可以让初级人力能源员工向商店的人力资源系统添加记录。因为它的总裁须求在记录拿到1个永久的ID之前查看记录,大家决定创办的应用程序把吸收的笔录在被审批以前都位居壹个“贮藏”表中。
—在作者的测试系统上,我创设了二个只含有Employee表的serviceBrokerExample数据库。那一个表只含有3个名为EmployeeInfo的列,它的数据类型为XML,那是本身应用程序接受的格式。利用以下代码可以在
–测试系统上开创这一个对像:
–create db
–create database servicebrokerexample
go
–create table
use servicebrokerexample
go
create table employee
(
employeeinfo xml null
) on [primary]
go
–创建了这一个后,就足以创立系统拔取的新闻类型了。那里自身指定项目以确保数据是适用的XML格式,但在生产环境中一般须要引用多个完全的XML架构文件:
create message type
[serviceBroker/example/employee/addEmployee]
validation=well_formed_xml
–未来得以成立系统应用的预订了,会话的每一方都有。可以观察,其中使用了正要制造的AddEmployee信息类型:
create contract
[serviceBroker/example/employee/addEmployeeContract]
(
[serviceBroker/example/employee/addEmployee]
Sent by initiator
)
go
–下边就是自身的从队列读取数据并插入到数据库的储存进程。那里运用的是XML转换函数把多少放置到数据库。有两个存储进度:其中一个布署,另三个反省并清空队列。
create proc addEmployee
@mb xml
as
insert into employee(employeeinfo)
values(@mb)
go

–读取队列的蕴藏进度如下:
create proc ProcessEmployee
as
begin
    begin tran
        declare @ch uniqueidentifier
        declare @mb varbinary(max)
        waitfor
        (
        receive top (1) @ch=conversation_handle,@mb=message_body
            from employeequeue
        ),
        timeout 1500

        execute addEmployee @mb
        end conversation @ch
    commit tran

end
–员工就位后要求创制存储数据的队列。创立队列时,需求关联一个用来拍卖它的服务程序(在本例中就是储存进程)
create queue [employeequeue]
with status=on,
activation
(
procedure_name=ProcessEmployee,
max_queue_readers=5,
execute as self
)
go
–未来大多了,接下去必要创建应答会话请求的服务,并把它与日前的预定关联起来:
create service addemployeeservice
on queue [employeequeue]
(
[serviceBroker/example/employee/addEmployeeContract]
)
go
–今后系统准备好了,可以动用使用sql server management studio
检查这一个对像。
–当服务器准备好service broker会话后,就足以开头3个完完全全的言传身教事件了。
–代码如下:发送
declare @ch uniqueidentifier
declare @employeename xml
set @employeename='<name>mary</name>’

begin dialog conversation @ch
from service addemployeeservice
to service
‘[serviceBroker/example/employee/addEmployeeContract]’
on contract
[serviceBroker/example/employee/addEmployeeContract];

send on conversation @ch
message type
[serviceBroker/example/employee/addEmployee] (@employeename)

go

—接收
GO

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;

RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_XML,name
FROM [employeequeue];

SELECT @RecvReqMsg AS ReceivedRequestMsg;

IF @RecvReqMsgName =
   N’serviceBroker/example/employee/addEmployee’
BEGIN
     DECLARE @ReplyMsg NVARCHAR(100);
     SELECT @ReplyMsg =
     N'<ReplyMsg>Message for Initiator
service.</ReplyMsg>’;
 
     SEND ON CONVERSATION @RecvReqDlgHandle
          MESSAGE TYPE
          [serviceBroker/example/employee/addEmployee]
          (@ReplyMsg);

     END CONVERSATION @RecvReqDlgHandle;
END

SELECT @ReplyMsg AS SentReplyMsg;

–要检查的话,可以动用动态管理视图

select * from sys.dm_broker_activated_tasks
select * from sys.dm_broker_connections
select * from sys.dm_broker_forwarded_messages
select * from sys.dm_broker_queue_monitors
go
select * from employee
go
SELECT * FROM [dbo].[employeequeue]

GO

相关文章

网站地图xml地图