BizTalk Development Series (40) BizTalk Wcf-sql Adapter read SQL Service broker messages

SQL Service Broker is a new feature in SQL Server 2005. Service Broker provides queues and reliable messaging for SQL Server, which can be used to build applications based on asynchronous messages. Certainly from the topic everyone may also see out. This article is not primarily about SQL Service Broker (SSB), but about how to use BizTalk Wcf-sql adapter to access SSB data.

SQL Service Broker (SSB)

For the next example to make it easier for everyone, let's talk a little bit about SSB knowledge. More detailed background information can be learned in the Service Broker Developer Information Center.

The functionality of the SQL Service broker implementation is somewhat similar to what we already know about MSMQ, but it is a feature that implements Message Queuing in a database. Service Broker can help developers build scalable, secure database applications. This technology is part of the database engine, which provides a message-based communication platform that enables standalone application components to run as a whole. Service Broker contains an infrastructure for asynchronous programming that can be used in a single database or an application in a single instance, or in a distributed application.

Service Broker provides most of the infrastructure needed to build distributed applications, reducing application development time. Service Broker also makes it easy to scale applications to accommodate the traffic that the application receives.

Wcf-sql Adapter

Wcf-sql Adapter is a new SQL Adapter that was added in BizTalk Server 2009 to replace the previous use. New data types for SQL Server, event notifications, and so on are new in Wcf-sql. The following is a comparison of the features in Wcf-sql compared to the previous SQL adapter:

