Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Typed Polling with WCF Adapters

Cesar Cordero

Typed Polling with WCF Adapters

It is very common to have a requirement in middleware design where we have to query SQL tables and retrieve some data when certein conditions are met.

The WCF-based adapters available with the BizTalk Adapter Pack enable the adapter clients to receive data-change messages from the SQL Server database(s). The adapter(s) support receiving “polling-based” messages wherein the adapter executes a specified SQL statement (SELECT statement or stored procedure), retrieves or updates the data, and provides the result to the adapter client at regular intervals of time. The adapters supports three types of polling:

  • Weakly-typed Polling (also called Polling),
  • Strongly-typed Polling (also called TypedPolling)
  • Xml-Polling polling using statements or procedures that include a FOR XML clause

For more information about the different types of polling, see Polling SQL Server by Using the SQL Adapter with BizTalk Server (http://go.microsoft.com/fwlink/?LinkId=221195).

Assumptions and prerequisites

This document is not intended as an MS SQL Primer and assumes that the reader has basic Database administrative skills to create and manage databases, tables and stored procedures. It is also assumed that the reader has basic knowledge of BizTalk, BizTalk administration and basic deployment methodologies around the same.

Create a new Database called WCFSQLTest

For the purposes of our discussion we will be creating a test database. Using SQL Server Management studio right click on the Database object and select new:

CreateWCFSQLTest

 

Create a new table in the WCFSQLTest DB called OrderItem:

Using SQL Server Management Studio and from within the newly created Database object:

CreateNewTableOrderItem

 

Launch Visual Studio

Launch visual studio and create a new empty BizTalk project called BTSDemo4

Add Generated Items:

From a newly  created BTSDemo4 solution/project right click on the project and choose “Add Generated Items” and choose

Consume Adapter Service from the Add Generated Items Wizard.

ConsumeAdapter

After clicking ADD then choose SQLBinding from the “select a binding” drop down and then press the configure button:

ConsumeAdapterWizard

Configure URI:

Choose the URI Properties and fill them out according to your DBName (initial catalog), SQL Instance name and server name. on the Inbound ID use a unique identifier related to the interface:

ConfigureURI

 

 

Binding Properties:

PollingDataAvailableStatement

After filling out the the URI PROPERTIES SECTION select the binding properties TAB and cut and paste the following select statement in the “PollingDataAvailableStatement” to determine if any records are available in our table:

select count(*) FROM [WCFSQLTest].[dbo].[OrderItem] where status = 0 where status =0 is NOT INTERFACED OR Processed by BizTalk.

Note: PLEASE BE SURE the Inbound OperationType is set to TypedPolling

BindingProperties

 

Polling Statement

Place the following SQL in the “Polling Statement” section:

select * from [WCFSQLTest].[dbo].[OrderItem] where status = 0;update [WCFSQLTest].[dbo].[OrderItem] set status =1 where status = 0

PollingStatement

Then click OK

 

Connecting to the Database

Click connect to ensure the URI configuration is correct then be sure and select SERVICE INBOUND in the dropdown for the contract type on left side of screen. Then Choose TypedPolling and click add. Also choose Generate unique Schema type. Then click OK

ConnectingToDatabase

 

This should add two files to your solution:

ConsumeAdapterWizard AddTwoFiles

Please build the solution to be sure nothing has “broken”.

 

Deploy the solution from visual studio to your local BTS instance.

Import the WcfReceivePort_SqlAdapterBinding_Custom.bindinginfo.xml binding file into the currently deployed WCF Test solution on your local BizTalk Instance

 

Restarting Host Instances:

Under platform settings from the BizTalk admin console restart the local host instances:

RestartingHostInstance

Turn on BizTalk Application

Turn on the receive location and then review the event log for errors.

We should find one subscription not found error that illustrated the pub/sub nature of BizTalk interfaces.

We will need to place a filter on a newly created send port to “subscribe” to the message type received in by our new receive location that polls the SQL table based on the schema generated by the Wizard in previous step and now deployed into our application via Visual Studio.

BizTalk Schemas Target Namespaces:

If we review the “schemas” section under our application tree in the BizTalk Admin console we will fine our schema there with a root name of “TypedPolling”:

BizTalkSchemasTargetNameSpace

Subscription Filters:

The target name space and the root name are the two key pieces to creating a filter in a send port that will act as the subscribing artifact in the BizTalk interface:

That is to say:

TargetNameSpace#RootName

In the case of this use case:

http://schemas.microsoft.com/Sql/2008/05/TypedPolling/ShipmentNotification#TypedPolling

Creating the Send Port:

From the BizTalk Administrative tool and in the newly deployed application let’s create a new one way send port and with a filter on BizTalk Message type as follows:

CreatingTheSendPort

 

Filters

Setting the Send Port Filter to create the subscriber

Here is the filter required:

Filters

After creating a send port that grabs all records from this new receive location, Start the application. When we put a new row into my database we should get one file emitted to disk.

If we create three records in the database, then we still get a single message/file out of BizTalk.

SingleFileOut

 

Debatching:

The final step in this exercise is optional and only need to be completed if we want to split this up so that these three records show up as three distinct messages/files

With the WCF adapter, you’ll see that we actually have three root nodes as part of the generated schema.  We can confirm this by looking at the Schemas section in the BizTalk Administration Console:

Debatching

So, this means that we should be able to change the existing schema to support debatching, and it all just works.  Let’s try that now:  Go back to the auto-generated schema, click the topmost Schema node, and changed its Envelope property to Yes.

BodyXPath EnvelopePropertyYES

Next, click the TypedPolling node (which acts as the primary root that comes out of the adapter) and set the Body XPath value to the node ABOVE the eventual leaf node:

BodyXPath

Finally, select the leaf node and set its Max Occurrence from Unbounded to 1. Rebuild the project and then redeployed it to the BizTalk Server.  When we add two records to the database, we end up with two records out on disk.

When the record gets debatched automatically by BizTalk in the XML receive pipeline, the resulting TypedPollingResultSet0 message(which matches a message type known by BizTalk) gets put in the MessageBox and routed around.

Good luck and Happy BizTalking!

 

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>