To poll for sales and product feedback data from SQL Server, we will use a WCF-SQL adapter in BizTalk 2016. The adapter supports receiving polling-based messages, where the adapter executes a specified SQL statement, retrieves or updates the data, and provides the result to the BizTalk receive location.
The WCF-SQL adapter supports three types of polling:
- Weakly-typed polling (also called polling)
- Strongly-typed polling (also called typed polling)
- XML polling, using statements or procedures that include a FOR XML clause
For the purposes of this sample, we will be using the sales table of Sunny Electrical's database. The table script is shown here:
CREATE TABLE [dbo].[SalesOrderTable](
[SalesId] [int] IDENTITY(1,1) NOT NULL,
[CustomerId] [int] NOT NULL,
[Transationdate] [datetime] NULL,
[ProductName] [varchar](50) NULL,
[IsProcessed] [int] NULL,
[ProductScore] [varchar](50) NULL,
[CustomerProdcutFeedback] [varchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
To start, create a Visual Studio BizTalk project and use Add Generated Items | Consume Adapter Service to generate an XML schema for the typed polling, which will return sales data based on the date processed state:
After selecting sqlBinding as the adapter, populate URI Properties with the server name, database name, and InboundId, as shown here:
In the Binding Properties tab, set the adapter to use typed polling and populate PollingDataAvailableStatement with a SQL statement that counts how many records match the polling query. In the PollingStatement value, enter a query that will return records from the sales table where the IsProcessed flag is 0:
select count(*) FROM [SalesOrderTable] where IsProcessed= 0
Click Connect to make sure that the URI configuration is correct. Select Service (Inbound Operation) in the dropdown for the contract type. Choose TypedPolling and click Add while selecting choose Generate Unique Schema type:
