Polling data from SQL using WCF-SQL Adapter in BizTalk 2010 - TypedPolling(From Multiple table)

{tocify} $title={Table of Contents}

Introduction

Starting from where I had left:

https://tech-findings.com/2013/07/insert-records-in-sql-server-using-wcf.html

Now it's time to look at how we can poll data from multiple tables - SQL.

Scenario

We need to send an Acknowledgement against the purchase order we  got and to do so we need to  poll data from two tables viz. Header and Detail table.

Let's start:

Steps in creating solution

1. Right click the project and  select Add-->Add Generated Items

2. Then select "Consume Adapter Service"

3. Now the Consume Adapter Service wizard wants us to provide it the required information, so firstly select "sqlbinding" against the label Select a binding.

4. Click the Configure button, on security tab select the Client credential type <(I have used windows as it is my login type to SQL )

5. On URI Properties tab:

      i.Type the name of the server where your database is located ( I have used "." as the database resides on same machine)

     ii. Provide the name of the database you want to use.

    iii. In front of InboundId (It is mandatory for Typed polling) ,type the name whatever you want so as to  identify the purpose of polling. It acts  as a unique identifier which helps in avoiding conflict when you poll same tables for some other purpose. Here am using "Ack" as the data polled will be used for generating Acknowledgement and  I will be polling from same for generating invoice and for that I will use "Inv" as InboundId.

   iv. I have left InstanceName as blank because am using the default instance of SQL (If need to use some other instance then provide the name of that instance)

6.On Binding Properties tab:

    i. Select TypedPolling  from the dropdown list in front of InboundOperationType

   ii. To check whether the data that we are interested to poll is present, we query in the table and for that            we  use PolledDataAvailableStatement, I have use following query:

          select count(*) from HDR4201 where ACKFlag is NULL

  iii. After checking is done for available data next is to poll it and for that we use PollingData , I have used following query (also stored procedure can be used):

  Select                  H.OrderID,H.OrderDate,H.BilltoID,H.BillToName,H.BillToCity,H.BillToState,H.BillToPostal,H.BillToCountry,H.ShipToID,H.ShipToName,H.ShipToCity,H.ShipToState,H.ShipToPostal,H.ShipToCountry,H.Comments,H.TotalAmount,H.TotalTax,H.Currency,H.DOCO,D.RequestDate,D.Quantity,D.UnitOfMeasure,D.ItemID,D.UnitPrice,D.LineComment FROM HDR4201 as H,DTL4211 as D Where H.OrderID=D.OrderID AND H.DOCO=D.Doco AND H.OrderDate=D.OrderDate AND H.ACKFlag is NULL;update HDR4201 set ACKFlag = 'Y' where ACKFlag is NULL

iv.How frequent we want to check the table and poll data if available can be set using PollingIntervalsInSeconds (default is 30 seconds)

v. Click ok 

7. On the first page click the Connect Button and do the following:

i. Select  Service (Inbound operations) as contract type under Select Contract Type

ii. Now under the select a category pane click the forward slash (...) and you should see the next pane i.e. Available categories and operations get populated

iii. Select TypedPolling and click Add button just below the pane.

iv.Filename prefix is optional but it's good to enter a value as it helps in categorizing the schema which will be generated. (I have used PolledData) . Thus my schema will have a name: PolledData_Ack.xsd

v.Click ok and you should see an XSD and an Orchestration added to your project.

8. Well you can go ahead and use that orchestration to receive the polled data and do further processing (will be posting about it in next post).

9. For this post I will create a receive port which will get polled data and a send port which subscribe to the message from the receive port.

10. Sign the project, build and deploy the project.

11. Open BizTalk admin Console and under your application go to the Receive Ports and create new receive port with :

i. Receive Location having Transport Type set as WCF-SQLAdapter

ii. Click configure button next to it, and repeat the process from above step 5- 7 

ii. Receive pipeline as XMLReceive 

12. Now create a send port with a Transport type as File and provide a location and 

add a filter on the send port:  BTSReceivePortName=="ReceivePortName"

13. Now test the application. The

Will keep on posting as an when I find something to share!!!!!!!!!!!!

If you have questions or suggestions, feel free to do in comments section below !!!

Do share if you find this helpful .......

 

                          Knowledge Sharing is Caring !!!!!!

Related Post 

• BizTalk Server: Multiple XML files to Single FlatFile Using File Adapter
• BizTalk 2013: Inserting RawXML (Whole Incoming XML Message) in SQL database
• BizTalk 2013: Inserting RawXML (Whole Incoming XML Message) in SQL database - Part 2
• Is it possible to have Map Chaining on the Port Level
• Promoting custom context property using Pipeline Component
• Custom ZipReceivePipeline to Unzip Multi-Type Messages
• Grouping and debatching Inbound Messages from WCF SQL Adapter - BizTalk 2010
• Polling data from SQL using WCF-SQL Adapter in BizTalk 2010 - TypedPolling(From Multiple table)
• Grouping XML Messages using custom XSLT- BizTalk 2010
• Insert Records in SQL Server using WCF-SQL Adapter in BizTalk 2010 - Composite operation(Multiple Records insertion)
• Insert Records in SQL Server using WCF-SQL Adapter in BizTalk 2010- Composite Operation (Message Transformation Pattern)
• Debatching(Splitting) XML Message - BizTalk 2010
• Debatching(Splitting) XML Message in Orchestration using DefaultPipeline - BizTalk 2010