How to: Receive data from Sql Procedure using Wcf-Sql adapter in BizTalk

In many cases we use a send port for sending a request message to a SQL procedure and receive a response message back. The problem with this solution, this must be done in an orchestration, or the transmission must be triggered in a different way.

In such cases, one can use a one-way receive port to receive data from SQL Procedure using WCF-Sql.

I will now show you how you can do this:

First we need to generate BizTalk Schema for receiving data.

In Visual Studio right-click on your project and select Add -> Generated Items

Then select Consume Adapter Service.

You will now see a window like this:

In the top left corner “Select a binding” choose “sqlBinding”. Then click Configure to configure your sql binding.

You will now see a window like this:

Go to Url Properties to configure server, database and InboundId. (InboundId is required for TypedPolling, and must be unike. You van choose what you want here)

Server = the server you connect to
InitialCatelog = DatabaseName
InboundId = unike Id for typedpolling.

Now, click on “Binding Properties” tab:

InboundOperationType you can choose TypedPolling like me, or choose what you like, but TypedPolling is required if you are using Strongly Typed Procedure.
PolledDataAvailableStatement is a count statement, the adapter will only poll data if this returns more than 1.
PollingIntervalInSeconds is how often you want the adapter to poll data.
PollingStatement is the SELECT query or Stored Procedure you are calling.

Click Ok when finish.

Now you can click the Connect button to connect to det database.

Under “Select Contract type” select Service (Inbound operations). Because this will be in to BizTalk.

Under “Select Category click on “…/”

Under “Available categories and operations” choose TypedPolling if you selected TypedPolling at the Binding Property. If you selected Polling, you need to select Polling here as well.

Click Add button, then choose a Filename prefix (this is optinal), and then click Ok.

Your schema and portBinding file will be added to your project.

If you now deploy your solution, and import the generated binding into BizTalk after deploy.

This port binding file will add a receive port and receive location with Wcf-Custom adapter. If you like you can add a Wcf-Sql binding instead.

I hope this helps you.

Thanks!