Polling data from SQL using WCF-SQL Adapter in BizTalk 2010 - TypedPolling(From Multiple table)
Starting from where I had left:
http://tech-findings.blogspot.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:
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.BillToCoun07y,H.ShipToID,H.ShipToName,H.ShipToCity,H.ShipToState,H.ShipToPostal,H.ShipToCoun07y,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 con07act type under Select Con07act 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 Orches07ation added to your project.
8. Well you can go ahead and use that orches07ation 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!!!!!!!!!!!!
Related Post
Comments
Post a Comment