Working with the WCF-SQL Receive Adapter: Return XML data (Adapter Pack v2.0)

Here is a quick 5 minute read on how to get you started on the WCF-Custom (SQL binding) adapter. This article explains the receive port and associated artifacts

Installation:

Install the required software components in the following order

1. WCF LOB Adapter SDK SP2 x86
2. en_biztalk_adapter_pack_2.0_x86_x64_cd_3397651
3. HotFix – KB942612
4. HotFix – KB 2300507

BizTalk Port Configuration:

1. Select the type WCF-Custom
2. Provide a URI for transport properties
   
3. You would have to create two stored procedures.
   1. CheckData (pollingDataAvailableStatement)
   2. GetData (pollingStatement)
4. Set the root node name (xmlStoredProcedureRootNodeName)
5. Optional: Change the inboundOperationType as XmlPolling
   

6. Select the Messages tab and check the last two properties

That’s pretty much it from your adapter point of view

SQL Server:

1. CheckData Procedure: Checks the availability of data by returning the count of messages available

--    Get messages criteria

SELECT COUNT(1)
FROM vw_ControlList v WITH (READPAST)
WHERE v.ApplicationId = @ApplicationId
AND (v.ProcessingId IS NOT NULL)

2. GetData Procedure: Poll data available

-- Return message

SELECT @result =   COALESCE(@result + '', '')
+ '<Messages><ProcessingId>'
+ CONVERT(VARCHAR(50),ISNULL(t.ProcessingId,0))
+ '</ProcessingId>'
+ '<Description>' + t.Description + '</Description>'
+ Convert(varchar(max),ISNULL(t1.MessageXML,'')) + '</Messages>'

FROM
vw_ControlList t INNER JOIN dbo.ProcessingList t1 ON t.ControlListId = t1.ProcessingListId

SELECT CONVERT(XML,@result)

BizTalk Schemas: NONE

My receive pipeline gets the incoming data, and maps it to a common schema in my project

That’s all to get you started 8)

IMPORTANT: Also, remember to read the Adpater polling information

Location: Click Here

Happing polling 8)