Function	SQL Adapter	WCF SQL Adapter
Execute create, read, update, and DELETE statements on tables and views; Execute stored procedures and generate T-SQL statements Execute create-read-update-delete statements on tables and views; Execute stored procedures and generic T-SQL statements	Partial support (the Send section supports only stored procedures and updategrams	Yes
Data polling with FOR XML Database polling via for XML	Yes	Yes
Data polling with tables (without adding for XML) Database polling via traditional tabular results	No	Yes
Implementing a database push with SQL query notifications Proactive database push via SQL Query Notification	No	Yes
To change the connection mechanism and behavior by extending the adapter configuration Expansive adapter configuration which impacts connection management and transaction behavior	No	Yes
Supports multiple transactions, which allow the operation of multiple tables and stored procedures to be placed in an atomic transaction. Composite transactions which allow aggregation of operations across tables or procedures to a single atomic Transaction	No	Yes
Rich data browsing and retrieval find and select database operations Rich metadata browsing and retrieval for finding and selecting database operations	No	Yes
Support for the latest data types such as XML and SQL Server 2008 platforms Support for the latest data types (e.g. XML) and SQL Server platform	No	Yes
Can be reused by WCF or HTTP clients other than BizTalk applications. Reusable outside of BizTalk applications by WCF or basic HTTP clients	No	Yes
Extending adapters and configurations through WCF-specific components and custom WCF behaviors Adapter extension and configuration through out of the box WCF components or custom WCF behaviors	No	Yes
Dynamically generating WSDL, reflecting the current system state in real time instead of a fixed contract requires explicit updates. Dynamic WSDL generation which always reflects current state of the system instead of a fixed contract which always requires Explicit updates	No	Yes

BizTalk Wcf-sql Adapter Read SQL Service broker messages

OK, it's all about Microsoft's own stuff. Of course, the compatibility of their own things a little better. So the title of this section actually I intentionally added a "BizTalk", because Microsoft since saw the value of adapter, began to split the adapter pack to sell. This means that you can also purchase adapter pack separately from your own developed application to access business systems such as SAP, Oracle, and SQL Server without buying BizTalk.

To get to the point, let's talk about the steps of the demonstration in the future, in order to simplify the BizTalk program we will not use orchestration.

1. Create an SSB app

In the following operations we will create a database, create a Service Broker message, queue, service, contract, and so on.

UseMaster
GO

CREATEDATABASECbcye_ssb

AlterDATABASECbcye_ssb
SETEnable_broker;
GO
UseCBCYE_SSB;
GO

CREATEMESSAGE TYPE
[Cbcye_ssbsample/requestmessage]
VALIDATION=Well_formed_xml;

CREATEContract[Cbcye_ssbsample/samplecontract]
([Cbcye_ssbsample/requestmessage]
SENTByInitiator
);
CREATEQUEUE initiatorqueue1db;

CREATESERVICE
[Cbcye_ssbsample/initiatorservice]
    on  QUEUE INITIATORQUEUE1DB; 

 QUEUE TARGETQUEUE1DB; 

create service
    < Span style= "color: #ff0000;" >[//cbcye_ssbsample/targetservice]
    on queue  TARGETQUEUE1DB
     ([// Cbcye_ssbsample/samplecontract]

2.BizTalk Receive ports and locations

Create a new port and location for the BizTalk application. Select Wcf-sql in the port type, and if you do not create a standalone adapter for Wcf-sql, you can select Wcf-customer, and the receive pipeline selects all passthrureceive. Then select Sqlbinding in the binding. In the port address, enter: MSSQL://LOCALHOST//CBCYE_SSB

Open the Binding configuration page, inbound polling for the configuration item:

As shown, set the value of Pooleddataavailablestatement to:SELECT COUNT (*) from targetqueue1db with (NOLOCK)//Set the response condition

Set the value of Poolingintervallnseconds to: 2//Set polling interval time

Set the Pollingstatement value to://Set Execution script

DECLARE@DlgHandleuniqueidentifier;
DECLARE@RecvMsgXML;
RECEIVETOP(1)
@DlgHandle=Conversation_handle,
@RecvMsg=CAST(Message_bodyAsXML)
Fromtargetqueue1db;
IF not  ( Span style= "color: #008000;" > @DlgHandle  isnull begin
end conversation  @DlgHandle ;&NBSP;
Span style= "color: #0000ff;" >select  @RecvMsg  as  Receivedmessage;
end

After Setup, you need to create a send port to subscribe to messages from the receive port. and sends the contents of the message to the folder as a file.

3. Send a message to the destination queue

Enter the following script in the SQL Management Studio query to send the message to the destination queue

DECLARE@RequestMsgXML;
SELECT@RequestMsg=N‘<cbcyemsg>hello, world</cbcyemsg>‘;
DECLARE@DlgHandleuniqueidentifier;
BEGINDIALOG@DlgHandle
FromSERVICE
[Cbcye_ssbsample/initiatorservice]
ToSERVICE
N‘Cbcye_ssbsample/targetservice‘
OnContract
[//cbcye_ssbsample/samplecontract]
with  Encryption =  off Send on conversation  @DlgHandle
message type 
[//cbcye_ssbsample/requestmessage]
( @RequestMsg

4. Modify the configuration to receive the actual messages sent

Open the destination folder, and you may find that the received message is not a message content that was previously sent, but rather an XML message for a complete dataset.

Dataset XML

Then we need to tell the WCF-SQL adapter, the path of the message content. Open the Wcf-sql configuration,

Select path in the Messaging key, enter in expression :/*[local-name () = ' Polling ']/*[local-name () = ' Polleddata ']/*[local-name () = ' DataSet ']/*[local-name () = ' DiffGram ']/*[local-name () = ' NewDataSet ']/*[local-name () = ' newtable ']/*[local-name () = ' Receivedmessage ']

Select string in the encoding.

Finally, the message is sent again to the destination queue. We can look in the output folder to see the message we Sent:

<cbcyemsg>Hello, World</cbcyemsg>

Summarize

SQL Service Broker is applied as the basis for asynchronous messaging implemented in SQL Server. BizTalk provides better support, and more and more valuable applications for both technologies may require business people and developers to be aware of the ongoing attempts.

SOURCE download

References :
Using the WCF SQL Adapter to read messages from SSB queues and submit them to BizTalk

BizTalk Development Series (40) BizTalk Wcf-sql Adapter read SQL Service broker messages